Using Global Variables in VBA

Question

Just started using VBA within the past week, and am playing around with Sheets vs. Modules and defining variables. Here is the code I currently have:

Sheet1:

Dim writtenvoe As Boolean
Dim verbalvoe As Boolean
Dim voerequired As Boolean
Dim CA14 As Boolean
Dim CA15 As Boolean
Dim W2s As Boolean
Dim tranapp As Boolean

Module1:

Public Sub ImpDocs()

'Is written voe on file?
If Sheet1.CheckBox6.Value = True Or Sheet1.CheckBox8.Value = True Or Sheet1.CheckBox9.Value = True Or Sheet1.CheckBox10.Value = True Then writtenvoe = True Else writtenvoe = False
If Sheet1.CheckBox6.Value = True Then Sheet1.[O40] = "hello" Else Sheet1.[O40] = ""
If writtenvoe = True Then Sheet1.[O39] = "writtenvoe = true" Else Sheet1.[O39] = "writtenvoe=false"

'Is verbal voe on file?
If Sheet1.CheckBox11.Value = True Then verbalvoe = True

'Is a written VOE required?
If Sheet1.CheckBox16.Value = True Or Sheet1.CheckBox18.Value = True Or Not IsEmpty(Sheet1.[H33]) Then voerequired = True
If voerequired = True Then Sheet1.[J35] = "hello" Else Sheet1.[J35] = ""

'Are tax docs CA14 or CA15?
If Sheet1.CheckBox31.Value = True Or Sheet1.CheckBox7.Value = True Or Sheet1.CheckBox32.Value = True Or Sheet1.[H29].Value > 25 Then CA15 = True Else CA15 = False
If CA15 = False Then CA14 = True Else CA14 = False

'Are W-2's on file?
If Sheet1.CheckBox12.Value = True And Sheet1.CheckBox13.Value = True Then W2s = True

'Are 4506-T's on file?
If Sheet1.CheckBox60.Value = True And Sheet1.CheckBox61.Value = True Then tranapp = True

    'Order Wage transcripts if W-2s and 4506-T not on file
    If W2s = False And tranapp = False And CA14 = True Then Sheet4.fullCA14

End Sub

Also Module 1 (2nd Subroutine):

Public Sub test()

If writtenvoe = True Then Sheet1.[N37] = "Yes" Else Sheet1.[N37] = "No"

End Sub

I noticed that in this current format, Sub ImpDocs works well and the variables are determined correctly, but Sub test always comes back as false. However, if I put all of the code into module 1, everythis works as expected. It seems like only Sub test is affected from declaring variables in Sheet1 vs Module1. Is that true? If so, why?

Thank you.

Maybe try putting Option Explicit at the top of your module to make sure all the variable declarations are being found properly (although it shouldn't make a difference because a bool's default value is false)? It's good practice to use this line anyway, as it reminds you if you don't define a variable. — Tianna Wrona
And this may be an obvious thing but, you've tested this with all the checkboxes in the if-statement set to false I assume right? — Tianna Wrona
@TiannaProcon Good advice, the Option Explicit addition did tell me that my variables were not defined in test. Do you know why ImpDocs is able to pull the variables, but test isn't? I did test the variables in both procedures and ImpDocs is returning accurately. — Kim

Mathieu Guindon Mathieu Guindon · Accepted Answer · 2017-09-22T17:17:50

Sheet1 is a Worksheet object, an instance of a class. So even if you declare everything Public on Sheet1, you won't be able to access them without first accessing the instance.

Dim is used for declaring local variables. It's also legal at module-level, but then it's equivalent to using Private to declare them: no variable declared with the Dim keyword is ever going to be publicly accessible - my recommendation is to use Dim for locals, and use Private/Public for module variables.

When you declare Public variables in a standard module (as opposed to a class module), you're effectively declaring global variables - each of which is accessible for both read and write, from anywhere in the project. If this sounds like a good idea, please take the time to research about "pros and cons of global variables" - regardless of the language, they're usually a recipe for disaster. You want your variables to be written by specific code only, and you want the scope of your variables to be as limited as possible.

Learn how to pass parameters to your procedures instead.

You're discovering the difference between standard modules and classes: pretty much everything around you (Application, Range, Sheet1, etc.) is an object. Objects are instances of a certain class (Excel.Application, Excel.Range, Excel.Worksheet, etc.), and each instance encapsulates its own state.

Standard modules are great for macro entry points, not so great for encapsulation - and encapsulation is one of the fundamental pillars of object-oriented programming (OOP).

Using Global Variables in VBA

2 Answers