|Visual Basic Tutorial 11- VBA II
VBA-Visual Basic for Applications is slowly becoming the macro language
for Windows. Slowly because unlike CShell in Unix or Rexx in OS/2 or .bat files
in DOS and Netware; VBA does not come free with Windows. Rather software
vendors have to pay a licensing fee to add VBA capabilities to their products
- so AutoDesk, Corel, Visio andnearly 150 others pay a fee (upto $30 per copy)
to include VBA macro capabilities in their programs.
In addition, there have been technical factors slowing the acceptance of VBA - Microsoft had really four editions
of the language varying more than slightly between Word, Excel, PowerPoint, and the standard VBA edition. This
has been somewhat rectified with Office 97,
Figure 1 - VBA in Word
|Office 2000 and Visual Studio 6 (see upcoming VB 12 tutorial for
details). Finally, VBA macros have been used by hackers as an infecting agent
for their viruses like the infamous Melissa Virus that spread like wildfire
through Outlook and Word last Spring and Summer. Again, Microsoft has moved
close the door with the default
behavior now being that macros cannot be run without user approval and signed-authenticated
macros allow organizations
to control in a password-like system what macros are allowed
to be used on their networks.
Despite these financial constraints and technical impediments, VBA has continued
to garner interest. And the reason is simple - VBA can allow users to draw
upon the vast functionality of Office 2000, Corel Office 2000, AutoDesk
AutoCAD, Micrografx Flowcharter, Peachtree Accounting, and dozens of other
programs are available through VBA macros and add-ons while developing their
own applications. The results is that major chunks of code do not
have to be developed but rather developers use directly Excel's charting routines
or Word's formating capabilities.With speed of development being a critical
factor in so many systems - many organizations are trying to leverage more
from their existing software assets and VBA allows them to develop with Corel,
Microsoft, Peachtree and other commercial software as part of their software
VBA-A Direct Subset of VB
If you look at Figure 1 you will see VBA from Word. It looks a lot like VB. In part, VBA is a direct subset of
VB - all the tools and command syntax of VBA work in VB. However, there are distinct differences. Each application
program that VBA is adapted to brings its own distinctive semantics. For example, VBA in Microsoft Word uses Documents
and Pages as in Sub Document_Open(); VBA in Microsoft Excel uses Workbooks and Sheets as in Sub Workbook_NewSheet();
and VBA in PowerPoint uses Presentations and Slides as in Sub Presentation_Close(). So your task will
be to apply the familiar syntax:
Integer, Long, Single, Double, Booleans, String, Object, Variant and other VB data types;
Dim, Public, Private - declaration of variables, arrays, subroutines, and procedures;
if - then, elseif, end if; select case, case, end select; goto; on error and other flow of control
for-next; do while - loop; do - while loop and other iteration and looping commands;
MsgBox, Format(), Mid$(), Left$(), IsNumeric() and hundreds of other already familiar VB functions;
Caption, Text, AddItem, Screen, Visible and hundreds of familiar objects, properties, methods, and events;
to the process of writing VBA code. Helping you will be many of the tools, dialogs, windows and wizards that you
have been using in our previous VB tutorials. For example, VBA uses the following VB tools:
For visual layout and design:
-Toolbox of active components to drag and drop on your form;
-Project Explorer to help navigate among forms, modules, and other source code;
-Property Sheet to change the properties of any object/control used on your form(s);
-Object Browser to help identify predefined classes and collections which is indispensable in VBA;
For source code editting:
-Code Editor which does automatic line-by-line syntax checking;
-Fancy Formatter in the Code Editor which auto-colors code, auto-indents and inserts spaces;
-Intellisense technology helps complete object names and fill in subroutine/function parameters;
For running and debugging your code:
-Immediate Window allows printing intermediate results;
-Watch window shows preselected variables and any changes in their values;
-Breakpoints in the Code Editor allow you to step through your code & inspect variable values by:
1)using mouse hover hints to reveal a variable's value;
2)printing and/or reassigning the variable in the immediate window;
3)adding and changing variables in the Watch window.
The whole trick in VBA is applying these familiar tools to the very specific collections and classes of a particular
VBA application like Corel Presentations or Microsoft Word. So let's create in Microsoft Word a special VBA dialog
called a UserForm which will insert header and footer text into a document.
|Figure 2 show the finished UserForm. It allows the user to insert any header text and a copyright symbol, ©,
at the front of the header text. If the header already has any text, then the text is inserted into the footer.
Finally, by checking the right boxes, users can insert any combination of Time, Page Number, and Date into the
footer as well.
The most important point to note is that all VBA's textboxes, command buttons, check boxes not only work the same
but use nearly the same icons plus
Figure 2 - Word Macro with UserForm
|drag and drop procedures readers are already familiar with from working with VB. Here is how we created the UserForm
in VBA in Word. First, we started Word (97 or 2000), clicked on Tools | Macro | Visual Basic Editor. VBA starts
up like in Figure 1. Second, in VBA select Insert | UserForm and a blank form just like in VB pops up with the
toolbox. From here, it was easy to drag and drop the five checkboxes, change their captions to Copyright, Time,
PageNumber, Page No with Totals and Date. Then adding the textbox (and changing its Multiline property to true)
and command buttons for Okay and Cancel is trivial. The harder part is getting the UserForm to put the text and
selected fields into any Word document header and footer. Fortunately, VBA provides a nifty shortcut.
All I had to do was record a macro (Tools | Record a new Macro) performing the tasks. VBA then records all the
steps for say inserting the Time and Date fields into a footer. Then after stopping recording the macro you immediately
edit it (Tools | Macro | Macros | Edit). Voila, VBA shows you the precise code to use and all you have to do is
copy the VBA code into the right spots in your UserForm's source code. Better still you can Export your UserForm
and re-use it in other VBA or VB programs (File | Export).
However, there remains two tricks to getting your UserForm to work from Word's menus and toolbars. First, I created
a new, empty macro named HdrFooter (Tools | Record a new Macro) and then stopped the macro immediately without
recording any keystrokes or mouse selections. Then I immediately editted HdrFooter (Tools | Macro | Macros | Edit)
and added one line:
I saved the changes and had just created my own Word command. In effect the HdrFooter macro calls my UserForm
and makes Word display it on screen as in Figure 2. Now the last task is to make your command readily avaialable
in Word. This means adding the HDrFooter macro to a toolbar or directly into Word's menus. Here is where another
trick is used.
To add a macro onto your own customized toolbar and into Word's menus is described in detail in the Word help system.
The basics are to create your own custom toolbar (Tools | Customize | Toolbars | New). Add the macro HdrFooter
to your toolbar. Then drag and drop the HdrFooter toolbar icon onto the specific menu you want to include it in.
And now you have customized Word with your own VBA-developed command.
This is the power and attraction of VBA - users can create their own
commands and customize programs like Corel Word Perfect, Peachtree Accounting,
Visio and any other program that runs VBA. If the concepts seem a bit
fuzzy, don't worry we have stepped quickly through an advanced exercise.
In our next tutorial we whall go over some of the steps in more detail.
In addition, there a
number of books on VBA - Visual Basic for Applications Fundamentals,
David Boctor - Microsoft Press, 1999 is a very good starting reference
neither Beginning VB or the VB6 Black Book cover VBA at all.