This chapter concentrates on the principles of good code behavior. Once you've built a macro that's useful and that works consistently as intended, you'll probably want to distribute it to some friends and coworkers, or even to a wider audience on the Internet.
Before you distribute it, though, you should make sure the macro behaves in a civilized manner. It should be sensitive in its interaction with users and with the settings they may have chosen on their computers. It's all too easy to distribute an apparently solid, useful procedure that nevertheless runs roughshod over a user's preferences or that fails unexpectedly under certain circumstances. In this chapter, you'll look at how to avoid such problems and how to construct your macros so that users will have no problem interacting with them.
The specifics of good macro behavior vary from application to application, so you will need to apply the principles of the application with which you're working. This chapter provides several examples.
A well-behaved macro leaves no trace of its actions beyond those the user expected it to perform. This means the following:
You can probably think of a couple of examples of applications you use that don't exactly do these things. For example, do you use Word? Then you're probably familiar with the less-than-inspiring behavior of the Page Up and Page Down feature. While working in a document, click next to a word to put the blinking insertion cursor there. Now press the Page Down key three times, and then press the Page Up key three times. Your blinking insertion point is supposed to be back in the exact location where it was before you paged down and then back up, right? Unfortunately, the insertion point doesn't always (let's be honest, will rarely) return to the exact point in the document where it started from.
So if you page through your document to look at some paragraph but then try to return to where you were last, you always need to check that the insertion point is in the right place before you start typing—otherwise, the characters are very likely to land in the wrong place. Word was first released in October 1983, so Microsoft has had time to fix this, right? It would be simple for Word to note the insertion point before the paging, but why that's never done remains a mystery. However, I will show you how to accomplish this in your macros in the section titled “Leaving the User in the Best Position to Continue Working” later in this chapter.
Such weaknesses in commercial applications’ interfaces provoke two main reactions among developers:
The first approach tends to be more economical in its code and the second more inventive. To get your work done and retain your sanity, you'll probably want to steer a course between these two extremes.
In many cases, your macros will run without even needing to change the user environment—but if not, restore it as closely as possible to its previous state. What exactly this means depends on the host application, but here are some examples of environment changes in Word, Excel, and PowerPoint:
If you've replaced users’ search and replacement parameters, they'll get a rude shock the next time they try to search or replace. This is particularly true if you've turned on some esoteric feature such as Match Case. The next time the users try to search for florida, they will find no matches, even if the document is about Miami and is jam-packed with the word Florida. Why? Because your macro left the Match Case filter turned on, and the user didn't capitalize Florida when initiating the search. Fail.
You'll want to save information about the user's environment so that you can restore it at the end of the procedure. If your procedure will mess around with the Match Case property of Word's Find and Replace feature, at the start of this procedure you save the user's current value in this property in a private variable, public variable, or custom object as appropriate.
Then at the end of your macro, fetch the saved value and restore it to the property you temporarily modified. Here's an example:
Dim CaseStatus As Boolean 'match case is either on or off
CaseStatus = Selection.Find.MatchCase 'save the user's setting
Selection.Find.MatchCase = True 'our macro needs to be case-sensitive
' execute statements in the macro
Selection.Find.MatchCase = CaseStatus 'restore the user's preference
After your macro finishes running, users need to be in the best possible position to pick up where they left off to continue their work. What exactly this best possible position entails depends on the situation, but here are three simple suggestions:
Listing 18. is an example macro that you can try out. It saves a Word document's current blinking insertion-cursor location in a bookmark. Next, it moves the cursor down a few lines and shows you a message box so you can see the new location of the cursor. Finally, it restores the cursor to its original location.
Notice in line 18 that we delete our bookmark when we've finished using it. Don't leave rubbish behind.
A key component of a well-behaved procedure is keeping the user adequately informed throughout the process. In a macro that performs a basic if tedious task, adequate information may require only a clear description in the macro's Description field to assure users that they're choosing the right procedure from the Macros dialog box.
With a more complex procedure, adequate information will probably have to be more extensive: You may need to display a starting message box or dialog box, show information on the status bar during the procedure, display an ending message box, or create a log file of information so that the user has a record of what took place during execution of the procedure.
You must first decide whether to disable user input during the procedure. In Word and Excel, you can disable user input to protect sensitive sections of your procedures by setting the EnableCancelKey
property of the Application
object (as discussed in “Disabling User Input While a Procedure Is Running” in Chapter 17, “Debugging Your Code and Handling Errors”). When you do so, it's a good idea to indicate to users at the beginning of the procedure that input will be disabled and explain why. Otherwise, users may react to a procedure that seems not to be executing in the same way they would respond to an application that had hung—by trying to close the application forcibly via Task Manager or the Break key.
To keep the user informed about other aspects of the procedure, you have several options, which are discussed in the following sections. But first, the sidebar “Disabling Screen Updating” examines how you can hide information from the user (and the reasons for doing so) by disabling screen updating in Word and Excel.
Word and Excel permit you to manipulate the icon used for the mouse pointer (cursor). You may need to do this because VBA automatically displays the busy cursor (an hourglass in Windows XP, a rotating ring in Windows versions since then) while a VBA procedure is running and then restores the normal cursor when it has finished. Sometimes, however, you may need or want to specify the cursor's appearance in your code.
Word implements the cursor via the System
object. To manipulate the cursor, you set the Cursor
property. This is a read/write Long property that can be set to the following values: wdCursorIBeam
(1
) for an I-beam cursor, wdCursorNormal
(2
) for a normal cursor, wdCursorNorthWestArrow
(3
) for a left-angled resizing arrow (pointing up), and wdCursorWait
(0
) for the busy cursor. The exact appearance of the cursor depends on the cursor scheme the user has selected.
For example, the following statement displays a busy cursor:
System.Cursor = wdCursorWait
Note that a user can customize the cursors by clicking the Mouse icon in Control Panel to open the Mouse Properties dialog box and then selecting the Pointers tab.
Excel lets you manipulate the cursor through the Cursor
property of the Application
object. Cursor
is a read/write Long property that can be set to the following values: xlIBeam (3)
for an I-beam cursor, xlDefault (-4143)
for a default cursor, xlNorthwestArrow (1)
for the arrow pointing up and to the left, and xlWait (2)
for the busy cursor.
For example, the following statement displays the busy cursor:
Application.Cursor = xlWait
When you explicitly set the Cursor
property of the Application
object in Excel, remember to reset it to something appropriate before your code stops executing. Otherwise, the cursor stays as you left it.
At the beginning of many procedures, you'll probably want to display a message box or a dialog box. For this purpose, you'll typically use a Yes/No or OK/Cancel message-box style. The message box tells users what the procedure will do and gives them the chance to cancel the procedure without running it any further.
Alternatively, a dialog box can present options for the procedure (for example, mutually exclusive options via option buttons or nonexclusive options via check boxes), allowing users to enter information (via text boxes, list boxes, or combo boxes) and, of course, letting them cancel the procedure if they've cued it by accident. If you have time to create a Help file to accompany the procedures and user forms you create, you might add a Help button to each message box or dialog box, linking it to the relevant topic in the Help file.
You can also use a message box or dialog box to warn the user that the procedure is going to disable user interrupts for part or all of its duration.
With some procedures, you'll find it useful to collect information on what the procedure is doing so that you can display that information to the user in a message box or dialog box after the procedure has finished its work. As you saw in Chapter 13, “Getting User Input with Message Boxes and Input Boxes,” message boxes are easier to use but are severely limited in their capabilities for laying out text—you're limited to the effects you can achieve with spaces, tabs, carriage returns, and bullets. With dialog boxes, however, you can lay out text however you need to (by using labels or text boxes) and even include images if necessary.
The easiest way to collect information while running a procedure is to build one or more strings containing the information you want to display. For an example of this, look back to the sidebar titled “Control a For…Next
Loop with User Input via a Dialog Box” in Chapter 12, “Using Loops to Repeat Actions,” in which a cmdOK_Click
procedure collects information while creating a series of folders and then at the end displays a message box telling the user what the procedure has accomplished.
If you need to collect a lot of information during the course of running a procedure and either present it to the user once the procedure has finished or just make it available for reference if needed, consider using a log file rather than a message box or dialog box. Log files are useful for lengthy procedures that manipulate critical data: By writing information periodically to a log file (and by saving it frequently), you create a record of what the procedure achieves in case it crashes.
Say you wrote a procedure for Word that collects information from a variety of sources each day and writes it into a report. You might want to keep a log file that tracks whether information from each source was successfully transferred and at what time. Listing 18.2 provides an example of such a procedure. At the end of the procedure, you could leave the log file open so that the user could check whether the procedure was successful in creating the report or leave the summary file open so that the user could read the report itself.
The procedure in Listing 18.2 creates a new document that contains a summary, opens a number of files in turn, copies the first paragraph out of each and pastes it into the summary document, and then closes the file. As it does this, the procedure maintains a string of log information from which it creates a log file at the end of the procedure or, if an error occurs, during the procedure. Here's what happens in the code:
strDate
, strPath
, strLogText
, strLogName
, strSummary
, and strFile
—and one String array, strCity
, containing 10 items. (The procedure uses an Option Base 1
statement that doesn't appear in the listing, so strCity(10)
produces 10 items in the array rather than 11.)i
, which the procedure will use as a counter.On Error GoTo
statement to start error handling and direct execution to the label Crash:
in the event of an error.strCity
array.strDate
a string created by concatenating the month, the day, and the year for the current date (with a hyphen between each part) by using the Month
, Day
, and Year
functions, respectively. For example, January 21, 2007, will produce this date string: 1-21-2007
. (The reason for creating a string like this is that Windows can't handle slashes in filenames—slashes are reserved for indicating folders.)strPath
to the f:Daily Data
folder.Reports
subfolder, and line 28 creates a filename for the summary file, also in the Reports
subfolder.Normal.dotm
, and line 30 saves this document under the name stored in the strSummary
variable. Line 31 is a spacer.For…Next
loop that runs from i = 1
to i = 10
.strFile
the filename for the first of the cities stored in the strCity
array: strPath & strCity(i) & " " & strDate & ".docm"
.If
statement that checks whether Dir(strFile)
returns an empty string. If not, line 35 opens the document specified by strFile
, line 36 copies its first paragraph, and line 37 closes it without saving changes.
The procedure doesn't make any changes to the document, but if the document contains any dynamic “hot fields” (such as date fields or links that automatically update themselves when the document is opened), it may have become dirty (modified).
Including the SaveChanges
argument ensures that users don't get an unexpected message box prompting them to save a document they know they haven't changed. (An alternative would be to set the Saved
property of the document to True
and then close it without using the SaveChanges
argument.)
With
statement that works with the Document
object specified by strSummary
.
EndKey
method with the Unit
argument wdStory
to move the selection to the end of the document.With
statement.strLogText
the contents of strCity(i)
, a tab, the text OK
, and a carriage return, which will produce a simple tabbed list of the cities and the status of their reports.Else
statement in line 45, and line 46 adds to strLogText
the contents of strCity(i)
, a tab, No file
, and a carriage return. Line 47 ends the If
statement, and line 48 ends the For…Next
loop, returning execution to line 32.Crash:
label and marks the start of the error handler. Unlike in many procedures, you don't want to stop execution before entering the error handler—as it happens, you want to execute these statements (to create the log file) even if an error occurs.strLogText
into the new document; and line 54 saves it under the name strLogName
.Another important consideration when creating a well-behaved procedure is to check that it's running under suitable conditions. This ideal is nearly impossible to achieve under all circumstances, but you should take some basic steps, such as the following:
Count
property of the Workbooks
collection to make sure at least one workbook is open:
If Workbooks.Count = 0 Then _
MsgBox "This procedure will not run without a " _
& "workbook open. Open one, then run the procedure again.", _
vbOKOnly + vbExclamation, _
"No Workbook Is Open"
Like your children or housemates, your procedures should learn to clean up after themselves. Cleaning up involves the following:
In some cases, you'll need to make changes to a document in order to run a procedure successfully. Here are a couple of examples:
In this case, it might be easier to split the table into two tables so that you can select columns in the relevant part and format or change them without affecting the columns in the other half of the original table. If you do this, you'll want to join the tables together again afterward by removing the break you've inserted between the original table's two halves. The easiest way to do this is to bookmark the break that you insert. You can then go back to the bookmark and delete it and the break at the same time.
Alternatively, you could use a Set
statement to define a range for the break and then return to the range and remove the break.
During a complex procedure, you may need to create scratch files in which you temporarily store or manipulate data, or scratch folders in which you store temporary files.
For example, if you need to perform complex formatting on a few paragraphs of a long document in Word, you may find it easier to copy and paste those paragraphs into a new blank document and manipulate them there than to continue working in the original document and risk unintentionally affecting other paragraphs as well. Likewise, in PowerPoint, you might need to create a new presentation that you could use for temporary or backup storage of intricate objects.
Creating scratch files, while often necessary for the safe and successful operation of a procedure, can be intrusive. You're cluttering up the user's hard drive with information that's probably of no use to that user. Creating scratch folders in which to save the scratch files is even worse. Always go the extra distance to clean up any temporary items that you've stored on the user's hard drive. If you're thinking that commercial applications don't always do this, not even Microsoft's applications, you're right. But that doesn't mean you should follow their example.
If your procedure is going to remove any scratch files it creates, you may be tempted to conceal from the user their creation and subsequent deletion. This usually isn't a good idea—in most cases, the best thing is to warn the user that the procedure will create scratch files. You might even let the user specify or create a suitable folder for the scratch files or present the user with a list that logs the files created and whether they were successfully deleted. Doing so will allow users to easily delete any scratch files left on their computer if your procedure goes wrong or is interrupted during execution.
Another approach is to use the API (application programming interface) commands GetTempDir
and GetTempFileName
to find out the location of the computer's temporary folder and a temporary filename that you can use. (How to make an API call is illustrated in Chapter 30, “Accessing One Application from Another Application,” in the sidebar titled “Using the Sleep
Function to Avoid Problems with Shell's Asynchrony.”) But even if you use the default temporary folder, you should delete any files that you create in it when your procedure is finished. Again, a disappointing number of commercial software developers fail to do this.
You can use the MkDir
command to create a folder. For example, the following statement creates a folder named Scratch Folder
on the C:
drive:
MkDir "c:Scratch Folder"
Before creating a folder, use the Dir
command to check to see that the name isn't already in use. (If a folder with that name already exists, an error results.) Here's how:
Dim s As String
s = "c:TempDir"
If Len(Dir(s, vbDirectory)) = 0 Then
MkDir s
End If
For temporary storage, you may want to use a folder name based on the date and time to lessen the chance that a folder with that name already exists. You could also use VBA's Rnd
function to generate a random number to use as part of the folder name.
You can use the RmDir
statement to remove an empty folder. (Make sure that you've deleted all files in the folder first—otherwise RmDir
will fail.) For example, the following statement removes the scratch folder named Scratch Folder
on the C:
drive:
RmDir "c:Scratch Folder"