Is there a way to call array member by key instead of index?

[gifariz]

I have been using an awkward method to store and call my UDT arrays by key.
That is by making an array and corresponding collection for storing the array index and calling the content by key, please see my code below.
Is there better practice for this in VBA? That maybe can call array member by key without making additional variable (collection) to store/call the array index by key.

Type tSampleType
     Name As String
     nData As Long
     DataX() As Double
     DataY() As Boolean
End Type

Sub SampleSub()

     Dim ArrayA() As tSampleType
     Dim ArrayA_ID As New Collection
     Dim i As Long, Name As String, DataX As Double
     
     'Store array
     ReDim ArrayA(99)
     For i = 0 To 99
          With ArrayA(i)
               .Name = "abc" & i
               ArrayA_ID.Add i, .Name
               nData = 1000
               ReDim .DataX(nData - 1), .DataY(nData - 1)
               'and so on
          End With
     Next i

     'Calling array member by key
     Name = "abc0"
     i = ArrayA_ID(Name)
     DataX = ArrayA(i).DataX(123)

End Sub

 

[bebo021999]

I am not understand what UDT arrays doing, but using 2D array may helps

Dim ArrayA(), i&, j&, Name as String
Redim ArrayA( 1 to 100, 1 to 4) ' array with 100 members down and 4 items accross (1 member & 3 items)
For i = 1 to 100
    For j = 1 to 4 
          ArrayA(i,1) = "abc" & i
          ArrayA(i,2) = ...
          ArrayA(i,3) = ...
          ArrayA(i,4) = ...
   Next
Next

'Get member2, items 3
Debug.print ArrayA(2,3)

 

[gifariz]

I am not understand what UDT arrays doing, but using 2D array may helps

Dim ArrayA(), i&, j&, Name as String
Redim ArrayA( 1 to 100, 1 to 4) ' array with 100 members down and 4 items accross (1 member & 3 items)
For i = 1 to 100
    For j = 1 to 4
          ArrayA(i,1) = "abc" & i
          ArrayA(i,2) = ...
          ArrayA(i,3) = ...
          ArrayA(i,4) = ...
   Next
Next

'Get member2, items 3
Debug.print ArrayA(2,3)

UDT is for storing complex structured data, like array inside array, with clear naming/key of each sub variable.
In your code, the array is called by index (e.g. 2, 3) instead of key/name like what I need.

 

[bebo021999]

UDT is for storing complex structured data, like array inside array, with clear naming/key of each sub variable.
In your code, the array is called by index (e.g. 2, 3) instead of key/name like what I need.

Another solution is using dictionary (for unique members) or collection, with members stored as key, and an array (or another dictionary or collection) stored as items.
Like this (collection sample)

Dim col as new collection
Dim arrayA 
For i = 1 to 100
    arrayA = array("a","b","c","d")
   col.add i, arrayA

To get the final working code, try to upload an actual sample sheet.

 

[gifariz]

Another solution is using dictionary (for unique members) or collection, with members stored as key, and an array (or another dictionary or collection) stored as items.
Like this (collection sample)

Dim col as new collection
Dim arrayA
For i = 1 to 100
    arrayA = array("a","b","c","d")
   col.add i, arrayA

To get the final working code, try to upload an actual sample sheet.

Thanks for your answer. Sorry for late reply.

So I now understand that collection and dictionary can have array or another dictionary/collection as items.

One more question:
Is it true that collection or dictionary can't have UDT as item?
Because I really need UDT for storing my data, it is very convenient and clear for complex structured data.

I got this error when trying it:

Only public user defined types defined in public object modules can be used as parameters or return types for public procedures of class modules or as fields of public user defined types

Office VBA reference topic

learn.microsoft.com

Type tSubData
    SubDataA As Double
    SubDataB As Double
End Type

Type tMainData
    Name As String
    nData As Long
    DataX() As tSubData
End Type

Sub xx()

Dim VarA As tMainData

Dim myDict As Scripting.Dictionary
Set myDict = New Scripting.Dictionary

Dim myColl As New Collection

VarA.Name = "Name1"

myDict.Add "Name1", VarA
myColl.Add VarA, "Name1"

End Sub

Sorry I cant upload sample sheet because the actual code is too long, it is actually an API to process data from other software so other people can't run it anyway.