Call a Collection Object with Variable 1

[rustychef]

I am writing in vba for MSExcel. I have set up procedures to read an xml config file and to store the default values into 3 different collections.

Dim ExportHdrs As Collection
Dim ExpectHdrs As Collection
Dim DeleteHdrs As Collection

I need to know if there is a way to reference a collection object through the use of a string or variable value. I would like to be able to refer to each collection simply by concatenating the action type with the string "Hdrs" (see below). I am having serious problems trying to find information on how to do this.

For instance; if the variable sType holds the first part of the collection name and the string "Hdrs" is the 2nd half...

to get the value of a control on a form, you use:

x = Me.Controls(sType & "Hdrs").value

a recordset:

x = rs.Fields(sType & "Hdrs")

Can the same be done for a collection? How?

x = ??????.??????(sType & "Hdrs")

Thanks in advance!

 

[PHV]

I'd use a Scripting.Dictionary object.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[rustychef]

PHV: Thanks for the reply, but it still wouldnt help me on how to refer to the object "indirectly" at this point it doesnt matter if its a Collection or a Scripting.Dictionary object. once all three (and possibly more in the future) variable names are declared, I need to be able to call upon them using a variable and a string. When these two are concatenated together, they equal the name of the object. For instance:

if sType = "Export", then sType & "Hdrs" = "ExportHdrs" which is the name of one of my Collections. I already know how to load and access the data in the collections, Im just looking for an easier way to "call" them without having to hard-code their name into my vba code.

One of the reasons I need to do this is for future expansion of data listed in the user's config.xml file. If I cant use an "indirect" way of accessing the Collections name property, then everytime there is a new category added to the config.xml file, I will have to hard-code new vba code to handle the new information.

 

[combo]

Why not create a collection of collections?

combo

 

[PHV]

Use sType & "Hdrs" as the key of your main dictionary.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[rustychef]

I probably should have started out by saying that the objects in my collections were arrays, but I was trying to keep it simple. I guess I may have to scrap it and work with recordsets. I SERIOUSLY THOUGHT THAT THERE WAS A QUICK WAY TO ACCESS THE NAME PROPERTY OF A COLLECTION THE SAME WAY YOU CAN WITH A CONTROL ON A FORM.

For example: If I had a textbox on my form called “txtExportHdr” I could place its value in a variable named X “directly” via code by using: X = Me.txtExportHdr.value

Or “indirectly” using the code: (assuming that sType = “Export”) X = Me.Controls(sType & “Hdrs”).value


PHV:
I can’t use sType & “Hdrs” as my key because they already have a key set up (UM, CC, & CJ) see the long explaination below.

combo:
I tried wrapping my brain around creating a collection of collections, but I just couldn’t do it (don’t have the excess time to do that kind of research since Im a fledgling in collections). The problem is that my collections are collections of arrays. ExportHdr is a 2 column array of a worksheets “column headers” and a “masterID”...fairly simple. Now the problem is with the DeleteHdrs and ExpectHdrs. These are also arrays, BUT each collection has 3 arrays (“UM”, “CC”, & “CJ”)

This is a snippet of the code I use to load the data from config.xml:

Dim xmlDoc As MSXML.DOMDocument
Dim xWords As MSXML.IXMLDOMNode
Dim xType As MSXML.IXMLDOMNode
Dim xword As MSXML.IXMLDOMNodeList
Dim xWordChild As MSXML.IXMLDOMNode
Dim oAttributes As MSXML.IXMLDOMNamedNodeMap
Dim oMasterID As MSXML.IXMLDOMNode
Dim oHeader As MSXML.IXMLDOMNode
Dim iELE As Integer
Dim sTEMP() As String
‘AFTER LOADING THE XML DOCUMENT...
For Each xType In xWords.childNodes
iELE = 0
fARR_DIM = False
Set xword = xType.childNodes
For Each xWordChild In xword
Set oAttributes = xWordChild.Attributes
Set oMasterID = oAttributes.getNamedItem("masterID")
Set oHeader = oAttributes.getNamedItem("name")
Select Case (xType.baseName)
Case "ModelType", "ExportWords", "ExpectUM", "ExpectCC", "ExpectECJ"
If Not fARR_DIM Then
ReDim sTEMP(1, xType.childNodes.Length - 1)
fARR_DIM = True
End If
sTEMP(0, iELE) = oMasterID.nodeValue
sTEMP(1, iELE) = oHeader.nodeValue
iELE = iELE + 1
...
End Select
Next xWordChild

‘FROM HERE THE ARRAYS ARE LOADED INTO A COLLECTION
...
Case "Expect"
sTypeBase = Replace(sTypeBase, "Expect", "")
Call ExpectHdrs.Add(sTEMP(), sTypeBase)
ReDim sTEMP(0, 0)
... ETC, ETC


AFTER ALL THAT, THIS IS A QUICK FUNCTION TO TEST READ THE COLLECTION DATA:

sWord = "ExportWords"
‘Function EBound() does the same thing as UBound() but built for this collection of arrays
For i = 0 To EBound(ExportHdrs, sWord, True)
Debug.Print ExportHdrs(sWord)(0, i); " ";
Debug.Print ExportHdrs(sWord)(1, i)
Next i

‘Combobox Me.cboType holds the different type of data that can be accessed (UM, CC, CJ)
For j = 0 To Me.cboType.ListCount - 1
sWord = Me.cboAircraftType.List(j, 0)
‘DeleteHdrs holds single dimensional arrays
For i = 0 To EBound(DeleteHdrs, sWord, False)
Debug.Print DeleteHdrs(sWord)(i)
Next i
‘ExpectHdrs holds mult-dimentional arrays
For i = 0 To EBound(ExpectHdrs, sWord, True)
Debug.Print ExpectHdrs(sWord)(0, i); " ";
Debug.Print ExpectHdrs(sWord)(1, i)
Next i
Next j


‘SAMPLE FROM CONFIG.XML:
<ExportWords>
<name masterID="1" name="ACAD" />
<name masterID="2" name="C1" />
<name masterID="3" name="C2" />
<name masterID="4" name="C3" />
<name masterID="5" name="C4" />
<name masterID="6" name="CodeF" />
</ExportWords>
<ExpectUM>
<name masterID="6" name="CodeF" />
<name masterID="7" name="CodeT" />
<name masterID="19" name="To" />
<name masterID="5" name="Wire" />
</ExpectUM>
<ExpectCC>
<name masterID="9" name="EFF" />
<name masterID="11" name="FROM" />
<name masterID="19" name="TO" />
<name masterID="20" name="NO" />
</ExpectCC>
<ExpectCJ>
<name masterID="11" name="FROM PL" />
<name masterID="19" name="TO PL" />
<name masterID="20" name=" NUMBER" />
</ExpectCJ>

 

[PHV]

FYI, a Dictionary can hold heterogeneous object.

 

[combo]

Using collections:

Dim col As Collection
Dim col1 As Collection
Dim col2 As Collection
Set col = New Collection
Set col1 = New Collection
Set col2 = New Collection
col.Add col1, "colection1"
col.Add col2, "collection2"
' fill collection 1
' fill collection 2
' this returns reference to collection 1 object (col1):
col.Item("collection1")

combo

 

[rustychef]

Kudos to you combo.

I had already coded just what you sent to me and I couldn't get it to work. I kept getting a Object variable or with block variable not set. I was already frustrated with everything else enough that I didnt realize that I left out my SET statement. I just thought I wasnt addressing the arrays correctly. After looking at what you wrote I thought I'd try it one more time and this time I coded it correctly (WITH a Set statement) and it works just great.

Dim oColl As Collection
Set oColl = New Collection

'My 3 previously defined collections
oColl.Add DeleteHdrs, "Delete"
oColl.Add ExpectHdrs, "Expect"
oColl.Add ExportHdrs, "Export"

Debug.Print oColl.Item("Expect")("UM")(0, i); " ";
Debug.Print oColl.Item("Expect")("UM")(1, i)

Printed out the first two values of the correct array data. Thanks. Im still surprised that you cant "indirectly" call a collection object with a variable.