How to Set a Variable in Another Module?

[Steve Drenker]

Basic Question: How do I assign a value to a variable in a code module from
the ThisWorkbook module, Workbook_Open event?

Background Context: I've got a worksheet (called "Assumptions") in a
workbook that needs to have Calculation set to manual. I want to be polite
to the user and store the existing Application.Calculation state so I can
restore it when the user switches to another worksheet. In other words, do
not just switch Calcualtion to manual and leave it there.

The following Worksheet-level code works fine AS LONG AS I closed the
workbook in another sheet and then select the Assumptions worksheet to
trigger the Worksheet_Activate event handler. This initializes the
module-leve global variable OrigCalc via the Worksheet_Activate handler.


Option Explicit

Dim OrigCalc As Long
' Need to store original state to a Long data type:
' xlAutomatic = -4105
' xlSemiautomatic = -2
' xlManual = -4135

Private Sub Worksheet_Activate()
OrigCalc = Application.Calculation
Application.Calculation = xlManual
End Sub

Private Sub Worksheet_Deactivate()
Application.Calculation = OrigCalc
End Sub

If, however, I closed the workbook with worksheet "Assumptions" activated,
the next time I open the workbook, the global "OrigCalc" does not get
initialized (it appears that the Worksheet_Activate event is not triggered
after the Workbook_Open event). In this case, the immediate window shows...
?origcalc
#NOTHING#

So, I thought a simple solution would be...

Private Sub Worksheet_Deactivate()
If Not(OrigCalc = Nothing) then
Application.Calculation = OrigCalc
End If
End Sub

But this doesn't work. I get a "Invalid Use of Object" error with "Nothing"
highlighted.

One solution would be to have the Workbook_Open event handler initialize the
OrigCalc global in my main module.

So, my question to you is how do I assign a value to a variable in a code
module from the ThisWorkbook module, Workbook_Open event?

Sorry for the long explanation for such a simple problem.

Steve

 

[Tom Ogilvy]

Declare you public variable in a general/standard module (in the vbe,
Insert=>Module).

Don't declare it anywhere else.

Then it is visible to all code in the project. You can set it in
Workbook_Open and reference it from your sheet modules.