Using Names to store an Array

dsimcox

Board Regular
Joined
Dec 8, 2004
Messages
75
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?
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,887
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:
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

Board Regular
Joined
Dec 8, 2004
Messages
75
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.
 

Forum statistics

Threads
1,181,658
Messages
5,931,268
Members
436,785
Latest member
KingGideon

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top