2.1. Getting a VB Program to Run
Regardless of the type of application you're writing and the development tool (hosted VBA or the retail version of VB) you're using, there has to be a starting point or an entry point for your program. Here there is a major difference between VB and VBA: a VB application is launched as an application in its own right, whereas the VBA program has to be launched by the host application. But in either case, the starting point you choose is decided by the type of application you are writing, as well as by the facilities offered by your development environment for launching applications. In this section, we'll look at the methods available to you for starting your application.
Because VBA is now hosted in a wide range of different applications, each of which has its own ways of launching an application or routine, it's impossible to describe here how to start your program running in each. Instead, we'll focus on the two most popular applications for hosted VBA, Word and Excel.
|
2.1.1. Running VBA Modules in Word
A Word/VBA program can take a multitude of forms, ranging from a small routine that accomplishes some utility function at one extreme to a complete application that handles every detail of the user's interaction with Word. Of course, you want the method that invokes your program to be consistent with its general purpose. Fortunately, Word provides several ways to launch a VBA application.
2.1.1.1. Storing your code
Whenever Word
starts, it automatically loads the default global template file,
normal.dot. It then loads all template
(.dot)
files in the Word startup directory,
which (assuming the software is Word 97) is defined by the
STARTUP-PATH value entry in the
HKEY_CURRENT_USERSoftwareMicrosoftOffice8.0
WordOptions key in the registry and can be customized by
selecting the File Locations tab from the Options dialog (Tools
Options) and modifying the Startup entry. These also become
part of Word's global layer, as do Word add-in
(.wll ) files, which are loaded last. So if your
application is to affect the Word environment or multiple Word
documents, you should place your code in a template that is loaded
into the global layer. If your program is to be distributed to other
users, you should store your code in a global template file other
than normal.dot, where you're likely to
overwrite customizations the user has made.
|
If your application applies to a set of documents that are based on a template (which is typically stored in the Office Template directory or one of its subdirectories), you can place your code in the template file. Each document created using that template maintains a reference to the template. So even though the code remains in the template and isn't copied to the document, the VBA code in the template can be executed as long as the reference is valid.
If your application applies only to a particular document, you can store the code in the document itself. You don't have to work with the templates loaded into Word's global layer.
2.1.1.2. At startup
If you are developing an application or routine that is responsible for initializing the Word environment, that provides some service expected to be available throughout a Word session, or that implements a customized interface that mediates between the user and Word, you want to have Word launch your application whenever Word itself is launched. Word provides two methods of doing this. Both are remnants of WordBasic and both require that you store your macro in a global template:
Add a procedure called AutoExec to any code module. In order to execute at startup, it must be a Public procedure.
Create a new module named AutoExec and add a procedure to it called Main. Once again, Main must be declared as Public in order to run at startup.
There is also a converse scenario—running a procedure when Word is closing—that operates in exactly the same way as AutoExec. You simply name the procedure AutoExit or include an AutoExit module with a Main procedure.
2.1.1.3. When a document loads
In many cases, your application should launch whenever a particular document (or a set of documents, or even all documents) is opened. Once again, Word offers several methods of executing code when an existing document is opened or a new one is created. All of them require, though, that the code be located either in the current template or in the document itself. The methods are:
Creating a procedure called AutoOpen, which is executed whenever an existing document containing a reference to AutoOpen's template or containing the actual AutoOpen code is fired. Similarly, you can create a procedure called AutoNew, which is executed when a new document using the template containing the code is created. The procedures must be declared as Public to be visible. AutoOpen and AutoNew macros are a WordBasic, rather than a VBA, feature.
Creating a code module named AutoOpen (or, for a new document, AutoNew) and defining a public Main procedure in it. AutoNew and AutoOpen code modules are a WordBasic, rather than a VBA, feature.
Attaching code to the template's or document's Document_Open event, which fires when an existing document is opened, or to its Document_New event, which fires when a new document is created based on the template containing the Document.New event handler. This is the "official" VBA way to create self-executing macros when a document loads.
You can also designate a cleanup routine to execute when a document closes. WordBasic recognizes either an AutoClose procedure or a Main routine in a code module named AutoClose. VBA fires the Document.Close event when a document closes.
2.1.1.4. In response to direct user action
Frequently, VBA/Word code is less an "application" as we typically understand it than a "macro"—i.e., a small piece of self-contained code that performs some useful function. For macros to be useful, there has to be a way for the user to run them easily[1] from the Word interface. In this respect, Word provides a rich environment for the macro developer, since it supports so many ways of hooking a macro to the user interface. These include:
[1] The most obvious method is one we won't discuss here. When the user selects Tools
Macro
Macros, Word displays the Macro dialog, which lists the names of all macros that are in scope (i.e., public macros in the global layer, macros in the current template, and macros in the current document).
Intercepting Word's built-in commands
Most common Word operations are public procedures. This means that if you create a procedure of the same name and store it in a global template, in the current document's template, or in the active document itself, your procedure, rather than Word's built-in procedure, will execute. For example, when the user selects the Close option from the File menu, the FileClose procedure executes. Ordinarily, Word closes the active document. However, you can modify Word's behavior by substituting a FileClose routine like the following, which gives the user the option of closing all open documents:
Public Sub FileClose() Dim lngResponse As Long Dim objDoc As Document If Documents.Count = 1 Then Application.ActiveDocument.Close Else lngResponse = MsgBox("Close all open documents?", _ vbQuestion Or vbYesNoCancel, _ "File Close") Select Case lngResponse Case vbYes For Each objDoc In Documents objDoc.Close Next Case vbNo Application.ActiveDocument.Close Case vbCancel Exit Sub End Select End If End Sub
Assigning a macro to a toolbar button
You can add a button to a toolbar and assign a macro to it. To do this from Word's user interface, select the Customize option from the Tools menu, or right-click on any toolbar and select the Customize option. Word opens the Customize dialog. Make sure that the toolbar to which you add the button is checked in the Toolbars tab, then select the Commands tab. Select Macros in the Categories list box and the macro you want to add to the toolbar in the Commands list box. Then drag the macro object from the Commands list box, position it on the toolbar, and drop.
Assigning a macro to a key
To assign a macro to a keyboard combination, open the Customize dialog and click on the Keyboard button. Select Macros in the Categories list box, then select the macro to which you'd like to assign a key combination in the Commands list box. Move the cursor to the "Press new shortcut key" text box and select the key combination you'd like to activate your macro.
2.1.2. Running VBA Modules in Excel
An Excel/VBA application, like its Word counterpart, can be anything from a small routine that performs a useful service to a large application that completely shields the user from Excel's basic interface. Excel, like Word, provides a variety of ways to launch an application that's consistent with its overall purpose.
2.1.2.1. Storing your code
When Excel loads, it automatically loads all workbook (.xls) and add-in (.xla) files stored in the XLStart directory (and notably Personal.xls, a worksheet that can serve as a repository for code, and that Excel makes hidden by default) and in an alternate startup directory. XLStart is created by Excel during installation (it's typically a subdirectory of the Office directory) and can't be changed. The alternate startup directory, if one is defined, supplements the XLStart directory, and it's configurable. To define or change it, you can select the Options option from the Tools menu, click on the General tab of the Options dialog, and enter the path in the "Alternate startup file location" text box.
Typically, to store global macros, you'd want to create your own add-in file or your own worksheet, which can be hidden and stored in the XLStart folder. Particularly since Personal.xls is a frequent target of Excel macro viruses, it's best not to use it as a repository for your code. Excel actually loads each of these startup files; consequently, it's important that they remain hidden. While Personal.xls and all add-in files are hidden automatically, other files aren't. To hide them, select the Hide option from the workbook's Window menu.
Like Word, Excel also supports the creation of documents from templates, which also can contain code. So if your VBA code applies only to a particular kind of workbook (that is to say, to all workbooks created from the same template), you can store the code in the template (.xlt) file. Excel's behavior here, though, is somewhat different from Word's; whereas Word adds a reference to the template to the document, Excel actually embeds the template's code in a newly created workbook.
Finally, if VBA code applies only to a single document, the code can be added to the document, rather than to autoloaded workbooks, add-ins, or templates.
2.1.2.2. At startup
When Excel starts. it automatically loads all add-in and workbook files in its startup and alternate startup directories. Their Workbook.Open event is fired. Note that, although this is a document-level event (that is, it's fired by a workbook being opened, rather than by Excel starting), the fact that no document workbooks are open when the startup workbooks and add-ins are loaded makes these Workbook.Open event handlers functionally similar to the Word AutoExec procedure.
2.1.2.3. When a document loads
To execute code when a particular workbook is opened, that workbook must have been created using a template that included a Workbook.Open event handler, or a Workbook.Open event handler must have been added to the workbook itself. In fact, Excel workbooks support a rich event model; you can attach event handlers to such Workbook events as Activate, BeforeClose, Deactivate, NewSheet, and SheetActivate.
2.1.2.4. In response to direct user action
Like Word code, VBA code in Excel often consists of a set of macros. Sometimes, you can hook these to a Workbook event. But more commonly, you have to provide a way for the user to run your macro from the Excel interface. Although Excel lacks Word's ability to intercept basic procedures, it does offer two major options for "hooking" your macro into the Excel interface:
Assigning a macro to a toolbar button
You can add a button to a toolbar or submenu and assign a macro to it. To do this from Excel's user interface, select the Customize option from the Tools menu or right-click on any toolbar and select the Customize option. Excel opens the Customize dialog. Make sure that the toolbar to which you add the button is checked in the Toolbars tab, then select the Commands tab. Select Macros in the Categories list box and either Custom Menu Item or Custom Button from the Commands list box, depending on how you will attach your macro to the interface. Next, drag the object from the Commands list box, position it on the toolbar, and drop. Finally, right-click on the new menu item or button, select the Assign Macro option from the context menu, and select the routine you want to assign to the menu item or toolbar button.
Assigning a macro to a key
To assign a macro to a keyboard combination, open the Macro dialog (Tools
Macro
Macros), and select
the macro you want to assign to a key combination from the Macro Name
list box. Next, click on the Options button to open the Macro Options
dialog. Finally, select a shortcut key to use along with the Ctrl key
to activate your macro. Note that Excel doesn't inform you if
you've chosen a key assignment already in use; it simply
overwrites the old assignment with the new one.
2.1.3. Running VB Executables
An application that is to be compiled into an executable file with the retail version of Visual Basic and that contains forms can be started by the Visual Basic runtime loading a form, or by running a specially named sub procedure called Main. An application that is to be compiled into an ActiveX EXE, DLL, or OCX can only be started using a Sub Main procedure. You specify the startup method for the project in the General tab of the Project Properties dialog box (you open it by selecting the <ProjectName> Properties option from the Project menu), where you select either a form name or Sub Main from the StartUp Object combo box.
Whether you specify a Form or a Sub Main procedure within a code module as the startup object for your program, the VB runtime module first loads into memory all Public or Global constants and variables in all code modules within the project. Therefore, you have instant access to these at startup. Beware, however, that publicly declared variables in form modules aren't loaded at startup time; they are only available while the form itself is loaded. This means that you can't assign a value to a Public variable in another form from that of your startup form or from a startup code module.
2.1.4. Using a Form at Startup
If you specify a form as the starting point for your project, the VB runtime module loads this form after loading project-level variables and constants but before executing any of your project code. When the form is loaded into memory, the form's Initialize event is fired, followed immediately by the Load event. Once the Form is displayed on screen, the Activate event is fired.
2.1.4.1. The Form Load and Initialize events
Until Version 4 of VB, the initialization code for a Form module was placed in the Form_Load event, and probably through habit—and possibly because it's still the default event—most VB developers continue to use the Form_Load event. However, in line with other object modules such as class modules, the Form module now contains an Initialize event, which is fired as the Form is loaded into memory. The Initialize event is immediately followed by the Form_Load event.
There is little operational difference between the Form's Load and Initialize events, and code to initialize the form—and the application if the form is the startup object—can be written in either. However, if you use both events to write initialization code, you may not always get the desired results. The reason for this is that controls contained on the form aren't completely loaded into memory when the Initialize event is fired. Therefore, any code in the Initialize event handler that references a control on the form forces the rest of the form to load, which then fires the Load event. The following example illustrates this problem:
Private Sub Form_Initialize()
Text1.Width = 2000
Text1.Text = "Hello "
End Sub
Private Sub Form_Load()
Text1.Text = Text1.Text & "World"
End SubGiven that the Initialize event fires before the Load event, you'd expect the code above to produce the tired old "Hello World" phrase in the text box. But you may be surprised to discover that when this form is run, only the word "Hello" appears. This is because when the Width property is set to 2000, execution branches to the Form Load event, and the string "World" is placed in the text box. Execution then passes back to the Initialize event and the string "Hello" is assigned to the text property, thereby overwriting the word "World."
Both the Form's Load and Initialize events are executed only once, each time the form is loaded into memory. Hiding the form and then reshowing it doesn't re-execute either event. However, another event, the Activate event, is executed in this situation. You shouldn't use the Activate event to write application initialization code because it executes every time the form regains the focus.
2.1.5. Using a Code Module at Startup
The preferred method of starting any Visual Basic application is to use a Sub Main procedure.
2.1.5.1. The Sub Main procedure
To create a Sub Main, you need to include a code module in your project. Then simply type the following:
Sub Main()
Visual Basic automatically adds an End Sub line for you. You can have only one Sub Main procedure in your project. A scope keyword—such as Private or Public—isn't required for the Sub Main procedure. While it's possible to call Sub Main from another procedure, it's definitely not recommended.
The Sub Main procedure doesn't necessarily have to contain any code. In fact, in projects such as ActiveX DLLs, EXEs, or OCXs, it's best not to write code in the Sub Main. If you are using a Sub Main to start up a project and require a form to be loaded on startup, you can use a Sub Main procedure similar to the following:
Sub Main()
Dim oForm as frmStartUp
Set oForm = New frmStartUp
oForm.Show vbModal
Set oForm = Nothing
End SubHere, an object variable is declared. A reference to a new instance of a Form object called frmStartup is then assigned to that object variable. The object variable can now be used to call the form's Show method. The form is shown modally, which means that the rest of the code in this procedure can't be executed until the form has completed its processing and is either hidden or unloaded. Finally, the object variable is set to Nothing, thereby unloading the form from memory. Using a Sub Main procedure in this way is now the recommended alternative to specifying a Startup form, since it allows you greater flexibility when initializing the application.