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



https://qiita.com/taleau/items/92182f94024f53b3158b

前回、`Application.Run`で別ブックを呼ぶと
> - クラスとして戻り値を得ることはできる
> - ただし型がVariantになり、インテリセンスも使えない

という認識でしたが、

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

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

<blockquote class="twitter-tweet"><p lang="ja" dir="ltr">なるほど<br>切り分け方は目的別・機能別で良さそうな気がしますが、個人次第なところがありますしなんとも<br>ちなみに上記の記事みたいなことされる場合、インテリ効かす方法はありますよ</p>&mdash; やまと💻 (@yamato_1413) <a href="https://twitter.com/yamato_1413/status/1546388097558282241?ref_src=twsrc%5Etfw">July 11, 2022</a></blockquote> <script async src="https://platform.twitter.com/widgets.js" charset="utf-8"></script>


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


<blockquote class="twitter-tweet"><p lang="ja" dir="ltr">・Book1で<br>Dim 変数 As Book2Project.クラス<br>のように型付き変数宣言できるようになるので、<a href="https://t.co/45Flw0zCiN">https://t.co/45Flw0zCiN</a>の戻り値をこの変数で受け取る<br><br>とすればインテリが使えます</p>&mdash; やまと💻 (@yamato_1413) <a href="https://twitter.com/yamato_1413/status/1546415736608149504?ref_src=twsrc%5Etfw">July 11, 2022</a></blockquote> <script async src="https://platform.twitter.com/widgets.js" charset="utf-8"></script>

なんですって・・・

### Book2 呼び出される側のVBAProject名を変更する
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/281933/ce06989a-8765-dcc9-593a-78a2268dc2c2.png)

### Book2内のクラスのInstancingプロパティを`PublicNotCreatable`変更する
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/281933/78fb9b35-42f3-00c0-3af5-e5b4b006878c.png)


### Book1 呼び出す側で参照設定に登録する
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/281933/20487286-7c8e-d4af-ff6e-6691713971ae.png)

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

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

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

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

``` vb: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が開く
1. Book1を閉じるまでBook2を閉じることができない
1. Book1を閉じてもBook2は自動的には閉じない

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

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

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

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

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


### Book2 呼び出される側
#### ThisWorkbook Openイベントで非表示にする
``` vb:Book2_ThisWorkbook
Private Sub Workbook_Open()
  ThisWorkbook.Windows(1).Visible = False
End Sub
```

#### Book2_標準モジュール 自ブックを閉じるだけのプロシージャを持たせておく
``` vb:Book2_標準モジュール
Sub CloseThisWorkBook()
  ThisWorkbook.Close
End Sub
```


## Book2のパスが変わったら

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

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

http://officetanaka.net/excel/vba/tips/tips100.htm

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

``` vb: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
``` 

### 参照設定を追加する
``` vb: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. ブックをまたぐ親子関係をきちんと管理できるか
1. 接続するブックを読み込むこと自体による負担増加が実作業上問題ないレベルか

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

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


<blockquote class="twitter-tweet"><p lang="ja" dir="ltr">畜生め<br><br>結局アドインをただのランチャーにして本体をサーバ上に置いたら呼ぶたびに3-5秒かかりやがる<br><br>在宅のネット環境次第ではもっとかかるかも<br><br>ここのボトルネック頭から完全に抜けてた...</p>&mdash; 青と緑のVBAer (@mechkawa) <a href="https://twitter.com/mechkawa/status/1547097094166441986?ref_src=twsrc%5Etfw">July 13, 2022</a></blockquote> <script async src="https://platform.twitter.com/widgets.js" charset="utf-8"></script>