この方法って見つかりそうで見つからなかったんですよね。ヒントになりそうな情報はインターネットでいくつか見つけることができたんですが、自分が欲しいそのままの形では見つかりませんでした。
ツールをつくっていると、もしかしたら「ExcelVBAで別ブックのユーザーフォームを操作」したい時があるかもしれません。
例えばツールをつくるにしても1から全てつくる場合もあるでしょうが、既にできたツールを流用したツールを作りたい時もあるかもしれません。
というのも、その既存のツールがある程度の規模で業務の中でも便利に使われている、とは言ってももう少し手間が少なくなると助かるなんて時があるかもしれません。
そういった時に最初から全てコーディングするのではなく、既存のツールを補完、もしくは流用できたら工数も手間も少なくなるでしょう。
例えばその方法のひとつとして「ExcelVBAで別ブックのユーザーフォームを操作」できたらどんなに良いでしょうか。今回はこのコードについて備忘録としても自分の勉強としても整理していこうかと思います。
ExcelVBAで別ブックのユーザーフォームを操作するためのコード（操作元ファイル）
結論としては、操作元ファイルの標準モジュールに以下のコードを入れます。
Sub Sample()
Dim p As String
Dim wb As Object
p = “C:\Users\user\Desktop\AAA.xlsm”
Set wb = Workbooks.Open(p)
wb.VBProject.VBComponents(“UserFormA”).Designer.Controls(“textbox1”).Value = “テスト”
MsgBox wb.VBProject.VBComponents(“UserFormA”).Designer.Controls(“CommandButton1”).Caption
Application.Run “AAA.xlsm!フォームを開く”
Application.Run “AAA.xlsm!コマンドボタンクリック”
End Sub
上記のコードを順番に解説していきます。ちなみに自分がこのコードを確認した環境は「Excel2013」です。
まず前提としては、デスクトップ上に「AAA」エクセルファイルと「BBB」エクセルファイルの2つのブックがあるとします。
上記のコードは「BBB」エクセルファイルの標準モジュールに記述しており、「BBB」エクセルファイルから「AAA」エクセルファイルにあるフォームモジュールの「UserFormA」を操作するというものです。
まず、上記のコードの2行目で「AAA」エクセルファイルのパス格納用の変数pを宣言、3行目で「AAA」エクセルファイルを格納するためのオブジェクト変数wbを宣言します。
5行目の「p = “C:\Users\user\Desktop\AAA.xlsm”」で変数pに「AAA」エクセルファイルがある場所のパスを格納します。
6行目の「Set wb = Workbooks.Open(p)」で「AAA」エクセルファイルを開いて、それをオブジェクト変数wbに格納します。
8行目の「wb.VBProject.VBComponents(“UserFormA”).Designer.Controls(“textbox1”).Value = “テスト”」では、まず「AAA」エクセルファイルのVBProjectオブジェクトを操作します。
「VBProject.VBComponents」というコードですが、このコードを使用すると場合によっては「Visual Basic～信頼性に～」といったエラーが出てしまうときがあるかと思います。
このような時は、エクセルを開いた画面からファイルタブを選択、そこから、オプション→セキュリティセンター→セキュリティセンターの設定→マクロの設定まで進みます。
その画面の開発者向けのマクロ設定の項目で「VBA プロジェクト オブジェクト モデルへのアクセスを信頼する(V)」にチェックを入れます。これで「VBProject.VBComponents」を使う場合のエラーは出なくなるかと思います。
このVBProjectオブジェクトは一般的には「VBEを操作する」という風に言われたりしますが、VBEの画面の左上にあるプロジェクトエクスプローラの部分を見るとわかりやすいかと思います。
「VBAProject(～)」とか「フォームモジュール」「シートモジュール」「標準モジュール」などがあるところです。この部分を操作すると考えるとイメージしやすいのではないでしょうか。
「VBProject」はそのエクセルファイルにある各モジュールがまとめられている所で、VBComponentsでその中の構成要素の一つ(例えば標準モジュールとかフォームモジュール)を取得できるというイメージになるかと思います。
次の「Designer」というコードですが、インターネットで調べても詳しく書いてある所をなかなか見つけられなくて、VBEのオブジェクトブラウザで調べた所、次のように表示されました。
Class IHTMLEditDesigner
MSHTML のメンバー
「IHTMLEditDesigner」をインターネットで調べてみてもイマイチよくわからなくて「MSHTML」を調べてみたら「Trident」について多く表示されました。ちなみにWikipediaには以下のように書かれています。
Trident （トライデント）は Internet Explorer に搭載されている HTML レンダリング エンジンの名称で、ライブラリ ファイルの名称から MSHTML とも呼ばれている。
Internet Explorer 4.0 より導入されたもので、Windows 向けではアップデートを重ねているが、Macintosh 向けの Internet Explorer for Mac は次のバージョンの 5.0 で Tasman に置き換えられた。Internet Explorer 7 とそれ以降に含まれるバージョンではウェブ標準に準拠するように開発されている。
Trident はソフトウェア開発者が自分のソフトウェアにウェブ ブラウズ機能を容易に追加できるよう、ソフトウェア コンポーネントとして設計されている。
う～ん・・・。これも難しくてよくわかりませんが、Webページをつくるための道具、といった感じでしょうか。
いろいろと試行錯誤してみましたが、とにかくこの「Designer」という部分がないと他のブックのユーザーフォームのコントロールは操作できませんでした。
この後の「.Controls(“textbox1”).Value = “テスト”」はUserFormAフォームモジュールに設定されているtextbox1というオブジェクト名のテキストボックスに「テスト」という値を入れる、という意味になります。
「Designer」の後に「.Controls(“textbox1”).Value = “テスト”」と繋げないとテキストボックスを操作できません。
9行目の
「MsgBox wb.VBProject.VBComponents(“UserFormA”).Designer.Controls(“CommandButton1”).Caption」ですが、「AAA」エクセルファイル内にあるUserFormAの中のコマンドボタンに表示されているキャプションをメッセージボックスで表示するというものです。
11行目の「Application.Run “AAA.xlsm!フォームを開く”」は、「AAA」エクセルファイル内にあるUserFormAを表示するマクロを実行するというものです。
「Application.Run」を使うことで別ブックのマクロを実行することができます。ポイントとしては、”(ダブルクォーテーション)で囲む必要があるということです。
「フォームを開く」というのは、「AAA」エクセルファイル内にあるフォームを開くためのプロシージャ名です。実際にフォームを表示させるには、この中に「UserFormA.Show」といったように別途コードを入れておく必要があります。
12行目の「Application.Run “AAA.xlsm!コマンドボタンクリック”」は次の項の操作先ファイルのコードで説明しますが、「AAA」エクセルファイルで開いたフォームに設置したコマンドボタンのクリックイベントを実行するコードです。
以上が「ExcelVBAで別ブックのユーザーフォームを操作する方法」のコードの操作元ファイルで必要なコードの説明になります。
次は操作先ファイルで必要なコードを説明していきます。
ExcelVBAで別ブックのユーザーフォームを操作するためのコード（操作先ファイル）
先程書いたのが操作元ファイル(BBB.xlsm)のコードで、ここでは操作先ファイル(AAA.xlsm)のコードの説明に入ります。結論としては以下のコードが必要になります。
以下が標準モジュールに入力したコード
Sub コマンドボタンクリック()
Call UserFormA.CommandButton1_Click
End Sub
以下がフォームモジュールに入力したコード
Sub CommandButton1_Click()
MsgBox “メッセージ”
End Sub
標準モジュールの「コマンドボタンクリック」プロシージャでフォームモジュールのCommandButton1_Clickの処理を実行するという流れです。フォームモジュールに入力したコードでポイントがひとつあります。
1行目、「Sub CommandButton1_Click()」のSubの前に「Private」を入れると「コマンドボタンクリック」プロシージャでCallしても受け付けてくれません。
フォームモジュールでコマンドボタンなどのコントロールを設置すると、基本的にはPrivateが入ったクリックイベントのコードが自動で作成されてしまいます。ですので、標準モジュールからのCallで動作させられるように忘れずに削除する必要があります。
なぜこのような順番にしたかというと、操作元ファイルからのクリックイベントは直接は操作できなかったからです。例えば次のようなコードだと動作しません。
Application.Run “AAA.xlsm!CommandButton1_Click”
Application.Runは基本的には「マクロ」じゃないと動作しないようなので、Excelからするとクリックイベントは違うという認識なのでしょう。
以上のことから、フォームモジュールの「MsgBox “メッセージ”」の部分を適宜必要な処理を実行できるコードに変更すれば「ExcelVBAのDesignerで別ブックのユーザーフォームを操作する」ことができます。
今回のコードを見つけるまで
今回のコードを見つけるのはなかなか大変でした。Googleで他のエクセルファイルのユーザーフォームを操作する方法について検索してみても「Application Run～」という風に、他のエクセルファイルのマクロを実行するためのコードはたくさん見つけることは出来ます。
けれども、他のエクセルファイルにあるユーザーフォームを操作する方法はなかなか見つかりませんでした。どうすればこのコードを見つけられるか考えた所、エクセルの「オブジェクト構造」を考えれば良いかもしれないと思いました。
例えばエクセルの特定のブックの特定のシートの特定のセルを参照するためには以下のようなコードを書くと思います。
Workbooks(“A”).WorkSheets(“B”).Range(“A1”).value
このように他のエクセルファイルも参照できるようなコードを書けば、そのファイルにあるユーザーフォームも操作できるはず、と考えました。
しかし現実は厳しいもので、例えば以下のコードのようにいろいろ試行錯誤はしてはみました。
Workbooks(p).UserForm1.Controls(“textbox1″).value=”テスト”
上記のような感じのコードを手を変え品を変え延々と打ち込んではみたのですが、うんともすんとも言わず、画面には無常にも黄色のラインとエラーのメッセージが表示され続ける状態でした。
「自分のイメージではなくて、ちゃんとしたエクセルのオブジェクト構造を確認するべき」だと考え直してインターネットで調べてはみたのですが、これもユーザーフォームを操作できる所まで書かれたものは見つけることが出来ませんでした。
あーでもないこーでもないといろいろとコードをいじっている内に、とある事に気づきます。「そういえば、VBEのプロジェクトエクスプローラの部分て階層構造っぽいな」と。
なぜここに気づいたかというと、今回の動作を実行するとなると二つのエクセルファイルのモジュールを見比べたり、行ったり来たりする必要があったので必然的にこの部分を見ていたからです。
ですから「VBAProject」とか、この部分のコードがあるんじゃないかと思いました。調べてみるとVBProjectとかVBComponentsというコードが見つかり、「おっ、これはいけるかもしれない！」と思ったのですが、また壁が立ちはだかります。
例えば次のようなコードでは動いてくれません。
wb.VBProject.VBComponents(“UserFormA”).Controls(“textbox1”).Value = “テスト”
何が足りないかというと「Designer」というコードです。今だからこれがあれば動作するとわかります。ですがこれに気づく前は、まさかこんなコードが必要だとは夢にも思っていなかったので偶然にもこのコードを見つけて動作した時は本当に驚きました。
「これはいけるか？」と思ったらまた壁が立ちはだかります。長くなるのでここで割愛しますが、この後もSendkeysを使ったりPowerShellを試してみたり、そもそもフォームを表示させる必要があるのか？と考えたりもしました。
このブログでは自分がさせたい動作のコードを見つけるまでに四苦八苦してきたことをいろんな所で書いてきましたが、今回の件でAccessとかOutlookだけではなくてExcelでもオブジェクト構造の理解は必要なんだなと感じました。
今回のコードの注意点
別に注意点というわけでもないのですが、今回のコードはユーザーフォームを表示しなくても必要な処理を実行出来ます。
つまり、今回使った「Application.Run “AAA.xlsm!フォームを開く”」でフォームを表示しなくても
「Application.Run “AAA.xlsm!コマンドボタンクリック”」だけで必要な処理を実行できるということです。
とりあえず「こういうこともできる」という自分への確認の意味で、今回のようなコードの形にしてみました。
もっと良い方法はたくさんあるとは思いますが、いろんなやり方のひとつとして見ていただければと思います。
コメント