標準モジュールの組み込み

カテゴリー名：　［VBEの自動操作によるExcelマクロの組込み］

2016/08/13

　標準モジュールの組込みについて記します。

　新しいコンポーネントを追加して、そこにマクロを書き込む方法と、
　既に存在しているコンポーネントにマクロを追加書き込みする方法の二つを取り上げます。

　マクロにショートカットキーを割り当てるノウハウにも触れます。

《このページの目次》

　　　　

1.　標準モジュールの新規の組込み

　次の３行からなるテキストファイル MacroFile01.txt があるとします。

Sub Macro1()
    Range("A1").Value = "test"
End Sub

　これは、A1セルに test の４文字を書き込むマクロです。

　このマクロをワークブックに組込み、Book1.xls という名前で保存するVBScriptプログラムを掲げます。

　マクロの名前は Macro1 ですが、それに `Control + j’ というショートカットキーを割り当てます。

　　　　

△　MacroIncorp01.vbs

 1Option Explicit
 2Dim FSO, BookPath
 3Dim EXLapp, WBobj, CPobj
 4
 5Set FSO = CreateObject("Scripting.FileSystemObject")
 6BookPath = FSO.GetAbsolutePathName("Book1.xls")
 7If (FSO.FileExists(BookPath) = True) Then FSO.DeleteFile(BookPath)
 8Set EXLapp = CreateObject("Excel.Application")  ' Excelの起動
 9EXLapp.Visible = True  ' Excelを見える状態に
10Set WBobj = EXLapp.Workbooks.Add()  ' Workbookの新規作成
11Set CPobj = WBobj.VBProject.VBComponents.Add(1)  ' 標準モジュール追加
12CPobj.CodeModule.AddFromFile(FSO.GetAbsolutePathName("MacroFile01.txt"))
13EXLapp.MacroOptions "Macro1",,,,True,"j"  ' マクロにShortcutKey割り当て
14WBobj.SaveAs BookPath, -4143
15EXLapp.quit

　　　　

　SaveAsの第２パラメータの -4143 は、保存するワークブックの形式を指定するものです。

　　　　

(1)　コンポーネントの追加

　WBobj.VBProject.VBComponents.Add(1) というのはコンポーネントを追加するための記述です。

　ワークブックを新規作成しているので、コンポーネントが何もないところに新たに作ります。

　Add(1) の数字はTypeの指定です。1を指定しているので標準モジュールの追加です。

　2ならクラスモジュールが作られます。

　最初の標準モジュール(コンポーネント)なので、その名前は Module1 になります。自動で割り当てられます。

　もし名前を変更したければ次のようにします。

Set CPobj = WBobj.VBProject.VBComponents.Add(1)
CPobj.Name = "Module3"

　こうすると Module1 でなく Module3 という名前になります。

　ただし、既にあるコンポーネントの名前を使おうとするとエラーになります。他のコンポーネントと競合しない名前にします。

　　　　

(2)　マクロファイルの読み込みと絶対パス

　次はマクロの読み込みです。

　CPobj.CodeModule.AddFromFile("MacroFile01.txt") とすれば MacroFile01.txt が読み込まれて、マクロとして登録されます。

　このとき、MacroFile01.txt は、ユーザーのドキュメントフォルダ(マイドキュメント)にないと読み込まれません。

　Excelにとってのカレントフォルダは、通常、ユーザーのドキュメントフォルダです。なので、そのフォルダにファイルがあるものと仮定して探します。

　ファイルがそれ以外のフォルダにある場合は、ドライブ名やフォルダ名をつけてファイルを指定する必要があります。

　しかし、そんなことをするのは面倒なので、Windowsの絶対パスを取得する仕組みを利用します。それが FSO.GetAbsolutePathName です。

　これは、Excelにとってのカレントフォルダではなく、ユーザーにとってのカレントフォルダを基準にして、指定したファイルの絶対パスを返してくれます。

　今、カレントフォルダが D:\usr\test だとすると、
FSO.GetAbsolutePathName("macro.txt") → D:\usr\test\macro.txt となります。

　MacroIncorp01.vbsをexplore(マイコンピュータ)で選択・実行する場合、
macro.txtが同じフォルダにあるのであれば、
FSO.GetAbsolutePathName() を利用しないと　うまくいきません。

　　　　

　なお、VBScript中において AddFromFile の代わりに AddFromString とすれば、
ファイル名でなく文字列を指定できます。
マクロのコードが代入された文字列を指定します。

　　　　

(3)　マクロへのショートカットキー割り当て

　マクロを実行するとき、わざわざExcelのメニューを出してあれこれ選択するのは面倒です。

　そこで、マクロにショートカットキーを割り当てます。サンプルでは Control + j を割り当てています。これを入力するとマクロが実行されます。

　ショートカットキーの割り当ては、Application.MacroOptions で行います。

　その詳細は省きますが、必要になるパラメータは１番、５番、６番です。

　第１パラメータがMacro(マクロ名)です。”Macro1”, “Macro2” などです、

　第５パラメータは HasShortcutKey です。ショートカットキーを割り当てるときは True にします。

　第６パラメータが ShortcutKey です。これを “j” とすれば Control + j になります。j は小文字です。

　大文字にして “J” にすると、Control + Shift + j になります。

　VBScriptでは、パラメータを順序どおりに列べて書かなければなりません。省略するパラメータのところはカンマ記号だけ書きます。

　’EXLapp.MacroOptions “Macro1”,,,,True,”j”` のような書き方です。

　　　　

2.　標準モジュールの追加の組込み

　　先に掲げた MacroIncorp01.vbs を実行すると、Macro1 が組み込まれたBook1.xlsが作成されます。

　このBook1.xlsの Module1 というコンポーネントに、今度は Macro2 という名前のマクロを追加で組込みたいと思ったとします。

　その場合、まずマクロを書いたファイル MacroFile02.txt を用意した上で
MacroIncorp02.vbs を実行することになります。
これは MacroIncorp01.vbs を書き換えたものになりますが、
変更点は以下のとおりです。

　VBScriptプログラムは下のようになります。

　　　　

△　MacroIncorp02.vbs

 1Option Explicit
 2Dim FSO, BookPath
 3Dim EXLapp, WBobj, CPobj
 4
 5Set FSO = CreateObject("Scripting.FileSystemObject")
 6BookPath = FSO.GetAbsolutePathName("Book1.xls")
 7If (FSO.FileExists(BookPath) = False) Then
 8    WScript.Echo "Book1.xlsがありません。"
 9    WScript.Quit
10End If
11Set EXLapp = CreateObject("Excel.Application")  ' Excelの起動
12EXLapp.Visible = True  ' Excelを見える状態に
13Set WBobj = EXLapp.Workbooks.Open(BookPath)
14Set CPobj = WBobj.VBProject.VBComponents("Module1")
15CPobj.CodeModule.AddFromFile(FSO.GetAbsolutePathName("MacroFile02.txt"))
16EXLapp.MacroOptions "Macro2",,,,True,"m"  ' マクロにShortcutKey割り当て
17WBobj.Save  ' ワークブックの上書き保存
18EXLapp.quit

　　　　

　上のプログラムを実行すると、Book1.xlsには Macro1, Macro2 の両法が組み込まれた状態になります。

　Macro2 には Control + m のショートカットキーが割り当てられています。

　MacroFile02.txt の内容は次のとおりです。

Sub Macro2()
    Range("A2").Formula = "=NOW()"
    Range("A2").NumberFormatLocal = "HH:MM:SS"
End Sub

　現在日時をA2セルに書き込みます。それは、17:09:12 のような形式で時刻だけ表示されます。

　　　　

3.　JScript

　MacroIncorp01.vbs, MacroIncorp02.vbs の二つのVBScriptをJScriptで書くとどうなるかを考えてみます。

　書き換える際の主な留意点は次のとおりです。

　そのほか、変数の宣言方法が違うとか、コメントの記述の仕方が違うなど、細かくはいろいろありますが省略して、ともあれプログラムを掲げます。

　　　　

△　MacroIncorp01.js

 1var fso, exlApp, wb, cp;
 2fso = WScript.CreateObject("Scripting.FileSystemObject");
 3var bookPath = fso.GetAbsolutePathName("Book1.xls");
 4if (fso.FileExists(bookPath)) fso.DeleteFile(bookPath);
 5exlApp = WScript.CreateObject("Excel.Application");  // Excelの起動
 6exlApp.Visible = true;  // Excelを見える状態に
 7wb = exlApp.Workbooks.Add();  // Workbookの新規作成
 8cp = wb.VBProject.VBComponents.Add(1);  // 標準モジュール追加
 9cp.CodeModule.AddFromFile(fso.GetAbsolutePathName("MacroFile01.txt"));
10exlApp.MacroOptions("Macro1", null, null, null, true, "j");
11wb.SaveAs(bookPath, -4143);  // ブックの保存
12exlApp.Quit();  // Excel終了

　　　　

△　MacroIncorp02.js

 1var fso, exlApp, wb, cp;
 2fso = WScript.CreateObject("Scripting.FileSystemObject");
 3var bookPath = fso.GetAbsolutePathName("Book1.xls");
 4if (fso.FileExists(bookPath) == false) {
 5    WScript.Echo("Book1.xlsがありません。");
 6    WScript.Quit();
 7}
 8exlApp = WScript.CreateObject("Excel.Application");  // Excelの起動
 9exlApp.Visible = true;  // Excelを見える状態に
10wb = exlApp.Workbooks.Open(bookPath);
11cp = wb.VBProject.VBComponents.Add(1);  // 標準モジュール追加
12cp.CodeModule.AddFromFile(fso.GetAbsolutePathName("MacroFile02.txt"));
13exlApp.MacroOptions("Macro2", null, null, null, true, "m");
14wb.Save();  // ワークブックの上書き保存
15exlApp.Quit();  // Excel終了

　　　　

〜　以上　〜