Declare public variables on Workbook.Open?

[svendiamond]

How do I get my workbook to declare public variables on the Workbook.Open event? The following code gives an error because I'm trying to declare a public variable inside a sub-routine (this is in the "ThisWorkbook" code)

Private Sub Workbook_Open()


Public enableTestMode As Boolean

enableTestMode = Range("i_enableTestMode").Value


End Sub

But that's basically what I want to do... as soon as the workbook opens I want to set up this variable. What about this:

Public enableTestMode As Boolean

Private Sub Workbook_Open()


enableTestMode = Range("i_enableTestMode").Value


End Sub

^ but would that work? How does Excel know to declare that variable on the Workbook.Open event when it's not in that sub-routine?

 

[Joe4]

Public variables are never declared in a sub-routine. They are always declared outside of them (usually put at the top of your module).
Their values are set within a sub-routine.

By declaring it outside of the subroutine, all you are doing is making its values available to all your procedures/functions in your module.

See:
Variable Scope in Excel VBA - Easy Excel Macros
and
https://msdn.microsoft.com/en-us/library/office/gg264241.aspx

 

[mikerickson]

You might want to re-cast the public variable as a function, put in a normal module.

Function EnableTestMode() as Variant
    enableTestMode = Range("i_enableTestMode").Value
End Function

 

[svendiamond]

Ah! Thank you Mike, that works perfectly!