ワークシートの追加 – ExcelVBAの散策とruby

2016/04/02

参考サイト：　 Excel VBA 入門講座 ワークシートの追加

《目次》

• 1.　概要
• 2.　マクロを登録するスクリプト
  • 〈補足〉 VBAとrubyの文字列の記述法の違い
• 3.　win32oleのスクリプト
• 4.　exlapのスクリプト

1.　概要

　ワークシートの追加は Worksheets.Add で行います。これを実行すると、アクティブシートの前に新しいワークシートが挿入されます。そして、その新しいワークシートがアクティブシートになります。

　新規のワークブックでは、Sheet1, Sheet2, Sheet3 の三つのワークシートがあり、Sheet1 がアクティブシートになっています。

　その状態で Worksheets.Add を実行すると、Sheet1 の前に新しいワークシートが作られます。結果、Sheet1 は第２ワークシートになります。

　新しいワークシート(第１ワークシート)の名前は Sheet4 です。自動的にそのワークシート名が割り当てられます。

　Worksheets.Add では四つのオプションを指定することができます。Before, After, Count, Type です。

　Worksheets.Add Before:=Worksheets("Sheet3") とすれば、Sheet3 の前に新しいワークシートが挿入されます。

　Worksheets.Add After:=Worksheets("Sheet3") とすれば、Sheet3 の後に新しいワークシートが追加されます。

　Worksheets.Add Count:=3 とすれば、アクティブシートの前に新しいワークシートが３枚挿入されます。３枚のワークシートの３番目のシートがアクティブシートになります。

　また、Worksheets.Add After:=Worksheets("Sheet3"), Count:=3 とすれば、Sheet3 の後に新しいワークシートが３枚追加されます。つまり Sheet4, Sheet5, Sheet6 が追加されます。そして、Sheet6 がアクティブシートになります。

　オプションのTypeは、ワークシートのタイプを指定するためのものです。通常のワークシートを挿入するのなら省略です。チャートのシートとかダイアログシートを挿入したい時に指定します。

目次に戻る

2.　マクロを登録するスクリプト

　「概要」で述べてきた事柄をVBAマクロとして登録するためのスクリプトを掲げます。

　これまでと同様に、マクロには Control + j というショートカットキーを割り当てます。

　ワークシートを追加したら、その新設されてアクティブシートになったところのA1セルに、実行されたVBAのコードを書き込みます。新設の要因となったコードを記す訳です。

 # encoding: Windows-31J
 require "./exlap"
 
 macro_str = <<'EOS'
 Sub Macro1()
     Worksheets(1).Activate
     Worksheets.Add
     Range("A1").Value = "Worksheets.Add"
     Worksheets.Add Before:=Worksheets("Sheet3")
    Range("A1").Value = "Worksheets.Add Before:=Worksheets(""Sheet3"")"
    Worksheets.Add After:=Worksheets("Sheet3"), Count:=3
    Range("A1").Value = "Worksheets.Add After:=" & _
        "Worksheets(""Sheet3""), Count:=3"
End Sub
EOS

macro_name = "Macro1"
File.delete("Book1.xls")  if test(?e, "Book1.xls")  # 既存のBook1.xlsを削除
Exlap.new("Book1.xls") do |wb|
  wb.macro_add(macro_str)
  wb.Application.MacroOptions("Macro"=>macro_name, "ShortcutKey"=>"j")
  wb.save
end

〈補足〉 VBAとrubyの文字列の記述法の違い

　VBAの文字列表現について補足します。VBAに慣れている人には言わずもがなでしょうが、ruby利用者には注意が必要な点です。

　まず、文字列の連結ですが、VBAでは半角の ‘&’ を用います。rubyでは ‘+’ です。

　VBAでは "abc" & "xyz" のように書きます。rubyだと "abc" + "xyz" です。

　次に、引用符 ‘”’ の使い方です。引用符を文字列中に含めたい場合、VBAでは ""abc"" のように引用符を２重にします。これが "abc" という５文字の文字列になります。

　一方、rubyでは円マーク ‘\’ を特殊文字として用い、"\"abc\"" のように書きます。引用符の中では ‘\”’ が引用符を表します。

目次に戻る

3.　win32oleのスクリプト

　前述のマクロと同じ処理を行うwin32oleのスクリプトは下のようになります。

 # encoding: Windows-31J
 require "win32ole"
 
 def fullpath(filename)
     fso = WIN32OLE.new("Scripting.FileSystemObject")
     return fso.GetAbsolutePathName(filename)
 end
 
 File.delete("Book1.xls")  if test(?e, "Book1.xls")  # 既存のBook1.xlsを削除
app = WIN32OLE.new("Excel.Application")
wb = app.Workbooks.Add()
wb.Worksheets(1).Activate
ws = wb.Worksheets.Add
ws.Range("A1").Value = "Worksheets.Add"
ws = wb.Worksheets.Add("Before"=>wb.Worksheets("Sheet3"))
ws.Range("A1").Value = "Worksheets.Add Before:=Worksheets(\"Sheet3\")"
ws = wb.Worksheets.Add("After"=>wb.Worksheets("Sheet3"), "Count"=>3)
ws.Range("A1").Value = "Worksheets.Add After:=" +
    "Worksheets(\"Sheet3\"), Count:=3"
wb.SaveAs(fullpath("Book1"), -4143)
app.Quit

　ほとんどはVBAマクロと共通ですが、ワークシートを追加する Worksheets.Add の戻り値を変数 ws に代入している点が異なるでしょうか。

　Addメソッドは、戻り値として新設されたワークシートオブジェクトを返します。複数のワークシートを追加した場合は、新設の最後のワークシートオブジェクトを返します。

　VBAマクロでは、Addメソッドの戻り値がアクティブシートになるので、それを受け止めて変数に代入しなくても大丈夫でした。

　しかし、rubyでは「暗黙のうちにアクティブシートを前提にする」といったことが通用しないので、Addメソッドの戻り値を変数に代入する必要があります。

目次に戻る

4.　exlapのスクリプト

　同じ処理を行うexlapのスクリプトを掲げます。

　基本的には win32のスクリプトと同じですが、ちょっとだけexlap独自のメソッドもあります。

 # encoding: Windows-31J
 require "./exlap"
 
 File.delete("Book1.xls")  if test(?e, "Book1.xls")  # 既存のBook1.xlsを削除
 Exlap.new("Book1.xls") do |wb|
     wb.ss(1).Activate
     ws = wb.Worksheets.Add
     ws.Range("A1").Value = "Worksheets.Add"
     ws = wb.insert_sheet("Sheet3")
    ws.Range("A1").Value = "wb.insert_sheet(\"Sheet3\")"
    ws = wb.Worksheets.Add("After"=>wb.Worksheets("Sheet3"), "Count"=>3)
    ws.Range("A1").Value = "Worksheets.Add After:=" +
        "Worksheets(\"Sheet3\"), Count:=3"
    wb.save
end

　wb.insert_sheet("Sheet3") というのは、Sheet3の前にワークシートを１枚挿入するものです。insert_sheet() は exlap独自のメソッドです。引数に 3 などの整数値を与えることもできます。

　この他、ワークシート追加の関連では wb.add_sheet というのもあります。ワークブックの最後に新しいワークシート１枚を追加します。