Global variable value not translating from Userform to Subroutine

Writing Dim monthYearEntered As String at the top of a module declares the variable monthYearEntered as global, but its scope is limited to the module where the definition is. That means, outside of the module the variable is unknown.

If you want the variable to be accessible outside the module, you need to declare it using the Global keyword, not the Dim keyword.

Global monthYearEntered As String 

As you declared the variable with Dim, the statement monthYearEntered = YearMonthField.Value in the form cannot write into the variable - it is simply unknown there. There are 2 possibilities, but at the end of the day the result in the same:

• You have a variable monthYearEntered declared in the form. This is a new variable and has nothing to do with the variable you declared in the module.

• You don't have a variable declared in the form:

  • You don't use Option Explicit in the form. When the Click-routine hits the assignment statement, the VBA runtime will see that at this time, monthYearEntered is unknown at create it on the fly. Again, this is a different variable.
  • You use Option Explicit: The compiler will complain.

You can easily check this in the VBA editor: move the cursor on the variable and press Shift+F2

• When the variable is declared locally, the VBA editor will jump to the declaration inside the form
• When the form accesses a globally declared variable, the editor jumps to the declaration inside the module.
• When the form uses an undeclared variable, you see a message "Identifier under cursor is not recognized".
• When you move the cursor to something that is part of the VBA environment (a function like Left or Date, or an object like Application), object browser window is displayed.

An example: I put the following code into a module1

Option Explicit

Global globVar As String
Dim dimVar As String

Sub test1()
    globVar = "global var from module1"
    dimVar = "dimmed var from module1"
    
    Debug.Print "Start in test1: ", globVar, dimVar
    test2
    Debug.Print "Back in test1: ", globVar, dimVar
End Sub

Now I create another module and put the following code (note that for the moment, Option Explicit is deactivated):

' Option Explicit
Sub test2()
    Debug.Print "Now in test2: ", globVar, dimVar
    globVar = "overwritten in test2"
    dimVar = "overwritten in test2"
    Debug.Print "Still in test2: ", globVar, dimVar
End Sub

Executing test1, you get the following output:

Start in test1:             global var from module1     dimmed var from module1
Now in test2:               global var from module1     
Still in test2:             overwritten in test2        overwritten in test2
Back in test1:              overwritten in test2        dimmed var from module1

You can see that globVar is displayed in test2, and the assigned new value is seen also when returning back to test1. However, printing dimVar in test2 doesn't show anything: The variable is unknown at that moment and created instantly. You can write into it, but this is not seen in test1 as it is a different variable, only with the same name.

If you now activate the Option Explicit in module2, you will get a compiler error because dimVar is unkwown. If you now add a declaration for dimVar into module2 (to get rid of that compiler error), you declare a new variable - the outcome is the same: globVar is visible in both modules but dimVar of module1 is not.

Option Explicit
Dim dimVar As String
Sub test2()
    Debug.Print "Now in test2: ", globVar, dimVar
    globVar = "overwritten in test2"
    dimVar = "overwritten in test2"
    Debug.Print "Still in test2: ", globVar, dimVar
End Sub

(Same output as before)

Just to make it complete: If you add a declaration for globVar in module2, you would hide the declaration of globVar in module1 and you have 2 different variables.

Option Explicit
Dim globvar As String
Dim dimvar As String
Sub test2()
    Debug.Print "Now in test2: ", globvar, dimvar
    globvar = "overwritten in test2"
    dimvar = "overwritten in test2"
    Debug.Print "Still in test2: ", globvar, dimvar
End Sub

Now you get

Start in test1:             global var from module1     dimmed var from module1
Now in test2:                             
Still in test2:             overwritten in test2        overwritten in test2
Back in test1:              global var from module1     dimmed var from module1

If you want to declare a "global" variable inside a form, you cannot use the keyword Global. The reason is that a form is a class, and you can have multiple instances of a class (you can open a form more than once). To make a variable visible outside the form, use the keyword public.

Form code: Public formVar As String

Private Sub Ok_clicked
    formVar = "I was filled in the form"
    Me.Hide
End Sub

Module code using the default instance of the form:

UserForm1.Show
Debug.Print UserForm1.formVar

or using an explicit instance:

Dim frm as New UserForm1
frm.Show
Debug.Print frm.formVar

Note that you need to use Hide to close the form. If you use the infamous Unload command, the userform object is destroyed (removed from memory) and frm.formVar no longer accessible.

Summary:

• Using Dim on module level declares a variable on module level that is unknown in other modules
• Using Global declares a variable that is known in all modules
• In a form (or a class module), you can't define a Global variable. To make a variable accessible, declare it as public.
• Always use Option Explicit to avoid that the VBA runtime creates undeclared variables on the fly.
• In case of a doubt, use Shift+F2 to see where a variable is declared.