Attributes. The High-end In VBA. – Useful Code - VBA

You have probably noticed, that whenever you export a class module from the Visual Basic Editor, you get some strange lines in the exported file, above the Option Explicit. This is something, that you have not written, but it is there. What is it?

<irony>If you are a developer, who has been taught Java and C# and forced to work with VBA, most probably somewhere at this point you realize that VBA is not a funny scripting language.</irony>

It looks like this:

code Visual Basic VERSION 1.0 CLASS BEGIN MultiUse = -1 'True END Attribute VB_Name = "CarWithDefaultProperty" Attribute VB_GlobalNameSpace = False Attribute VB_Creatable = False Attribute VB_PredeclaredId = False Attribute VB_Exposed = False

123456789

VERSION1.0CLASSBEGINMultiUse=-1'TrueENDAttribute VB_Name="CarWithDefaultProperty"Attribute VB_GlobalNameSpace=FalseAttribute VB_Creatable=FalseAttribute VB_PredeclaredId=FalseAttribute VB_Exposed=False

It is attribute. And it is used to give the VBA class some information. In this article, I will show how to create classes with:

• default property
• default procedure
• not requiring initialization (a.k.a. static from C# and the other fancy languages)

Classes with default property or default procedure means that if you assign the class to a variable, the class will return its default property. Or if you call the class, it would call the default procedure.

Class with default property

A class with a default property should have in the property the following magic word – Attribute! This word should be present in the exported version of the class, the one that could be opened in Notepad. In the VBA Editor you will not see the word. But you will understand that the class has a default property, if you go to the VBEditor library with F2 and see the small green dot over the property:

Furthermore, a description could be added as well:

This is how the property looks like:

Property.vb Visual Basic Public Property Get Price() As Currency Attribute Price.VB_Description = "Some nice description should be here." Attribute Price.VB_UserMemId = 0 Price = m_Price End Property

1234567

PublicPropertyGetPrice()AsCurrencyAttribute Price.VB_Description="Some nice description should be here."Attribute Price.VB_UserMemId=0 Price=m_Price EndProperty

It is really important, that you do not have an empty line between the declaration and the Attribute, because VBA will not assign the attribute correctly.

Class with default procedure

The idea is of the default procedure is exactly as the default property. In this code every time you call the object of class TruckWithDefaultProcedure, the Price of the truck will be increased with 10 and some information will be printed on the console:

DefaultProcedure.vb Visual Basic Public Function IncreasePriceWith10() Attribute IncreasePriceWith10.VB_Description = "Increases the price with 10. It is the default." Attribute IncreasePriceWith10.VB_UserMemId = 0 Price = Price + 10 Debug.Print "The price is now " & Price End Function

123456

PublicFunctionIncreasePriceWith10()Attribute IncreasePriceWith10.VB_Description="Increases the price with 10. It is the default."Attribute IncreasePriceWith10.VB_UserMemId=0Price=Price+10Debug.Print"The price is now "&PriceEndFunction

Important – you can have classes with either a default property or a default procedure. Both are not allowed.

Static VBA Class

Static classes are something anyone from the .Net world uses or knows how to use since their forth lesson of OOP. Until recently, I did not know that it exists in VBA. And to be honest, if I have needed it, I would have simply used a module for it. But it is good to know that VBA has it. In order to make a static class, simply change the Attribute VB_PredeclaredId = False to True. And then feel free to access this class without initializing it. As cool as it can be!

Examples of usage could be found in my GitHub account here.

That’s all, folks!

Further reading and courtesy:

• http://www.stackoverflow.com
• https://christopherjmcclellan.wordpress.com/2015/04/21/vb-attributes-what-are-they-and-why-should-we-use-them
• http://www.cpearson.com/excel/vbe.aspx

Related posts:

1. VBA – Counting Strings – Function
2. VBA – Displaying the available AddIns in Excel
3. VBA – Create Query with VBA