Using a string to reference an array in VBA

[egoburnswell]

I have several global arrays of the form:

A1Array
A2Array
etc...

And I want to use a Combox dropdown to determine the contents of a second combobox.

So the first combobox is populated with:

A1
A2
etc...

I could use Case statements:

Select Case ComboBox1.Value
Case "A1"
For Each Item In A1Array
ComboBox2.AddItem Item
Next Item
Case "A2"
For Each Item In A2Array
ComboBox2.AddItem Item
Next Item
etc
End Select


But I have a lot of arrays, so this is messy.
I would like do something like

combobox2Array = ComboBox1.Value & "Array"

For Each Item In combobox2Array
ComboBox2.AddItem Item
Next Item

To iterate through the correct array, but this doesn't work.
Is there a way to do this, or a better way to achieve the end result?

Thanks.

 

[MickG]

Have you tried:-

Combobox1.list = A1array
Combobox2.list = A2Array

 

[egoburnswell]

I think you misunderstood my question.

The problem is that I can get a string from the first combobox and then use that string to reference an array to populate a second combobox.

simplified example:

combo1array = array("list1", "list2")
list1 = array(a,b,c,d,e,f)
list2 = array(1,2,3,4,5)

combobox1.list = combo1array

Then I select a value in the combo box, say combobox1.value = "list1"

and I want to be able to say

combobox2.list = combobox1.value

But this gives the error " Could not set List property. Invalid property array index."

 

[Jon Peltier]

You could use a collection to store the arrays. A collection allows you to add a key, that is, a string element that names the item. You can then refer to the item by name.

So here's my userform code:

Option Explicit

Dim coll As New Collection

Private Sub combo1_Click()
  On Error Resume Next
  combo2.List = coll(combo1.Value)
End Sub

Private Sub UserForm_Initialize()
  Dim list1 As Variant
  Dim list2 As Variant

  list1 = Array("a", "b", "c", "d")
  list2 = Array(1, 2, 3, 4)
  coll.Add list1, "list1"
  coll.Add list2, "list2"
  Me.combo1.List = Array("list1", "list2")
End Sub

At first I didn't know how useful this might be, but once I dummied up the example, I think I could make use of it.

 

[egoburnswell]

Yes, that does work. Thanks.
It still seems like a messy way of doing it though.

 

[Jon Peltier]

Any time you're using text to reference other variables and objects, it will be messy.

 

[MickG]

This is a another way :-

Private Sub CommandButton1_Click()
'Load Combobox1 with Array Names
ComboBox1.List = Array("List1", "List2")
End Sub


Private Sub ComboBox1_Change()
'Display array selected CB1 in CB2
Dim List1, List2
List1 = Array("a", "b", "c", "d", "e", "f")
List2 = Array(1, 2, 3, 4, 5)
Select Case ComboBox1.Value
    Case "List1": ComboBox2.List = List1
    Case "List2": ComboBox2.List = List2
End Select
End Sub