How to code a vba form without using global variables

Global variables ruin applications

Here is how to code a form without using those dreaded globals.

This is especially useful for forms that must appear in the middle of a running process, and that need to collect information from the user and then pass the user’s choice back to the calling code and continue running.

Using object oriented programming we can solve this without the need to use those nasty globals.

Say you have a form where a list of countries are presented to the user, she needs to choose one country and have the code run with it in an elegant manner.

The form should contain code like this

01  ' Code inside of form
02  Private mbCancelClicked As Boolean
03  private mszCountryChosen as string
04
05  ' I prefix my properties and methods with aaa so that they sort to the 
06  ' top in the vba intellisense
07
08  Public Property Get aaaCancelClicked() As Boolean     
09    aaaCancelClicked = mbCancelClicked   
10  End Property    
11    
12  Public Property Get aaaCountryChosen() as string   
13    aaaCountryChosen=mszCountryChosen 
14  End Property   
15    
16  ' Add Cancel Button called cmdCancel   
17  Private Sub cmdCancel_Click()  
18    mbCancelClicked = True   
19    Hide
20  End Sub   
21    
22  ' Add OK Button called cmdOK    
23  Private Sub cmdOK_Click()    
24    Hide
25  End Sub

In the above code extract we assume that the user clicks on a combo box or a list box, the “list changed” event updates the (“hidden”) module level variable with the chosen country (line 3 mszCountryChosen), which is then passed to the property procedure aaaCountryChosen (lines 12 through 14).

The code that calls the form is below.

This code would be placed in a general module….

01  Sub Test
02    dim ofC as frmCountries 
03    dim szCountryChosen as string 
04
05    set ofC = new frmCountries
06 	  ofC.Show
07    If ofC.aaaCancelClicked then 
08      set ofC=nothing
09      Exit Sub
10    End If
11    
12    szCountryChosen=ofC.aaaCountryChosen
13    set ofC = Nothing
14    SomeOtherRoutine szCountyChosen
15  End Sub   

The above OO coding technique is clean, avoids global variables and helps you build solid and maintainable vba solutions.