Global variable value not translating from Userform to Subroutine

In my code, I have a combobox on a userform. The user selects an option from the drop down and the userform sets the selected value to a global variable "monthYearEntered".

Then i have a sub routine that uses the global variable. However, the value of the global variable is not carrying over to the subroutine. I know because I made a MsgBox of the variable from within the userform and then again in the sub routine. I get the right value in the form but not the routine.

I researched a bit and saw some potential solutions but none of them worked for me. In some past codes, I made a workaround by setting cell to the value and then referencing the cell to set the variable in the subroutine. I would like to avoid that since its just a Band-Aid.

USERFORM CODE

Private Sub CBN_OK_Click()

If YearMonthField = "" Then
    MsgBox "Value cannot be blank. Please enter correct value."
    Exit Sub
Else
    monthYearEntered = YearMonthField.Value
    MsgBox monthYearEntered
    Me.Hide
    Application.Run "DeleteMonth"
End If

End Sub

MODULE

Option Explicit
Dim monthYearEntered As String

Private Sub RangetoDelete()

    Application.DisplayAlerts = False
    
    Dim ws As Worksheet
    Dim tableSalesCombined As ListObject
    Dim cell, rng As Range

    Set ws = ThisWorkbook.Worksheets("Sales Data Combined")
    Set tableSalesCombined = ws.ListObjects("Table_SalesCombined")
    Set rng = ws.Columns("I:I")
    
    DeleteMonthForm.Show 'show user form
    
    'monthYearEntered.ClearContents
    Application.Run "Protect"
    Application.DisplayAlerts = True
    
End Sub

Private Sub DeleteMonth()

    Application.DisplayAlerts = False
    
    Dim ws As Worksheet
    Dim tableSalesCombined As ListObject
    'Dim monthYearValue
    Dim cell, rng As Range

    Set ws = ThisWorkbook.Worksheets("Sales Data Combined")
    Set tableSalesCombined = ws.ListObjects("Table_SalesCombined")
    Set rng = ws.Columns("I:I")
    
    ws.Unprotect Password:=PassW

    'look for value entered by user (monthYearValue)
    Set cell = rng.Find(What:=monthYearEntered, LookIn:=xlFormulas, LookAt:=xlWhole, MatchCase:=False)
    
    MsgBox monthYearEntered
    
    If cell Is Nothing Then
        MsgBox monthYearEntered & "not found in Combined Sales Data.  Please confirm value and try again."
        Exit Sub
    End If
    
    With tableSalesCombined
         .Range.AutoFilter Field:=9, Criteria1:=monthYearEntered
         If Not .DataBodyRange Is Nothing Then
            '.DataBodyRange.SpecialCells(xlCellTypeVisible).Delete
         End If
         '.Range.AutoFilter
    End With

    tableSalesCombined.AutoFilter.ShowAllData
    
    'monthYearEntered.ClearContents
    Application.Run "Protect"
    Application.DisplayAlerts = True
    
End Sub

Solution

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.