rubyによるExcel操縦あれこれ 〜 マクロの取扱い
rubyによるExcel操縦あれこれ 〜 マクロの取扱い
最終更新日： 2012/06/10
Excelのマクロの取扱いについて記します。
文字列｜ファイルによるマクロの組み込み・取り出し、マクロの削除、マクロの一覧情報の取得、マクロの実行などを扱います。
以降で掲げるサンプルスクリプトを私が実行した環境は次のとおり。
- MS-Windows xp | vista
- Excel2002(Office xp版) | Excel2007
- ruby ver 1.8.7
- 使用ライブラリ: 拙作exlap.rb v1.13
macro.zip には、以下で掲げるサンプルスクリプトのほか、exlap.rbも含まれています。別途ダウンロードする必要はありません。
なお、rubyがインストールされていない環境でサンプルスクリプトを実行したい方は、exl.exeをお試し下さい。詳しくは exl.exeの使い方 を参照して下さい。
--------
- ＜はじめに＞
- 1. マクロを利用するための準備
- 2. 文字列を媒介とするマクロの組み込みと取り出し
- 3. 組み込まれているコンポーネントの情報を取得する
- (1) クラスモジュールの組み込み
- (2) コンポーネントに関する情報の取得(macro_list, macro_name)
- (3) 別のワークブックへのマクロのコピーおよびマクロの削除(macro_remove)
- 4. ファイルを媒介とするマクロの書き出しと読み込み
- 5. マクロの取扱いに関する覚え書き
＜はじめに＞
Excelでマクロを扱う場合の仕組みとして、まずプロジェクト(VBProject)というのがあり、その中に複数のコンポーネント(VBComponent)が含まれています。そして、コンポーネントの本体(ソースコード)を見ると、「Sub Macro1 …… End Sub」などのマクロ(プロシージャ)が書かれています。一つのコンポーネントに複数のプロシージャが書かれていることもしばしばです。
コンポーネントには、標準モジュールとかクラスモジュールなどの種類(Type)と、「Module1」とか「Class1」などの名前(Name)の属性があります。これら属性の値は、component.Type(数値)、component.Name(文字列)で知ることができます。
exlap.rb(v1.13)のメソッドが対象とするのは、コンポーネントのタイプ 1:標準モジュール、2:クラスモジュール、3:ユーザーフォームの３種類です。他に 11:ActiveXデザイナ、100:ドキュメントモジュールがありますが、この２種類は対象としません。
以下の解説でも、タイプ1〜3を対象にします。
ところで、ExcelをGUI操作していると、メニューの中に「新しいマクロの記録」があります。これを選んでからキーやマウスで操作して「記録終了」を選ぶと、その間の操作がマクロとして記録されます。
この「マクロの記録」と「記録終了」の間に、rubyスクリプトによるExcel操縦を挿入してやれば、その操縦の内容がマクロとして記録されるのでは？と思われるかもしれません。
しかし、残念ながら、ほとんどの場合は記録されません。これについては最後の方で少し触れます。
--------
1. マクロを利用するための準備
rubyスクリプトでマクロの組み込みや取り出しを行う場合、Excelのマクロのセキュリティを調整する必要があります。
ExcelをGUI操作して、次の設定変更を行います。具体的な操作方法は、Excelのバージョンによって違うのでここでは言及しません。申し訳ありませんが他のサイトなどを参考にして下さい。
a. 「Visual Basic プロジェクトへのアクセスを信頼する」をonにする。
この調整は、rubyスクリプトでマクロを扱う場合、必須です。
b. マクロのセキュリティレベルの調整
rubyスクリプトでExcelを操縦するだけなら、マクロのセキュリティレベルを気にする必要はないと思います。
ただ、rubyスクリプトでワークブックにマクロを組み込んで、後でそのワークブックを開いてGUI操作中にマクロを実行しようとする場合は、マクロのセキュリティレベルをゆるめる必要があります。
Excel2002なら、セキュリティレベルを「中」か「低」にします。
Excel2007では、「警告を表示してすべてのマクロを無効にする」か「すべてのマクロを有効にする」にします。
--------
2. 文字列を媒介とするマクロの組み込みと取り出し
(1) マクロの組み込み(macro_add)
rubyスクリプトで、変数 str にマクロのソースコードが記録されているとします。つまり "Sub Macro1 …… End Sub" が str にセットされているものとします。
このとき、wbがワークブックを指しているとすれば、
wb.macro_add(str)
と記述することで、マクロを組み込むことができます。標準モジュールの「Module1」という名前のコンポーネントとして組み込まれます。「Module1」が既に存在するのであれば「Module2」になります。
というような部分的な説明では分かりにくいと思うので、サンプルを示します。A1欄に「test」と書き込むだけのマクロ Macro1 を組み込みます。
組み込むだけでは何なので、それを実行してからワークブックを保存して終了することにします。保存されたワークブックのSheet1のA1欄には「test」が書き込まれているはずです。
マクロの実行は、「wb.run("Macro1")」で行います。
−−−− mcr01.rb ここから
#! ruby -Ks
# マクロの組み込みと実行
require "exlap"
str = DATA.read # マクロソースコードをstrに代入
filename = "mcr01.xls"
Exlap.new(filename) do |wb|
wb.macro_add(str) # マクロの組込み
ss = wb.fes # 空のワークシートを選択
ss.Activate
wb.run("Macro1") # Macro1を実行
wb.save
end
__END__
Sub Macro1()
Cells(1,1).Value = "test"
End Sub
−−−− mcr01.rb ここまで
「str = DATA.read」は、スクリプトの最後の方にある「__END__」よりも後ろを読み込んで、それを文字列 str に代入するものです。マクロのソースコードの代入です。
変数 str への代入は、もちろん「str = "Sub Macro1 ……"」のようにしてもいいわけですが、後で長めのVBAプログラムも出てくるので、rubyスクリプトと交じってごちゃごちゃしないよう、VBAプログラムを最後の方に置くようにしました。
「wb.macro_add(str)」とした場合、コンポーネント(今回は標準モジュール)の名前をどうするかは、Excelにお任せになります。
どんな名前が割り当てられたかを知りたい場合は、
component = wb.macro_add(str)
として、component.Name の値を確認します。component.Type の方は、数値が代入されているはずです。1なら標準モジュール、2であればクラスモジュールなどです。
コンポーネントの名前を指定したい時は、
wb.macro_add(str, 1, "Module5")
のようにします。第２引数がタイプ、第３引数が名前です。
マクロがテキストファイルに書かれている場合は、
wb.macro_add("macro.txt")
のようにして、引数にファイル名を与えることができます。
第１引数の文字列が、キャリッジリターン(CR)も改行(LF)も含んでおらず、かつ、それがファイル名として有効な場合(実際に該当のファイルが存在する場合)、その文字列をファイル名とみなして、その中身をマクロとして組み込みます。
(2) マクロのソースコードの取り出し(macro_code)
ワークブックに組み込まれているマクロのソースコードを取り出して、文字列に代入するには、
str = wb.macro_code()
のようにします。この場合、引数を省略しているので、タイプ1〜3の全コンポーネントのソースコードを取得します。
str = wb.macro_code(1, "Module1")
とすれば、標準モジュールで「Module1」というコンポーネントのソースコードを取り出します。
str = wb.macro_code(1)
のように、タイプのみ指定して、名前を省略すると、タイプ1(標準モジュール)である全コンポーネントのソースコードを取り出します。
取り出したソースコード(文字列)には、要所要所に
' <<type:Standard, name:Module1>>
のようなコメント行が挿入されています。これは、コンポーネントのタイプと名前をコメントとして挿入したものです。オリジナルのソースコードにはありません。
サンプルの mcr02.rb は、二つのマクロを組み込んで、後の方でそれを文字列として取り出し、ファイル mcr02.txt に書き出しています。
一つ目のマクロ MacroSetValue() は、ワークシートのA列に番号、B列に果物の名前を書き出します。
MacroSort() は、ワークシートの内容を降順に並べ替えるものです。
全半は mcr01.rb とほとんど同じですが、参考まで下に掲げます。
−−−− mcr02.rb ここから
#! ruby -Ks
# マクロソースコードの取り出し
require "exlap"
str = DATA.read # マクロ記述をstrに代入
filename = "mcr02.xls"
Exlap.new(filename) do |wb|
wb.macro_add(str) # マクロの組込み
ss = wb.fes # 空のワークシートを選択
ss.Activate
wb.run("MacroSetValue")
wb.run("MacroSort")
wb.save
str2 = wb.macro_code()
File.open("mcr02.txt", "w") {|ff| ff.write str2}
end
__END__
Sub MacroSetValue()
Dim i As Integer
Dim fruit As Variant
fruit = Array("りんご", "メロン", "ぶどう", "パニナップル")
For i = LBound(fruit) To UBound(fruit)
Cells(i+1, 1).Value = i+1
Cells(i+1, 2).Value = fruit(i)
Next i
End Sub
Sub MacroSort()
Application.CommandBars.FindControl(ID:=211).Execute ' 降順で並べ替え
End Sub
−−−− mcr02.rb ここまで
「Application.CommandBars.FindControl……」は、ExcelをGUI操作している最中に、メニューの「降順で並べ替え」をクリックすることに相当します。これについては後述します。
--------
3. 組み込まれているコンポーネントの情報を取得する
この項では、コンポーネントのタイプ、名前、ソースコード、そのソースコードに書かれているプロシージャ名の一覧を取得する方法を取り上げます。プロシージャ名は、これまでのサンプルでいうと Macro1, MacroSetValue, MacroSort といった名前のことです。
情報取得の前に、標準モジュールしか組み込まれていないのはつまらないので、クラスモジュールも組み込むことにします。そのやり方から始めます。
(1) クラスモジュールの組み込み
サンプルの mcr03.rb では、Personsというクラスを設定しています。ごく簡単なクラスで、メンバー変数を持たず、二つのメソッドがあるだけです。わざわざクラスにしなくても、そのまま標準モジュールにして差し支えない内容ですが、あえてクラスにしてみます。
このクラスの目的は、生年月日を与えると、その年齢(生年月日から今日までの年数)、月齢(今日までの月数)、日齢(今日までの日数)をワークシートに書き出すというものです。
メソッド Title(row) は、ワークシートの指定行(row)に、氏名、生年月日、年齢、月齢、日齢という５つの見出しを出力します。
メソッド Contents(row, name, birthday) は、指定行(row)に、引数として与えられた氏名と生年月日を書き出し、加えて年齢、月齢、日齢を算出するための関数記述を書き込みます。
rubyでクラスを設定する時は、「class Persons …… end」のように書きますが、VBAでクラスを設ける場合は、ソースコード中に「Persons」というクラス名を書きません。
「wb.macro_add(str, 2, "Persons")」のようにして、コンポーネントを組み込む際に、クラスモジュールの名前「Persons」を指定します。すると、これがクラスの名前になります。
mcr03.rb では、クラス Persons をテストするためのマクロ TestPersons() を標準モジュールとして組み込んでいます。
この TestPersons を実行すると、ワークシートに氏名や生年月日等の見出し、そして、３人分のデータが書き出されます。
この項の本旨はコンポーネントの情報を取得することですが、とりあえずそれは横に置いておいて、mcr03.rb では、クラスモジュールと標準モジュールの組み込み、およびその実行を行っています。
ここで作成されるワークブック mcr03.xls は、以降の mcr04.rb〜mcr06.rb で使うので、消去せずに残しておいて下さい。
以下、mcr03.rb を掲げます。
−−−− mcr03.rb ここから
#! ruby -Ks
# クラスモジュールの組み込みと実行
require "exlap"
cls_str, mdl_str = DATA.read.split(/\n##\n/)
filename = "mcr03.xls"
Exlap.new(filename) do |wb|
wb.macro_add(cls_str, 2, "Persons") # クラスモジュールの組み込み
wb.macro_add(mdl_str) # 標準モジュールの組み込み
ss = wb.fes # 空のワークシートを選択
ss.Activate
wb.run("TestPersons")
ss.range_autofit # 行の高さ・列の幅の自動調整
wb.save
end
__END__
' Class Module: Persons
Sub Title(row As Integer)
Range(Cells(row,1), Cells(row,5)).Value = Array("氏名", "生年月日", _
"年齢", "月齢", "日齢")
End Sub
Sub Contents(row As Integer, name As String, birthday As String)
Cells(row,1).Value = name
Cells(row,2).Value = birthday
Cells(row,3).Formula = "=DATEDIF(RC[-1],TODAY(),""Y"")"
Cells(row,4).Formula = "=DATEDIF(RC[-2],TODAY(),""M"")"
Cells(row,5).Formula = "=DATEDIF(RC[-3],TODAY(),""D"")"
End Sub
##
' standard module
Sub TestPersons()
Dim psn As Persons
Set psn = New Persons
psn.Title 1
psn.Contents 2, "加藤", "1970/01/01"
psn.Contents 3, "鈴木", "1980/02/02"
psn.Contents 4, "戸山", "1990/03/03"
End Sub
−−−− mcr03.rb ここまで
VBAノソースコードは、「##」という２文字からなる行で区切られています。それより前がクラスモジュール、それより後が標準モジュールです。
rubyスクリプトの先頭に近いところで
cls_str, mdl_str = DATA.read.split(/\n##\n/)
としているのは、「##」を区切りとして、cls_strにクラスモジュールのソースコードを、mdl_strに標準モジュールのソースコードを代入するためのものです。
VBAのソースコードに出てくる TODAY() は、今日の日付を得る関数です。
DATEDIF() は、第１引数と第２引数の日付の年数差、月数差、日数差等を求めるための関数です。
(2) コンポーネントに関する情報の取得(macro_list, macro_name)
コンポーネントのソースコードを macro_code() で得られることは先述したとおりです。
その他、ワークブックに組み込まれているコンポーネントのタイプと名前の組みを得る macro_list() があります。このメソッドに対する引数(type, name)の与え方は、macro_code() と同様です。
戻り値は [[type1, name1], [type2, name2], ……] のような配列です。
全コンポーネントの一覧を出力するには次のようにします。
mlist = wb.macro_list()
mlist.each do |type, name|
printf("type:%d name:%s\n", type, name)
end
なお、macro_list2() を用いると、該当のコンポーネントオブジェクトを配列に入れて返します。使い方は macro_list() と同じです。
次に、プロシージャ名の取得ですが、これは macro_name() で行います。
引数を与えなければ、タイプ1〜3の全コンポーネントのプロシージャ名を取得します。
「wb.macro_name(1, "Module1")」のようにすると、標準モジュール「Module1」に含まれるプロシージャの名前群を取得します。
戻り値が少々ややこしくて、ハッシュが返されます。ハッシュの key は、コンポーネントを指す [type, name] 形式の配列です。具体的には「[1, "Module1"]」などです。これが key です。
ハッシュの value の方は、プロシージャ名を要素とする配列で、例えば「["Macro1", "Macro2"]」のような形になります。
プロシージャ名の一覧を出力するには、例えば次のようにします。
hs = wb.macro_name()
hs.each do |key, proc_names|
type, name = key
printf("%d %s: ", type, name)
puts proc_names.join(", ")
end
サンプル mcr04.rb は、上の macro_list, macro_name を mcr03.xls に対して適用しています。
また、各コンポーネントのソースコードは、macro_code() で取得して、それをテキストファイルに書き出しています。
以下、mcr04.rb を掲げます。
−−−− mcr04.rb ここから
#! ruby -Ks
# コンポーネントの情報を取得
require "exlap"
filename = "mcr03.xls"
Exlap.new(filename) do |wb|
# コンポーネントの type, name を出力
puts "コンポーネント一覧"
mlist = wb.macro_list()
mlist.each do |type, name|
printf("type:%d name:%s\n", type, name)
end
printf("\n")
# プロシージャ名一覧の出力
puts "プロシージャ名一覧"
hs = wb.macro_name
hs.each do |key, proc_names|
type, name = key
printf("%d %s: ", type, name)
puts proc_names.join(", ")
end
# ソースコードをファイルに出力
mlist.each do |type, name|
str = wb.macro_code(type, name)
fname = "#{name}_code.txt"
File.open(fname, "w") {|ff| ff.write str}
end
end
−−−− mcr04.rb ここまで
(3) 別のワークブックへのマクロのコピーおよびマクロの削除(macro_remove)
各コンポーネントの情報を取得できるようになったので、それらを別のワークブックにコピーしてみます。
マクロを別のワークブックにコピーすることについては、後述の macro_export, macro_import を使う方が確実です。これらはファイルを経由してコピーする方法です。
一方、ここで紹介するのは、ファイルを介さずにコピーする方法です。参考まで記してみます。mcr05.rbがそのサンプルです。
二つのワークブック mcr03.xls と mcr05.xls を同時に開いて、前者のコンポーネント情報を取得し、それに基づいて後者にマクロを組み込みます。
macro_remove(マクロの削除)を別にすれば、これまで紹介したメソッドだけで行っていますので、説明を付け加える必要はないと思います。
以下、mcr05.rbを掲げます。
−−−− mcr05.rb ここから
#! ruby -Ks
# ファイルを介さずにマクロを別のワークブックにコピー
require "exlap"
xl = Exlap.new
xl.opens_once("mcr03.xls", "mcr05.xls") do |wb1, wb2|
wb2.macro_remove # 念のためwb2のマクロを削除
mlist = wb1.macro_list
mlist.each do |type, name|
str = wb1.macro_code(type, name)
wb2.macro_add(str, type, name)
end
ss = wb2.fes # 空のワークシートを選択
ss.Activate
wb2.run("TestPersons")
ss.range_autofit # 行の高さ・列の幅の自動調整
wb2.save
end
xl.quit
−−−− mcr05.rb ここまで
「wb2.macro_remove」は、wb2に組み込まれているタイプ1〜3の全コンポーネントを削除します。wb2にマクロが何も組み込まれていなければ、結果として何も行われません。
「wb.macro_remove(1, "Module1")」のように引数を指定すれば、標準モジュールの「Module1」だけを削除します。
「wb.macro_remove(1)」のようにタイプだけ指定すれば、該当のタイプ1(標準モジュール)を総て削除します。
「wb.macro_remove(nil, "Module5")」とすれば、タイプを問わず、「Module5」という名前のコンポーネントを削除します。
［補足］
mcr05.rb で採用したマクロコピーの方法は、要するにソースコードをコピーしているだけです。ソースコード以外の情報も必要になるケースでは適切なコピーが行われないことになります。
例えば、コンポーネントのタイプ3(ユーザーフォーム)には、多くの場合、ソースコード以外にも付随情報があります。なので、mcr05.rbのやり方でコピーすることはできません。
標準モジュールとクラスモジュールについては概ね大丈夫だと思いますが、確実にコピーするなら、次項の macro_export, macro_import を用いるのがいいと思います。
--------
4. ファイルを媒介とするマクロの書き出しと読み込み
ここでは、マクロをファイルとして書き出す方法と、そのファイルを読み込んでマクロを組み込む方法について記します。
ここでいうファイルは、単にマクロのソースコードが書かれているものではなく、コンポーネントの各種Attributeが一緒に記録されているファイルです。
(1) マクロの書き出し(macro_export)
「wb.macro_export()」とすれば、タイプ1〜3のコンポーネント総てをファイルとして書き出します。
そのとき、標準モジュールの拡張子は ".bas"、クラスモジュールは ".cls"、ユーザーフォームは ".frm" になります。
ファイル名本体(拡張子を除く)は、コンポーネントの名前がそのまま用いられます。つまり「Module1」とか「Class1」などになります。ファイル名は、「Module1.bas」 「Class1.cls」のようになります。
ファイルが書き出されるフォルダは、ワークブックが存在するのと同じフォルダです。
「wb.macro_export(1, "Module1")」とすれば、「Module1.bas」だけが書き出されます。
macro_export() の戻り値は、出力成功コンポーネントのタイプと名前の組みが複数含まれる配列 type1, name1], [type2, name2 などになります。
何も出力しなかった時は、空配列 [] を返します。
rubyスクリプト中で、漏れなくexportが行われたかどうかをチェックするには、例えば次のような方法があります。
full_list = wb.macro_list
export_list = wb.macro_export
ary = full_list - export_list
上のようにして、ary が空配列 [] でない時は、exportに失敗したコンポーネントがあることになります。
ワークブック mcr03.xls の全コンポーネントを書き出すrubyスクリプト mcr06a.rb を下に掲げます。ごく簡単なものです。
これを実行すると、Module1.bas, Persons.cls の二つのファイルが書き出されます。
−−−− mcr06a.rb ここから
#! ruby -Ks
# マクロファイルの書き出し
require "exlap"
Exlap.new("mcr03.xls") do |wb|
wb.macro_export
end
−−−− mcr06a.rb ここまで
［補足］
macro_export()の第３引数に true を与えると、書き出しファイル名にワークブック名を付加します。「test.xls!Module1.bas」のようなファイル名に書き出します。
すべてのコンポーネントをワークブック名つきで書き出すには
wb.macro_export(nil, nil, true)
とします。
(2) マクロの読み込み(macro_import)
「wb.macro_import("Module1.bas")」とすれば、ファイル「Module1.bas」を読み込みます。
「wb.macro_import("Module1.bas", true)」のように、第２引数として true または false を指定できます。これは、ワークブック内に既に同名のコンポーネントがあるかどうかをチェックするかしないかのフラグです。いわば重複チェックの有無です。
第２引数が true だと、重複チェックを行います。指定したファイルが「Module1.bas」のとき、拡張子を除いた「Module1」という名前のコンポーネントがワークブック中に既に存在するかを確認し、存在する場合は読込みを行いません。
false であれば、重複チェックは行わず、ファイルを読み込みます。その場合のコンポーネントの名前は、Excelが自動的に調整することになります。
第２引数のデフォルト値は false です。
先述の macro_export で書き出したファイルを、別のワークブックにおいて macro_import で読み込めば、そのワークブックに同じマクロを組み込むことができます。
macro_import() の戻り値は、読み込みによって生成されたコンポーネントのタイプと名前 [type, name] となります。
読み込みが行われなかった時は nil を返します。
先の mcr06a.rb で書き出された Module1.bas, Persons.cls を読み込むサンプル mcr06b.rb を下に掲げます。ワークブック mcr06.xls に読み込みます。
マクロがちゃんと組み込まれているのを確認する意味で、マクロ TestPersons を実行しています。
−−−− mcr06b.rb ここから
#! ruby -Ks
# マクロファイルの読み込み
require "exlap"
Exlap.new("mcr06.xls") do |wb|
wb.macro_remove # 念のためマクロを削除
Dir.glob("*.bas\0*.cls\0*.frm") do |filename|
wb.macro_import(filename)
end
# 読み込んだマクロを試しに実行してみる
ss = wb.fes # 空のワークシートを選択
ss.Activate
wb.run("TestPersons")
ss.range_autofit # 行の高さ・列の幅の自動調整
wb.save
end
−−−− mcr06b.rb ここまで
「Dir.glob("*.bas\0*.cls\0*.frm") ……」は、３種類のワイルドカード指定「*.bas」 「*.cls」 「*.frm」に該当するファイルの名前を検出するものです。"\0" は、ワイルドカードを区切るための区切り文字です。
「*.frm」に該当するファイルはみつからないと思いますが、「*.bas」と「*.cls」は一つづつ検出されるはずです。その検出されたファイルを macro_import() で読み込んでいます。
正しく読み込みが行われていれば、mcr06.xls でも TestPersons() を実行できるはずなので、run() でそれを実行しています。
以上、exlap.rbで用意した「マクロを取り扱うためのメソッド」についての説明は一通り終了です。
各メソッドの仕様について説明しきれていない面もありますが、それらについてはexlapの本来の解説 exlapの使い方 を参照して下さい。
--------
5. マクロの取扱いに関する覚え書き
Excelのマクロに関連して、ちょっとした覚え書きを記しておきます。
(1) 異なるモジュールに同名のマクロが登録されている時の扱い方
Module1 と Module2 の両方に Macro1 という名前のマクロが登録されていたとします。このとき、その二つのマクロを実行するには、それぞれ次のようにします。
wb.run("Module1.Macro1")
wb.run("Module2.Macro1")
モジュール名の後に半角ピリオドを置いて、その後にマクロ名を記します。
全モジュールを見わたして、同名のマクロが他にないのであれば、わざわざモジュール名を付けなくて大丈夫です。
ここには掲げませんが、サンプルとして mcr07.rb を同梱しておきます。内容は mcr02.rb と同じで、果物のリストをワークシートに書き出し、それを降順に並べ替えます。
(2) マクロのショートカットキー設定(MacroOptions)
組み込んだマクロにショートカットキーを割り当てるような場合、VBAでいうところの「Application.MacroOptions()」を用います。
ショートカットキーを割り当てると、ExcelをGUI操作している最中に、例えば、コントロールキーを押しながらuキーをたたくことによって、マクロを実行できるようになります。
rubyスクリプトでこの MacroOptions を実行するサンプルを下に掲げます。２番目のサンプル mcr02.rb を少し書き換えて、二つのマクロに対してショートカットキーを割り当てます。
果物を番号付きでワークシートに書き出すマクロ(MacroSetValue)、それを降順に並べ替えるマクロ(MacroSort)の二つに対し、「コントロール+シフト+u」と「コントロール+シフト+y」のショートカットキーを割り当てます。
後でExcelを起動してGUI操作している時に、キーボードから「コントロール+シフト+u」を入力すると、MacroSetValueが実行されます。
以下、サンプル mcr08.rb を掲げます。
−−−− mcr08.rb ここから
#! ruby -Ks
# マクロにショートカットキーを設定する
require "exlap"
str = DATA.read # マクロ記述をstrに代入
filename = "mcr08.xls"
Exlap.new(filename) do |wb|
wb.macro_add(str) # マクロの組込み
app = wb.app.obj
app.MacroOptions({'Macro'=>"MacroSetValue", 'ShortcutKey'=>"U"})
app.MacroOptions({'Macro'=>"MacroSort", 'ShortcutKey'=>"Y"})
wb.save
end
__END__
Sub MacroSetValue()
Dim i As Integer
Dim fruit As Variant
fruit = Array("りんご", "メロン", "ぶどう", "パニナップル")
For i = LBound(fruit) To UBound(fruit)
Cells(i+1, 1).Value = i+1
Cells(i+1, 2).Value = fruit(i)
Next i
End Sub
Sub MacroSort()
Application.CommandBars.FindControl(ID:=211).Execute ' 降順で並べ替え
End Sub
−−−− mcr08.rb ここまで
ショートカットキーを指定する時は、半角アルファベットで指定します。サンプルでは "U" と "Y" のように大文字にしました。
このように大文字にすると「コントロール+シフト+u」のショートカットキーが割り当てられ、小文字にすると「コントロール+u」になります。オルトキーとの組合せを指定することはできないようです。「Application.OnKey()」を用いると、オルトキーとの組合せなども可能になるようですが、ここではパスします。
ショートカットキーを設定してあるマクロに対して、そのショートカットキーを解除する場合は、
app.MacroOptions({'Macro'=>"MacroSetValue", 'ShortcutKey'=>""})
のように、空文字列 "" を指定すれば大丈夫のようです。
(3) Excel2007以降のxlsmファイル
これまでのサンプルは、ワークブックの拡張子が ".xls" のものを扱ってきました。
一方、Excel2007以降では、マクロ付きのワークブックとして、拡張子 ".xlsm" を扱うことができます。
もし Excel2007 以降を動かせる環境にあるなら、これまでのサンプルの mcr01.xls などを mcr01.xlsm のように変更しても、そのまま問題なく動きます。
Excelのバージョンが判然としない場合に、2003までならxls、2007以降の時はxlsmにしたいということであれば、例えば次のようにします。
xl = Exlap.new
filename = (xl.excel_version < 12.0) ? "test.xls" : "test.xlsm"
xl.opens(filename) do |wb|
…………
end
xl.quit
参考まで、mcr01.rb を上の流儀で書き換えたものを mcr09.rb として同梱しておきます。
(4) Excelのメニューおよび「新しいマクロの記録」と「記録終了」
サンプルスクリプト mcr02.rb のVBAソースコード中に
Application.CommandBars.FindControl(ID:=211).Execute ' 降順で並べ替え
というのを書きました。これは、ExcelをGUI操作している最中に、メニューの「降順で並べ替え」をクリックすることに相当します。
ここで出てくる「ID:=211」の211が「降順で並べ替え」であることが、どうやって確認できるのかというと、同梱の cmdbars.rb を実行することで確認できます。
このサンプルを実行すると、CommandBarsに関する情報の一覧が CommandBars.xls, CommandBars.txt として書き出されます。内容は両方とも同じです。後者はタブ区切りテキストファイルです。
これを見ると、211が「降順で並べ替え(&C)」となっています。ちなみに 210 は「昇順で並べ替え(&A)」です。
クリックしたいメニューのID番号が分かれば、あとはサンプルのような記述でそれを実行できます。
VBAのソースコードでなく rubyスクリプト中でこれを記述する場合は、例えば次のようにします。
wb.app.obj.CommandBars.FindControl({'ID'=>211}).Execute
ところで、メニューの「マクロの記録」のIDは 184、「記録終了」は 2186 です。
「マクロの記録」と「記録終了」の間に挟まれたExcelの動きは、マクロとして記録されるはずです。
これを確かめるため、mcr10.rb を実行してみます。参考まで、このサンプルを下に掲げます。
−−−− mcr10.rb ここから
#! ruby -Ks
# 「マクロの記録」と「記録終了」の効力を確かめる
require "exlap"
str = nil
filename = "mcr10.xls"
Exlap.new(filename) do |wb|
app = wb.app.obj
ss = wb.fes
ss.Activate
app.CommandBars.FindControl({'ID'=>184}).Execute # マクロの記録
# ↑ ダイアログが出るのでGUI操作で応答
rng = ss.Range("A1:B3")
rng.Value = [%w(3 rat), %w(1 cat), %w(2 dog)]
app.CommandBars.FindControl({'ID'=>210}).Execute # 昇順で並べ替え
app.CommandBars.FindControl({'ID'=>2186}).Execute # 記録終了
str = wb.macro_code
wb.save
end
File.open("mcr10.txt", "w") {|ff| ff.print str}
−−−− mcr10.rb ここまで
このrubyスクリプトを実行すると、途中でマクロ名等を指定するためのダイアログが出ます。キー｜マウス操作でそれに応答すれば、やがてExcelが終了し、rubyの実行も完了します。
組み込まれたマクロのソースコードは、mcr10.txt というテキストファイルに書き出されているはずです。
この中身を見ると、セルへの cat, dog, rat の書き込み部分は見あたらず、並べ替えのSort関連の記述のみが書かれています。
おそらく、マクロとして記録されるのは、キー｜マウス操作と同じ効力を持つものだけなのだろうと推測します。
「CommandBars.FindControl(……).Execute」は、キー｜マウスでメニューをクリックしたのと同じ効力を持つため、たまたまマクロとして記録された、ということだろうと思います。
残念ながら、rubyスクリプトで書かれたExcel操縦部分を「マクロの記録」でVBAソースコードにすることはできないようです。
なお、実用的な意味はありませんが、Application.RecordMacro() を用いて、VBAソースコードをマクロの記録に挿入してやるサンプルを mcr10b.rb として同梱しておきます。
前述のサンプルでは、cat, dog, rat のセルへの書き込みがマクロとして記録されませんでしたが、今回は、無理矢理？記録の中に挿入してやります。VBAのソースコードを挿入するだけなので rubyからみてメリットはありません。
mcr10.rb とは少し違うスタイルにしてあります。よかったら参考にして下さい。
(5) ユーザーフォームの例
コンポーネントのタイプ1〜3を対象にすると言いながら、タイプ3(ユーザーフォーム)については触れないままでした。
遅ればせながら、ここでサンプルとして mcr11.rb を掲げておきます。
SampleFormという名前のコンポーネント(ユーザーフォーム)を設けて、それにテキストボックス、リストボックス、コマンドボタンの三つを貼り付けます。そして、そのユーザーフォームをテストするための標準モジュールを組み込みます。マクロ名は TestSampleForm です。最後に、このテストのためのマクロにショートカットキーを割り当てます。これら一連の作業は rubyスクリプト側で行います。
VBAソースコードの方では、Finishボタンがおされた時の終了処理のほか、テキストボックス・リストボックス・コマンドボタンのプロパティ設定等を行っています。
mcr11.rbを実行すると mcr11.xls が作られます。これをExcelで開いてGUI操作します。
TestSampleForm というマクロが組み込まれているので、それを実行します。ショートカットキー「コントロール+シフト+u」を入力すると、そのマクロが実行されるはずです。
テキストボックスとリストボックスでそれぞれ適当に入力または選択し、Finishというコマンドボタンをクリックすると、入力・選択したものがワークシートのA1欄とB1欄に書き込まれます。
以下、mcr11.rb です。
−−−− mcr11.rb ここから
#! ruby -Ks
# the sample for UserForm
require "exlap"
uf_str, mdl_str = DATA.read.split(/\n##\n/)
filename = "mcr11.xls"
Exlap.new(filename) do |wb|
uf = wb.macro_add(uf_str, 3, "SampleForm") # add UserForm component
uf.Designer.Controls.Add("Forms.TextBox.1") # add TextBox
uf.Designer.Controls.Add("Forms.ListBox.1") # add ListBox
uf.Designer.Controls.Add("Forms.CommandButton.1") # add CommandButton
wb.macro_add(mdl_str) # add standard module component
wb.app.obj.MacroOptions('Macro'=>"TestSampleForm", 'ShortcutKey'=>"U")
wb.save
end
__END__
' UserForm module
Private Sub CommandButton1_Click()
Range("A1").Value = Me.TextBox1.Value
Range("B1").Value = Me.ListBox1.Value
Unload Me
End Sub
Private Sub UserForm_Initialize()
With TextBox1
.AutoSize = True
End With
With ListBox1
.AddItem "cat"
.AddItem "dog"
.AddItem "fox"
End With
With CommandButton1
.Caption = "Finish"
End With
End Sub
##
' standard module
Sub TestSampleForm()
SampleForm.Show
End Sub
−−−− mcr11.rb ここまで
上記スクリプトで作成される mcr11.xls に対して macro_export を適用すると(mcr06a.rb参照)、SampleForm.frm の他に SampleForm.frx というファイルも書き出されます。前者はテキストファイル、後者はバイナリーファイルです。
このユーザーフォームに関する二つのファイルを macro_import で読み込む時は、テキストファイルの SampleForm.frm の方を読み込めば大丈夫です。そうすれば、バイナリーの方も付随して読み込まれます。バイナリーファイルが同じフォルダにないと正しく読込みが行われないので注意して下さい。
(6) VBEの非表示
最後に、VBE(Visual Basic Editor)を非表示にする方法を記しておきます。
VBA記述では「Application.VBE.MainWindow.Visible = False」と書きます。
rubyでは(exlap利用時)「wb.app.obj.VBE.MainWindow.Visible = false」です。
--------
マクロの取扱いについて、この辺でおわりにします。
メニューへのマクロの登録その他いろいろな話題に触れないままですが、ご容赦のほど。
〜 以上 〜
Copyright (C) T. Yoshiizumi, 2012 All rights reserved.
Keyword(s):
References: