Excel :: Global Class Instance

[Seorch]

Hi,

I’m new to VBA and especially how to use it in an OO way. My intention is to create a base class "myClass" with common methods to use within my Excel application. These methods should be available in userForms and other modules (global scope).

Don’t know if it’s possible, but I would prefer to insatiate the class on "Workbook_Open" and then use the instance in other modules.

Example

myClass (Class Module)

Public Sub Alert(Value As String)
   MsgBox Value
End Sub

ThisWorkbook

Dim My As myClass

Public Sub Workbook_Open()
   Set My = New myClass
End Sub

Module1

Sub Test()
   My.Alert "Message"
End Sub

Is there a way of achieving this? The reason is that I don’t want to instantiate the class over and over again.

 

[Norie]

Not 100% sure what you are trying to do but for this example try declaring My as Public in a standard module.