vba object reference from userform not working

Question

I am working and have hit a bit of a roadblock. For one I am not the strongest programmer, I was just given this task but have hit a roadblock (I think its syntax but not sure).

We have done a lot of research to find some data correlations for some of our less technical consultants, and this VBA tool is going to be a quick reference for them to give customers real time projections. The subject is irrelevant to the question.

I have created a class to contain the values, and then the plan was to reference the class from the VBA userform. When I reference the class, I don't get the value that I have stored, but I get the name of the property.

Private Sub ddMonthOfUse_Change()

Dim mv As String

Set chillWater = New clsMonth
    With chillWater
        .Janurary = 0.7136
        .Feburary = 0.6755
        .March = 0.6528
        .April = 0.7773
        .May = 0.8213
        .June = 0.8715
        .July = 0.9
        .August = 1.0243
        .September = 1.0516
        .October = 0.8514
        .November = 0.7095
        .December = 0.6994
    End With

Set DX = New clsMonth
    With DX
        .Janurary = 0.5777
        .Feburary = 0.5536
        .March = 0.5166
        .April = 0.6112
        .May = 0.7035
        .June = 0.75
        .July = 0.8
        .August = 0.8345
        .September = 0.9333
        .October = 0.6865
        .November = 0.5976
        .December = 0.4907
    End With


    MsgBox chillWater.month

    End Sub

In the class module I have this

 Option Explicit

'property decleration
Public Janurary As Single
Public Feburary As Single
Public March As Single
Public April As Single
Public May As Single
Public June As Single
Public July As Single
Public August As Single
Public September As Single
Public October As Single
Public November As Single
Public December As Single
Public chillWater As clsMonth, DX As clsMonth

Public Property Get month() As String

month = Main.ddMonthOfUse.value

End Property

Instead of the message box returning the value, if the user selects month March the message box says "March." If, for example, I replace the reference to the dropdown and just type March, I get the value.

I can't figure this out for the life of me. Please keep in mind I am not a veteran programmer at all, I have very rudimentary knowledge of the subject so I could be completely off here. Also I should mention that the message box is not the final use of the information, I was just testing to see if I was calling it correctly.

Answer 1

@Mat'sMug when the combobox in the userform is selected (say the user chooses May), I want the message box to display the value of chillwater.May

Okay, got it. Remove all these, they're toxic:

Public chillWater As clsMonth, DX As clsMonth

Public Property Get month() As String

month = Main.ddMonthOfUse.value

End Property

You need to expose a Function that takes a month's name and returns the corresponding field's value.

One way could be to leverage CallByName and do something like this:

Public Function ValueFor(ByVal monthName As String) As Single
    On Error GoTo CleanFail

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

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

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

Notice the VbGet parameter - it's going to work with a public field (I just tested it), but ideally you would encapsulate these values and expose them as properties instead. I like encapsulating my stuff like this:

Option Explicit

Private Type TMonth '"T" + ClassName, just by convention
    January As Single
    February As Single
    '...
    December As Single
End Type

Private this As TMonth 'the only private field you need!

'now expose a property accessor for each member:

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

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

'...

---

And then your form code can do this:

Set chillWater = New clsMonth
With chillWater
    .Janurary = 0.7136
    .Feburary = 0.6755
    .March = 0.6528
    .April = 0.7773
    .May = 0.8213
    .June = 0.8715
    .July = 0.9
    .August = 1.0243
    .September = 1.0516
    .October = 0.8514
    .November = 0.7095
    .December = 0.6994

    MsgBox "Value for " & ddMonthOfUse.value & ": " & .ValueFor(ddMonthOfUse.Value)

End With