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

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)