VBA - Create Dynamic Variable from different strings-CodePudding

I'm a beginner on VBA. I have been following SO for years but have never really posted. I'm really struggling to understand a concept and have found no answers elsewhere.I want to use a for loop that 's going to loop these three arrays going like the following:

EUR_Buy = (1,2,3,4,5,6)
USD_BUY = (2,4,6,8,10,12)
GBP_BUY = (1,3,5,7,9,11)
curr = (EUR,USD,GBP)

For i = 0 To 2
For j = 0 To 5
If curr(i) & "_BUY" & (j) = 8

MsgBox Yes
End If
Next j
Next i

The only thing I get is the name of the variable (ex: Eur_Buy(0) but not the value of the value which would be "1". Any idea how I could get this? Would be very helpful).

Thanks a lot and please do not hesitate if you have any questions.

CodePudding user response：

You cannot create a string from pieces and then expect the runtime to use this as variable name.
If you have a list of names and associated values, you can use a Collection (or a Dictionary).

The following piece of code gives you the idea how to use them.

' Create collection and fill it with 3 elements, each holding an array of 6 values
Dim myVars As New Collection
' Elements are added to a collection with add <value>, <key>
myVars.Add Array(1, 2, 3, 4, 5, 6), "EUR_Buy"
myVars.Add Array(2, 4, 6, 8, 10, 12), "USD_BUY"
myVars.Add Array(1, 3, 5, 7, 9, 11), "GBP_BUY"

Dim curr as Variant
Dim j As Long
For Each curr In Array("EUR", "USD", "GBP")
    Dim key As String
    key = curr & "_BUY"
    ' You can access an element of a collection with it's key (name) or index.
    For j = 0 To 5
        If myVars(key)(j) = 5 Then Debug.Print curr, j, "Found 8 in " & key
    Next
Next

CodePudding user response：

Referencing an Array of arrays via Enum statement

If you have to deal with a greater number of currencies, it can increase readibility to

use an enumeration defined in the head of a code module and to
reference an Array of arrays (aka jagged array) by these placeholder variables in the main code and which
holds the individual currency arrays for its part; you may think it as sort of container.

Option Explicit         ' head of code module
Enum C                  ' Enum statement allows automatic increments (if no special assignments)
    [_Start] = -1
    EUR
    USD
    GBP
    LastElement = GBP    ' (re-)set to last currency (here GBP), if changed
End Enum

Note that you can easily insert or add other currencies without caring in further code for the actual number as Enum automatically increments the start element (if not assigned explicitly).

The following example code

assigns the individual arrays (starting a little bit tricky with the "Name" of the array as string value, e.g. "EUR") to buy() serving as container array and
executes a Match over all enumerated currencies eventually.

Sub ExampleCall()
    '1) define zero-based buy arrays referenced by Enum values (~> module top)
    Dim buy(C.LastElement)                           ' declare 0-based Array of arrays
    buy(C.EUR) = Array("EUR", 1, 2, 3, 4, 5, 6)      ' assign the individual arrays
    buy(C.USD) = Array("USD", 2, 4, 6, 8, 10, 12)
    buy(C.GBP) = Array("GBP", 1, 3, 5, 7, 9, 11)
    '2) define a search value
    Dim srch As Variant
    srch = 5
    '3) find value 5
    Dim curr As Long
    For curr = 0 To C.LastElement
        Dim no As Variant
        no = Application.Match(srch, buy(curr), 0)   ' << Find ordinal element position
        If IsNumeric(no) Then                        ' check for valid findings only
            no = no - 1                              ' adjust counter as Match returns 1-based numbers
            '4) display result of individual sub-array buy(curr)
            Debug.Print _
                buy(curr)(0), _
                "index " & no, _
                "Found " & buy(curr)(no) & " in " & buy(curr)(0) & "_BUY"
        End If
    Next

End Sub

Note that Application.Match always returns a 1-based position number (adjusted to the 0-based index by a -1 subtraction) within the individual arrays or an Error if there is no finding at all; checking the no result by IsNumeric allows to get only valid findings.

Results in the VB Editor's immediate window would be displayed e.g. as follows:

EUR           index 5       Found 5 in EUR_BUY
GBP           index 3       Found 5 in GBP_BUY