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.
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.