VBA の function で Range の配列を返す方法がわからなくなることがよくあります。検索と忘却ばかりを繰り返す、救いようのない私を救うため、記事に残しておきます。
(Range の配列の返し方に関する)全ての疑問を、調べる前に消し去りたい。全ての宇宙、過去と未来の全ての疑問を、この記事で。
上手くいく例
以下の2か所で Set
ステートメントを使わなかったら上手くいきました。
- Function プロシージャ内で関数名に値を代入する箇所
- Sub プロシージャ内で戻り値を受け取る箇所
Function プロシージャ内で配列の各要素に代入する箇所では Set
ステートメントが必要です。
Function ReturnRangeArray_OK() As Range() Dim results() As Range ReDim results(1 To 10) Dim i As Long For i = 1 To 10 Set results(i) = ActiveSheet.Cells(i, 1) Next i ReturnRangeArray_OK = results() '...<1> ReturnRangeArray_OK = results '.....<2> End Function Sub UseRangeArray_OK() Dim rangeArray() As Range rangeArray() = ReturnRangeArray_OK '...<1> rangeArray = ReturnRangeArray_OK '.....<2> End Sub
関数名への代入は、<1>
の書き方でも <2>
の書き方でも、どちらか一方があればいいです。同様に戻り値の受け取りも、<1>
でも <2>
でもいいようです。
引数があっても大丈夫。こちらも <1>
か <2>
があればいいです。
Function ReturnRangeArray_OK2(number As Long) As Range() Dim results() As Range ReDim results(1 To number) Dim i As Long For i = 1 To number Set results(i) = ActiveSheet.Cells(i, 1) Next i ReturnRangeArray_OK2 = results() '...<1> ReturnRangeArray_OK2 = results '.....<2> End Function Sub UseRangeArray_OK2() Dim rangeArray() As Range rangeArray() = ReturnRangeArray_OK2(10) '...<1> rangeArray = ReturnRangeArray_OK2(10) '.....<2> End Sub
他のオブジェクトも同じ結果でした。
Function ReturnWorksheetArray_OK() As Worksheet() Dim results() As Worksheet ReDim results(1 To 10) Dim i As Long For i = 1 To 10 Set results(i) = ActiveWorkbook.Worksheets(i) Next i ReturnWorksheetArray_OK = results() '...<1> ReturnWorksheetArray_OK = results '.....<2> End Function Sub UseWorksheetArray_OK() Dim worksheetArray() As Worksheet worksheetArray() = ReturnWorksheetArray_OK '...<1> worksheetArray = ReturnWorksheetArray_OK '.....<2> End Sub
上手くいかない例
関数名や変数への代入で Set
ステートメントを使うとエラーが出ます。
Function ReturnRangeArray_NG() As Range() Dim results() As Range ReDim results(1 To 10) Dim i As Long For i = 1 To 10 Set results(i) = ActiveSheet.Cells(i, 1) Next i Set ReturnRangeArray_NG = results() '...エラー End Function Sub UseRangeArray_NG() Dim rangeArray() As Range Set rangeArray() = ReturnRangeArray_NG '...エラー Set rangeArray = ReturnRangeArray_NG '...エラー End Sub
以下のように、関数名への代入で、関数名に ()
を付けてもエラーが出ます。これはオブジェクトに限らず、配列全般で起こるようです。
Function ReturnLongArray() As Long() Dim results(1 To 10) As Long Dim i As Long For i = 1 To 10 results(i) = i * i Next i ReturnLongArray() = results() '...エラー End Function
Set が必要なのは単体のオブジェクトの代入のみ?
Microsoft Docs の Set
ステートメントのページで Syntax の objectexpression を見てみますと、以下のようにあります。
Expression consisting of the name of an object, another declared variable of the same object type, or a function or method that returns an object of the same object type.
'an object' ということは、単体のオブジェクトの代入にしか Set
ステートメントは使わないということでしょうか。さっそく実験です。
Sub AssignRangeArray() Dim rangeArray1() As Range ReDim rangeArray1(1 To 10) Dim i As Long For i = 1 To 10 Set rangeArray1(i) = ActiveSheet.Cells(i, 1) Next i Dim rangeArray2() As Range rangeArray2 = rangeArray1 Dim rangeArray3() As Range Set rangeArray3 = rangeArray1 ' エラー End Sub
Set
ステートメントを使うとエラーが出ました。ということで、Set
ステートメントが必要なのは、単体のオブジェクトを変数に代入する場合のみのようです。配列変数にオブジェクトの配列を代入する時は付けてはいけません。きっと。
Function の問題ではなく、Set
ステートメントの問題でしたね。
結び
検索しても思ったものが出てこなかったのと、調べたことをメモしておかなかったのとで(あたしって、ほんとバカ)、「昔のコードにあった、ような……」と、自分が書いたものの中から掘り起こしてきました。
そのおかげで新しいことを知れたので、良しとしましょう。
誰かの役に立てるとしたら、それは、とっても嬉しいなって、思ってしまうのでした。