Re: Objects, Scope, and Worksheet_Calculate Event Procedure

Tech-Archive recommends: Speed Up your PC by fixing your registry

From: onedaywhen (onedaywhen_at_fmail.co.uk)
Date: 03/31/04 

Date: 31 Mar 2004 00:20:42 -0800

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. 

--
"Braden" <anonymous@discussions.microsoft.com> wrote in message news:<1174601c416c2$d35b2990$a001280a@phx.gbl>...
> 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
Quantcast