この記事では、閉じているExcelファイルにあるVBAオブジェクトモジュールの名称を全て取得する方法についてご説明します。
【動画】閉じているExcelファイルにあるVBAオブジェクトモジュールの名称を全て取得する実際の動き
本題に入る前に、まずは次の動画をご覧ください。
閉じているExcelファイルを開き、VBComponentsコレクションを使ってVBAのオブジェクトモジュールの名称を全て取得しています。
実はExcelファイルが閉じたままだとVBAのオブジェクトモジュールの名称を取得できません。
マクロがExcelのファイルが開いた状態にしておき、VBComponentsコレクションを使ってVBAのオブジェクトモジュールの名称を全て取得しています。
ちなみに、今回は読み取り専用でExcelのファイルを開いています。
マクロ作成の流れ
名称はVBComponentsコレクションのNameプロパティから取得することができます。
【注意】VBComponentsコレクションを扱うにはある設定を行わないとエラーになり動作しない
VBComponentsコレクションを扱うにはある設定を行わないとエラーになり動作しません。
設定を行わないままVBComponentsコレクションを呼び出そうとすると下のようにエラーが発生して正常に動作しません。
ではその必要な設定はというと、次の画面(トラストセンター画面)で行う「VBA プロジェクト オブジェクト モデルへのアクセスを信頼する」のチェックを付ける設定です。
このチェックを付けておかないとエラーが先ほどのエラーが発生してしまうので、必ずチェックをつけましょう。
なお、チェックを付けるとセキュリティ面が弱くなってしまうデメリットがあるので、もし本マクロを使わないときはチェックを外しておきましょう。
ただし、本マクロを使い終わった後のチェックの有無設定はExcelを使用している端末の環境の仕様を最優先し、その仕様に合わせて行って下さい。
あくまでExcelの設定はお使いの環境の仕様を絶対に守るようお使いくださいね。
トラストセンター画面の開き方及び「マクロの設定」の表示方法
トラストセンター画面の開き方及び「マクロの設定」の表示方法は次の通りです。
①「ファイル」をクリックする
「ファイル」をクリックします。
②「オプション」をクリックする
「オプション」をクリックします。
③「Excelのオプション」画面でトラストセンターをクリックしトラストセンターボタンをクリックする
「Excelのオプション」画面が表示されたら、「トラストセンター」をクリックします。
するとトラストセンターに関する画面が表示されるので、その中にある「トラストセンターの設定」ボタンをクリックします。
④トラストセンター画面の開き方及び「マクロの設定」が表示される
「トラストセンター」画面が表示され、「マクロの設定」をクリックすると、「マクロの設定」に関する画面が表示されます。
手順は以上になります。
コードの例
Excelのマクロのコード(例)
Option Explicit
Sub test()
Dim VBComp As Object '標準モジュールやシートなどを扱うためのオブジェクト変数
Dim cnt As Integer 'カウンタ用変数
Dim excelFilePath As String 'VBAオブジェクトモジュールの名称を取得したいExcelファイルの格納先
Dim buf As String 'Excelのファイル名を受け取る用の一時格納用変数
'カウンタを初期化する
cnt = 1
'VBAオブジェクトモジュールの名称を取得したいExcelファイルの格納先を取得する
excelFilePath = ThisWorkbook.Path & "\" & "file" & "\"
'VBAオブジェクトモジュールの名称を取得したいExcelファイルを取得する
buf = Dir(excelFilePath & "*.xlsm")
'Excelファイルの数だけ処理を繰り返すループ
Do While buf <> ""
'Excelファイルを読み取り専用で開く
Workbooks.Open excelFilePath & buf, ReadOnly:=True
'VBAオブジェクトモジュールの数だけ処理を繰り返すループ
For Each VBComp In ActiveWorkbook.VBProject.VBComponents
'マクロ側の(ブックと)シート
With Workbooks(ThisWorkbook.Name).Worksheets("work")
Select Case VBComp.Type
Case 1
'標準モジュールの場合
'コンポーネントに付けられている名前をセルに出力する
.Range("A" & cnt).Value = VBComp.Name
'種類をセルに出力する
.Range("B" & cnt).Value = "標準モジュール"
Case 2
'クラスモジュールの場合
'コンポーネントに付けられている名前をセルに出力する
.Range("A" & cnt).Value = VBComp.Name
'種類をセルに出力する
.Range("B" & cnt).Value = "クラスモジュール"
Case 3
'ユーザーフォームの場合
'コンポーネントに付けられている名前をセルに出力する
.Range("A" & cnt).Value = VBComp.Name
'種類をセルに出力する
.Range("B" & cnt).Value = "ユーザフォーム"
Case 11
'ユーザーフォームの場合
'コンポーネントに付けられている名前をセルに出力する
.Range("A" & cnt).Value = VBComp.Name
'種類をセルに出力する
.Range("B" & cnt).Value = "ActiveX デザイナ"
Case 100
'ワークブックやシートの場合
'コンポーネントに付けられている名前をセルに出力する
.Range("A" & cnt).Value = VBComp.Name
'種類をセルに出力する
.Range("B" & cnt).Value = "ワークブックまたはシート"
Case Else
End Select
End With
cnt = cnt + 1
Next
'開いたExcelファイルを閉じる
Workbooks(buf).Close SaveChanges:=False
'Excelファイル名を取得する
buf = Dir()
Loop
End Sub
注目すべきコード①
最初に見て頂きたいのは14行目から20行目です。
'VBAオブジェクトモジュールの名称を取得したいExcelファイルの格納先を取得する
excelFilePath = ThisWorkbook.Path & "\" & "file" & "\"
'VBAオブジェクトモジュールの名称を取得したいExcelファイルを取得する
buf = Dir(excelFilePath & "*.xlsm")
'Excelファイルの数だけ処理を繰り返すループ
Do While buf <> ""
以上のコードは、VBAオブジェクトモジュールの名称を全て取得したいExcelファイルの置き場と、そのExcelファイルの名前を取得するコードです。
17行目で、フォルダ内の拡張子が「xlsm」のExcelファイルをDir関数を実行して取得しています。
次に20行目のDoループですが、Excelファイルの置き場にあるExcelファイル全てを対象に処理が終わると、この20行目のループは終了します。
注目すべきコード②
次に見て頂きたいのは23行目です。
'Excelファイルを読み取り専用で開く
Workbooks.Open excelFilePath & buf, ReadOnly:=True
以上のコードは、Excelファイルを読み取り専用で開く処理のコードです。
実はExcelファイルが閉じたままだとVBAのオブジェクトモジュールの名称を取得できません。
開いている状態でないとVBAのオブジェクトモジュールの名称を取得できないので、マクロがExcelのファイルが開いた状態にしておきます。
なお、開いたExcelファイルに誤って何か変更を加えて保存してしまうことが無いように、読み取り専用でExcelをファイルを開くようにしています。
注目すべきコード③
次に見て頂きたいのは26行目です。
'VBAオブジェクトモジュールの数だけ処理を繰り返すループ
For Each VBComp In ActiveWorkbook.VBProject.VBComponents
以上のコードは、開いたExcelファイルにあるVBAオブジェクトモジュールの名称をVBComponentsコレクションから取得するためのループ処理のコードです。
VBComponentsコレクションから取得したオブジェクトを変数VBCompが受け取ります。
ActiveWorkbookは開いたExcelなので、そのExcelファイルからVBAオブジェクトモジュールの名称をVBComponentsコレクションから取得することができるようになります。
なおこのループは開いたExcelファイルにあるVBAオブジェクトモジュールの数だけループします。
注目すべきコード④
次に見て頂きたいのは31行目です。
Select Case VBComp.Type
以上のコードは、VBComponentsコレクションのTypeプロパティの値を判定するコードです。
VBComponentsコレクションのTypeプロパティの値は次の種類があります。
- 1：標準モジュール
- 2：クラスモジュール
- 3：ユーザーフォーム
- 11：ActiveXデザイナ
- 100：Excelオブジェクト(ワークブック・シート)
このVBComponentsコレクションのTypeプロパティによって、以上の表のVBAオブジェクトモジュールが特定することができます。
今回はVBAオブジェクトモジュールの名称が目的なので、以上のVBComponentsコレクションのTypeプロパティの話は本筋から外れているかもしれませんが、VBAオブジェクトモジュールの種類を明示したいために、VBComponentsコレクションのTypeプロパティの話をさせて頂きました。
なお、VBAオブジェクトモジュールの種類を明示するコードは41行目で行っています。
'種類をセルに出力する
.Range("B" & cnt).Value = "標準モジュール"
以上は標準モジュールの場合です。
注目すべきコード③
次に見て頂きたいのは38行目です。
'種類をセルに出力する
.Range("B" & cnt).Value = "標準モジュール"
以上のコードは、VBAオブジェクトモジュールの名前をNameプロパティから取得してセルに出力するコードです。
動作確認
マクロ実行前
以下のシートにVBAオブジェクトモジュールの名前を出力します。
以下の3つのExcelファイルを対象にVBAオブジェクトモジュールの名前を取得してシートに出力します。
今回用意したExcelファイルのVBEのプロジェクトエクスプローラーには下の画面の通り、フォームやシート、標準モジュールなどが存在しています。
以上のフォームやシート、標準モジュールなどの名称を全てExcelのシートに出力します。
マクロ実行後
マクロを実行すると、VBEのプロジェクトエクスプローラーに存在するフォームやシート、標準モジュールなどの名称がExcelのシートに出力されました。
最後に
本記事では、閉じているExcelファイルにあるVBAオブジェクトモジュールの名称を全て取得する方法についてご説明しました。
本マクロを実行すれば簡単に閉じているExcelファイルにあるVBAオブジェクトモジュールの名称を全て取得することができるので、現在VBAに存在するVBAオブジェクトモジュールの名称が欲しい場合は参考にしてみてくださいね。
Excelのスキル向上やExcelの基礎知識をしっかりと学びたいなら
Excelのスキルを習得したい、Excelの基礎知識をもっと理解したい、そう考えているなら「無期限サポート付きExcel講座【すごい改善】」がおすすめです。
Excelのスキルの基礎を身につけるなら【すごい改善】で無期限サポート付きがあるので、これで「Excelのスキルや基礎」を学ぶのにおすすめですよ。
→ 受講後、何度でも無期限でメールで質問できるアフターサポートがついているExcelマスター講座はこちら
