Share:

" /> Visio Guy » VBA Class Property Generator
Home » Add-ins & Tools, Code, Tools

VBA Class Property Generator

Submitted by on May 20, 2009 – 7:58 pm | | 16209 views 5 Comments

vba-class-properties-generatorDon’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:

VbaPropertyGenerator Code Module (2.47 KB) - 284

5 Comments »

  • nashwaan says:

    Hi Visio Guy,

    I have download the VBA but i don’t know how to install it. Should i copy the .bas file to certain folder? and how to run this VBA module.

    Thanks,
    Yousuf

  • Visio Guy says:

    Hi Yousuf,

    Usually you can import .BAS files into a VBA project.

    To use the code, do as the article says: “First you configure a string-constant with a list of names and types, separated by semicolons”

    This is the PropertyList$ near the beginning of the code.

    Then you simply run Sub VbaPropertyGenerator(). Your class will be output to the immediate (Debug) window.

  • Chabu says:

    Hello Visio Guy,

    Thanks for the utility, It will save time (also by avoiding dumb typing errors)
    I Think you should add Date to the NativeTypes though.

    Greetings
    Bruno

  • rikkouri says:

    awesome tool! sick of writing properties! Thanks for this!

  • semerkhet says:

    very nice tool.
    bravo!

Leave a comment!

Add your comment below, or trackback from your own site. You can also subscribe to these comments via RSS.

Be nice. Keep it clean. Stay on topic. No spam.

You can use these tags:
<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

This is a Gravatar-enabled weblog. To get your own globally-recognized-avatar, please register at Gravatar.

*