Using Names to store an Array

[dsimcox]

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?

 

[pgc01]

Hi dsimcox

The name you have created is thisArray and so this is the one you should evaluate.

I post a small example where I store a 2-dimensional array in a name, then retrieve it and display the values.

Run this code:

Sub NameArray()
Dim arr, i As Integer

arr = [{"A","B","C";"Item1","Item2","Item3"}]

Names.Add Name:="thisArray", RefersTo:=arr

x = [thisArray]

For i = LBound(x, 2) To UBound(x, 2)
    MsgBox x(1, i) & " : " & x(2, i)
Next i

End Sub

Hope this helps
PGC

 

[dsimcox]

Good catch!

Actually, in my userform module, the correct reference is in place.
In my post, all occurrences of arRecordSet in my code should read thisArray.

Thanks for your example - and it works as advertised.

For some reason, my larger array is not responding to the Evaluate method (x = [thisArray]). It comes up Empty.

If I try to extract the array by using x = Names("thisArray"), I get the String representation which is entirely unexpected.