Construct call to enum type as string and use in Evaluate function in VBA

Question

I have an Enum type in VBA and I want to use it as an index to an array. The problem is that I want to construct the Enum during runtime based on input from another function.

So I have:

Enum Rating_
   AAA = 1
   AA = 2
   A = 3
End Enum

and the following array:

Dim arr(1 To 3) as Double

arr(1) = 0.1
arr(2) = 0.2
arr(3) = 0.3

Also I have a function that outputs one of the following strings. The function's inner workings are irrelevant. For solving this problem it could be just a string (one of the Enum values):

funct(x,y) = "AAA"
funct(x,y) = "AA"
funct(x,y) = "A"

What I want to construct is a string with the Enum call such as:

Dim str As String
str = "Rating_." & funct(x,y)

and use it together with Evaluate function like:

Evaluate("arr(" & str & ")")

The above code gives an error:

Error 2029

and the following

Evaluate("""arr(" & str & ")""")

where funct(x,y) = "AAA" gives:

arr(Rating_.AAA)

as a string.

What I would like to get is 0.1 (0.2 if funct(x,y) = "AA", 0.3 if funct(x,y) = "A").

Thank you in advance.

Answer 1

Expanding on @BigBen's comment that a dictionary might be what you're looking for, I agree with him, and here's how you'd implement it:

Sub tgr()

    Dim Rating_ As Object
    Set Rating_ = CreateObject("Scripting.Dictionary")
    Rating_("AAA") = 1
    Rating_("AA") = 2
    Rating_("A") = 3

    Dim arr(1 To 3) As Double
    arr(1) = 0.1
    arr(2) = 0.2
    arr(3) = 0.3

    Dim str As String
    str = "AA" 'replace with your funct output here

    MsgBox arr(Rating_(str))    '<-- returns 0.2, no Evaluate required

End Sub