Lifetime and class modules

Hi there,

I need to have some object during all excel session. I have some class module and need to access the object of that. Now I have some trouble when using it - everytime I access the object the constructor is called each time and all old values/settings within the object are missed and even destructor is called after each access.

I would need to declare it on the start and on the end only like that:

Private Sub Workbook_Open()
    Set custom_object = New TCustom_Object
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Set custom_object = Nothing
End Sub

And then access it from VA modules like Global variable

Module1 :: Public Sub make_change1()
		custom_object.	
	   End Sub

Module2 :: Public Sub make_change2()
		custom_object.	
	   End Sub

I've tried to declare it as Static object inside procedure - still no work.

Public Sub...
         Static custom_object As New TCustom_Object

Any idea ? TIA

Hi mikerickson,

first of all THX for replay but I don't think it's the solution. Moreover this is not a correct OOP construction.

What I need is to have object which is instanced only once on the Excel's start and available all the Excel's session time without any reinstancing.

It should have something to do with Static word statement and a correct scope (and even maybe instancing of the module class too).

Anyway THX for nfo !

Sorry but I don't agree with you still. Your maybe work for constant but I need to have counter variable inside the class. Like this one:

Class modules :: TCustom_Object

Private p_sheet As Worksheet
Private p_left as Integer
Public count As Integer

Private Sub Class_Initialize()
          ' constructor
           count = 0
End Sub

Private Sub Class_Terminate()
           ' destructor
End Sub

Public Property Let left(vdata As Integer)
         p_left = vdata
End Property

Public Sub proccess1()
         ....                         ' some code here
         count = count + 1
End Sub

This is my class module. It's all about how to stay alive during full Excel's session. When Excel's terminating I want to show count variable of the object. That's all. But now everytime I call obj.proccess1 both constructor & destructor is called -> no old count status...

Problem still persists but THX for your effort !