他ブックのマクロブックを参照設定で接続する【ExcelVBA】

前回、Application.Runで別ブックを呼ぶと

• クラスとして戻り値を得ることはできる
• ただし型がVariantになり、インテリセンスも使えない

という認識でしたが、

先日この記事を起点にTwitterで相談したところ、多くのつよつよの方からご意見いただきました。

中でもすぐにメモしておかないと、後々調べるエッセンスすら忘れそうなやべーやつをまとめておきました。

なるほど
切り分け方は目的別・機能別で良さそうな気がしますが、個人次第なところがありますしなんとも
ちなみに上記の記事みたいなことされる場合、インテリ効かす方法はありますよ

— やまと💻 (@yamato_1413) July 11, 2022

参照設定で他ブックを接続する

・Book1で
Dim 変数 As Book2Project.クラス
のように型付き変数宣言できるようになるので、https://t.co/45Flw0zCiNの戻り値をこの変数で受け取る

とすればインテリが使えます

— やまと💻 (@yamato_1413) July 11, 2022

なんですって・・・

Book2 呼び出される側のVBAProject名を変更する

Book2内のクラスのInstancingプロパティをPublicNotCreatable変更する

Book1 呼び出す側で参照設定に登録する

Book1 Book2のクラスを呼び出すプロシージャを作る

Dim 戻り値 As Book2くらいでCtrl+Jを押すとBook2Projectが、その後.をつけてCtrl+Jを押すとクラスもインテリされる！！！

その後のDebug.Print .Str部でももちろんインテリされる。

というのと、コードがめちゃくちゃスッキリした。

Book1_標準モジュール

Sub test5_別ブックの参照設定して直接呼出す()

  Dim 戻り値 As Book2Project.clsTest
  Set 戻り値 = Book2Project.testClass("aaaaaa", 12345)
  
  Stop
  With 戻り値
    Debug.Print .Str
    Debug.Print .Val
  End With

End Sub

Book2を非表示にする、Book1を閉じると連動して閉じるようにする

参照設定で接続するとこうなります。

1. Book1を開くと自動的にBook2が開く
2. Book1を閉じるまでBook2を閉じることができない
3. Book1を閉じてもBook2は自動的には閉じない

そこでひと手間加えます。

1. Book2を非表示にする
2. Book1を閉じると連動して閉じるようにする

Book1 呼び出す側

ThisWorkbook BeforeCloseイベントでBook2を閉じるプロシージャを呼び出す

厳密に言うとBook1を閉じる前にBook2を閉じることはできないとエラーが出るがOn Error Resume Nextで黙らせる。

Book2_ThisWorkbook

Private Sub Workbook_BeforeClose(Cancel As Boolean)
  On Error Resume Next
  Call Book2Project.CloseThisWorkBook
End Sub

Book2 呼び出される側

ThisWorkbook Openイベントで非表示にする

Book2_ThisWorkbook

Private Sub Workbook_Open()
  ThisWorkbook.Windows(1).Visible = False
End Sub

Book2_標準モジュール 自ブックを閉じるだけのプロシージャを持たせておく

Book2_標準モジュール

Sub CloseThisWorkBook()
  ThisWorkbook.Close
End Sub

Book2のパスが変わったら

ツール>参照設定から手動でパスの更新をしてもいいけど面倒なのでコードでやっちゃう。

田中先生のものをベースに少し変えてます。

参照不可になっている参照設定を解除する

これをやっておかないと、次の参照設定を追加するで読み込むVBAProject名が重複していると怒られます。

Book1_標準モジュール

Sub 参照不可になっている参照設定を解除する()
'http://officetanaka.net/excel/vba/tips/tips100.htm
    Dim Ref As Variant
    With ThisWorkbook.VBProject
      For Each Ref In ThisWorkbook.VBProject.References
        If Ref.IsBroken Then .References.Remove Ref
      Next Ref
    End With
End Sub

参照設定を追加する

Book1_標準モジュール

' Book2のフルパスをここに置いておき、
' 変更があればここを変えて参照設定の更新を行う
Private Const 参照設定wb = "C:\Users\hoge\Book2.xlsm"

Sub 参照設定を追加する()
  'http://officetanaka.net/excel/vba/tips/tips100.htm
  ' AddFromFile の後にブックのフルパスを入れる。
    
  ThisWorkbook.VBProject.References.AddFromFile _
      参照設定wb
  
  Dim buf, ub As Long, wbName As String
  buf = Split(参照設定wb, "\")
  ub = UBound(buf)
  wbName = buf(ub)
  
  Dim wb As Workbook: Set wb = Workbooks(wbName)
  Dim pjName As String: pjName = wb.VBProject.Name
    
  MsgBox 参照設定wb & vbLf & _
    "を参照設定に追加しました。" & vbLf & vbLf & _
    "VBAProject名：" & pjName
    
  Set wb = Nothing

End Sub

以上です。
凄いですね・・・というか皆さんどこから情報仕入れてくるんだ・・・

凄いけど使い所は要検討

1. ブックをまたぐ親子関係をきちんと管理できるか
2. 接続するブックを読み込むこと自体による負担増加が実作業上問題ないレベルか

特に弊社の共有サーバー、ムチャクチャ重いのがボトルネックとなり、今回のこの分割方法は見送りとなりました・・・

なのでマクロブックとしてどこにもおそらく保存されず風化していくので今回記事化しておきました。
いつか、誰かがこうせざるを得ないシーンに遭遇した時さっと出せるとカッコいいですね。

畜生め

結局アドインをただのランチャーにして本体をサーバ上に置いたら呼ぶたびに3-5秒かかりやがる

在宅のネット環境次第ではもっとかかるかも

ここのボトルネック頭から完全に抜けてた...

— 青と緑のVBAer (@mechkawa) July 13, 2022