EXCELでVBAを作っていると、ブックごと(.xlsmファイルごと)にVBAが保存されます。
しかし他のブックでも同じようなものを作っていることがあり、
共通化したいと感じていました。
①一定の場所に全てのモジュール(.bas)を保存しておき、
ブックを起動するたびに、
モジュールはそこから自動的に読み込めれば
便利です。
②また、EXCELの作業中にモジュールを変更した場合は、
まとめて一定の場所のモジュールを更新しておけば
全てのブックで最新版のモジュールが使えます。
もちろん、モジュールを変更する時は、他で問題が起きないように
注意する必要はあります。
調べてみると、
こういうことはすでに情報を出してくださっている方が
いらっしゃるので、それを参考に(参考と言うかそのまま頂いています)
次のようなものを作りました。
なお、モジュールの保存場所は、
ブックのあるドライブの
usersフォルダーの下の
ユーザー名フォルダーの下の
Documentsの下の
vbalib
つまりマイドキュメントの下のvbalibフォルダ にしています。
使いやすい場所に設定すれば良いです。
変更する時は、①②両方のPathName= のところを変更してください。
①モジュールの自動読み込み
以下のコードをEXCELブックのVBAエディターで
Microsoft Excel Objects の下の ThisWorkbook に書きます。
'*************************************
Option Explicit
'読み込むモジュール名を列挙する
Const ModuleNames = "Module_lib, ModuleVBAref"
' ワークブックを開く時のイベント
Private Sub Workbook_Open()
Dim PathName, UName
Dim WshNetworkObject As Object
Set WshNetworkObject = CreateObject("WScript.Network")
'モジュールが保存されているPATHを指定する
UName = WshNetworkObject.UserName
PathName = Left(ThisWorkbook.Path, 1) & ":\Users\" & UName & "\Documents\vbalib"
'ModuleNamesに列挙したモジュールを読み込み
load_from_conf2 PathName, ModuleNames
End Sub
' 設定ファイルに書いてある外部ライブラリを読み込みます。
Sub load_from_conf2(PathName, ModuleNames)
Dim FileNames, FilePath, Cmp, i
'ModuleNamesに列挙したものを「,」で分け、前後の文字の空白を除去する
FileNames = Split(ModuleNames, ",")
For i = 0 To UBound(FileNames)
FileNames(i) = Trim(FileNames(i))
Next i
'ModuleNamesに列挙したものと同じ名前の読み込み済みのモジュールをリムーブする
For Each Cmp In ThisWorkbook.VBProject.VBComponents
If InStr(ModuleNames, Cmp.name) > 0 Then
ThisWorkbook.VBProject.VBComponents.Remove Cmp
End If
Next Cmp
'ModuleNamesに列挙したモジュールを読み込む
For i = 0 To UBound(FileNames)
FilePath = PathName & "\" & FileNames(i) & ".bas"
If Dir(FilePath) = "" Then
MsgBox (FilePath & " は存在しません。スキップします。")
Else
ThisWorkbook.VBProject.VBComponents.Import FilePath
End If
Next i
'保存する
ThisWorkbook.Save
End Sub
'読み込むモジュール名を列挙する
Const ModuleNames = "Module_lib, ModuleVBAref"
' ワークブックを開く時のイベント
Private Sub Workbook_Open()
Dim PathName, UName
Dim WshNetworkObject As Object
Set WshNetworkObject = CreateObject("WScript.Network")
'モジュールが保存されているPATHを指定する
UName = WshNetworkObject.UserName
PathName = Left(ThisWorkbook.Path, 1) & ":\Users\" & UName & "\Documents\vbalib"
'ModuleNamesに列挙したモジュールを読み込み
load_from_conf2 PathName, ModuleNames
End Sub
' 設定ファイルに書いてある外部ライブラリを読み込みます。
Sub load_from_conf2(PathName, ModuleNames)
Dim FileNames, FilePath, Cmp, i
'ModuleNamesに列挙したものを「,」で分け、前後の文字の空白を除去する
FileNames = Split(ModuleNames, ",")
For i = 0 To UBound(FileNames)
FileNames(i) = Trim(FileNames(i))
Next i
'ModuleNamesに列挙したものと同じ名前の読み込み済みのモジュールをリムーブする
For Each Cmp In ThisWorkbook.VBProject.VBComponents
If InStr(ModuleNames, Cmp.name) > 0 Then
ThisWorkbook.VBProject.VBComponents.Remove Cmp
End If
Next Cmp
'ModuleNamesに列挙したモジュールを読み込む
For i = 0 To UBound(FileNames)
FilePath = PathName & "\" & FileNames(i) & ".bas"
If Dir(FilePath) = "" Then
MsgBox (FilePath & " は存在しません。スキップします。")
Else
ThisWorkbook.VBProject.VBComponents.Import FilePath
End If
Next i
'保存する
ThisWorkbook.Save
End Sub
'*************************************
②モジュールの一括保存
任意の共用モジュールに書きます。自動では動きません。
EXCELの開発メニューやVBAエディターからマニュアルで起動するなどしてください。
'*************************************
'アクティブブックの全標準モジュールをエクスポートする
Sub ExportModule()
Dim VBC, str As String, icnt As Integer, PathName As String, UName
Dim WshNetworkObject As Object
Set WshNetworkObject = CreateObject("WScript.Network")
UName = WshNetworkObject.UserName
PathName = Left(ThisWorkbook.Path, 1) & ":\Users\" & UName & "\Documents\vbalib\"
str = ""
icnt = 0
With ActiveWorkbook.VBProject
For Each VBC In .VBComponents
If VBC.Type = 1 Then
VBC.Export PathName & VBC.name & ".bas"
str = str & vbCrLf & VBC.name & ".bas"
icnt = icnt + 1
End If
Next VBC
End With
MsgBox icnt & "個のモジュ-ルを保存しました。" & vbCrLf & str
End Sub
Sub ExportModule()
Dim VBC, str As String, icnt As Integer, PathName As String, UName
Dim WshNetworkObject As Object
Set WshNetworkObject = CreateObject("WScript.Network")
UName = WshNetworkObject.UserName
PathName = Left(ThisWorkbook.Path, 1) & ":\Users\" & UName & "\Documents\vbalib\"
str = ""
icnt = 0
With ActiveWorkbook.VBProject
For Each VBC In .VBComponents
If VBC.Type = 1 Then
VBC.Export PathName & VBC.name & ".bas"
str = str & vbCrLf & VBC.name & ".bas"
icnt = icnt + 1
End If
Next VBC
End With
MsgBox icnt & "個のモジュ-ルを保存しました。" & vbCrLf & str
End Sub
'*************************************
参考にさせていただいたWebSite
ユーザー名やコンピュータ名を取得する(WshNetworkオブジェクト)
Excelマクロを外部ファイルにして複数ブックから利用できるようにする
Excel VBAのマクロを,複数のブックから利用する方法 (標準モジュールをブックの外部で管理して,共通ライブラリとして読み込み)
アクティブブックの全標準モジュールをエクスポートする