Public Variables, Class Modules and Standard Modules

[Excel Monkey]

I have a Class module that I am using to trap Application level events. I
have a bunch of variables declared at the top of the class module. I then
call a bunch of subs and compare the originally declared "old" variables and
the "new" ones. An example of this might be a vOldName and CurrentName.

As Excel does not have an AfterSave event, I a simulating one (See Example
2)using the Application.OnTime stmt in the BeforeSave event. What I like
about this is that if the user chooses SaveAs the sequence of events is as
follows:

- Call Application Ontime Now "'AfterSave'"
- SaveAs dialog pops up
- AfterSave sub runs and uses the renamed file name entered in the SaveAs
dilag box and passes this to CurrentName.

However I cannot put the vOldName = CurrentName in this other sub as
vOldValue is not a public variable. I considered making CurrentName a public
variable in the other sub and then putting the vOldName = CurrentName in the
Class module. However this affects the sequence of events as the SaveAs
dialog is displayed after the AfterSave sub runs. This precludes me from
getting a value for CurrentName.

How do I get around this? I obviously need to keep my variables declared at
the top of the Class module to retain them after the code runs. However, the
forced usage of a sub outside the Class module via the Application.OnTime
stmt does not allow me to update my Class variable within this sub. Trying
to do so in the Class module renders the sequence of events useless.

Any ideas?

Example 2
'***************************************
'This is in a class module
Dim vOldName as String
App_WorkbookBeforeSave(ByVal Wb As Workbook, _
ByVal SaveAsUI As Boolean, Cancel As Boolean)

'Not the sue of the double double and single quotes
Application.OnTime Now "'AfterSave """ & vOldName & """'"

'Note you might think that I could put vOldName = CurrentName here after the
call for DoSomething. However this displays the SaveAs Dialog after this very
line of code

End sub

'This is in a regular module
Public CurrentName as String
Sub AfterSave(ByVal vOldName)
'Do Something

'Here is the problem. vOldName will not retain this value after the routine
'has finished as its not a public variable.
vOldName = CurrentName

End sub
'*************************************

 

[Chip Pearson]

You can certainly update variables in an instance of a class from code
outside the class. For example,


'''''''''''''''''''''' In a regular module
Dim CEx As Class1

Sub SetIt()
If CEx Is Nothing Then
Set CEx = New Class1
End If
CEx.TheClassVar = 1234
End Sub

Sub GetIt()
Debug.Print CEx.TheClassSVar
End Sub

'''''''''''''''''''''''''''' In Class1
Public TheClassVar As Long



Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

 

[Excel Monkey]

So Chip I think this works. Pardon my daftness, how do I now retain the old
value of vOldName prior to assigning its new value? Typically what I have
been doing is declaring this value at the top of the Class (i.e. not a public
variable in the class but a regular variable declared at the top of the
class) and then I pass it to the AfterSave sub. The value of this variable
was always in memory even after the code ran.

Here I am delaring it as a public variable and not passing it all. When its
first value is set in my App_WorkbookOpen event it will not be retained as it
is now being declared as a public variable which is lost after my
App_WorkbookOpen evnt runs.


'Class Module
Public vOldName as String 'This is now a public variable
Private Sub App_WorkbookOpen(ByVal wkb As Workbook)
vOldName = ActiveWorkbook.Name 'This wil be lost after the code runs
End Sub

Private Sub App_WorkbookBeforeSave(ByVal Wb As Workbook, _
ByVal SaveAsUI As Boolean, Cancel As Boolean)
Application.OnTime Now, "'AfterSave'"
End Sub
'******************************************************
'Regular Module
Sub AfterSave()
Dim CurrentWkbkName As String
Dim CEx As EventClass

CurrentWkbkName = ActiveWorkbook.Name

If CEx Is Nothing Then
Set CEx = New EventClass
End If

'!!!This will not have a value in it as it was lost after the initial code ran
Debug.Print vOldWrkBkName

CEx.vOldWrkBkName = CurrentWkbkName

Debug.Print vOldWrkBkName 'This works
End Sub

Thanks for your response.

EM