Passing a Excel table to array in VBA

[rkaczano]

I have a routine that loads data into a array as follows:

Dim locations() As Variant
locations = Array(Array("San Francisco", 37.7749, -122.4194), _
Array("London", 51.5074, -0.1278), _
Array("Sydney", -33.8599, 151.2111))

I reference the data in the locations object elsewhere in the code as follows:

For i = 0 To UBound(locations)
Dim locationName As String
locationName = locations(i)(0)
Dim lat As Double
lat = locations(i)(1)
Dim lng As Double
lng = locations(i)(2)
Next

And this works.

I now want to instead store the data in a table and read that table into the same locations variable. I want the full table with headers and I am using the following.

Set TL = Sheet1.ListObjects("Table1")
Debug.Print TL.ListRows.Count
Dim locations As Variant
locations = TL.Range 'using .Range as I want the headers too

But it fails on locationName = locations(i)(0) fails when I do this with a Run-time error '9' Subscript out of range.

Why is this?

Thanks in advance.

 

[Eric W]

Because when you load a table like this, it's a 1-based array meaning the lowest index is 1, not 0. Also, it stores the data in a 2-D array, not an array of arrays. So locations(1,1) would return the heading for column 1, and locations(2,1) would return the first data from column 1.