Object Oriented VBA: Static Classes


In C# classes can be given the access modifier “static”. A static class cannot be instantiated and it cannot be inherited. To call a method of a static class you simply specify

className.methodName(any parameters of the method)

Does this look familiar? In VBA, Modules are static classes. However, there is another way, a way which has the class show up under Class Modules.

In the VBA IDE create a new class. Then Remove it, making sure you Export it when asked. Then, open the .cls file in a text editor (eg. Notepad) and change the Attribute “VB_PredeclaredId” to TRUE, ie:

Attribute VB_PredeclaredId = True

Then save it, go back into the VBA IDE and Import the class back in to your Project. Now try to call any method you care to create in your static class, without going through the DIM..SET protocol. For example, My StaticClass.cls file is:

VERSION 1.0 CLASS
BEGIN
MultiUse = -1 'True
END
Attribute VB_Name = "StaticClass"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = False
Option Compare Database
Option Explicit

Public Sub StaticMethod(param1, param2)
Debug.Print "from StaticMethod:", param1, param2
End Sub

As you can see I’ve been less than imaginative with the name of the class and the name of the method I’ve defined, but there is method to this. You don’t want the name of your static class to conflict with any of your Module names nor any in-built static classes in the VBA object model, eg. don’t call it “Workbooks”. Be sensible with your use of names.

Here is a test harness that demonstrates using my static class:

Public Sub TestStatic()
Call StaticClass.StaticMethod("My", "StaticClass")
End Sub

Put this in a Standard Module and run it. You’ll see the output in the Immediate Window. Notice that I didn’t have to create a pointer to the class (ie. Dim … As StaticClass) nor did I have to create an instance of the object defined by the class (ie. Set … = New StaticClass).

There you have it. You now have two choices when creating a static class in Visual Basic for Applications.

Categories:VBA

6 comments

  1. Neat, eh? I find this is an interesting way to provide a kind of class factory method that is stored right inside of the class instead of a standard module with a bunch of different classes to sort through.

    • Thanks, I hadn’t thought of using it that way. The method would act like a Constructor – creating an instance and setting its state before returning it to the caller.

      • Yeah. Kind of exactly like that. There is a memory overhead, because you end up with a global default instance (it’s a bit different from a true static class), and it’s not *really* a constructor, but I do use it that way when I create library code or even sometimes when I’m tempted to create an `Initialize(someVar)` method.

  2. Thanks a lot
    Simple, clear and easy to do 🙂
    Regards

  3. I know this article is a little old, but I’m kinda curious… how is this different than a normal Module in VBA?

    Thanks for your time.

    • There’s very little difference. Standard Code Modules behave like Static Classes. It mainly boils down to how you want to organise your code. Static classes allow you to keep all of your code in the Class Modules section of the VBA Project.

Leave a Reply to Kyle Beckman Cancel reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: