2

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.

Improve this question
3
  • 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. Commented Sep 22, 2017 at 17:01
  • 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? Commented Sep 22, 2017 at 17:03
  • @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. Commented Sep 22, 2017 at 17:04

2 Answers 2

Reset to default
4

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).

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

@MatsMug thank you for taking the time to write out that context; it's very helpful for me as I develop my conceptual understanding. I will research more about global variables.
@khelm spread the word, and give us a star on GitHub if you like!
3

This is a quirk on how VBA does its namespaces (https://en.wikipedia.org/wiki/Namespace). Back in Sheet1, you'll need to make writtenvoe a Public member of Sheet1:

Public writtenvoe As Boolean

Then, back in Module1, every time you want the writtenvoe of Sheet1, you must specify Sheet1.writtenvoe, e.g.:

'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 Sheet1.writtenvoe = True Else Sheet1.writtenvoe = False

When you moved writtenvoe out of Sheet1 and into Module1, you've effectively removed Sheet1.writtenvoe and created Module1.writtenvoe, thereby making something easily accessible by Module1. The namespaces of modules are very permissive, so anything declared Public inside a module is pretty much available anywhere - another module, any Sheet, any Class Module, etc.

Improve this answer

5 Comments

Oh, and yes, making use of Option Explicit clears up a lot of potential confusion, too!
It's not a "VBA quirk", it's basic OOP 101 - Sheet1 is an instance of the Worksheet class.
There are no quirks about namespaces in VBA because there are no namespaces to begin with. Sheet1 is an object of type Worksheet, Module1 is a module. Neither has or is a namespace. What you are talking about is called scope.
Yep, I get what your saying. In my defence, per Wikipedia: "Namespace versus scope: A namespace identifier may provide context (Scope in computer science) to a name, and the terms are sometimes used interchangeably. However, the context of a name may also be provided by other factors, such as the location where it occurs or the syntax of the name."
@rskar That worked great, thank you! I tested other variations in other modules too, just to make sure I understand/am pulling them correctly. That was really helpful.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.