arraysvbavariables

Can I substitute an array name for a variable and add a value to it?


I have VBA code which loops through a dataset and depending on certain parameters, stores elements of that data within 1 of 4 public, 3D arrays (as data from the arrays is called later from another function). The structure of the arrays is the same (as in the same number of dimensions) but the size of each 3rd dimension of each array varies, so each is Redim'd to full size, then data is added procedurally. The different 3rd dimension size is the reason for having 4 separate arrays, as I also output the array info as a summary into a sheet later on.

The code runs through Ifs and Selects, then adds the data to an array in a separate function.

I want to also determine which array the data is sent to based on the looped data, and use a variable to store the array name, passing it to the function which actually stores the data.

Something like this pseudocode:

Public Arr1(), Arr2(), Arr3(), Arr4()

Sub ProcessData()
Dim WhichArray, Dim1, Dim2, Dim3 'Also tried WhichArray as Variant

For Each datum in Data
'Ifs and Selects, etc to determine what array the data belongs to and to Redim each array to accommodate.
'e.g. datum belongs in Arr3(3, 1, 2)
    WhichArray = Arr3 ' Also tried Set WhichArray = Arr3 here too.
    Dim1 = 3
    Dim2 = 1
    Dim3 = 2

    Call fnAddToArray(WhichArray, 3, 1, 2, datum) 'I've tried "ByRef WhichArray As Variant" here too
Next datum
End Sub

Then I want to call the sub:

Sub fnAddToArray(VarArray, Dim1, Dim2, Dim3, Datum)

VarArray(Dim1,Dim2,Dim3) = Datum

End Sub

Because there is so much data and it can go into any 1 of these 4 arrays, I want to be able to pass the array name as a variable to the Sub, but this just generates a new array called VarArray and stores it there instead of using the Arr3 as stored within the variable.

I've also tried a jagged array to store the arrays in, then simply pass the index to the sub and store the value in the inner array, as I'd seen this done elsewhere on SO:

Public DataArrays()
DataArrays = Array(Arr1, Arr2, Arr3, Arr4)
...'same For loop as before, datum belongs in Arr3(3, 1, 2)
    WhichArray = 2 '(0 base array)
    Dim1 = 3
    Dim2 = 1
    Dim3 = 2

    Call fnAddToArray(WhichArray, Dim1, Dim2, Dim3)
Next datum
End Sub

With the Sub:

Sub fnAddToArray(ArrInd, Dim1, Dim2, Dim3, Datum)

DataArrays(ArrInd)(Dim1, Dim2, Dim3) = Datum

End Sub

This just returns type mismatch errors at runtime, even declaring as Variants.

Is what I want to do possible? If so, what am I doing wrong here?

===================EDIT after Tim's answer================

This is closer to my actual code, in case I missed something crucial:

Public arrAZVATSummData(), arrEBSummData(), arrWSSummData(), arrAZWSSummData() 'Arrays to store the summarised data for output
Public arrSummDatas(1 To 4)

Sub ProcessData()
arrSummDatas(1) = Array(arrAZVATSummData)
arrSummDatas(2) = Array(arrEBSummData)
arrSummDatas(3) = Array(arrWSSummData)
arrSummDatas(4) = Array(arrAZWSSummData)

Call fnProcessAZVAT 'AZVAT data has a different structure, stored in arrSummDatas(1)
Call fnProcessWS    'WS data contains EB, WS and AZWS data, stored in arrSummDatas(2),(3) or (4) and is the reason I want to do this this way if possible.
'Do more stuff, output a summary, etc.
End Sub



Sub fnProcessAZVAT()
Dim SummArrayNo As Long
Dim D3size, D1, D2, D3, Value 
SummArrayNo = 1 'I want all data to go to arrAZVATSummData, for this sub

'...Code to determine 3rd dimension size
ReDim Preserve arrAZVATSummData(12, 3, D3Size) 'Can I do Redim Preserve arrSummDatas(SummArrayNo)(12, 3, D3size) here?
'I also think Preserve isn't necessary as no data has yet been passed to the array?

'...For Loop to process data, result:
   D1 = 2
   D2 = 1
   D3 = 3
   Value = 18.99

   Call AddToDataSummArray(SummArrayNo, D1, D2, D3, Value)
'Next item
End Sub

Sub fnProcessWS()
Dim SummArrayNo As Long
Dim D3Size, D1, D2, D3, Value 

'...Code to determine 3rd dimension size (same for all 3 child arrays)
ReDim Preserve arrEBSummData(12, 3, D3Size)
ReDim Preserve arrWSSummData(12, 3, D3Size)
ReDim Preserve arrAZWSSummData(12, 3, D3Size)

'...For Loop to process data, result:
   SummArrayNo = 2 'this value belongs in EB, determined by loop.
   D1 = 11
   D2 = 0
   D3 = 5
   Value = 29.99

   Call AddToDataSummArray(SummArrayNo, D1, D2, D3, Value)
'Next item
End Sub

Sub AddToDataSummArray(SummArrayNo As Long, D1, D2, D3, Value)

arrSummDatas(SummArrayNo)(D1, D2, D3) = arrSummDatas(SummArrayNo)(D1, D2, D3) + Value ' Currently throws subscript out of range error on 1st call.

End Sub

Solution

  • This worked for me:

    Option Explicit
    
    Public DataArrays(1 To 4)
    
    Sub tester()
        Dim i As Long
        
        InitArrays 'populate the global array
        
        fnAddToArray 2, 1, 3, 44 'update a couple of arrays stored in DataArrays
        fnAddToArray 4, 1, 5, 99
        
        'dump the updated arrays back to the sheet
        For i = 1 To 4
            ActiveSheet.Cells(i, 7).Resize(1, 5).Value = DataArrays(i)
        Next i
    
    End Sub
    
    'Populate `DataArrays` with 2D arrays read from a worksheet
    Sub InitArrays()
        Dim i As Long
        For i = 1 To 4
            DataArrays(i) = ActiveSheet.Cells(i, 1).Resize(1, 5).Value
        Next i
    End Sub
    
    Sub fnAddToArray(ArrInd As Long, Dim1 As Long, Dim2 As Long, Datum)
        DataArrays(ArrInd)(Dim1, Dim2) = Datum
    End Sub
    

    Before/after values on worksheet:

    enter image description here