Calling Public variable from userform

Question

In a userform, I have this at the top:

Public DelMonth As Variant

The value of DelMonth is read from a ComboBox, and I can call it from different subroutines within that userform just fine. But when I call it from a separate module, it doesn't read it. It doesn't even throw an error. If I do a MsgBox DelMonth, it doesn't do anything.

You need to use userformname.DelMonth from any routine outside the form. — Rory
– Rory, Commented Aug 22, 2017 at 14:48

Mathieu Guindon · Accepted Answer · 2017-08-22 14:56:40Z

3

A form is an object; a public field in an object module belongs to an instance of that object. UserForms are little more than class modules with a default instance (i.e. a VB_PredeclaredId = True attribute) and a designer.

If you're using the form's default instance (a rather bad idea), then you can do this:

MsgBox UserForm1.DelMonth

Note that storing state in global objects is bug-prone, and will end up causing issues.

If you're treating the form like the full-fledged class it is, then you'll have something like this:

With New UserForm1
    .Show
    MsgBox .DelMonth
End With

Note that the field being Public means anyone, anywhere can go and write to it. What you mean is for the form to determine its value, and for the caller to be able to read that value. You do this by encapsulating the field with a Property Get member - start by making the field Private:

Option Explicit
Private DelMonth As Variant ' wouldn't Integer or Long be more appropriate?

Public Property Get DeliveryMonth() As Long
    DeliveryMonth = DelMonth
End Property

Now the callers don't get to see the private DelMonth, and all they can do with DeliveryMonth is call the Get accessor, which doesn't let them tamper with the encapsulated value.

It doesn't even throw an error.

That's worrying. You're allowing VBA to happily compile typos and otherwise illegal code. Specify Option Explicit at the top of every module. Always.

edited Aug 22, 2017 at 14:56

answered Aug 22, 2017 at 14:50

Mathieu Guindon

71.5k8 gold badges113 silver badges250 bronze badges

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

6 Comments

Robby Over a year ago

I'm trying to call DelMonth from the userform in a SaveAs dialog box. And that dialog box is closing the userform. Is that why it's not able to grab the variable?

Mathieu Guindon Over a year ago

You're doing it backwards. A form's purpose is to collect/display data, not to process it. A form doesn't do any work. If you've unloaded the default instance, the global state is destroyed, yes. Please do things properly, avoid global state, and have forms that do a form's job. Thank yourself later.

Robby Over a year ago

I'm not following. I'm setting the DelMonth variable from a userform ComboxBox. From there, in a module, I need to call that variable in a dialog box. How am I doing it backwards?

Mathieu Guindon Over a year ago

Ok, sorry I misunderstood. So the module is retrieving the value after the form has closed, correct? If the form is Unloaded, or X'd-out, then the object is destroyed and the state is lost. You need to handle the QueryClose event to prevent that, and remove any Unload Me you might have in that form's code-behind. See UserForm best practices on Documentation.SO (quick while it's still up! SO is sunsetting documentation)

Robby Over a year ago

Technically, yes. I have the SaveAs box set to open before it's Unloaded, but it seems like the dialog box forces the userform to close automatically.

|

Collectives™ on Stack Overflow

Calling Public variable from userform

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related