VBA Class Property Generator
Don’t you hate typing Property Get and Property Let and Property Set into your VBA class modules over and over and over and over?
Today I’d like to share a little VBA code module that I like to use to generate VBA class properties.
In .NET, Visual Basic’s syntax has gotten more efficient which makes the job easier. Visual Studio has gotten smarter: it assists you with a lot of mundane typing tasks. So once you get used to modern technology, going back to VBA or VB6 can be rather painful.
So I’ve created this utility code to ease my sorrow, and hopefully help you out too!
A lot of customers still like to get smaller solutions built in VBA. I do a lot of automation work with Visio, and customers like that they can get in and tweak the code without having to do a re-compile. At very worst, they can sick a summer intern on it. There’s a comfort in knowing that the consultant’s work isn’t buried in some .exe or .dll that nobody will know how to update. Fair enough.
When you create a class, you often times declare a private variable, then expose it with public properties. Your private variable might have some sort of prefix to distinguish it from the public variables, and an initial lower-case letter:
Private m_visShape As Visio.Shape
Then you expose them with public properties. They usually don’t have the prefixes, and have initial caps, like these:
Public Property Get VisShape() As Visio.Shape Set VisShape = m_visShape End Property Public Property Set VisShape(value as Visio.Shape) Set m_visShape = value End Property
So you have to type Visio.Shape three times, Set twice, Property twice (VBA does the End Property for you), and VisShape + m_visShape six times! This is mind-numbing at best, especially when you consider that the code hardly does anything other than “enforce good coding habits.”
I figured, I would rather just type the variable names and data types once, then let some code automate the creation of the properties. The VBA module that accompanies this article does just that.
First you configure a string-constant with a list of names and types, separated by semicolons:
Private Const PropertyList$ = _ "ID;String;VisShape;Visio.Shape;VisDoc;Visio.Document;" & _ "VisPage;Visio.Page;ShapeID;Integer"
Then you run the code, and it creates the skeleton of a VBA class for you in the Debug (or Immediate) window! You can just copy and paste the output into a new class definition.
The code-generation does a few other things as well:
- Adds “Option Explicit” at the beginning
- Determines which data types are objects that require Set instead of Let
- Creates some header comments
- Creates a ToString() function that serves as a text dump for you class.
Be sure and read the comments that are written in this function!
- Creates a Class_Terminate function that sets all object-variables to Nothing
When you run the code for the PropertyList$ constant shown above, you’ll get this as a result:
Note: this is the output code, not the source code!
Option Explicit '// Class: C..._ '// A class for... Private m_iD As String Private m_visShape As Visio.Shape Private m_visDoc As Visio.Document Private m_visPage As Visio.Page Private m_shapeID As Integer Public Property Get ID() As String ID = m_iD End Property Public Property Let ID(value as String) m_iD = value End Property Public Property Get VisShape() As Visio.Shape Set VisShape = m_visShape End Property Public Property Set VisShape(value as Visio.Shape) Set m_visShape = value End Property Public Property Get VisDoc() As Visio.Document Set VisDoc = m_visDoc End Property Public Property Set VisDoc(value as Visio.Document) Set m_visDoc = value End Property Public Property Get VisPage() As Visio.Page Set VisPage = m_visPage End Property Public Property Set VisPage(value as Visio.Page) Set m_visPage = value End Property Public Property Get ShapeID() As Integer ShapeID = m_shapeID End Property Public Property Let ShapeID(value as Integer) m_shapeID = value End Property Public Function ToString() as String '// Note: not all of the variables in this class will '// necessarily be convertible to strings. In these '// cases, you will get errors in this function. '// In these cases, either remove the line that contains '// a variable that can't be converted to string, or '// substitute some option that can be, such as '// m_someObject.String or m_someObject.Name Dim s As String s = "ID = " & CStr(m_iD) & VbCrLf & _ "VisShape = " & CStr(m_visShape) & VbCrLf & _ "VisDoc = " & CStr(m_visDoc) & VbCrLf & _ "VisPage = " & CStr(m_visPage) & VbCrLf & _ "ShapeID = " & CStr(m_shapeID) ToString = s End Function Private Sub Class_Terminate() Set m_visShape = Nothing '//...(Type = Visio.Shape) Set m_visDoc = Nothing '//...(Type = Visio.Document) Set m_visPage = Nothing '//...(Type = Visio.Page) End Sub
Here’s the VBA module that will help you get it done: