3D Datastructure with Index

Question

This is my first question on stackoverflow, and I am earnestly open to feedback on how/where/when to ask better questions and how to contribute to stackoverflow better.

Background:
My ultimate goal is to graph projected equipment usage by date at various test labs.

I have identical equipment in use at several labs, and I'm creating a sheet that will show me a future projection of equipment usage at each lab.

What I'm Starting With:
I have an Excel document with several worksheets, each containing information on what equipment will be used at which test house during what period of time.

My Goal:
To create a graph of equipment usage for each test lab. The graph will show how many of each piece of equipment are in use for a given date. My intention is to have a chart series for each type of equipment with Date as the X-axis and the number of pieces of that equipment in use on the Y-axis.

What I've Done So Far:
I have written code that loops through all my information sheets and creates a vba collection of every unique test lab name and a separate vba collection of every unique piece of equipment I want to track. This code also finds the first date and last date any piece of equipment is used.

Help Request:
Because I essentially have three "dimensions" - Test Lab, Piece of Equipment, and Equipment Use Date - I had planned to use a 3D array to aggregate all my data and provide the source for my usage graphs. This array would have equipment as one dimension, date as the second, and test lab as the third.

However, as I've considered this implementation, it seems rather clumsy. It will hold all my data, but, as far as I can see, I can't refer to the elements of the array by keys or labels. I would have to create separate 2D arrays to hold index labels for each dimension of the 3D array.

Is there a 3D data structure in Excel VBA that supports index keys for each dimension?

Failed Searches and Attempts:
I first tried to create a unique array to hold equipment and usage date, each array named for a unique test lab. I learned from this post that I am not able to dynamically create and name an undefined number of new arrays within a sub: Naming an array using a variable.

I then looked into whether I could use the collections I had already created to somehow function as labels for the array indices, but it seems that I'm not able to find the collection index by the key. I would have to loop through the collection to find the index every time I want to reference an element in the 3D array: Retrieve the index of an object stored in a collection using its key (VBA).

you might look along the lines of structures or classes in vba. They are a way to keep multiple pieces of related information together, which you can create a list or collection of. check out this article to see if this is what you are after. https://stackoverflow.com/questions/12414168/use-of-custom-data-types-in-vba — Jeremy, Mar 06 '18 at 17:33
You can consider using nested `scripting.dictionaries` or `collections`. External `Dictionary` holds lab IDs as keys and each item is another `dictionary` that holds equipment IDs as keys and dates as items. — Victor K, Mar 06 '18 at 18:26
Thanks, all, for your comments! I'm attempting to create a custom data type as modeled in Jeremy's suggested article with scripting.dictionaries contained within the type as Victor K and HackSlash suggested. How should I show the solution when I get it working? Would I answer my own question? — TempleGuard527, Mar 06 '18 at 20:37

score 1 · Answer 1 · answered Mar 06 '18 at 18:18

If you need to call out a collection by key, that collection should instead be declared as a dictionary.

Dim dict As Object
Set dict = CreateObject("Scripting.Dictionary")
dict(Key) = Value

It is much more powerful than a collection. I hope that helps.

FULL INFORMATION: https://excelmacromastery.com/vba-dictionary/

score 0 · Answer 2 · answered Mar 07 '18 at 19:14

I implemented information from all the comments and answers I received. Thank you Jeremy, Victor K, and HackSlash!

Here's the solution that worked for me in a nutshell: An array of a user-defined data type containing arrays of a user-defined data type containing scripting dictionaries, i.e. an array of arrays of dictionaries. I also created reference dictionaries for use in retrieving data. (See working example below)

First, in order to use scripting dictionaries in VBA, go to Tools > References and check the box next to "Microsoft Scripting Runtime." I learned this here: Does VBA have Dictionary Structure?. I also learned that this setting is included if the sheet is distributed (others won't have to enter VBA and check the box before they can use your sheet): http://www.snb-vba.eu/VBA_Dictionary_en.html.

Public Type ItemTracked
    ItemName As String
    UseDates As Scripting.Dictionary
End Type

Public Type TrackingStructure
    TestLab As String
    TrackedItems() As ItemTracked
End Type

Sub Tracking()

Dim TrackingArr() As TrackingStructure

'**************
'Example Data
'**************
    'Create array of example dates
    Dim DateArray As Variant
    DateArray = Array(43164, 43171, 43178) 'Excel date codes for 3/5/2018, 3/12/2018, and 3/19/2018

    'Create array of example equipment
    Dim EquipArray As Variant
    EquipArray = Array("Cooling Pump", "Heating Pad", "Power Supply")

    'Create array of example number of pieces of equipment in use
    Dim UseArray As Variant
    UseArray = Array(0, 1, 2)

    'Create array of example test lab names
    Dim LabNames As Variant
    LabNames = Array("LabABC", "Lab123", "LabDOREMI")

'**************
'Creating and Populating Data Structure
'**************

    'Create array of TrackingStructure Type with space to track test labs
    ReDim TrackingArr(UBound(LabNames))

    'Loop through TrackingArr to populate usage for each test lab
    For i = LBound(TrackingArr) To UBound(TrackingArr)

        'Record lab name
        TrackingArr(i).TestLab = LabNames(i)

        'Redimension size of TrackedItems to accomodate example equipment
        ReDim TrackingArr(i).TrackedItems(UBound(EquipArray))

            'Loop through EquipArray for each test lab
            For j = LBound(EquipArray) To UBound(EquipArray)
                Set TrackingArr(i).TrackedItems(j).UseDates = New Scripting.Dictionary
                TrackingArr(i).TrackedItems(j).ItemName = EquipArray(j)

                'Loop through dates and usage for each piece of equipment
                For k = LBound(DateArray) To UBound(DateArray)
                    'Populate date and equipment use
                    TrackingArr(i).TrackedItems(j).UseDates.Add DateArray(k), UseArray(k)
                Next k
            Next j
    Next i

'**************
'Referencing Data
'**************

'Create and Populate Dictionaries for Use in Referring to Data
Set LabNamesRef = New Scripting.Dictionary
Set EquipArrayRef = New Scripting.Dictionary

For i = LBound(TrackingArr) To UBound(TrackingArr)
    LabNamesRef.Add TrackingArr(i).TestLab, i
Next i

For i = LBound(EquipArray) To UBound(EquipArray)
    EquipArrayRef.Add EquipArray(i), i
Next i

'Demonstration Print of Entire Data Structure
For i = LBound(TrackingArr) To UBound(TrackingArr)
    Debug.Print "Lab Name: " & TrackingArr(i).TestLab
    For j = LBound(TrackingArr(i).TrackedItems) To UBound(TrackingArr(i).TrackedItems)
        Debug.Print TrackingArr(i).TrackedItems(j).ItemName
        For k = 0 To TrackingArr(i).TrackedItems(j).UseDates.Count - 1
            Debug.Print TrackingArr(i).TrackedItems(j).UseDates.Keys(k), TrackingArr(i).TrackedItems(j).UseDates.Items(k)
        Next k
    Next j
Next i

'Access One Example Entry
Debug.Print "Lab Name:" & TrackingArr(LabNamesRef("Lab123")).TestLab
Debug.Print "Equipment:" & TrackingArr(LabNamesRef("Lab123")).TrackedItems(EquipArrayRef("Cooling Pump")).ItemName
Debug.Print "Usage on Date 43164: " & TrackingArr(LabNamesRef("Lab123")).TrackedItems(EquipArrayRef("Cooling Pump")).UseDates(43164)

End Sub

3D Datastructure with Index

2 Answers2