Hey all,

I'm trying ot figure out how to build code dynamically in vba (excel) by dynamically building the commands as strings and then evaluating the strings. Ín MATLAB, the command is just eval(string_to_evaluate_as_command) and you can use it for just the right hand side of an assignment or for an entire command.

e.x eval("i = 6") sets the value of i to 6
or eval("intControlName_7 = lblsomelabel.name") assigns intControlName_7 the name of the label as a string and the string inside the eval command can be built dynamically

I have a bunch of collections the have names such as Col1, Col2, Col3, etc. and from a string that is passed into a function, I want to access the items one of the collections. However, I need to build the name of the collection that I want to access because I don't know which one will be needed until runtime. It seems redundant and would create vastly bloated code to keep putting in case statements everywhere.


Thanks,
Puck312

Collections items have a unique key (string) that can be used to get the element.

I would create a "master" collection that hold the "inner" collection assigning to them unique keys, like:

myMasterCollection.Add Col1, "Col1"
myMasterCollection.Add Col2, "Col2"

do the same with the inner collections, that is assign to each element a unique string key. To access the elemnt "foo" of the collection "colx" use:

dim c as Collection
set c = myMasterCOllection.Item("colx")
c.item("foo")

Marco

marcostraf,

I tried implementing a collection of collections like you suggested. It works fine as long as I don't need to access anything inside the sub-collections by item number instead of key, or try to use the count method because data that I add to one sub-collection, gets added to all of them.

In my form initialization event I have:

Dim c As Collection
Set c = New Collection

Set colDeck = New Collection
Set colDiscard = New Collection
Set colPlayed = New Collection

colPlayed.Add c, "colTop"
colPlayed.Add c, "Col1"
colPlayed.Add c, "Col2"
colPlayed.Add c, "Col3"
colPlayed.Add c, "Col4"
colPlayed.Add c, "Col5"
colPlayed.Add c, "Col6"
colPlayed.Add c, "Col7"


And then in a cmd button click event I have:

Dim c As Collection
Dim labelname as String
Dim lbl As Card

Set c = colPlayed.Item("ColTop")
Set lbl = New Card
labelname = "lblColTop_Deck"
c.Add lbl, labelname
Set lbl = Nothing

At this point, all the sub-collections (ColTop, Col1, Col2, etc.) contain the data from the card "lbl"

How do I get the data to only be added to one of the sub-collections instead of all of them?

you added the same collection to all of the other ones. you have to do the other way around...

''this will be the "master" collection (and c is not a good name for it)
Dim c As Collection
Set c = New Collection

' define your collections
Set colDeck = New Collection
Set colDiscard = New Collection
Set colPlayed = New Collection

'' add them to "c"
c.Add colDeck , "colDesk"
c.Add colDiscard , "colDiscard"
c.Add colPlayed , "colPlayed"

''to get for example colDeck:
dim cd as collection
set cd = c.Item("colDeck")

Marco