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.

I assume you have fill your combobox with "January February March ...", it is it ? — Maxime Porté
– Maxime Porté, Commented Jun 22, 2016 at 20:19
Please clarify exactly what you're trying to accomplish, I can't figure out what month is supposed to be doing - all I can say is that coupling the default instance of your UserForm to all instances of that clsMonth class is a very very very bad idea. — Mathieu Guindon
– Mathieu Guindon, Commented Jun 22, 2016 at 20:21
@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 — ct4242
– ct4242, Commented Jun 22, 2016 at 20:24

Mathieu Guindon · Accepted Answer · 2016-06-22 20:35:19Z

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

answered Jun 22, 2016 at 20:35

Mathieu Guindon

71.5k8 gold badges113 silver badges250 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

ct4242 Over a year ago

Matt, thanks for your help. I do have a few questions is you get the chance to answer them.

ct4242 Over a year ago

I am a little confused on the VbGet parameter. Is that a built in VBA function or is that referring to something in your code that I don't see. Second, I'm confused with the first part of the code overall. The part with Dim Instance as Object, and then using that combined with the VbGet. Thanks again

Mathieu Guindon Over a year ago

See CallByName docs; the function requires an instance of an object that exposes the member you want to call; in this case the object is Me - the current instance; but Me cannot be passed directly to CallByName for some reason, so you make a copy (instance) and pass that instead. The function also requires the name of the member to call and an enum value that tells VBA how it should call it - VbGet works for Property Get members (and public fields) and says "I'm expecting something to be returned from this call".

ct4242 Over a year ago

And two more questions now, bare with me. All of this is done in the clsMonth Module, correct? And does the Private Type TMonth eliminate the need for my initial deceleration of all the months in my original code?

Mathieu Guindon Over a year ago

Nah, the Private Type TMonth is just making it easier to encapsulate things with properties (vs exposing public fields), and eventually to serialize the data into a binary file, if that's ever needed. I just systematically do it that way, because I don't like exposing public fields in objects. And yes, TMonth, the this field, the 12 properties and the ValueFor function all belong in that class module.

|

Collectives™ on Stack Overflow

vba object reference from userform not working

1 Answer 1

8 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related