カテゴリー名： ［VBEの自動操作によるExcelマクロの組込み］
Excelのクラスモジュール、ユーザーフォームを組み込む例を示してみます。
クラスモジュールもユーザーフォームも、それを呼び出すための標準モジュールのマクロが必要です。なので、標準モジュールの組み込みも一緒に行います。
クラスというのは、オブジェクトをプログラミングするための機構です。所属するメンバー変数(プロパティ)と、それに処理を施すメソッドを一体化して扱います。
ユーザーフォームは、ユーザーに入力してもらうための機構です。テキストボックス(文字入力)、リストボックス(選択)、OKボタン、Cancelボタンなどを備えた一種のオブジェクトです。
クラスモジュールの組み込みは、標準モジュールと同様の方法で行いますが、
モジュールの名前を「お任せ」にするのでなく、
ちゃんと付けた方がいい点が異なります。
モジュール名(つまりコンポーネントの名前)がクラス名になります。
今、クラスの定義が Dice.txt に、
そのクラスを利用するためのマクロ(標準モジュール)が DiceTest.txt に
書かれているものとします。
クラスは、さいころ(ダイス)を扱うためのクラスです。説明は後述。
これらクラスモジュール、標準モジュールを組み込むVBScriptプログラムは下のとおりです。
Book1.xlsを生成します。
1Option Explicit 2Dim FSO, BookPath 3Dim EXLapp, WBobj, CPobj1, CPobj2 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 CPobj1 = WBobj.VBProject.VBComponents.Add(2) ' クラスモジュール追加 12CPobj1.Name = "Dice" ' クラスモジュールの名前設定 13CPobj1.CodeModule.AddFromFile(FSO.GetAbsolutePathName("Dice.txt")) 14Set CPobj2 = WBobj.VBProject.VBComponents.Add(1) ' 標準モジュール追加 15CPobj2.CodeModule.AddFromFile(FSO.GetAbsolutePathName("DiceTest.txt")) 16EXLapp.MacroOptions "DiceTest",,,,True,"j" ' マクロにShortcutKey割り当て 17WBobj.SaveAs BookPath, -4143 18EXLapp.quit
標準モジュールの DiceTest という名前のマクロに、
ショートカットキー Control + j を割り当てています。
これを実行すると、二つのさいころがふられて、
その目の数がアクティブセルとその右隣に書き込まれます。
もし二つのさいころの目が一致していれば、
「Matching! = 4」などのようにメッセージが出ます。
Diceというクラスには、二つのさいころが所属しています。
それぞれの目の数を示すクラス変数(プロパティ)の Roll1, Roll2 があります。
メソッドとしては、さいころをふる Shootメソッド(乱数発生により実現)、
さいころの目の数をアクティブセルに書き込む PutDataメソッドの二つがあります。
VBAのクラスについて詳述する余裕はないので、マクロコードだけ掲げておきます。
-------- Dice.txt ここから
' Class Module: Dice
Private Roll1 As Integer ' さいころ１の目の数
Private Roll2 As Integer ' さいころ２の目の数
Sub Shoot() ' さいころ２個をふる
Randomize
Roll1 = Int(Rnd*6)+1
Roll2 = Int(Rnd*6)+1
If Roll1 = Roll2 Then
MsgBox "Matching! = " & Roll1
End If
End Sub
Sub PutData() ' アクティブセルに書き込む
Dim rr As Integer
Dim cc As Integer
rr = ActiveCell.Row
cc = ActiveCell.Column
Cells(rr, cc).Value = Roll1
Cells(rr, cc+1).Value = Roll2
End Sub
-------- Dice.txt ここまで
-------- DiceTest.txt ここから
' standard module
Sub DiceTest()
Dim Dc As Dice
Set Dc = New Dice
Dc.Shoot
Dc.PutData
End Sub
-------- DiceTest.txt ここまで
今、ユーザーフォームの定義が SampleForm.txt に、
そのユーザーフォームを利用するためのマクロ(標準モジュール)が SampleFormTest.txt に書かれているものとします。
SampleFormIncorp.vbsを実行すると、ユーザーフォームと標準モジュールが組み込まれた Book1.xls が作られます。これをExcelで開いてGUI操作します。
SampleFormTest という標準モジュールのマクロが組み込まれているので、それを実行します。ショートカットキー Control + j を入力すると、そのマクロが実行されるはずです。
実行すると、ユーザーフォームが出ます。
テキストボックスとリストボックスでそれぞれ適当に入力または選択し、
OKボタンを押すと、入力／選択したものがA1セルとB1セルに書き込まれます。
Cancelボタンを押すと、何もせずに通常の操作状態に戻ります。
1Option Explicit 2Dim FSO, BookPath 3Dim EXLapp, WBobj, CPobj1, CPobj2 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 CPobj1 = WBobj.VBProject.VBComponents.Add(3) ' ユーザーフォーム追加 12CPobj1.Name = "SampleForm" ' ユーザーフォームの名前設定 13CPobj1.CodeModule.AddFromFile(FSO.GetAbsolutePathName("SampleForm.txt")) 14CPobj1.Designer.Controls.Add "Forms.TextBox.1" 15CPobj1.Designer.Controls.Add "Forms.ListBox.1" 16CPobj1.Designer.Controls.Add "Forms.CommandButton.1", "cmdOK", True 17CPobj1.Designer.Controls.Add "Forms.CommandButton.1", "cmdCancel", True 18Set CPobj2 = WBobj.VBProject.VBComponents.Add(1) ' 標準モジュール追加 19CPobj2.CodeModule.AddFromFile(FSO.GetAbsolutePathName("SampleFormTest.txt")) 20EXLapp.MacroOptions "SampleFormTest",,,,True,"j" ' ShortcutKey割り当て 21WBobj.SaveAs BookPath, -4143 22EXLapp.quit
ユーザーフォームの場合、コンポーネントにテキストボックスやボタンなどを貼り付ける作業が必要になります。
それを行っているのが Designer.Controls.Add です。細かな説明は省略。
SampleFormTest.txt, SampleForm.txt を掲げておきます。
-------- SampleFormTest.txt ここから
' standard module
Sub SampleFormTest()
SampleForm.Show
End Sub
-------- SampleFormTest.txt ここまで
-------- SampleForm.txt ここから
' UserForm module
Private Sub cmdOK_Click()
Range("A1").Value = Me.TextBox1.Value
Range("B1").Value = Me.ListBox1.Value
Unload Me
End Sub
Private Sub cmdCancel_Click()
Unload Me
End Sub
Private Sub UserForm_Initialize()
With TextBox1
.Value = ""
.AutoSize = True
End With
With ListBox1
.AddItem "リンゴ"
.AddItem "バナな"
.AddItem "オレンジ"
End With
With cmdOK
.Caption = "OK"
.Default = True ' Enterキー押下も同じ扱いに
End With
With cmdCancel
.Caption = "Cancel"
.Cancel = True ' Escapeキー押下も同じ扱いに
End With
End Sub
-------- SampleForm.txt ここまで
クラスモジュールを組み込むためのプログラムは下のとおり。
Dice.txt, DiceTest.txt は、VBScriptのときと同じものを用います。
1var fso, exlApp, wb, cp1, cp2; 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の新規作成 8cp1 = wb.VBProject.VBComponents.Add(2); // クラスモジュール追加 9cp1.Name = "Dice"; // クラスモジュールの名前設定 10cp1.CodeModule.AddFromFile(fso.GetAbsolutePathName("Dice.txt")); 11cp2 = wb.VBProject.VBComponents.Add(1); // 標準モジュール追加 12cp2.CodeModule.AddFromFile(fso.GetAbsolutePathName("DiceTest.txt")); 13exlApp.MacroOptions("DiceTest", null, null, null, true, "j"); 14wb.SaveAs(bookPath, -4143); // ブックの保存 15exlApp.Quit(); // Excel終了
ユーザーフォームを組み込むためのプログラムは次のとおりです。
1var fso, exlApp, wb, cp1, cp2; 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の新規作成 8cp1 = wb.VBProject.VBComponents.Add(3); // ユーザーフォーム追加 9cp1.Name = "SampleForm"; // ユーザーフォームの名前設定 10cp1.CodeModule.AddFromFile(fso.GetAbsolutePathName("SampleForm.txt")); 11cp1.Designer.Controls.Add("Forms.TextBox.1"); 12cp1.Designer.Controls.Add("Forms.ListBox.1"); 13cp1.Designer.Controls.Add("Forms.CommandButton.1", "cmdOK", true); 14cp1.Designer.Controls.Add("Forms.CommandButton.1", "cmdCancel", true); 15cp2 = wb.VBProject.VBComponents.Add(1); // 標準モジュール追加 16cp2.CodeModule.AddFromFile(fso.GetAbsolutePathName("SampleFormTest.txt")); 17exlApp.MacroOptions("SampleFormTest", null, null, null, true, "j"); 18wb.SaveAs(bookPath, -4143); // ブックの保存 19exlApp.Quit(); // Excel終了
〜 以上 〜