ExcelVBA 非アクティブブックのマクロ実行エラーを解決したい

Question

Excel　VBAで、非アクティブブックのマクロを実行させようとするとエラーが発生します。
一般化してしまっていますが以下がコードです。
この中で
ThisWorkbook.Activate
をなくすと正常に動作します。

何かわかる方がいましたらアドバイスいただけると幸いです。

'fileURL  開きたいブックのURL
'fileName  開いたブックの名前
'macro1　　開いたブックのマクロ名

Workbooks.Open fileURL　　'fileURLは開きたいブックのURLです。
ThisWorkbook.Activate   '開いたファイルを非アクティブにする。
Application.Run fileName & "!" & macro1     '開いた別ブックのマクロを実行

Answer 1

呼び出している処理側の問題のようなので、参考としてActive依存をチェックする方法を載せておきます。

グローバルなRangeプロパティへの参照を機械的に検知するアイディア - Qiita
imihito_Sandbox/DisableExcelGlobal.bas at master · imihito/imihito_Sandbox

今回の場合であれば以下のようにすればチェックできるでしょう

1. Application.Runで呼び出しているブックに標準モジュールを追加
2. 1. で追加した標準モジュールに以下のコードを貼り付ける
3. メニューバー「デバッグ」の中の「~のコンパイル」を実行(シュートカットキー Alt→D→L)
4. Activeに依存した処理の箇所でコンパイルエラーが発生するため、適切に修正
5. 3.、4.をエラーが無くなるまで繰り返す
6. 1.で追加した標準モジュールを削除

'Excelのグローバルなメンバーを無効化するモジュール
'https://qiita.com/nukie_53/items/4e9226ac8f747e772afd
'https://github.com/imihito/imihito_Sandbox/blob/master/Excel/DisableExcelGlobal.bas

'https://teratail.com/questions/257856

Option Private Module

'親を明示しないと危ないもの、使用頻度が低そうなメンバー(主観)の定義を上書きし無効化する。
'上位階層のプロパティや関数的なメソッドのみが残る。

'Method override
Public Sub Calculate(a): End Sub
Public Sub DDEExecute(): End Sub
Public Sub DDEInitiate(): End Sub
Public Sub DDEPoke(): End Sub
Public Sub DDERequest(): End Sub
Public Sub DDETerminate(): End Sub

'Property override
Public Sub ActiveCell(): End Sub
Public Sub ActiveChart(): End Sub
Public Sub ActivePrinter(): End Sub
Public Sub ActiveSheet(): End Sub
Public Sub ActiveWindow(): End Sub
Public Sub ActiveWorkbook(): End Sub
Public Sub AddIns(): End Sub
Public Sub Cells(): End Sub
Public Sub Charts(): End Sub
Public Sub Columns(): End Sub
Public Sub CommandBars(): End Sub
Public Sub Creator(): End Sub
Public Sub DDEAppReturnCode(): End Sub
Public Sub Excel4IntlMacroSheets(): End Sub
Public Sub Excel4MacroSheets(): End Sub
Public Sub Names(): End Sub
Public Sub Parent(): End Sub
Public Sub Range(): End Sub
Public Sub Rows(): End Sub
Public Sub Selection(): End Sub
Public Sub Sheets(): End Sub
Public Sub Worksheets(): End Sub

'Hidden Property
Public Sub ActiveDialog(): End Sub
Public Sub ActiveMenuBar(): End Sub
Public Sub Assistant(): End Sub
Public Sub DialogSheets(): End Sub
Public Sub MenuBars(): End Sub
Public Sub Modules(): End Sub
Public Sub ShortcutMenus(): End Sub
Public Sub Toolbars(): End Sub

---

ymakoto さんの回答と近い話ですが、fileNameの中身がおかしいのではないでしょうか？

また、開いたブックから名前を取得できるためfileName自体不要でしょう。

以下参考コードです。
fileURLとなっていたことから、適当なOneDrive上のファイルのURLで試してみましたが問題無く動作しました。

Public Sub teratail257856()
    
    Dim fileURL As String
    fileURL = "https://example.com/Sample.xlsm"
    
    Dim macro1 As String
    macro1 = "Macro1"
    
    Dim macroBook As Workbook
    Set macroBook = Workbooks.Open(fileURL)
    
    ThisWorkbook.Activate
    
    Dim appXl As Application
    Set appXl = macroBook.Application
    
    'macro1 が Sub プロシージャなら不要。    
    Dim resultOfMacro1 As Variant
    
    '以下のどちらでも動作する。
        'ブックの名前などは「'」で囲った方がベター。
    'ブック名のみ指定。
    resultOfMacro1 = appXl.Run("'" & macroBook.Name & "'!" & macro1)
    'フルパスで指定。
    resultOfMacro1 = appXl.Run("'" & macroBook.FullName & "'!" & macro1)
    
    macroBook.Saved = True
    macroBook.Close
    
End Sub

Comments

[退会済みユーザー]

勉強になりました。ありがとうございます！

[shirakoponzu000]

ありがとうございます。
コード勉強になりました。

しかしながらうまくいきません。
質問にもあるとおいThisWorkbook.Activeをなくす（コメントブロック）すれば正常に動くので、ブック名やURLには問題ないと思うのですが。。。。

自分でも再度見直してみます。

[imihito]

そういえば、そもそもどんなエラーが出るのですか？

[shirakoponzu000]

エラーは

実行時エラー　9
インデックスが有効な範囲にありません

となります。

色々試していますが、うまくいかないです。

[imihito]

となると Application.Run ではなく、外部ブックのマクロの作りの問題ですね。
恐らく ActiveWorkbookに依存した処理があるのでしょう。
呼び出し先のプロジェクトがロックされていなければ、「デバッグ」で問題の箇所がわかるはずです。

[shirakoponzu000]

アドバイスありがとうございます。
アドバイスを参考にしていましたら原因がわかりました。
まさに外部ブックのマクロの中に、その外部ブック内の指定シートの操作がありました。
ブック名から設定していたつもりでしたが指定しておらず、その外部ブックがアクティブな状態では指定シートを探し出せ、アクティブでない場合は指定シートが探しだせないエラーでした。
ありがとうございました。

Answer 2

勘違いでしたら申し訳ありません。VBAの提示が一部ですので何とも言えませんが
通常ですとFilepathをつけて開かれると思います。
サンプルではfileNameのみでfileURLと紐づいていないような気がします。

Thisworkbook.activeを入れることによってOPENしたExcelのファイル名はわかるものの
pathが見つけられなるのでエラーになるのではないでしょうか。

違っていたらすみません（無視してください）。。。

Sub OpenWorkbookTest()
  Dim Filepath
  Filepath = "C:¥temp¥test1.xlsx"'■通常はこのように記述する

  Dim targetWorkbook As Workbook
  Set targetWorkbook = Workbooks.Open(Filepath)

End Sub

Comments

[shirakoponzu000]

ありがとうございます。

ご提示いただいたように、やっていたのですが、うまくいきまんせん。
ブック名やURLには問題ないと思うのですが。。。。

再度見直してみます。