You need to use variables that are shared by all instances of the
class. Unfortunately, VBA doesn't support that concept directly --
well, at least as far as I have been able to figure out, it doesn't. A
workaround, while easy, leaves you vulnerable to (future) lazy
programmers.

Create a new *standard* module called cOpenServerGlobals. This will
hold the globals for your cOpenServer class. This module will contain

Option Explicit
Option Private Module
Public OpenServerUserCount As Long
Public x 'reference to the activex component

In the cOpenServer class module, add Initialize and Terminate
procedures:
Option Explicit

Private Sub Class_Initialize()
OpenServerUserCount = OpenServerUserCount + 1
If OpenServerUserCount = 1 Then
'instantiate x
End If
End Sub

Private Sub Class_Terminate()
OpenServerUserCount = OpenServerUserCount - 1
If OpenServerUserCount = 0 Then
'close and release x
End If
End Sub

Now, you can use the cOpenServer class like any other class module. It
will correctly handle the linking to the ActiveX component.

Option Explicit

Dim aServerRef As cOpenServer
Sub doSomething()
Set aServerRef = New cOpenServer
'...
Set aServerRef = Nothing
End Sub
or

Option Explicit

Sub doSomething()
Dim aServerRef As cOpenServer
Set aServerRef = New cOpenServer
'...
End Sub

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article .com,
says...
I have written a class module which creates a link to an ActiveX server
and provides some functions. Because the server doesn't seem to be
able to handle multiple references being set to it I am trying to
ensure that only one instance of my class is created and that all other
attempts to create a new instance merely set a pointer to the existing
instance. To do this I have the following in a standard module:

Private m_clsOpenServer As cOpenServer

Public Function GetOpenServer() As cOpenServer
If m_clsOpenServer Is Nothing Then
Set m_clsOpenServer = New cOpenServer
End If
Set GetOpenServer = m_clsOpenServer
End Function


I then reference the class by:
Dim clsOpenServer As cOpenServer
Set clsOpenServer = GetOpenServer

When I'm finished I destroy the object, ie.
Set clsOpenServer = Nothing

Now comes the problem. Even though clsOpenServer is destroyed,
m_clsOpenServer remains in memory and so there is still a reference to
my class object and to the ActiveX server.


I thought the thing to do here was to add a terminate procedu
Public Sub TerminateOpenServer()
Set m_clsOpenServer = Nothing
End Sub
and then call this after clsOpenServer is set to nothing. If there
were other pointers set to m_clsOpenServer then I thought it would be
held in memory even though I set it to nothing. This is not the case
however (not sure why not??) and so the next time I call GetOpenServer
a new instance is created (and then pretty soon the application I am
calling freezes up).

Is there any robust way to handle this type of situation?

Thanks a lot,
Andrew