0

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.

Community
  • 1
  • 1
wooobie
  • 345
  • 2
  • 12
  • 3
    Try `Debug.Print TestArray(5,1)` Though it is one column it is still a two dimensional array – Scott Craner Jan 22 '16 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 Jan 22 '16 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 Jan 22 '16 at 15:44
  • @JohnColeman Don't worry I plan to trim the range to its actual size before assigning it to an array. – wooobie Jan 22 '16 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: http://stackoverflow.com/a/31144434/4996248 – John Coleman Jan 22 '16 at 17:31
  • Neat, I may use this. Thanks @JohnColeman – wooobie Jan 22 '16 at 20:18

1 Answers1

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)
Scott Craner
  • 148,073
  • 10
  • 49
  • 81