VBA Range not being assigned to an Array

Question

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.

Try Debug.Print TestArray(5,1) Though it is one column it is still a two dimensional array — Scott Craner
– Scott Craner, Commented Jan 22, 2016 at 15:40
@ScottCraner is correct about that (though you could declare it simple as TestArray rather than TestArray() when you set it equal to InputRange.Value. I hope that you don't try to assign A:A to a variant in real code -- that would be an array with over a million entries — John Coleman
– John Coleman, Commented Jan 22, 2016 at 15:44
That was my issue. Thank you so much! If you want to post this as an answer I'll accept it. @ScottCraner — wooobie
– wooobie, Commented Jan 22, 2016 at 15:44
@JohnColeman Don't worry I plan to trim the range to its actual size before assigning it to an array. — wooobie
– wooobie, Commented Jan 22, 2016 at 15:52
I wrote a function for taking these pseudo two dimensional arrays and converting them into one dimensional arrays. Sometimes I use the function, more often then not I just live with the annoying index. Still, you might find it helpful: stackoverflow.com/a/31144434/4996248 — John Coleman
– John Coleman, Commented Jan 22, 2016 at 17:31

Scott Craner · Accepted Answer · 2016-01-22 15:47:44Z

3

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)

answered Jan 22, 2016 at 15:47

Scott Craner

153k10 gold badges52 silver badges88 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

VBA Range not being assigned to an Array

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related