Choose which array to use based on a variable in VBA

Question

Is it possible to choose which array to populate based on a variable name ?

I have 4 sets of data which belong to Yellow, Blue, Green and Lilac I have created 4 Arrays which are exactly the same. The data is the same except it has Y,B,G or L at the start and I want to populate the corresponding array, but don't want to create 4 sets of everything.

For i = 0 To UBound(myLines)
     myAlrmStrDate = Mid(myLines(i), 6, 2) & "/" & Mid(myLines(i), 4, 2) & "/" & Mid(myLines(i), 2, 2)
     myAlrmStrTime = Mid(myLines(i), 9, 2) & ":" & Mid(myLines(i), 11, 2) & ":" & Mid(myLines(i), 13, 2)
     myAlrmEndDate = Mid(myLines(i), 38, 2) & "/" & Mid(myLines(i), 36, 2) & "/" & Mid(myLines(i), 34, 2)
     myAlrmEndTime = Mid(myLines(i), 41, 2) & ":" & Mid(myLines(i), 43, 2) & ":" & Mid(myLines(i), 45, 2)
     myFaultDesc = (Mid(myLines(i), 49, 50))
     mySorter = (Mid(myLines(i), 53, 1))
        myCtime = CDate(myAlrmStrTime)
        myMinutes = Hour(myCtime) * 3600 + Minute(myCtime) * 60 + Second(myCtime)
        myftime = Int(myMinutes / 900) * 900
        myftime = myftime / 86400 * 96

  ' Find IOTT Alarms
     If InStr(1, (Mid(myLines(i), 49, 50)), "IOTT") And InStr(1, (Mid(myLines(i), 49, 50)), "CSC ack") <> 0 Then
        myTimeDiff = CDate(myAlrmEndTime) - CDate(myAlrmStrTime)
        mySorter(myftime).IOTT = mySorter(myftime).IOTT + 1
        mySorter(myftime).IOTTDUR = mySorter(myftime).IOTTDUR + myTimeDiff
        mySorter(96).IOTT = mySorter(96).IOTT + 1
          If mySorter(myftime).IOTT > mySorter(98).IOTT Then
            mySorter(97).IOTT = myftime
            mySorter(98).IOTT = mySorter(myftime).IOTT
          End If
       MsgBox "Line" & "=" & i & myFaultDesc
     End If

In the code above the line mySorter = (Mid(myLines(i), 53, 1)) selects the Y,B,G or L and it is this which I then want to set the Array to use to start populating the array.

Essentially I want it to point to the arrays below but don't want to create complicated if statements etc

Y(myftime).IOTTDUR
B(myftime).IOTTDUR
G(myftime).IOTTDUR
L(myftime).IOTTDUR

my Current Array is set up as follows

Type Sorter
     Date                As Date
     time                As Date
     Colour              As Long
     Area                As String
     Cart                As Long
     ISD                 As Long
     WNI                 As Long
     WNIDUR              As Date
     LM                  As Long
     IOTT                As Long
     IOTTDUR             As Date
     IOC                 As Long
End Type

Global Y() As Sorter
Global G() As Sorter
Global B() As Sorter
Global L() As Sorter
Global YGBL() As Sorter

ReDim Y(99)
ReDim G(99)
ReDim B(99)
ReDim L(99)
ReDim YGBL(99, 4)

The YGBL is an addition as suggested but still working on how this could work

Thanks

It looks like the code you provided makes it even more difficult to understand what you want. It would appear you want to move code that fills the array into a different sub and pass one of the four arrays to it as a `byref` parameter. — GSerg, May 16 '17 at 08:44
Hi, I currently have 4 separate arrays and depending on whether it's Y,B,G or L in the string I search for I then want to add different values into the corresponding array — Fabby, May 16 '17 at 10:06
Declare one array, fill it, and in the end assign it to Y,B,G or L? Or if you're fancy and want to avoid copying, [create a reference](http://stackoverflow.com/a/11713643/11683) to one of the four arrays, fill the reference and destroy it? — GSerg, May 16 '17 at 10:25
Serg, the data could be different on each line so I have to check if it's Y,B,G,L and then add values into each of the arrays as I go through the file until i reach the end I then take the array an output this to a sheet. So need to keep all the arrays "live" but just switch as I go — Fabby, May 16 '17 at 10:53

score 0 · Accepted Answer · answered May 16 '17 at 08:51

0

You can create a multidimensional array, one of the dimension corresponds to the color. you could determine the index of the first dimension with the following instruction

colind=instr("YBGL",mysorter)

if the name of your multidimensional array is YBGL then you can use, for the example you mention

YBGL(colind,myftime).IOTTDUR

answered May 16 '17 at 08:51

h2so4

1,559
1
10
11

H2s04 : Thanks you are on the right lights, so if I am correct in what you suggest I should create an array that contains all 4 and then select the appropriate column as you suggest above. No quite sure i understand your first statement ? – Fabby May 16 '17 at 10:03
The first statement translate code in mysorter into a number from 1 to 4, 1 for Y, 2 for B, 3 for G and 4 for L. this number is then used as index for the first dimension of the array. the Array YBGL should be dimension as follows `Dim YBGL(4,99)` – h2so4 May 16 '17 at 11:41
H2so4 : Perfect your suggestions worked, after a bit of jigging about got it to work, thank you so much a great help and made the code so much simpilar – Fabby May 16 '17 at 12:38

Choose which array to use based on a variable in VBA

1 Answers1