How to create common/shared instance in vba

I would like to create one instance of class that all other could use so far I create each instance in each 开发者_Go百科class that needes its methods.

Is there a way that I could create only one instance which would be seen by other classes.

Normaly I could create some sort of static variable that would be seen by others. But it seems not possible in VBA :/

In a Module:

Private objSharedClass As myClass

Public Function GetShared() As myClass

    If objSharedClass Is Nothing Then
        Set objSharedClass = New myClass
    End If

    Set GetShared = objSharedClass

End Function

It's the VB(A) implementation of the Singleton pattern. You need to consider carefully whether or not it's really appropriate, and if so, that's the way to do it. When I use it, I usually put the above code in a Module by itself (except that if I'm using more than one Singleton in the application I put them all together in a single Module). You can add a destruction routine to the same module and call it from your application exit:

Public Sub CloseSingleton()

    Set objSharedClass = Nothing

End Sub

Or you can just let it go out of scope when the app closes--not as tidy, but I've never seen it cause a problem (I usually do clean up, though...).

EDIT

Usage (just in case it's not obvious). Either:

...
Set objLocalCopy = GetShared
DoSomethingWith objLocalCopy.MethodOrProperty
...

Or:

...
DoSomethingWith GetShared.MethodOrProperty
...

The first is preferable if you're going to use the shared class more than once in the calling routine, but the second works fine for a single call.

Make the sub or function in a Module and you'll be able to access it from your other classes.

ModuleName.MethodName