nanoteknz nanoteknz - 9 months ago 61 Question

VB Excel Using a column in a table as an array

I am quite new to VB scripting so I am probably missing something quite simple.

I have an excel spreadsheet where there are two tables on two different sheets, and am trying to join the results as text to feed back into a different table.

I am using the following sample VB which results in a Subscript out of range error.

Sub ColCars_with_table()

Dim myTableCars As ListObject
Dim myTableColours As ListObject
Dim carAlias As Variant
Dim colourAlias As Variant
Dim x As Long
Dim y As Long

Set myTableCars = Sheets("Cars").ListObjects("CarTable")
Set myTableColours = Sheets("Colours").ListObjects("ColourTable")

carAlias = myTableCars.ListColumns(1).DataBodyRange
colourAlias = myTableColours.ListColumns(1).DataBodyRange

For x = LBound(carAlias) To UBound(carAlias)
For y = LBound(colourAlias) To UBound(colourAlias)
MsgBox ("Colour and make is " & colourAlias(y) & " " & carAlias(x))
Next y
Next x

If I convert the VB to have straight arrays I can get it to work:

Sub ColCars_without_table()
'this works
carAlias = Array("Subaru", "Toyata", "Ferrari", "Volkswagen", "Audi", "BMW", "Fiat", "Mazda")
colourAlias = Array("Red", "Blue", "Yellow")

For x = LBound(carAlias) To UBound(carAlias)
For y = LBound(colourAlias) To UBound(colourAlias)
MsgBox ("This is a " & colourAlias(y) & carAlias(x)) 'this will produce the correct results
Next y
Next x

End Sub

I am probably doing something fundamentally wrong with the way I am getting the data out of the tables and hope someone might be able to steer me in the right direction.

Sam Sam
Answer Source

I would suggest using a named range to define the Colors and Cars, then populating a variant array in VBA via the named range.

The first step using this approach would be to navigate to Formulas -> Name Manager in the Ribbon and create two named ranges, one for your cars and one for your colors.

Once the named ranges are defined, you can reference them in code like below

Sub ColCars_without_table()

Dim carArray() As Variant
Dim colorArray() As Variant

carArray = Range("Cars")        'Populates variant array with values from Cars Named Range
colorArray = Range("Colors")    'Populates variant array with values from Colors Named Range

For x = LBound(carArray, 1) To UBound(carArray, 1)
    For y = LBound(colorArray, 1) To UBound(colorArray, 1)
        MsgBox ("This is a " & colorArray(y, 1) & carArray(x, 1))
    Next y
Next x

End Sub