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

User · Answer

You can do the following to learn test the concept    Open new Excel Workbook and in Excel VBA editor right-click on Modules- Insert- Module In newly added Module1 add the declaration  Public Global1 As String in Worksheet VBA Module Sheet1 Sheet1  put the code snippet     Sub setMe         Global1    Hello  End Sub     in Worksheet VBA Module Sheet2 Sheet2  put the code snippet     Sub showMe       Debug Print  Global1  End Sub     Run in sequence Sub setMe   and then Sub showMe   to test the global visibility accessibility of the var Global1   Hope this will help

User · Answer

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

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

Examples related to excel

Examples related to module

Examples related to global-variables

Examples related to public

Examples related to vba