Advanced VBA Topics in Visio

Ken Getz and Mike Gilbert

Roll Your Own Objects
Class modules are one of the most exciting additions to VBA 5.0. (These modules were introduced in Visual Basic 4.0.) Using class modules, you can create your own objects. These objects can expose their own set of properties and methods, and you can create multiple instances of the object, if your application needs this functionality.

In the simplest possible case, the code below demonstrates a Student class, and the demonstration procedure creates two Student objects, sets their properties, and then calls a method of each. (Note that public variables in the class become the properties of the class, and public procedures become its methods.)

' From Student.CLS

Public FirstName As String
Public BirthDate As Date
Public Sub Speak()
MsgBox "My name is " & FirstName & _
" and I was born on " & BirthDate & "."
End Sub

' From TestClass.BAS

Sub WorkWithStudents()
Dim Student1 As New Student
Dim Student2 As New Student

With Student1
.FirstName = "Sue"
.BirthDate = #5/1/76#
End With

With Student2
.FirstName = "Tom"
.BirthDate = #4/12/65#
End With

Student1.Speak
Student2.Speak
End Sub

In the example, the variables Student1 and Student2 refer to two instances of the class module, and each maintains its own set of properties. Therefore, when you execute the Speak method of the class, each "student" can speak for him or herself.

WAIT! If you're currently creating utility libraries as DLLs (written in C/C++/Pascal, perhaps), stop! Visual Basic 4.0 (and 5.0, of course) makes it simple to create ActiveX automation servers that can wrap up your DLL functionality, or even replace it. Then, you can call the functions exposed by the ActiveX server from any VBA host – all it requires is that your applications register the server using the Tools|References dialog. Whether you simply wrap a layer over your existing functionality, or replace it with new ActiveX servers, this is the way to go, and class modules in Visual Basic make it all possible. Just say no to Declare statements in VBA code! (You can't create these stand-alone ActiveX Automation servers in VBA, you'll need Visual Basic 4 or higher to manage this task.)

Properties Aren't Always So Simple
Sometimes, you may want to restrict a property so that it's read-only. Or perhaps you want to perform some action when a property gets set. Using public variables for properties, you can't do either of these. VBA provides Property Get and Property Let procedures, so you can run code when a property is retrieved or set, or both. The following code, a revised version of the original class module and its test procedure, demonstrates this behavior.

' From Student1.CLS

Public FirstName As String
Public LastName As String
Public BirthDate As Date
Private mstrMiddleName As String
Private mstrMI As String
Public Sub Speak()
MsgBox "My name is " & FirstName & _
" and I was born on " & BirthDate & "."
End Sub

Public Sub FormalSpeak()
MsgBox "My name is " & FullName & "."
End Sub

Property Get FullName() As String

' Retrieve the FullName property.
' This is a read-only property, of course.
' Note the missing Property Let procedure!

If Len(MI) > 0 Then
FullName = FirstName & " " & MI & ". " & LastName
Else
FullName = FirstName & " " & LastName
End If
End Property
Property Let MiddleName(pstrMiddleName As String)

' Store away the middle name.

mstrMiddleName = pstrMiddleName

' If the middle name was longer than
' a single character, store away the
' middle initial, as well.

If Len(pstrMiddleName) > 0 Then
mstrMI = Left$(pstrMiddleName, 1)
End If
End Property

Property Get MiddleName() As String

' Return the middle name.

MiddleName = mstrMiddleName
End Property

Property Get MI() As String

' Another read-only property.

MI = mstrMI
End Property

' From TestClass1.BAS

Sub WorkWithStudents1()
Dim Student1 As New Student
Dim Student2 As New Student

With Student1
.FirstName = "Sue"
.MiddleName = "Ellen"
.LastName = "Smith"
.BirthDate = #5/1/76#
End With

With Student2
.FirstName = "Tom"
.LastName = "Powers"
.BirthDate = #4/12/65#
End With

Student2.FormalSpeak
Student1.FormalSpeak
End Sub

Collectors' Items
If you've programmed in just about any language, you've come across the humble array. This data structure allows you to group a number of items of the same data type into a single variable, indexed by their position within the variable. This data structure, in Basic, is somewhat limiting, because:

• You must either specify the size of the array, once and for all, when you create it, or
• You can create a dynamic array, but resizing the array requires a massive move of all the elements

To work with collections, you'll need to first create the collection itself:

Dim colNames As New Collection

By using the New keyword, you've instructed VBA to create a new collection object for you. Now you're all set to use the Add, Remove, and Item methods of the collection object, allowing you to add, remove, and retrieve items in the collection.

Let's modify the previous class module example one more time, using a collection to store Student objects. Once you're done entering the students, the code will loop through the collection, retrieving the Name property for each student, in turn.

Sub UseCollection()
Dim colStudents As New Collection
Dim strName As String
Dim oStudent As Student

' Retrieve names until you get tired of it.

Do
strName = InputBox("Enter a name:")
If Len(strName) > 0 Then

' If you entered a name, create
' a new student, and add him/her
' to the collection.

Set oStudent = New Student
oStudent.FirstName = strName
colStudents.Add oStudent
End If
Loop While Len(strName) > 0

' Now loop through all the students, "speaking"
' the name.

For Each oStudent In colStudents
oStudent.Speak
Next oStudent

' Although the following two lines aren't
' necessary, they're a good idea.

Set oStudent = Nothing
Set colStudents = Nothing
End Sub

Passing Parameters Your Way
VBA 5.0 adds some useful new techniques for passing parameters to procedures. The following sections will introduce each of these new methods for passing parameters to procedures.

Named Parameters
Think WordBasic. Think procedures that accept 15, 20, perhaps even 25 parameters. Think of calling procedures like this:

SomeWordProcedure ,,,,,,,,,,,,1,,,3,,5

when all you wanted to do was set three of the umpteen possible parameters.

Named parameters (introduced in WordBasic, for obvious reasons, and only recently incorporated into VBA) allow you to specify only the parameters you want to specify, assuming the default values for all the rest. (See the next section, "Optional Parameters", to find out how you can create your own optional parameters.) If you specify the name for the parameters you want to send, then you needn't include all the comma placeholders:

SomeWordProcedure Widgets:=1, Foogles:=3, Wombats:=5

Use the ":=" combination to indicate that you're supplying a named parameter, and you're all set. Note that once you supply a named parameter, you must supply the names for all subsequent parameters – by supplying a name at all, you've turned off VBA's ability to use the position of the parameters.

Optional Parameters
To make the procedures you write as useful as possible in as many circumstances as possible, you may want to use optional parameters. If you do, you'll also want to supply default values for the optional parameters.

For example, the following procedure accepts two parameters, a first name and a birthdate. If you don't specify the first name, the code assumes you want to use "Sue". If you don't specify a birthdate, it assumes you want to use the current date.

Sub TestOptional(Optional FirstName As String = "Sue", _
Optional BirthDate As Variant)
Dim oStudent As New Student

If IsMissing(BirthDate) Then BirthDate = Date
With oStudent
.FirstName = FirstName
.BirthDate = BirthDate
.Speak
End With
End Sub

Of course, you can combine named parameters with optional parameters. You could call TestOptional like this:

TestOptional BirthDate:=#5/16/96#

to specify just the birthdate, for example.

ParamArray
VBA, for some reason, doesn’t include a procedure to calculate the minimum value of a list of numbers. Suppose you'd like to write such a function, allowing it to accept 1, 2, or more parameters. You need some way to take a list of parameters, and treat them as a group.

The ParamArray keyword allows you to do this. The following function accepts a list of values, and returns the minimum value in that list.

Function Minimum(ParamArray Items() As Variant) AS Variant
Dim varMin As Variant
Dim intI As Integer

' UBound returns -1 if the array has no items
' in it. Use that to make sure the caller
' passed some items at all.

If UBound(Items) > 0 Then
varMin = Items(LBound(Items))
For intI = LBound(Items) To UBound(Items)
If Items(intI) < varMin Then
varMin = Items(intI)
End If
Next intI
End If
Minimum = varMin
End Function

You might call Minimum like this:

VarMin = Minimum(1, 2, 3, 4, 5, -1, 12, 65)

No matter how many items you pass, VBA converts them into an array and hands them to Minimum in the Items parameter. The code can then work with the array as if you'd put the items in an array before calling Minimum.

Note: You can't have an optional ParamArray parameter, and once you've declared a parameter as being ParamArray, you can't have any subsequent parameters. (How would VBA know when the ParamArray list stopped, and the next parameter started?)

Part I

Ken Getz and Mike Gilbert

Also in this series:

VBA and Visio

Visio and the VBA IDE

Microsoft Forms

Advanced VBA

VBA Developer's Handbook by Ken Getz and Mike Gilbert