Using variables to load a table column into an array

[George]

Hi All,

I'm having a little trouble with loading a column from a table to an array. I've posted the code below, and the error message I get is "type mismatch", but I thought you could assign anything to a Variant (please let me know if I'm wrong about this).

Thanks in advance,

George

Public Sub GenerateAppts()
Dim InstRng As Range
Dim InstArr() As Variant
Dim InstTypeArr() As Variant
Dim AllTypeArr() As Variant
Dim i As Double
Dim j As Double
Dim iws As Worksheet
Dim sws As Worksheet
Dim iTblNm As String


Set sws = ThisWorkbook.Sheets("Summary")
Set InstRng = ThisWorkbook.Sheets("Summary").Range("InstanceList")
    InstArr() = InstRng

'loop through all instances and run SQLappts on it, adding worksheets as required
    For i = 1 To UBound(InstArr)
        SQLappts CStr(InstArr(i, 1))
'set iws as the worksheet name for the instance (error handling for this covered in SQLappts)
Set iws = ThisWorkbook.Sheets(CStr(InstArr(i, 1)))
        iTblNm = CStr(InstArr(i, 1)) & "Tbl"
'make sure the InstTypeArr array is empty
        Erase InstTypeArr
'set the InstTypeArray as the Type column from the instance table.  NOT WORKING!
        InstTypeArr() = iws.Range(iws.iTblNm & "[Type]")
        For j = LBound(InstTypeArr) To UBound(InstTypeArr)
'stuff will happen here
        Next j
    Next i
End Sub

 

[NARAYANK991]

Hi George ,

You are right that anything can be assigned to a Variant , but in this case , your declaration is NOT a Variant.

A declaration such as :

Dim InstArr() As Variant

is NOT a declaration of a Variant ; it is a declaration of an Array of Variants.

The parentheses following the variable name tell Excel to expect an array , whose dimensions are not known ; these can be defined later through a ReDim declaration.

Just omit the parentheses , and declare your variable as :

Dim InstArr As Variant

Narayan

 

[George]

Cheers Narayan, but I need it to be an array so I can cycle through the values.
Basically this bit:
InstTypeArr() = iws.Range(iws.iTblNm & "[Type]")
should be an array containing whatever is in the column [Type] in whatever table we're dealing with (table name stored as iTblNm). I will then be using this to build a list of unique values across all the sheets.
I could use a range instead of loading it into an array I suppose, but this is going to get pretty big and excel seems to handle arrays faster than ranges (am I right about this?).

 

[NARAYANK991]

Hi George ,

Probably I have misunderstood your requirement ; is your requirement to load a table into an array ?

To load a table or a table column into an array , you can do the following :

Dim Array_To_Hold_Table_Column as Variant

Array_To_Hold_Table_Column = [TableName[FieldName]]

Narayan

 

[George]

slightly confused about this - it doesn't look like Array_To_Hold_Table_Column is an array, so how do you use it?

 

[NARAYANK991]

Hi George ,

The variable which is declared as a Variant , will assume the number of rows and number of columns in the range which it is assigned.

For example , let us first consider that we have a table named Table1 , with 11 columns ; we can assign this to our earlier named variable Array_To_Hold_Table_Column , by the following lines of code :

Dim Array_To_Hold_Table_Column as Variant

Array_To_Hold_Table_Column = [Table1]

If you now look at the number of columns in the array , you will see :

?UBound(Array_To_Hold_Table_Column,2)

shows 11.

The number of rows in the variable Array_To_Hold_Table_Column will be the number of rows in Table1.

Now , let us consider that we have a column named Item Number ; assigning the data in this column to a variant with the name Array_To_Hold_Table_Column is done by the statement :

Array_To_Hold_Table_Column = [Table1[Item Number]]

The only thing to remember here is that though the right hand side is a single column , the variable still has 2 dimensions , and any element in the variable will need to be accessed using both indices , as in :

Array_To_Hold_Table_Column( 1 , 1 )

Array_To_Hold_Table_Column( 2 , 1 )

Array_To_Hold_Table_Column( 3 , 1 )

The column index will remain 1 , since the input range was only one column wide. You can see this using :

?UBound(Array_To_Hold_Table_Column,2)

will show 1.

Narayan