WROX Excel 2002 VBA has a brief section about storing arrays in the Names Collection of a workbook. (pg. 133)
I'm trying to implement this using a 2-dimensional array created from the GetNames method of the Recordset object. The result of this is an array I named arRecordset.
The array is created and stored as a workbook name using:
Names.Add Names:="thisArray", RefersTo:=arRecordset
When I look at the Name thisArray, I see the following structure:
={"A","B","C","D","E";"Item1","Item2","Item3","Item4","Item5"}
In my VBA Module, I have the following code:
=================================
Private Sub opHWAssy_Click()
Dim HWAssy, n As Integer, x As Variant
thisCtrl.Clear
x = [thisArray] ' corrected
For n = 1 To UBound(x, 1)
thisCtrl.AddItem x(0, n)
Next
End Sub
=================================
thisCtrl is an object variable pointing to a combobox.
When I run this code, x evaluates as "empty", yet the named array in fact exists when I check the Names Collection for the workbook.
If I substitute the following code for x = [thisArray] ' corrected
x = Names("thisArray") ' corrected
then x evaluates as "={"A","B","C","D","E";"Item1","Item2","Item3","Item4","Item5"}"
The String result causes the UBound function to fail.
I don't know how to extract the array saved as a name in a form that I can use it.
Where am I going wrong?
I'm trying to implement this using a 2-dimensional array created from the GetNames method of the Recordset object. The result of this is an array I named arRecordset.
The array is created and stored as a workbook name using:
Names.Add Names:="thisArray", RefersTo:=arRecordset
When I look at the Name thisArray, I see the following structure:
={"A","B","C","D","E";"Item1","Item2","Item3","Item4","Item5"}
In my VBA Module, I have the following code:
=================================
Private Sub opHWAssy_Click()
Dim HWAssy, n As Integer, x As Variant
thisCtrl.Clear
x = [thisArray] ' corrected
For n = 1 To UBound(x, 1)
thisCtrl.AddItem x(0, n)
Next
End Sub
=================================
thisCtrl is an object variable pointing to a combobox.
When I run this code, x evaluates as "empty", yet the named array in fact exists when I check the Names Collection for the workbook.
If I substitute the following code for x = [thisArray] ' corrected
x = Names("thisArray") ' corrected
then x evaluates as "={"A","B","C","D","E";"Item1","Item2","Item3","Item4","Item5"}"
The String result causes the UBound function to fail.
I don't know how to extract the array saved as a name in a form that I can use it.
Where am I going wrong?