Sunday, October 07, 2012

How to create a public shared instance of a class module in VBA

To create a public shared instance of a class in VBA, export a class module to text and load it into a text editor of your choice.  You'll see something a lot like this at the top of the module...
VERSION 1.0 CLASS
BEGIN
  MultiUse = -1  'True
END
Attribute VB_Name = "Class1"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = False
Attribute VB_Exposed = False
Option Compare Database
Option Explicit

Property Get Notice() As String
    Notice = "This is a shared class"
End Property
Then, change the values of the GlobalNamespace and PredeclaredID attributes to True and re-import the module. To test it in the immediate window type...
? Class1.Notice
...and you'll see This is a shared class printed in the immediate pane. I'll use this capability to create a single cSystem class that can both raise and handle events, and that provides system-global exposure for classes and members that I like to hang on to for the lifetime of an application.  I'm curious to see how this affects load speed of an application, since until now I've opened a hidden "Form_fSystem", but it seems to load very slowly. 

No comments:

Post a Comment