Global variable dropping out of scope

[jmazorra]

Hello folks:

I have the following global variable:

Option Explicit
'Public declarations for the project
Public locationCode As String

I then reference like this:

Public Property Get locationCode() As String
'Declaration to access the data entered in the
'locationCode from anywhere on the project
locationCode = frmEnterLocation.txtLocationCode.Value

End Property

I would like to call it from my userform, here is my code:

Private Sub cmdOKButton_Click()
 
frmEnterLocation.txtLocationCode.SetFocus


If locationCode = "" Then

    MsgBox "You have not entered a Location Code", vbCritical, "Please Enter a Location Code"

           frmEnterLocation.txtLocationCode.SetFocus
Else
     Unload Me
          On Error Resume Next
          
Set ws = wksDenialReport
filterRange = ("G2:G")
compOperator = "<>"
filterString = locationCode

Call filterSheets (ws, filterRange, compOperator, filterString)

The code runs, but the variable is not storing. The code filters everything out, instead of what I entered in the form. For reference, here is the filterSheets function

Public Function filterSheets(Sheets As Worksheet, searchRange As String, operator As String, filterString As String)
 
Dim lngLastRow As Long
    Application.ScreenUpdating = False
    
    With Sheets
    
        lngLastRow = GETLASTROW(.Cells)
        
        If lngLastRow > 1 Then
            'we don't want to delete our header row
            With .Range(searchRange & lngLastRow)
            
                    .AutoFilter Field:=1, Criteria1:=operator & filterString

                    .EntireRow.delete
            End With
        End If
    End With
    
    Application.ScreenUpdating = True
    
End Function

 

[Jerry Sullivan]

Hi jmazorra,

Why are you using locationCode as both a global variable and a property?

Typically, a property in a UserForm is used to control access to a private variable defined in the UserForm. Control is gained by limiting the ability to modify that variable to those UserForm properties or methods that have access to the private variable. If you were to also then have a public reference to the variable (even if it worked) that would defeat any benefit of using a property.

I'd suggest that you use either a global variable or a property that provides access to a private variable. The global variable approach is easier, but provides less control. If you take the approach of using a property, you'll need to either:

1. Apply the filter before the userform is unloaded.

2. Pass the value of the private variable, locationCode, to another variable that has scope within the procedure that applies the filters after the userform is unloaded.