How to declare Global Variables in Excel VBA to be visible across the Workbook

Question

I have a question about global scope and have abstracted the problem into a simple example:

In an Excel Workbook: In Sheet1 I have two(2) buttons.
The first is labeled SetMe and is linked to a subroutine in Sheet1's module:
Sheet1 code:

Option Explicit
Sub setMe()
    Global1 = "Hello"
End Sub

The second is labeled ShowMe and is linked to a subroutine in ThisWorkbook's module:
ThisWorkbook code:

Option Explicit
Public Global1 As String
Debug.Print("Hello")
Sub showMe()
    Debug.Print (Global1)
End Sub

Clicking on SetMe produces a compiler error: variable not defined.
When I create a separate module and move the declaration of Global1 into it everything works.

So my question is: Everything I have read says that Global variables, declared at the top of a module, outside of any code should be visible to all modules in the project. Clearly this is not the case. Unless my understanding of Module is not correct.
The objects Sheet1, Sheet2, ThisWorkbook,... that come with a workbook: are these not modules capable of declaring variables at global scope?

Or is the only place one can declare a global, in a separate module of type Modules.

Solution 1

Your question is: are these not modules capable of declaring variables at global scope?

Answer: YES, they are "capable"

The only point is that references to global variables in ThisWorkbook or a Sheet module have to be fully qualified (i.e., referred to as ThisWorkbook.Global1, e.g.) References to global variables in a standard module have to be fully qualified only in case of ambiguity (e.g., if there is more than one standard module defining a variable with name Global1, and you mean to use it in a third module).

For instance, place in Sheet1 code

Public glob_sh1 As String

Sub test_sh1()
    Debug.Print (glob_mod)
    Debug.Print (ThisWorkbook.glob_this)
    Debug.Print (Sheet1.glob_sh1)
End Sub

place in ThisWorkbook code

Public glob_this As String

Sub test_this()
    Debug.Print (glob_mod)
    Debug.Print (ThisWorkbook.glob_this)
    Debug.Print (Sheet1.glob_sh1)
End Sub

and in a Standard Module code

Public glob_mod As String

Sub test_mod()
    glob_mod = "glob_mod"
    ThisWorkbook.glob_this = "glob_this"
    Sheet1.glob_sh1 = "glob_sh1"
    Debug.Print (glob_mod)
    Debug.Print (ThisWorkbook.glob_this)
    Debug.Print (Sheet1.glob_sh1)
End Sub

All three subs work fine.

PS1: This answer is based essentially on info from here. It is much worth reading (from the great Chip Pearson).

PS2: Your line Debug.Print ("Hello") will give you the compile error Invalid outside procedure.

PS3: You could (partly) check your code with Debug -> Compile VBAProject in the VB editor. All compile errors will pop.

PS4: Check also Put Excel-VBA code in module or sheet?.

PS5: You might be not able to declare a global variable in, say, Sheet1, and use it in code from other workbook (reading http://msdn.microsoft.com/en-us/library/office/gg264241%28v=office.15%29.aspx#sectionSection0; I did not test this point, so this issue is yet to be confirmed as such). But you do not mean to do that in your example, anyway.

PS6: There are several cases that lead to ambiguity in case of not fully qualifying global variables. You may tinker a little to find them. They are compile errors.

Solution 2

You can do the following to learn/test the concept:

1. Open new Excel Workbook and in Excel VBA editor right-click on Modules->Insert->Module

2. In newly added Module1 add the declaration; Public Global1 As String

3. in Worksheet VBA Module Sheet1(Sheet1) put the code snippet:

Sub setMe()
      Global1 = "Hello"
End Sub

4. in Worksheet VBA Module Sheet2(Sheet2) put the code snippet:

Sub showMe()
    Debug.Print (Global1)
End Sub

5. Run in sequence Sub setMe() and then Sub showMe() to test the global visibility/accessibility of the var Global1

Hope this will help.

Comments

[L42]

I misinterpreted one line in the link I posted. Learned something new today :). Definitely +1. If I could vote more than once, I would've done so.

[oooyaya]

This was driving me mad - adding Public thing as New Thing to a module instead of to ThisWorkbook solved all of the "424" error issues when trying to access thing.something()

[Tin Bum]

Simple, thx, works for me. I've named one module "Globals" and use it for nothing else but defining global variables - no subs or functions or any code in there, just globals defined

[Alexander Bell]

You are welcome! Glad it works for you. Please mark the answer accepted if you are satisfied with the solution. Best regards,