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...
  MultiUse = -1  'True
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. 

How to create a collection class in VBA

What you need is a NewEnum property of type IUnknown, which is a type exposed by the stdole library which Access, and presumably other VBA environments, references by default.
Private m_items As VBA.Collection

Private Property Get Items() As VBA.collection
   If m_items Is Nothing Then Set m_items = New VBA.Collection
   Set Items = m_items
End Property

Property Get NewEnum() As IUnknown
Attribute NewEnum.VB_UserMemId = -4
    Set NewEnum = Items.[_NewEnum]
End Property
Note how the NewEnum property returns an enumerator for the collection object that is contained by the class module.  Also note that the code to populate that collection is not shown. You could define an Add method...
Sub Add(Item, Optional Key, Optional Before, Optional After)
    Items.Add Item, Key, Before, After
End Sub 
...that simply leverages the Add method of the collection contained by your class, but the cool thing is that consumers won't be able to distinguish your custom collection from a VBA.Collection.  But note also the Attribute line.  To add an attribute, export the module to text, add the Attribute in a text editor, and re-import the module.

Saturday, October 06, 2012

How to create a default property in a VBA class

To create a default member in a VBA class you add the attribute...
Attribute MemberName.VB_UserMemId = 0 the second line in the procedure you want to make the default, so something like...
Private m_value As Long

Property Get Value() As Long
    Attribute Value.VB_UserMemId = 0
    Value = m_value
End Property
...makes the Value property the default, but the trick is you can't do it in the IDE's text editor. Export the module as a .cls file and add the attribute in WordPad or NotePad or something, and then re-import the class. The attribute line in the default property will not be visible in the text of the module in the IDE, but you can see in the object browser that member has the default property indicator.