Object Oriented Programming in VBA
This month I will be writing about an advanced
programming technique – Object Oriented Programming or OOP. I will only be
touching on the very basics and how it relates to VBA. Visual Basic (at
least version 6 and earlier) is not a true object oriented programming
language. VB.Net is closer to being a pure object oriented design, but I
digress. Before we delve into whether or not it is an object oriented
language, let’s find out what object oriented programming is.
Object oriented programming by definition is taking
real-world situations and objects and creating a program based on those
objects. For example, an ATM is something we are all familiar with.
Converting it to OOP we will say that the ATM is an object. Not only that,
but the ATM has other objects as components. It has a screen object, a
keypad object, a vault object and many others. Each object interacts with
each other and reacts to events. One event is inserting a card object into
the card reader object. The card reader reacts to that event by reading the
magnetic strip on the card and initializing a connection with my bank
account. A successful connection is another event, and the screen responds
by displaying a prompt for my PIN. The next few events consist of key
presses, which in turn signal transactions, removal of money from the vault,
and disconnection from my bank account.
The MS Office products are also composed of objects.
Excel, for example, has a workbook object. A workbook object is composed of
sheet objects which have cells, rows, columns, charts, pivot tables and etc.
(all objects). To view all of the objects which can be created press
Alt+F11 from most Office applications to open the Visual Basic Editor. Then
press F2 to open the Object Browser. One of the panes in the object browser
will be called “Classes.” Scroll through the list to see all of the types
of objects which can be created and used.
If you are in Excel, select Worksheet from the
“Classes” list. In the pane named “Members of ‘Worksheet’” the first three
items are: Activate, Activate and Application. Each has a different icon in
front of them. The first one looks like a green flying thingy (how’s that
for descriptive?) and indicates a method. Methods can be called from macros
or events. The syntax is Object.Method([Parameters]). Using the
Activate method on Sheet1 would be: Worksheets(“Sheet1”).Activate.
The second item in the list is Activate again, but this
time the icon is a lightening bolt which represents an event. Events are
handled in the module associated with the object. The Project Explorer
contains a list of all of the open projects in the application. In Excel,
this usually consists of one or more workbooks and any add-ins that have
been installed. Within each project is a list of the major objects. Double
click any object in the Project Explorer to open the module associated with
it. To handle the Activate event for Sheet1, double click ‘Sheet1’ and
type:
Private Sub Worksheet_Activate()
End Sub
The general form is:
Private Sub ObjectType_Event([Parameters])
‘Code
End Sub
Now any code you type between the first and last line
will be run whenever the event is triggered (Sheet1 is Activated).
The third item in the list is Application. The icon
looks like a hand holding a piece of paper. This indicates a property of
the object. A property is used within macros and events just like the
method, but instead of performing an action, it will return or set a value.
The general form is: variable = Object.Property to retrieve a
value or Object.Property = variable to set a value. In the
case of Application, it is a read-only property so the value can’t be set.
To retrieve it use: strVariable = Worksheets(“Sheet1”).Application.
That will store “Microsoft Excel” in the strVariable.
We’ve just touched the surface of OOP. Most
universities teach OOP in classes that range from one semester to four.
That is how important objects are in programming. I’ve shown you the basics
in this article not so that you will be expert OO programmers, but to
enhance your understanding of what is available in VBA. Few people
understand the power of the events. For example, if you want a date/time
stamp placed on a document every time it is saved, use the BeforeSave
event. If you want to update a footer whenever it is printed use the
BeforePrint event. The list goes on and on. As you become more
familiar with the events and capabilities, your documents will become more
automatic and even more professional.
Thank you for your suggestions of topics for this
article. Please keep the suggestions coming! If I use your suggestion and
you are the first to submit it, I will pay you $5.00.
Please send an email to chad@welchkins.com and put Idea for Macro Mania in the subject line.
Chad K. Welch works as a technician/enabler in
Utah. He is available for
consulting or application programming with Microsoft Office and VBA.
Do you have a question or
tip you’d like to have Chad address in this column? Send an
email to linda@personal-computer-tutor.com
or contact him directly at chad@welchkins.com for more information.
,
