この記事では、フォルダ内のExcelファイルにあるモジュールを全てエクスポートする方法についてご説明します。
【動画】フォルダ内のExcelファイルにあるモジュールを全てエクスポートする実際の動き
本題に入る前に、まずは次の動画をご覧ください。
モジュールをエクスポートしたいExcelファイルを開き、そのExcelファイルの各モジュールをExportメソッドを実行してエクスポートしています。
モジュールをエクスポートする際はモジュール名が必要ですが、そのモジュールの名称はVBProject.VBComponentsオブジェクトのNameプロパティを使って取得しています。
マクロ作成の流れ
名称はVBComponentsコレクションのNameプロパティから取得することができます。
Excelファイルの例
今回は次のExcelファイルを用意しました。
A2の黄色のセルにはモジュールを出力したいExcelファイルの置き場を入力し、A5の黄色のセルにはモジュールの保存先を入力します。
実行ボタンをクリックするとマクロが呼び出されます。
【注意】VBComponentsコレクションを扱うにはある設定を行わないとエラーになり動作しない
VBComponentsコレクションを扱うにはある設定を行わないとエラーになり動作しません。
設定を行わないままVBComponentsコレクションを呼び出そうとすると下のようにエラーが発生して正常に動作しません。
ではその必要な設定はというと、次の画面(トラストセンター画面)で行う「VBA プロジェクト オブジェクト モデルへのアクセスを信頼する」のチェックを付ける設定です。
このチェックを付けておかないとエラーが先ほどのエラーが発生してしまうので、必ずチェックをつけましょう。
なお、チェックを付けるとセキュリティ面が弱くなってしまうデメリットがあるので、もし本マクロを使わないときはチェックを外しておきましょう。
ただし、本マクロを使い終わった後のチェックの有無設定はExcelを使用している端末の環境の仕様を最優先し、その仕様に合わせて行って下さい。
あくまでExcelの設定はお使いの環境の仕様を絶対に守るようお使いくださいね。
トラストセンター画面の開き方及び「マクロの設定」の表示方法
トラストセンター画面の開き方及び「マクロの設定」の表示方法は次の通りです。
①「ファイル」をクリックする
「ファイル」をクリックします。
②「オプション」をクリックする
「オプション」をクリックします。
③「Excelのオプション」画面でトラストセンターをクリックしトラストセンターボタンをクリックする
「Excelのオプション」画面が表示されたら、「トラストセンター」をクリックします。
するとトラストセンターに関する画面が表示されるので、その中にある「トラストセンターの設定」ボタンをクリックします。
④トラストセンター画面の開き方及び「マクロの設定」が表示される
「トラストセンター」画面が表示され、「マクロの設定」をクリックすると、「マクロの設定」に関する画面が表示されます。
手順は以上になります。
コードの例
Excelのマクロのコード(例)
Option Explicit Private Sub btn_exec_Click() Dim cnt As Integer 'カウンタ用変数 Dim aryCnt As Integer 'カウンタ用変数 Dim excelFilePath As String 'VBAオブジェクトモジュールの名称を取得したいExcelファイルの格納先 Dim expPath As String 'エクスポートしたファイルの保存先のパス Dim buf As String 'Excelのファイル名を受け取る用の一時格納用変数 Dim ws As Worksheet 'Worksheet用変数 Dim sheetNM() As String 'シート名用配列 Dim VBComp As Object '標準モジュールやシートなどを扱うためのオブジェクト変数 Dim expFile As String 'エクスポートするファイル名 Dim wsChk As Boolean 'ワークシート判定フラグ 'カウンタを初期化する cnt = 0 aryCnt = 0 'VBAオブジェクトモジュールの名称を取得したいExcelファイルの格納先を取得する excelFilePath = Worksheets("work").Range("filePath").Value 'モジュールの保存先を取得する expPath = Worksheets("work").Range("expPath").Value 'VBAオブジェクトモジュールの名称を取得したいExcelファイルを取得する buf = Dir(excelFilePath & "*.xlsm") 'Excelファイルの数だけ処理を繰り返すループ Do While buf <> "" 'Excelファイルを読み取り専用で開く Workbooks.Open excelFilePath & buf, ReadOnly:=True 'フォルダ名とファイル名貼り付け用シート有無のチェック For Each ws In Worksheets '動的配列の再宣言を行う(配列に格納されている値を残したまま) ReDim Preserve sheetNM(1, cnt) 'シート名を配列に格納する sheetNM(0, cnt) = ws.Name 'モジュールのコード名を配列に格納する sheetNM(1, cnt) = ws.CodeName cnt = cnt + 1 Next ws 'VBAオブジェクトモジュールの数だけ処理を繰り返すループ For Each VBComp In ActiveWorkbook.VBProject.VBComponents If Right(expPath, 1) <> "\" Then '入力されたパスの末尾に「\」が付いていない場合に付ける expPath = expPath & "\" End If 'エクスポートするファイル名を取得する expFile = expPath & Replace(buf, ".xlsm", "") & "_" & VBComp.Name Select Case VBComp.Type Case 1 '標準モジュールの場合 'モジュールをエクスポートする ActiveWorkbook.VBProject _ .VBComponents(VBComp.Name).Export expFile & ".bas" Case 2 'クラスモジュールの場合 'モジュールをエクスポートする ActiveWorkbook.VBProject _ .VBComponents(VBComp.Name).Export expFile & ".cls" Case 3 'ユーザーフォームの場合 'モジュールをエクスポートする ActiveWorkbook.VBProject _ .VBComponents(VBComp.Name).Export expFile & ".frm" Case 100 'ワークブックやシートの場合 For aryCnt = 0 To UBound(sheetNM, 2) If sheetNM(1, aryCnt) = VBComp.Name Then '配列にシートモジュール名が格納されている場合 'モジュールをエクスポートする(シートモジュールであることを知らせるためファイル名に「_sht」を付ける) ActiveWorkbook.VBProject _ .VBComponents(VBComp.Name).Export expFile & "_sht.cls" 'ワークシートの場合に wsChk = True Exit For End If Next aryCnt If wsChk = False Then 'wsChkの値がFalseの場合 '→VBComp.Nameの値がワークブックモジュールに該当 'モジュールをエクスポートする(ブックモジュールであることを知らせるためファイル名に「_sht」を付ける) ActiveWorkbook.VBProject _ .VBComponents(VBComp.Name).Export expFile & "_twb.cls" End If 'フラグを初期化する wsChk = False Case Else End Select cnt = cnt + 1 Next '開いたExcelファイルを閉じる Workbooks(buf).Close SaveChanges:=False 'Excelファイル名を取得する buf = Dir() Loop End Sub
注目すべきコード①
最初に見て頂きたいのは21行目から24行目です。
'VBAオブジェクトモジュールの名称を取得したいExcelファイルの格納先を取得する excelFilePath = Worksheets("work").Range("filePath").Value 'モジュールの保存先を取得する expPath = Worksheets("work").Range("expPath").Value
以上のコードは、VBAオブジェクトモジュールの名称を取得したいExcelファイルの格納先とモジュールの保存先を取得するコードです。
注目すべきコード②
次に見て頂きたいのは27行目です。
'VBAオブジェクトモジュールの名称を取得したいExcelファイルを取得する buf = Dir(excelFilePath & "*.xlsm")
以上のコードは、フォルダ内の拡張子が「xlsm」のExcelファイル名を取得するコードです。
今回はフォルダ内の拡張子が「xlsm」のExcelファイルからモジュールをエクスポートするので、Dir関数にエクスポートしたいExcelファイルの置き場と「xlsm」の拡張子を指定しています。
注目すべきコード③
次に見て頂きたいのは30行目です。
'Excelファイルの数だけ処理を繰り返すループ Do While buf <> ""
以上のコードは、「注目すべきコード②」で説明したExcelファイルの置き場にあるExcelファイルのモジュールを全てエクスポートし終わるまで繰り返すループです。
なお、1ファイルの全モジュールをエクスポートし終わったら、下のコードを実行して次のExcelファイル内のモジュールをエクスポートします。
'Excelファイル名を取得する buf = Dir()
注目すべきコード④
次に見て頂きたいのは33行目です。
'Excelファイルを読み取り専用で開く Workbooks.Open excelFilePath & buf, ReadOnly:=True
以上のコードは、モジュールをエクスポートするExcelファイルを開く処理のコードです。
OpenメソッドにモジュールをエクスポートするExcelファイルを指定して実行することでExcelファイルを開くことができます。
Excelファイルを開くことでモジュールをエクスポートすることができます。
注目すべきコード⑤
次に見て頂きたいのは36行目から49行目です。
'フォルダ名とファイル名貼り付け用シート有無のチェック For Each ws In Worksheets '動的配列の再宣言を行う(配列に格納されている値を残したまま) ReDim Preserve sheetNM(1, cnt) 'シート名を配列に格納する sheetNM(0, cnt) = ws.Name 'モジュールのコード名を配列に格納する sheetNM(1, cnt) = ws.CodeName cnt = cnt + 1 Next ws
以上のコードは、Excelファイルから全てのシート名とそのシートのモジュール名を取得するコードです。
なぜシート名とシートのモジュール名を取得するのかというと、シートモジュールなのかブックモジュールなのかを判別するためです。
というのも、モジュール名を取得する際シートモジュールなのかブックモジュールなのかを判別する情報がありません。
そこでどうやって判別するのかというと、シートモジュールにはシート名がありますがブックモジュールにはシート名がありません。
- シートモジュール:シート名がある
- ブックモジュール:シート名がない
なので、シート名の有無でシートモジュールなのかブックモジュールなのかが判別することができます。
そのために以上のコードでシート名を取得しています。
今回は取得したシート名を42行目で、モジュール名を45行目で配列に格納しています。
39行目は以上のシート名とモジュール名をどんどん配列sheetNMに格納するために配列の再宣言を行っています。
シート名とモジュール名を取得したら配列sheetNMの要素数を1つ拡張してその拡張した要素位置に格納します。
この配列sheetNMに取得したシート名とモジュール名を、Excelのファイル内に存在するシートの数だけ繰り返します。
この繰り返しの処理を36行目のループ内で行います。
注目すべきコード⑥
次に見て頂きたいのは52行目です。
'VBAオブジェクトモジュールの数だけ処理を繰り返すループ For Each VBComp In ActiveWorkbook.VBProject.VBComponents
以上のコードは、開いたExcelファイルにあるモジュールの名称をVBComponentsコレクションから取得するためのループ処理のコードです。
VBComponentsコレクションから取得したオブジェクトを変数VBCompが受け取ります。
ActiveWorkbookは開いたExcelなので、そのExcelファイルからモジュールの名称をVBComponentsコレクションから取得することができるようになります。
なおこのループは開いたExcelファイルにあるモジュールの数だけループします。
注目すべきコード⑦
次に見て頂きたいのは62行目です。
'エクスポートするファイル名を取得する expFile = expPath & Replace(buf, ".xlsm", "") & "_" & VBComp.Name
以上のコードは、エクスポートするファイル名を取得するコードです。
ただし、ここではクラスモジュールなのかフォームモジュールなのか、またシートモジュールなのかまだ特定できていないので、以上のコードが実行されたタイミングではまだ拡張子がない状態です。
C:\work\10_勉強\10_VBA関連\0202\file\output\file1_ThisWorkbook
「file1_ThisWorkbook」に「.cls」「.frm」「.bas」のいずれかを後続のコードで行います。
なおファイル名の構成は、「エクスポート元のExcelのファイル名(拡張子抜き)」+「_」+「モジュール名」としています。
注目すべきコード⑧
次に見て頂きたいのは64行目です。
Select Case VBComp.Type
以上のコードは、VBComponentsコレクションのTypeプロパティの値を判定するコードです。
VBComponentsコレクションのTypeプロパティの値によって「.cls」「.frm」「.bas」のいずれかを付与してファイルをエクスポートします。
- 1:標準モジュール(.bas)
- 2:クラスモジュール(.cls)
- 3:ユーザーフォーム(.frm)
- 100:Excelオブジェクト(ワークブック・シート)(.cls)
1:標準モジュール(.bas)
標準モジュールの場合は66行目の条件に合致し、71行目と72行目のコードが実行されます。
Case 1 '標準モジュールの場合 'モジュールをエクスポートする ActiveWorkbook.VBProject _ .VBComponents(VBComp.Name).Export expFile & ".bas"
標準モジュールの場合は拡張子が「bas」なので「.bas」の文字列をエクスポートするファイルに結合させてExportを実行してエクスポートします。
2:クラスモジュール(.cls)
クラスモジュールの場合は74行目の条件に合致し、79行目と80行目のコードが実行されます。
Case 2 'クラスモジュールの場合 'モジュールをエクスポートする ActiveWorkbook.VBProject _ .VBComponents(VBComp.Name).Export expFile & ".cls"
クラスモジュールの場合は拡張子が「cls」なので「.cls」の文字列をエクスポートするファイルに結合させてExportを実行してエクスポートします。
3:ユーザーフォーム(.frm)
フォームモジュールの場合は82行目の条件に合致し、87行目と88行目のコードが実行されます。
Case 3 'ユーザーフォームの場合 'モジュールをエクスポートする ActiveWorkbook.VBProject _ .VBComponents(VBComp.Name).Export expFile & ".frm"
フォームモジュールの場合は拡張子が「frm」なので「.frm」の文字列をエクスポートするファイルに結合させてExportを実行してエクスポートします。
100:Excelオブジェクト(ワークブック・シート)(.cls)
ブックモジュールまたはシートモジュールの場合は90行目の条件に合致し、94行目と125行目のコードが実行されます。
Case 100 'ワークブックやシートの場合 For aryCnt = 0 To UBound(sheetNM, 2) If sheetNM(1, aryCnt) = VBComp.Name Then '配列にシートモジュール名が格納されている場合 'モジュールをエクスポートする(シートモジュールであることを知らせるためファイル名に「_sht」を付ける) ActiveWorkbook.VBProject _ .VBComponents(VBComp.Name).Export expFile & "_sht.cls" 'ワークシートの場合に wsChk = True Exit For End If Next aryCnt If wsChk = False Then 'wsChkの値がFalseの場合 '→VBComp.Nameの値がワークブックモジュールに該当 'モジュールをエクスポートする(ブックモジュールであることを知らせるためファイル名に「_sht」を付ける) ActiveWorkbook.VBProject _ .VBComponents(VBComp.Name).Export expFile & "_twb.cls" End If 'フラグを初期化する wsChk = False
ブックモジュールとシートモジュールどちらもVBComponentsコレクションのTypeプロパティの値が100なのですが、「注目すべきコード⑤」でも説明した通りブックモジュールとシートモジュールの判定する情報がありません。
どちらのモジュールもTypeプロパティの値が100なので、もしブックモジュールの名称をデフォルトの「ThisWorkBook」から適当な名称に変更されてしまった場合に、エクスポートファイルを見てもそれがブックモジュールのファイルなのかシートモジュールのファイルなのかが分かりません。
ブックモジュールの名称が変更されてもエクスポートファイルがブックモジュールのものなのかを特定するためにはどうすればいいのかというと、シート名とそのシートのモジュール名を使って判定します。
- シートモジュール:シート名がある
- ブックモジュール:シート名がない
シートモジュールはシート名があるので95行目の条件に合致し、100行目と101行目でエクスポートするファイルに「_sht.cls」を結合させてExportを実行してエクスポートします。(「sht」はsheetを略した文字列にしました)
ブックモジュールはシート名がないので113行目の条件に合致し、119行目と120行目でエクスポートするファイルに「_twb.cls」を結合させてExportを実行してエクスポートします。(「twb」はThisWorkBookを略した文字列にしました)
注目すべきコード⑨
次に見て頂きたいのは136行目です。
'開いたExcelファイルを閉じる Workbooks(buf).Close SaveChanges:=False
上記のコードは、モジュールすべてをエクスポートし終わったらExcelファイルを閉じる処理のコードです。
もう開いておく必要が無いのでCloseメソッドを実行してExcelファイルを閉じます。
動作確認
マクロ実行前
今回は以下のファイルを使います。
A2のセルにはモジュールを出力したいExcelファイルの置き場を入力し、A4のセルにはモジュールの保存先を入力します。
今回モジュールをエクスポートさせるExcelファイルは以下の3ファイルです。
3ファイルにあるすべてのモジュールをエクスポートします。
マクロ実行後
マクロを実行すると、以下のフォルダにモジュールがエクスポートされます。
モジュールのコードとエクスポートしたファイルの中身の確認
①標準モジュール
cls_getRowPosfile1_mdl_funcExp.bas
ソースコードの上部に何か情報が書き込まれていますが、これはエクスポート時に書き込まれる情報です。
2行目から最後まではソースコードに相違なく書き込まれていることが確認できます。
②クラスモジュール
cls_getRowPosfile1_cls_getrowPos.cls
ソースコードの上部に何か情報が書き込まれていますが、これはエクスポート時に書き込まれる情報です。
10行目から最後まではソースコードに相違なく書き込まれていることが確認できます。
③フォームモジュール
cls_getRowPosfile1_frm_getFileInfo.frm
ソースコードの上部に何か情報が書き込まれていますが、これはエクスポート時に書き込まれる情報です。
16行目から最後まではソースコードに相違なく書き込まれていることが確認できます。
なお、フォームモジュールは「.frx」の拡張子が付いたファイルもエクスポート時に作成されます。
このファイルはフォームモジュールのエクスポートファイルを使ってインポートする際必要になります。
もし「.frx」の拡張子が付いたファイルが無いとエラーになりインポートができません。
④シートモジュール
Sheet1file1_Sheet1_sht.cls
ソースコードの上部に何か情報が書き込まれていますが、これはエクスポート時に書き込まれる情報です。
10行目から最後まではソースコードに相違なく書き込まれていることが確認できます。
また、シートモジュールはエクスポートするのに拡張子が「.cls」で生成されるので、シートモジュールのものと分かるようにエクスポートファイルに「_sht」の文字列が付けられています。
手動でエクスポート時「クラスモジュール(.cls)」しか選べない⑤ブックモジュール
TWBソースコードの上部に何か情報が書き込まれていますが、これはエクスポート時に書き込まれる情報です。
10行目から最後まではソースコードに相違なく書き込まれていることが確認できます。
また、ブックモジュールはエクスポートするのに拡張子が「.cls」で生成されるので、ブックモジュールのものと分かるようにエクスポートファイルに「_sht」の文字列が付けられています。
手動でエクスポート時「クラスモジュール(.cls)」しか選べない最後に
本記事では、フォルダ内のExcelファイルにあるモジュールを全てエクスポートする方法についてご説明しました。
モジュールの中身を確認したい場合にExcelファイルを開かないと確認できないですが、今回のマクロを使ってモジュールをエクスポートしておけばExcelファイルを開かずにモジュールのソースの中身を確認することができます。
ソースの中のコードをGrep検索したい時には便利です。
Excelファイルを開いてソースを開く手間を減らしたい、grepで一括検索したい、などのいくつかの場面で役に立つかもしれないので本記事を参考にしてみてくださいね。
Excelのスキル向上やExcelの基礎知識をしっかりと学びたいなら
Excelのスキルを習得したい、Excelの基礎知識をもっと理解したい、そう考えているなら「無期限サポート付きExcel講座【すごい改善】」がおすすめです。
Excelのスキルの基礎を身につけるなら【すごい改善】で無期限サポート付きがあるので、これで「Excelのスキルや基礎」を学ぶのにおすすめですよ。