あるブックから別のブックの関数を呼び出す

結局 VBA いじる羽目になった俺です。どーも。

Book1.xls から Book2.xls 上の関数を呼び出す処理について、大ハマリやらかしたので反省の意味も込めてここにメモ。

呼び出し方

いろいろあるけど、たいていはこんな感じ。エラー処理とかはオサボリです。

• Book1.xls

Public Sub Caller()
  '### Book2.xls!Called() を呼び出す
  Dim strFullPath As String
  strFullPath = "C:\Documents and Settings\First-Last\excel\template\Book2.xls"
  Dim strProc As String
  strProc = "Book2.xls!Called"
  Workbooks.Open Filename:= strFullPath
  Application.Run( strProc )
  Workbooks(GetBaseFileName(strFullPath)).close
End Sub

Public Function GetBaseFileName( strFullPath as String ) as String
    '### フルパス名からベースネームを取り出す
    Dim pos As Long
    Dim strBase As String
    
    pos = InStrRev(strFullPath, "\")
    strBase = Mid(strFullPath, pos + 1)
    GetBaseFileName = strBase
End Function

• Book2.xls

Public Sub Called()
  '### 呼び出される
  MsgBox( "呼んだ?" )
End Sub

注意すべきこと（ハマリの原因）

「マクロが見つかりません」が出たら、以下を疑う。

• OpenしたブックをフルパスでApplication.Run() してないか？
  • Open したブックを Application.Run() する場合は、Run()の引数は "ファイル名!マクロ名"だけでいい。
  • なお、Openしてないブックをやる場合、いきなり Run()内に"絶対パス\ファイル名!マクロ名"と書く。どっちでも Ok
  • だけど、Open 済みのブックをフルパスで Run() しようとすると、別のブックを開けたと解釈されて、パスが合っていてもマクロが見つからないと言われる。

• 呼び出される側(=Book2.xls)のマクロが呼び出す側から見えてるか？
  • 確認方法：VisualBasicEditorを立ち上げて、そこの「ツール＞マクロ」を選択したとき、そのマクロの名前（ここでは Called）がリストに載っていることを確認する*1
  • ポイント：Excelの通常の画面からデザインモード（三角定規のアイコンのアレ）に入って、適当にボタンを配置、そいつをクリックして VBEditor を起動してスクリプトを入れるってやっていると、どういう訳かこのリストに載らなくなる。後でそれに気づいて直しても、どうもダメっぽい。そういう時は新しいブックを作った方がいい……orz
• 呼び出される側のマクロは、きちんと標準モジュール上に載っているか？
  • 確認方法：プロジェクト・エクスプローラー（「表示＞プロジェクト・エクスプローラー」）を表示し、標準モジュールがあるか、あったとしたらそいつをダブルクリックして、そこにしかるべきスクリプトが表示されるか、を確認。
  • ポイント：上と同じ。最初からきちんと「標準モジュール」を挿入してから作業をすること。なお、標準モジュール内の"Module1"などの部分は、特に書かなくてもいいようだ。"Book2.xls!Called"でも、"Book2.xls!Module1.Called"でもどっちでも OKだった。

反省

ブック間をまたがる呼び出しをしなければ、上に書いたような適当な作り方をしても問題なく動く。でもブックの境界を越えて呼び出そうとした瞬間、「マクロが見つからない」というエラーにやられる。それが嫌なら最初からきちんとモジュールを指定して書いておくこと。

*1:Public Subのみ。Public でも Function はこのリストには出ない。