CODEWITHSUNDEEP
vbaexcel
wooobie
wooobie

Reputation: 345

VBA Range not being assigned to an Array

So rather simply I defined a test function:

Option Base 1
Function TestFunction(InputRange As Range)
    Dim TestArray() As Variant
    TestArray = InputRange

    Debug.Print TestArray(5)
End Function

I then call it on a sheet with =TestFunction(A:A), and expect to see the value in row 5 printed to the Immediate window. Instead I don't get any output. The first 100 or so rows of A have data so I'd expect TestArray(5) to output something.

I've also tried changing the above to:

    TestArray = InputRange.Value

and:

    TestArray = Range(InputRange)

None of this seems to work.

Upvotes: 0

Views: 286

Answers (1)

Scott Craner
Scott Craner

Reputation: 152660

The issue is when assigning a range to an array it is made into a two dimensional array regardless if it is one column or one row. So all references must refer to it as a two dimensional array:

Debug.Print TestArray(5,1)

Upvotes: 3

Related Questions