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!

'// 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: