VBAでのExecuteExcel4Macroの値取得でエラー

質問

こんにちは。
Excel VBAより、ExecuteExcel4Macroを実行して、
外部のExcelファイルの、名前（店名、月度）を定義したセルの値を取得したいと思っています。
店名：文字列型
月度：Date型

そこで、ExecuteExcel4Macro()を実行し、以下のような処理を加えました。

------------------------------------------------------------------------------------

dim 店名 as Variant, 月度 as Variant
If 外部マクロ実行("'c:\[test.xls]出勤簿'!店名", 店名) = False Or _
外部マクロ実行("'c:\[test.xls]出勤簿'!月度", 月度) = False Then
MsgBox "取得失敗", vbExclamation
End
End If

Public Function 外部マクロ実行(com As String, ByRef result As Variant) As Boolean
On Error GoTo erron3
result = ExecuteExcel4Macro(com)
On Error GoTo 0
外部マクロ実行 = True
Exit Function

erron3:
外部マクロ実行 = False
End Function

------------------------------------------------------------------------------------

これを実行したところ、「月度」の値は取得できるのですが、
「店名」の値には「エラー 2042」という値が入ります。
※dirname, filenameは正しい値が入っています。
※シート「出勤簿」および「月度」「店名」のセル名の定義も存在します。

test.xlsを開いてるときは、上記の現象は起こらず、
「店名」の値は正常に取得できます。

また、試しに、test.xlsを開き、
Worksheets("出勤簿").Range("店名")を実行すると、正常な値が取得できました。

まとめると、
・閉じたブックの、あるシートにある、セルに定義された名前を指定して
・ExecuteExcel4Macroで、文字列が入っている値を取得しようとした時、
・正常に値が取得できない
という現象に遭遇しています。

3日ほど調べているのですが、どうしても原因が分かりません。
解決策をお持ちの方、いらっしゃいましたらアドバイスを頂けると助かります。

環境：WindowsXP Pro SP3
Excel 2003 (11.5612.5606)

以上、よろしくお願いいたします。

回答No.1

onlyrom · ベストアンサー

'-------------------------------------------------
Sub Test()
　Dim 店名
　Dim 月度

　店名 = 外部マクロ実行("'C:\[test.xls]出勤簿'!店名")
　月度 = 外部マクロ実行("'C:\[test.xls]出勤簿'!月度")

　　　If 店名 = 0 Or 月度 = 0 Then
　　　　　MsgBox "取得失敗", vbExclamation
　　　Else
　　　　　MsgBox 店名 & " @ " & Format(月度, "yyyy/m/d")
　　　End If

End Sub
'-----------------------------------------------

Function 外部マクロ実行(com As String)
　　外部マクロ実行 = ExecuteExcel4Macro(com)
End Function
'-----------------------------------------------

●ただ今回の場合は、Functionは無しでもいいような。。。

'----------------------------------------------
Sub Test555()
　Dim 店名
　Dim 月度

　店名 = ExecuteExcel4Macro("'C:\[test.xls]出勤簿'!店名")
　月度 = ExecuteExcel4Macro("'C:\[test.xls]出勤簿'!月度")

　　　If 店名 = 0 Or 月度 = 0 Then
　　　　　MsgBox "取得失敗", vbExclamation
　　　Else
　　　　　MsgBox 店名 & " @ " & Format(月度, "yyyy/m/d")
　　　End If

End Sub
'----------------------------------------------------

以上。

回答No.2

Wendy02

こんにちは。

ExecuteExcel4Macro というスタイルは、直接、マクロ関数を取る以外は、追いかけないほうがよいかもしれませんが、そのコード自体は間違いありません。

>「エラー 2042」という値が入ります。

という意味は、「#N/A 」の戻り値です。
だから、そのエラー値は、数式側だけの問題です。数式を調べないといけません。

"'c:\[test.xls]出勤簿'!店名"

私としては、その書き方は、一見良いのですが、実際、ルートに置くわけではないでしょうから、失敗しやすいです。

これは、目的のファイルが開いていない状態で扱うときですが、実務上では、ファイルが開いていなければ、開けて、Evaluate で取るというのが、一般的な書き方です。そうでなければ、DAOやADOを使って開くというスタイルになります。

全体的に、Excelマクロとしては変則的なコードだと思います。

今回の場合は、ユーザー定義関数側では、参照渡しは、あまり良いとは思いません。実行時エラーと両方を取ろうとしたので、Boolean値のユーザー定義関数自体を戻り値にしたのだと思いますが、この種のものは、実行時エラーが発生するのは、コードの自体の間違いか、ファイルが存在しない間違いですから、Dir 関数などの回避の仕方はあると思います。単に、数式の戻り値を、ユーザー定義関数側に返すだけで十分な気がします。

なお、そのコードは「外部マクロ実行」ではありません。

'----------------------------------------
　　Dim 店名 As Variant, 月度 As Variant
　　Dim nShiki1 As String
　　Dim nShiki2 As String
　　Const nPATH = "'C:\Documents and Settings\[UserName]\My Documents\"
　　Const nWBNAME = "test.xls"
　　Const nSHNAME = "出席簿"
　　Const nADDRESS1 = "店名" '名前登録はそのまま、アドレスなら、R1C1方式
　　Const nADDRESS2 = "月度"

　　nShiki1 = "[" & nWBNAME & "]" & nSHNAME & "!" & nADDRESS1
　　店名 = Evaluate(nShiki1)　'ファイルが開いているかチェックする

　　If IsError(店名) Then
　　　　nShiki1 = nPATH & "[" & nWBNAME & "]" & nSHNAME & "'!" & nADDRESS1
　　　　nShiki2 = nPATH & "[" & nWBNAME & "]" & nSHNAME & "'!" & nADDRESS2

　　　　　 'Debug.Print nShiki　''数式の内容チェック

　　　　If 外部マクロ実行(nShiki1, 店名) = False Or _
　　　　　　外部マクロ実行(nShiki2, 月度) = False Then
　　　　　　MsgBox "取得失敗", vbExclamation
　　　　　　Exit Sub
　　　　End If
　　Else
　　　　nShiki2 = "[" & nWBNAME & "]" & nSHNAME & "!" & nADDRESS2
　　　　月度 = Evaluate(nShiki2)
　　End If

End Sub
'-----------------------------------