Refer to a constant

[SabCie]

Hello,

I want to do a small function in excel that will permit the user to retrieve information stored in a VBA module. I cannot find a function in VBA that will make VBA understand that this is a variable.

Here is my example:

Const Key199309 =20
Const Key199310 =25
Const Key199311 =30

Function Rates(ValYear As String, ValMonth As String)

Rates = "key" & ValYear & Month

End Function

This of course does not work...I tried to put a ME() but nothing worked...


So for example Rates(1993,09) would return me 20.


How can I achieve this!!

Thanks a lot

 

[mikerickson]

You can't address variables that way.
One workaround would be to use a function rather than constants

Function Key(index as Long) as Long
    Select Case index
        Case 199309 
            Key = 20
        Case 199310 
            Key = 25
        Case 199311 
            Key = 30
    End Select
End Function

Function Rates(ValYear As String, ValMonth As String) as Long
    Rates = Key(Val(ValYear&Month))
End Function

 

[SabCie]

Thank you very much for responding!

What I find strange is that in a form you have the ability to do it. For example, in an old code I was able to make VBA recognise variable that way:


Dim Test(1 to 20) As Object

For I = 1 To 20
Set Test(I) = Form1("Label" & I)
Next I

Thanks

 

[mikerickson]

In the form situation, you are building the string index to refer to a member of a Collection. (The Controls collection of the form, to be specific)

Set Test(I) = Form1.Controls("Label" & I)

VBA constants are not members of a collection.