VBA – Abstract classes. Classes, that cannot be instantiated.

There are parts of #VBA, that can scare any VBA developer, who has not bumped into other languages. Although, the “abstract class” term is quite popular in C# and other languages, and is quite easy to be created, in VBA, this is not the fact.

Let’s first start explaining what Abstract Class is – with 1 sentence – this is a class, that cannot be instantiated. Then why do we need it? In C# and other “fancy” languages, it is quite useful, because it can be inherited from, without being instanced and this is useful, because the declared methods are virtual. Which is quite fine, because later these could be overriden and implemented in the derived class. Which is one of the cool stuff about OOP and inheritance.

What about VBA? Well, VBA has a class that cannot be in instantiated. This is the way to build it.

  • Build a class with some methods and properties. Like this one:
  • Then export the class to a text file (sorry for the German, my Excel is not in English, but “Export” is quite understandable):
German is a fancy language…
  • Open the text file and change the VB_PredeclaredId = True. It should look like this:
  • Now save and import back the txt file to the VBEditor. Override the changes. And voila, you are allowed to use the class without instancing it, in a module like this:

This is how the library looks like in the VBEditor:

Noone takes the built-in VBE library interface seriously. Still, it looks nice, once you hit F2.

And because of the nice usage of Private and Public, some of the internal functins (like Dearement and Increment) are not visible when we are calling the class:

Ctrl+Space is quite useful 😉

Although the class cannot be instantiated, it gets created and it has a constructor (in VBA the constructor is only one, built-in, unless some factory method is used). Thus, in our class we have the Class_Initialize() constructor, that prints “Class is initialized” and gets the time. If we do not kill the VBA by closing Excel or writing “END” in the direct window, this time would stay the same, even if we call the class after 1 hour:

JustSeeTimeCreated written in the immediate window calls the public sub with the same name.

Creating a class, that cannot be instantiated is quite useful. With a simple “xl.LastRow(nameOfWorksheet)” you would always know the name of the worksheet. And things may get well sorted this way. Your way!

Pretty much, this is all. The code is mainly in the article and here.


A VBA Developer.