Using Variables as References VBA

Question

There must be a way to do this but I just don't know how.

Say I have a Data Type that has these objects

        Set test = New clsMonth 

            With test 

                .January = 0.7136 / 20
                .February = 0.6755 / 20
                .March = 0.6528 / 20
                .April = 0.7773 / 20
                .May = 0.8213 / 20
                .June = 0.8715 / 20
                .July = 0.9 / 20
                .August = 1.0243 / 20
                .September = 1.0516 / 20
                .October = 0.8514 / 20
                .November = 0.7095 / 20
                .December = 0.6994 / 20

            End With

And I have the ones I want to reference stored in an array. How can I use that array to ref them. I have tried something like this (this is sloppy but I did it quickly for an example):

Set test = New clsMonth 

                    With test 

                        .January = 0.7136 / 20
                        .February = 0.6755 / 20
                        .March = 0.6528 / 20
                        .April = 0.7773 / 20
                        .May = 0.8213 / 20
                        .June = 0.8715 / 20
                        .July = 0.9 / 20
                        .August = 1.0243 / 20
                        .September = 1.0516 / 20
                        .October = 0.8514 / 20
                        .November = 0.7095 / 20
                        .December = 0.6994 / 20

                    End With

        Dim month(1 To 2) As String

        month(1) = "March"

        MsgBox test.month(1)

Thank you in advanced. This will assist me in so many ways!!

ClsMonth:

Option Explicit

'encapsulating values to be exposed as properties

Private Type TMonth
    January As Single
    February As Single
    March As Single
    April As Single
    May As Single
    June As Single
    July As Single
    August As Single
    September As Single
    October As Single
    November As Single
    December As Single
End Type

Private this As TMonth

'property accessors for each member
'this "talks" to the form code to assign and retrieve values for each month

Public Property Get January() As Single
    January = this.January
End Property

Public Property Let January(ByVal value As Single)
    this.January = value
End Property

Public Property Get February() As Single
    February = this.February
End Property

Public Property Let February(ByVal value As Single)
    this.February = value
End Property

Public Property Get March() As Single
    March = this.March
End Property

Public Property Let March(ByVal value As Single)
    this.March = value
End Property

Public Property Get April() As Single
    April = this.April
End Property

Public Property Let April(ByVal value As Single)
    this.April = value
End Property

Public Property Get May() As Single
    May = this.May
End Property

Public Property Let May(ByVal value As Single)
    this.May = value
End Property

Public Property Get June() As Single
    June = this.June
End Property

Public Property Let June(ByVal value As Single)
    this.June = value
End Property

Public Property Get July() As Single
    July = this.July
End Property

Public Property Let July(ByVal value As Single)
    this.July = value
End Property

Public Property Get August() As Single
    August = this.August
End Property

Public Property Let August(ByVal value As Single)
    this.August = value
End Property

Public Property Get September() As Single
    September = this.September
End Property

Public Property Let September(ByVal value As Single)
    this.September = value
End Property

Public Property Get October() As Single
    October = this.October
End Property

Public Property Let October(ByVal value As Single)
    this.October = value
End Property

Public Property Get November() As Single
    November = this.November
End Property

Public Property Let November(ByVal value As Single)
    this.November = value
End Property

Public Property Get December() As Single
    December = this.December
End Property

Public Property Let December(ByVal value As Single)
    this.December = value
End Property
'end of property accessors'


Public Function ValueFor(ByVal monthName As String) As Single 'function to take month name and respond w property value
    On Error GoTo CleanFail

    Dim instance As Object
    Set instance = Me 'CallByName can't take "me" directly for some reason, must create an instance of me

    Dim result As Single
    result = CallByName(instance, monthName, VbGet)

CleanExit:
    ValueFor = result
    Exit Function
CleanFail:
    result = 0
    Resume CleanExit

End Function 

Answer 1

Sub Tester()

    Dim o As New clsTest, arr, e

    o.One = "First"
    o.Two = "Second"
    o.Three = "Third"

    arr = Array("One", "Two", "Three")

    For Each e In arr

        Debug.Print CallByName(o, e, VbGet)

    Next e

End Sub

output:

First 
Second 
Third

clsTest:

Public One As String
Public Two As String
Public Three As String

Answer 2

You could tweak that ValueFor function to accept a monthNameOrIndex As Variant, like so:

Public Function ValueFor(ByVal monthNameOrIndex As Variant) As Single
    On Error GoTo CleanFail

    Dim name As String
    If IsNumeric(monthNameOrIndex) Then
        name = MonthName(monthNameOrIndex)
    Else
        name = CStr(monthNameOrIndex)
    End If

    Dim instance As Object
    Set instance = Me 'CallByName can't take "Me" directly

    Dim result As Single
    result = CallByName(instance, name, VbGet)

CleanExit:
    ValueFor = result
    Exit Function
CleanFail:
    result = 0
    Resume CleanExit
End Function

And then you could do this:

MsgBox test.ValueFor(3)

Just as you could do that:

MsgBox test.ValueFor("March")

---

Looking closer at this snippet:

Dim month(1 To 2) As String

month(1) = "March"

MsgBox test.month(1)

If you're storing month names in an array, and you want to retrieve a value and pass it to this test.ValueFor function, you need to do this:

Dim month(1 To 2) As String
month(1) = "March"

MsgBox test.ValueFor(month(1))
'same: text.ValueFor("March")