VBA use global variable in userform code

Question

I'm working in Excel 2010 VBA. Is there a way of accessing values in global variables declared outside a userform, in code inside the userform? Code inside my userform returns the global variable as null - can't work out why!

The variable is declared in the ThisWorkbook module as:

Public TargetCell As Range
Public TargetCellWorksheet as Worksheet
Public CurrentValue As Long

Inside the userform, I have this code on the "Update" button:

Private Sub Update_Click()
    MsgBox ("Start of Update sub. TargetCellWorksheet =" & TargetCellWorksheet)
End Sub

The msgbox returns "" for the variable.

Hoping someone may be able to help me understand this and how to access the variable inside the userform? Thank you in advance

it makes no difference whether you use a global or local variable, if the value for the variable is not set, then the variable is empty ("") — Vasily Ivoyzha
Thanks Vasily - I have left the Set out of my code here (for brevity). The variable is set, accessible, and not empty outside of the userform. — fatgecko
try to call macro where you set variable before msgbox, because currently it seems to be not assigned — Vasily Ivoyzha

Matteo NNZ Matteo NNZ · Accepted Answer · 2015-01-20T11:31:54

As for the problem itself, you declare

Public TargetCellWorksheet as Worksheet

and then try to show it into a MsgBox:

MsgBox ("Start of Update sub. TargetCellWorksheet =" & TargetCellWorksheet)

Did you maybe mean TargetCellWorksheet.Name, or TargetCellWorksheet.Range("A1").Value, since the MsgBox expects to receive a string?

However, if you're sure about your code, it might depend on the fact that the variable is not properly declared as Public and it goes at module level only. You might want to add a property to your form, if the variable is part of the form itself (I assume that you meant to use CurrentValue, but you can simply change the type of the property from Long to Worksheet and use it instead):

This goes inside the code of your form

Dim pCurrentValue As Long
Public Property Get CurrentValue() As Long
    CurrentValue = pCurrentValue 
End Property
Public Property Let CurrentValue (value As Long)
    pCurrentValue = value
End Property

Hence, passing the variable from the module to the form like this:

This goes into your module, before you enter the code of the form

Dim myForm As New yourForm
myForm.CurrentValue  = whateverYourVariableIs

and so using the variable inside your form like this:

You can hence use your variable by calling it from the property of the form

myVariableInTheForm = Me.CurrentValue

I must say that, however, it is strange that a public variable is not reaching the stack of the form. Are you sure you're not only declaring the variable without assigning any value before?

VBA use global variable in userform code

1 Answers