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