VB6/VBA 11, Overview 2
 
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 to partially 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 team.

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 commands;
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:

UserForm1.Show

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 since neither Beginning VB or the VB6 Black Book cover VBA at all.
 
Top of Page  Tutorials Home