Objects, Scope, and Worksheet_Calculate Event Procedure

[Braden]

Sorry about the last post. I tried to format my code and
the message went out prematurely.

Again, I would like to create an object on Workbook_Open
that is accessible to and that persists between
successive calls to a Worksheet_Calculate event procedure.

I can successfully create an object on Workbook_Open.
The following code works (please forgive the formatting):
------------------------------------
Private Sub Workbook_Open()
Dim tester As EventTry
Set tester = New EventTry

Worksheets("ValsAndParameters").Cells(25, 1).Value
= "Working"

End Sub
-----------------------------------


(At least I think this code works fine since the cell
value above does show up as "Working" after I open the
workbook.)


My EventTry Class is defined as follows:
---------------------------------------------
Option Explicit

Private m_nextFiveRow As Long

Private Sub Class_Initialize()
m_nextFiveRow = 7
End Sub

Public Sub loveMe()
Worksheets("ValsAndParameters").Cells(10, 1).Value =
m_nextFiveRow

End Sub
----------------------------------------------


As you can see, I am instantiating my tester object in
the Workbook_Open event procedure. I try to call it from
a Worksheet_Calculate event procedure with the following
code:
--------------------------------------------
Private Sub Worksheet_Calculate()
tester.loveMe
End Sub
--------------------------------------------


When the above Worksheet_Calculate event procedure is
called it generates the following error:
--------------------------------------
Run-time Error '424':
Object Required
--------------------------------------


Again, I would like to create an object on Workbook_Open
that persists and that is accessible to successive calls
to a Worksheet_Calculate event procedure. I expected to
run into this sort of scoping problem, but unfortunately,
I don't know how to get around it.

If anyone can help me with this problem I would be
greatly appreciative.

Thanks in advance for any help.

Braden

 

[Braden]

I figured out how to do this. Thanks.

Just needed to look for help about scope instead of
looking for help about events.

Braden

 

[onedaywhen]

In your code the 'tester' variable is local to the Workbook_Open sub
procedure and will get destroyed when Workbook_Open ends (put a
breakpoint in your class's _Terminate event to demonstrate this).

One way to resolve this is to declare it somewhere which will ensure
it survives the scope of Workbook_Open e.g. declare it as a
module-level variable in the ThisWorkbook code module i.e.

Private tester As EventTry

Something else you may need is to test whether the object variable has
been instantiated e.g.

Private Sub Workbook_Open()
If tester Is Nothing Then
Set tester = New EventTry
End If
...

This is good practice, although one could argue that Workbook_Open
only gets executed once (of course any sub procedure in the
ThisWorkbook module can call Workbook_Open; change its scope to Public
and it also can be called from elsewhere in the project).

Aside: if your class has custom events - its name suggests it might -
then take a look at the WithEvents keyword, you may need it in your
variable's declaration.

--