マクロの追加と削除

ExcelファイルにVisual Basicマクロを組み込んでみる。

Excelファイルがたくさんあって、すべてに共通のマクロを組み込んだり、マクロを修正したらすべてのExcelファイルに 最新のマクロを組み込むといった用途が考えられる。

テンプレート

マクロを組み込むExcelファイルを用意する。このファイルにはマクロは組み込んでない。

Excel2003からは、セキュリティがやや強化されて、デフォルトでは外部からExcelファイル内の「Visual Basicプロジェクト」 にアクセスできなくなった。 Rubyからアクセスさせるには、セキュリティ設定をちょっと弱める必要がある。

  • Excelファイルを開く
  • [ツール/マクロ/セキュリティ]メニューを開く
  • 「信頼のおける発行元」タブを開き、以下のように「VisualBasicプロジェクトへのアクセスを信頼する」にチェックを入れておく

セキュリティ設定

これでこのファイルに限ってセキュリティレベルを下げることができる。

STEP1:マクロの組み込み

Excelファイルにマクロを組み込むときは、Workbook->VBProject->VBComponents の順にアクセスして、VBComponentのコレクションを 得る。コレクションのAddメソッドを使い、VBComponentオブジェクトを手に入れる。

ここからさらに、VBComponent->CodeModule の順にアクセスし、CodeModuleオブジェクトを手に入れる。CodeModuleはコンポーネントに 関連付けられているコードを表す。後はCodeModule#AddFromString()メソッドを使って、マクロを示す文字列を与えてやれば、マクロを組み込む ことができる。

異様に階層が深いが、コードを見るとなんとなくわかる。

  require 'excel_lib'
  
  Excel::Vbext_ct_StdModule = 1  # 標準モジュール
  
  excel = Excel.new
  book = excel.copy_book("./add_macro.xls")
  vb_component = book.VBProject.VBComponents.Add(Excel::Vbext_ct_StdModule)  # (A)
  macro_str = DATA.read                                      # (B)
  vb_component.CodeModule.AddFromString(macro_str)           # (C)
  excel.MacroOptions("Macro"=>"Macro4", "ShortcutKey"=>"p")  # (D)
  excel.MacroOptions("Macro"=>"Macro5", "ShortcutKey"=>"u")
  
  __END__
  Sub Macro4()
      Set range_obj = Selection
      left_pos = range_obj.Left + (range_obj.Width / 2)
      top_pos = range_obj.Top
      ActiveSheet.Shapes.AddShape(msoShapeLineCallout2, left_pos, top_pos, 100#, 60#) _
          .Select
      Selection.Characters.Text = ""
      With Selection.Font
          .Name = "MS Pゴシック"
          .FontStyle = "標準"
          .Size = 9
          .Underline = xlUnderlineStyleNone
          .ColorIndex = xlAutomatic
      End With
  End Sub
  
  Sub Macro5()
      color_index = Selection.Font.ColorIndex
      If color_index = 34 Then
        Selection.Font.ColorIndex = 1
      ElseIf color_index = 1 Then
        Selection.Font.ColorIndex = 3
      ElseIf color_index = 3 Then
        Selection.Font.ColorIndex = 34
      Else
        Selection.Font.ColorIndex = 1
      End If
  End Sub
  

このスクリプトを実行すると、Excelファイルにマクロが組み込まれているのがわかる。 確認するなら、[ツール/マクロ/マクロ]メニューを実行し、Macro4とMacro5が組み込まれているのを見るといいだろう。 [編集]ボタンを押せば、マクロの内容も確認できる。

さらに、ショートカットも定義しておいたので、CTRL+Pで吹き出しを作ってくれる。CTRL+Uでセルの文字色を切り替えてくれる。

組み込んだマクロの確認

解説する。

  • (A)でVBComponentオブジェクトを得ている。VBComponents#Add()の引数でコンポーネントの種類を指定する
  • (B)で__END__の下のマクロ文字列を取り込んでいる
  • (C)でマクロを組み込んでいる
  • (D)でショートカットを定義

STEP2:マクロの削除

逆に、Excelファイルのマクロを削除することもできる。 STEP1で作成したExcelファイルをmacro_file.xlsという名前で保存しておき、このファイルからマクロを取り除いてみよう。

  require 'excel_lib'
  
  excel = Excel.new
  book = excel.open_book("./macro_file.xls")
  vb_components = book.VBProject.VBComponents     # (A)
  vb_components.each {|component|
    if component.name == "Module1"                # (B)
      book.VBProject.VBComponents.Remove(component)
    end
  }
  

解説する。

  • (A)で得ているのは、Excelファイル内VBComponentのコレクション。コレクションなので、eachを使って要素を順番に得ることができる。
  • (B)で名前が"Module1"のVBComponentを選び、これを削除する


戻る