タイダログ

もっと怠けますか? (y/n)

VBA の function で Range の配列を返す

VBA の function で Range の配列を返す方法がわからなくなることがよくあります。検索と忘却ばかりを繰り返す、救いようのない私を救うため、記事に残しておきます。

(Range の配列の返し方に関する)全ての疑問を、調べる前に消し去りたい。全ての宇宙、過去と未来の全ての疑問を、この記事で。

上手くいく例

以下の2か所で Set ステートメント使わなかったら上手くいきました

  1. Function プロシージャ内で関数名に値を代入する箇所
  2. 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 ステートメントの問題でしたね。

結び

検索しても思ったものが出てこなかったのと、調べたことをメモしておかなかったのとで(あたしって、ほんとバカ)、「昔のコードにあった、ような……」と、自分が書いたものの中から掘り起こしてきました。

そのおかげで新しいことを知れたので、良しとしましょう。

誰かの役に立てるとしたら、それは、とっても嬉しいなって、思ってしまうのでした。

参考