Excel VBAで別ブックのマクロから配列を取る

質問

Excel VBAで別ブックのマクロで計算した結果を配列で渡したいのですが、上手い方法が見つかりません。
同じブック内であれば、
Function GetAry(Imax As Integer, ByRef MyAry As Variant) as Boolean
のような関数を作れば、GetAry = True の時に返値の MyAry が有効であるという判断ができますが、この関数を別ブックから使う場合は、参照渡しができません。
これはVBAの仕様なので仕方ないとして、以下のようなマクロを組んでみました。

'-------------------------------------------------------
' Book1.xlsm（呼び出される側）
'-------------------------------------------------------
Function MyAry(Imax As Integer) As Variant
Dim i As Integer
Dim SubAry() As Variant
If Imax > 10 Then
MyAry = False
Else
For i = 1 To Imax
ReDim Preserve SubAry(i)
SubAry(i) = i
Next
MyAry = SubAry
End If
End Function

'-------------------------------------------------------
' Book2.xlsm（呼び出す側）
'-------------------------------------------------------
Sub GetMyAry()
Dim DataAry As Variant
Dim Imax As Integer
Imax = 11
DataAry = Application.Run("Book1!MyAry", Imax)
If DataAry <> False Then
MsgBox UBound(DataAry)
Else
MsgBox DataAry
End If
End Sub
'-------------------------------------------------------

Imax = 11 であれば、メッセージボックスに False が表示されますが、Imax = 10 だと当然ですが「型が一致しません」というエラーになります。
エラートラップで誤魔化そうかとも思ったのですが、もっとスマートな方法がないでしょうか。

よろしくお願いします。

回答No.2

chie65536（@chie65535） · ベストアンサー

Sub GetMyAry()
Dim DataAry As Variant
Dim Imax As Integer
Imax = 10
DataAry = Application.Run("Book1!MyAry", Imax)
MsgBox VarType(DataAry)
If VarType(DataAry) <> vbBoolean Then
MsgBox UBound(DataAry)
Else
MsgBox DataAry
End If
End Sub

返って来た結果が「ブール型なのかどうか？」で判断して下さい。

VerType関数で「バリアント型変数に、どんな値が入って居るのか？」が判るので、vbBoolean（11）なら「Imaxが11以上でfalseが返された」と、vbArray＋vbVariant（8204）なら「Imaxが10以下で、バリアント型の配列が返された」と 判ります。

「ブール型の値が返された時」は
If DataAry <> False Then
と言う比較演算は可能です。しかし「バリアント型の配列が返された時」は
If DataAry <> False Then
と言う比較演算はエラーになります。

その為「どんな値が返って来たのか？」を「VarType関数で調べる」のが「正当な方法」です。

お礼

結果のTrue/Falseではなく、返ってきた変数の型で判断するというのは気付きませんでした。エラー処理に関するコメントもありがとうございました。

回答No.3

chie65536（@chie65535）

追記。

細かいエラー処理まで含めて「きちんと処理する」と言う場合、以下のようにします。

'-------------------------------------------------------
' Book1.xlsm（呼び出される側）
'-------------------------------------------------------
Option Base 1

Function MyAry(Imax As Integer) As Variant
Dim i As Integer
Dim SubAry() As Variant
If Imax > 10 Then
MyAry = CVErr(2000)
ElseIf Imax < 0 Then
MyAry = CVErr(2001)
ElseIf Imax = 0 Then
MyAry = CVErr(2002)
Else
For i = 1 To Imax
ReDim Preserve SubAry(i)
SubAry(i) = i
Next
MyAry = SubAry
End If
End Function

'-------------------------------------------------------
' Book2.xlsm（呼び出す側）
'-------------------------------------------------------
Sub GetMyAry()
Dim DataAry As Variant
Dim Imax As Integer
Imax = 10
DataAry = Application.Run("Book1!MyAry", Imax)
MsgBox VarType(DataAry)
If Not IsError(DataAry) Then
MsgBox UBound(DataAry)
Else
MsgBox CStr(DataAry) & vbCrLf & Error(CInt(DataAry))
End If
End Sub

「呼び出される側」では「Imaxが11以上ならエラー2000を、Imaxがマイナスならエラー2001を、Imaxが0ならエラー2002を、そうじゃないならバリアント配列を返す」と言う処理をしています。

「呼び出す側」では「Not IsError()なら正常呼び出しとしてUBoundの表示を行い、エラーならエラーメッセージを表示する」と言う処理をしています。

エラー番号は、取り合えず未使用の2000～2002を使用しましたが、エラー番号9の「インデックスが有効範囲にありません」を使用しても良いでしょう。

回答No.1

ushi2015

こんにちは
Function MyAry(Imax As Integer) As Variant
　　Dim i As Integer
　　Dim SubAry() As Variant
　　If Imax > 10 Then
　　　　MyAry = False
　　Else
　　　　For i = 1 To Imax
　　　　　　ReDim Preserve SubAry(1 To i)
　　　　　　SubAry(i) = i
　　　　Next
　　　　MyAry = SubAry
　　End If
End Function
Sub GetMyAry()
　　Dim DataAry As Variant
　　Dim Imax As Integer
　　Imax = 10
　　DataAry = Application.Run("Book1!MyAry", Imax)
　　If IsArray(DataAry) = True Then
　　　　MsgBox UBound(DataAry)
　　Else
　　　　MsgBox DataAry
　　End If
End Sub
こんな感じでは？
Imaxがマイナスの場合とかのエラーもトラップした方がいいかも・・・

お礼

戻り値の値ではなく、配列か否かで判定するというのは気付きませんでした。
ありがとうございました。