Forum Discussion

perkin_warbeck's avatar
perkin_warbeck
Brass Contributor
Oct 10, 2020
Solved

Declaring a variable public in VBA

I declared a Public variable at the top of the Workbook module (immediately after Option Explicit) and initialized it in Workbook_open.  I added a Debug.Print statement to Workbook_open to make sure...
Macros and VBA
  • HansVogelaar's avatar
    HansVogelaar
    Oct 10, 2020

    perkin_warbeck 

    To declare a public variable, do so in a standard module (the kind that you create by selecting Insert > Module) instead of in the ThisWorkbook module. You only need to do this in one module.

    Make sure that you use the keyword Public:

     

    Public variablename

     

    You can still initialize it in Workbook_Open in the ThisWorkbook module.

     

    (You can, in fact, declare it in ThisWorkbook, but then you must refer to it as ThisWorkbook.variablename instead of just variablename)

HansVogelaar's avatar
HansVogelaar
MVP
Oct 10, 2020

perkin_warbeck 

To declare a public variable, do so in a standard module (the kind that you create by selecting Insert > Module) instead of in the ThisWorkbook module. You only need to do this in one module.

Make sure that you use the keyword Public:

 

Public variablename

 

You can still initialize it in Workbook_Open in the ThisWorkbook module.

 

(You can, in fact, declare it in ThisWorkbook, but then you must refer to it as ThisWorkbook.variablename instead of just variablename)

Resources