Populating UserForm with an array

Hi All,

I'm populating Combobox1 on UserForm1 with an array: aMasterList. aMasterList contains names of other arrays. When I select an item from ComboBox1 (which is a name of an array) I want to populate ListBox1 with the items contained with the passed array name.


Here's what I thought should work.

This part works fine:

Private Sub UserForm_Initialize()

For i = 1 To UBound(aMasterList)
Me.ComboBox1.AddItem aMasterList(i)
Next i

End sub


This part don't work:

Private Sub ComboBox1_Click()

‘get array name from ComboBox and feed it to ListBox
‘.Value contains array name
For i = 1 To UBound(.Value)

‘add each item from array into listbox
Me.ListBox1.AddItem .Value(i)
Next i

The problem seems to be passing the array name as a variable. For instance, if I populate the array with range names instead of array names the following works

Msgbox range(.value).address

However, this does not work

Msgbox ubound(.value)


So I’m thinking that an array name cannot be passed as a variable. So, if you had the name of an array contained in a variable, any idea how to pass that information to the user form?

Thanks for your help,

David

So I’m thinking that an array name cannot be passed as a variable.
You're thinking right. More generally, there is no generic way in Visual Basic to turn a string into the entity (variable/array/object/whatever) of that name, or to get the name of an entity as a string.
Still, there is a way to emulate this operation, namely by using association lists of some kind. Such a list associates a (string) key with a reference to an entity, just as an array associates an index with a value. This key may, but does not have to, be the name of the entity is is associated with.

A convenient way of implementing this concept is VB's collection (http://msdn.microsoft.com/en-us/library/aa242681(VS.60).aspx) object. Wrapping both the collection and its member entities, if they are not object-types already, into classes is good practice and may even be absolutely necessary, depending on the data structuring.

So, what we would need to know to advise you further is how familiar you are with object-oriented programming, and a little more background info on your application design, especially how those data arrays are set up.

If most of what I've just said makes no sense at all to you, and if the number of arrays in question isn't too large, there's always the Select Case block:
Select Case ComboBox.Value
Case "firstArray": 'use firstArray
Case "secondArray": 'use secondArray
Case "thirdArray": 'use thirdArray
'...
End Select

Not very elegant, but much less involved. Of course, this will only be an option if the names of the arrays are fixed and known.

Sorry to be so long-winded, but the only short answer to your question would have been "no, that can't be done".

Welcome to the forum! :)

I appreciate the advice, looking at collections is on my list, after I learn about forms which I’m finding a tad frustrating.

Here’s where I’m at as the Case approach won’t work for me.

The arrays I’m working with are already named so I read the RefersTo: into a string. I then assign the string to an array. This works fine.

Dim x As Variant, myArray As Variant

‘read the array string
s = ActiveWorkbook.Names(sArrayName).RefersTo

‘clean off { and extra “ from front and end of string
s = Right(s, Len(s) - 3)
s = Left(s, Len(s) - 2)

'split returns 0 based array regardless of option base setting
x = Split(s, Chr(34) & "," & Chr(34))

My problem is working with the x array . This works fine:

For i = 0 To UBound(x)
Debug.Print x(i)
Next i


This won’t work
myArray = x

nor will this
For i = 0 To UBound(x)
myArray(i) = x(i)
Next i

Any suggestions?

Thanks again

Ah, okay, we're talking about named ranges here, as opposed to VBA variables. That makes things simpler. Are both the masterlist and the individual arrays named ranges?

This won’t work
myArray = x
nor will this
For i = 0 To UBound(x)
myArray(i) = x(i)
Next i


The first version should work just fine, are you getting an error? Or do you mean it doesn't do what you intend it to do? The second version won't work because you need to ReDim (http://msdn.microsoft.com/en-us/library/aa266231(VS.60).aspx) a variant before you can use it as an array.

At first I was using the array name as variables then I moved on to trying to get the array elements from the named array.

I’m pretty sure it has something to do with the way the Split function is making the array.

Here is my procedures. The first will build some test arrays. Thanks a million for all your help.

PS: Sorry about posting code this way, I’m not sure how to put it in a box like your doing in your post.

Option Explicit
Dim x As Variant
Public aMasterList() As Variant, aList1() As Variant, aList2() As Variant, aList3() As Variant

Sub MakeArrayList()
'Make some test Named Arrays

aMasterList = Array("aList1", "aList2", "aList3")
aList1 = Array("Green", "Blue", "Yellow")
aList2 = Array("Apple", "Pear", "Grape")
aList3 = Array("Flowers", "Trees", "Bushes")

Names.Add Name:="aMasterList", RefersTo:=aMasterList
Names.Add Name:="aList1", RefersTo:=aList1
Names.Add Name:="aList2", RefersTo:=aList2
Names.Add Name:="aList3", RefersTo:=aList3

End Sub


Sub GetNamedArrayElements()

Dim myarray() As Variant
Dim s As String
Dim i As Long
Dim sArrayName As String

'use this test name
sArrayName = "aMasterList"

'contains the array elements in aMasterList
s = ActiveWorkbook.Names(sArrayName).RefersTo

'Clean out brackets and extra quoate marks
s = Right(s, Len(s) - 3)
s = Left(s, Len(s) - 2)


'Put array elements into array x. Remember split returns 0 based array regardless of _
option base setting
x = Split(s, Chr(34) & "," & Chr(34))


'this works
For i = 0 To UBound(x)
Debug.Print x(i)
Next i

'this don't
aMasterList = x


End sub




Please use the .. tags when you post your code. Edit or reply to this post to see how.

Thank you.

Here's a great tutorial on UserForms :)

Also, may I suggest using the 'List' property of your ComboBoxes; you can simply assign an entire array to this (or the 'Column' property depending on the orientation of the array). This works very well with the 'Value' property of the Range object:

ComboBox1.List = ThisWorkbook.Names("xyz").RefersToRange.Value

Another way to approach the "array of arrays" issue, might be to use the 'ListIndex' property of the ComboBox:

With Me.ComboBox1
Me.Controls("ComboChild" & .ListIndex).list = ArrayOfArrays(.ListIndex)
End With

Public aMasterList As Variant, aList1 As Variant, aList2 As Variant, aList3 As Variant

The problem was in your variable declaration. Split returns a string array, you can test that by putting
Debug.Print TypeName(Split("", ""))

which will produce "String()". Array assignments are a tricky business, and you can't assign a string array to a variant array. You can, however, assign a string array to a variant. Go figure :).
Well, actually, it does make sense if you know a little bit about the inner workings of variants, but there's no need to get into that here. This MSDN page (http://msdn.microsoft.com/en-us/library/aa261343(VS.60).aspx) discusses the array assignment rules, but somewhat superficially.

If you can restructure your design so that Timbo's second suggestion works, it'll make things easier, but from what you said before I gathered that aspect was out of your hands.

I got the first method to work, the problem was my declaration.

I can’t get the below to work because I have the information in a variant array and as a named array. This method seems to want the array as a string. Not really necessary to figure this out as the first approach is now working but I was just wondering.


With Me.ComboBox1
Me.Controls("ComboChild" & .ListIndex).list = ArrayOfArrays(.ListIndex)
End With


Also, thanks for the range tip, that worked great.

Thanks for all your help.