Excel (VBA)

Excel VBAに関するフォーラムです。
  • 解決済みのトピックにはコメントできません。
このトピックは解決済みです。
質問

 
(Windows 8.1 Pro : Excel 2010)
他ブックマクロを実行し、呼び出したブックを終了する際に引数を呼び出し元に返す方法について
投稿日時: 17/10/02 15:57:42
投稿者: ふぁんふぁn

呼び出し元のマクロから、Apprication.Runで他ブックのマクロを実行し、他ブックをCloseした時に、呼び出し元に引数を渡し呼び出し元の特定セルに値をセットしたいのですが、「インデックスが有効範囲にありません」のエラーが出てしまいます。
ご教示いただけましたら嬉しいです。
 
呼び出し元 
 ブック名 = メニューブック.xls
 パス名 = C:\Users\k3860\Desktop\VBA関係\
呼び出し先
  ブック名 = 集計ブック.xls
  パス名 = C:\Users\k3860\Desktop\VBA関係\
 
下記の例で、呼び出し元の@sub 他ブックマクロを実行します。
 
  @Sub 他ブックマクロ()
 
      Dim 引数1 As Integer
      Dim 引数2 As Integer
      Dim 引数3 As Integer
      Dim 引数4 As Integer
      引数1 = 1
      引数2 = 2
      引数3 = 3
      引数4 = 4
      Dim 作業パス名, 作業ブック As String
      作業パス名 = "C:\Users\m4961\Desktop\VBA関係\"
      作業ブック = 作業パス名 & "集計ブック.xls"
      Workbooks.Open Filename:=作業ブック, UpdateLinks:=3
      Application.Run "'" & 作業ブック & "'" & "!ブック_Open", 引数1, 引数2, 引数3, 引数4
   End Sub
 
呼び出し先のASub ブック_Openが実行され正常に引数が表示され指定のセルに移ります。
  ASub ブック_Open(引数1, 引数2, 引数3, 引数4)
      MsgBox 引数1 & "- " & 引数2 & "- " & 引数3 & "- " & 引数4 ⇒ OK
      Worksheets("集計シート").Select ⇒ OK
      Range("A1").Select ⇒ OK
     End Sub
 
呼び出し先のブックを終了する際に、呼び出し元に変数を引き渡すBマクロが実行されます。
  BPrivate Sub Workbook_BeforeClose(Cancel As Boolean)
      Dim 作業パス名, 作業ブック As String
      Dim 変数A As Integer, 変数B As Integer
      作業パス名 = "C:\Users\m4961\Desktop\VBA関係\"
      作業ブック = 作業パス名 & "メニューブック.xls"
      変数A = 9
      変数B = 1
      If Worksheets("集計シート").Range("A1") = 1 Then
         Application.Run "'" & 作業ブック & "'" & "!他ブックから戻った", 変数A, 変数B
      Else
      End If
   End Sub
 
呼び出し元のCSub 他ブックから戻ったが実行されますが、エラー
  Option Explicit
  Public 変数A, 変数B
  CSub 他ブックから戻った(変数A, 変数B)
      MsgBox "戻った 変数A - 変数B : " & 変数A & " - " & 変数B ⇒ メッセージ表示OK
      Worksheets("メニュー").Select ⇒ ★エラー★
      Range("L36")
   End Sub
 
エラー
  実行時エラー'9'
  インデックスが有効範囲にありません。

回答
投稿日時: 17/10/02 16:54:58
投稿者: WinArrow
投稿者のウェブサイトに移動

>   Worksheets("メニュー").Select ⇒ ★エラー★
>   Range("L36")
 
この時、アクティブなブックは、どちらなんでしょうか?
 
2つ以上のブックを操作する場合は
必ず、ブックで修飾することが鉄則です。
そしてシートを選択する前には、ブックを選択することも鉄則です。

投稿日時: 17/10/02 17:05:09
投稿者: ふぁんふぁn

この時、アクティブなブックは呼び出し元のブックです。
 
 

WinArrow さんの引用:
>   Worksheets("メニュー").Select ⇒ ★エラー★
>   Range("L36")
 
この時、アクティブなブックは、どちらなんでしょうか?
 
2つ以上のブックを操作する場合は
必ず、ブックで修飾することが鉄則です。
そしてシートを選択する前には、ブックを選択することも鉄則です。

 

投稿日時: 17/10/02 17:11:46
投稿者: ふぁんふぁn

呼び出し先の Private Sub Workbook_BeforeClose マクロを下記のように変更しましたら、解決しました。
 
 Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Call 元ブック処理
  End Sub
 
 Sub 元ブック処理()
     Dim 変数A As Integer, 変数B As Integer
     作業パス名 = "C:\Users\k3860\Desktop\VBA関係\"
     作業ブック = 作業パス名 & "メニューブック.xls"
     変数A = 9
     変数B = 1
     If Worksheets("集計シート").Range("A1") = 1 Then
       Windows("メニューブック.xls").Activate
       Sheets("メニュー").Select
       Range("L36").Select
       ActiveCell.FormulaR1C1 = "1"
       Range("L37").Select
       ActiveCell.FormulaR1C1 = "2"
       Range("L38").Select
       Application.Run "'" & 作業ブック & "'" & "!他ブックから戻った", 変数A, 変数B
    Else
    End If
End Sub
 
ありがとうございました・