This chapter concentrates on the principles of good behavior. Once you've built a procedure that's useful and that works consistently as intended, you'll probably want to distribute it to as many of your coworkers as might use it or even to a wider audience on the Internet. Before you distribute it, though, you should make sure that the procedure is as civilized as possible in its interaction with users and with the settings they may have chosen on their computer. It's all too easy to distribute an apparently successful procedure that runs roughshod over the user's preferences or one that fails unexpectedly under certain circumstances. In this chapter, you'll look at how to avoid such problems and how to construct your procedures so that the user will have no problem interacting with them.
The specifics of good program behavior vary from application to application, and you will need to apply the principles to the application with which you're working. This chapter gives some examples.
In this chapter you will learn to do the following:
Understand the characteristics of well-behaved procedures
Retain and restore the user environment
Let the user know what's happening
Check that the procedure is running under suitable conditions
Clean up after a procedure
A well-behaved procedure leaves no trace of its actions beyond those that the user expected it to perform. This means the following:
Making no detectable changes to the user environment or, if the procedure does need to make changes (for example, in order to run successfully), restoring the previous settings
Presenting the user with relevant choices for the procedure and relevant information once the procedure has finished running
Showing or telling the user what is happening while the procedure is running
Making sure (if possible) that conditions are appropriate for the procedure to run successfully—before the procedure takes any actions
Anticipating or trapping errors wherever possible so that the procedure doesn't crash or, if it does crash under exceptional circumstances, does so as gracefully as possible, minimizing damage to the user's work
Leaving users in the optimal position to continue their work after the procedure finishes executing
Deleting any scratch documents, folders, or other detritus that the procedure created in order to perform its duties but that are no longer needed
If you stop for a moment, you can probably think of a couple of examples in which applications you use don't exactly do this. 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, press the Page Down key three times, then press the Page Up key three times. Your blinking insertion point should be back in the exact location where it was before you paged down, then back up, right? Unfortunately, the insertion point doesn't always (let's be honest, it will rarely) return to the exact point in the document as it should.
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. I'm really not sure why it would be so hard for Word to note the insertion point before the paging, but why that's so difficult remains a mystery.
Such weaknesses in commercial applications' interfaces provoke two main reactions among developers. First, if users are accustomed to such niggles as having to reposition the selection or change the view when they shouldn't need to, they're unlikely to have a problem with having to perform similar actions after running one of our procedures—particularly a procedure that saves them plenty of time and effort, for which they should be grateful rather than picky. The second reaction is an impressive (and sometimes overzealous) determination to restore the user environment absolutely perfectly even if major software corporations seem incapable of producing software that does so.
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 the two extremes.
In many cases, your procedures will run without even needing to change the user environment—but if not, restore it as closely as possible to its previous state. What this means depends on the host application, but here are some examples of environment changes in Word, Excel, and PowerPoint:
In Word: Changing the revision-marking (Track Changes) setting, so that you could change the text without the changes being marked as revisions.
In Word or PowerPoint: Changing the view to a different view so that you could perform certain operations that cannot be performed in the original view.
In Excel: Creating a temporary worksheet on which you can manipulate data secure in the knowledge that you don't need to check whether any ranges are already occupied by user data.
In any application that lets you manipulate its Find and Replace features: Using the Find and Replace features to identify and/or modify parts of a document, then restoring users' last search (and replace, if necessary) so that they can perform it again seamlessly. The problem here is that most applications have "sticky" Find and Replace settings to allow the user to perform the same search or replacement operation again quickly without reentering the parameters. 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 like Match Case. The next time the user tries 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 you left the Match Case filter turned on, and the user didn't capitalize Florida when initiating the search. Fail.
To save information about the user's environment so that you can restore it before exiting your code, you can use private variables, public variables, or custom objects as appropriate.
After your procedure finishes running, users need to be in the best possible position to continue their work. What exactly this best possible position entails depends on the situation, but here are three simple suggestions:
Usually, you'll want to leave users facing the same document they were working on when they started running the procedure. There are some obvious exceptions to this, such as when the procedure creates a new file for the user and the user is expecting to work in that file, but the general principle is sound.
If the file is essentially untouched (at least from the user's point of view), the selection should probably be back where it was when the user started running the procedure. To restore the selection, you may want to define a range at the beginning of the procedure and then move the selection back to it at the end of the procedure. In some applications, you could also use a bookmark or a named range—but if you do, be sure to remove it afterward. Remember, leave no debris behind.
If the procedure has created a new object in the file, and the user will be expecting to work with it, you may want to have that object selected at the end of the procedure.
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.
In 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 the user at the beginning of the procedure that input will be disabled and explain why. Otherwise, a user 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. 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 cursor (the mouse pointer). You may need to do this because VBA automatically displays the busy cursor (an hourglass in Windows XP, a rotating ring in Vista and Windows 7) 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, 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: xlDefault (−4143) for a default cursor, xlWait (2) for the busy cursor, xlNorthwestArrow (1) for the arrow pointing up and to the left, and xlIBeam (3) for an I-beam 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.
As mentioned earlier, you can also use a message box or dialog box to warn the user that the procedure is going to turn off screen updating. Likewise, if the procedure will disable user interrupts for part or all of its duration, warn the user about that too.
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, 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, 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 if just want to 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.1 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.
Example 18.1. Creating a Log File
1. Sub Create_Log_File()
2.
3. Dim strDate As String
4. Dim strPath As String
5. Dim strCity(10) As String
6. Dim strLogText As String
7. Dim strLogName As String
8. Dim strSummary As String
9. Dim strFile As String
10. Dim i As Integer
11.
12. On Error GoTo Crash
13.
14. strCity(1) = "Chicago"
15. strCity(2) = "Toronto"
16. strCity(3) = "New York"
17. strCity(4) = "London"
18. strCity(5) = "Lyons"
19. strCity(6) = "Antwerp"
20. strCity(7) = "Copenhagen"
21. strCity(8) = "Krakow"
22. strCity(9) = "Pinsk"
23. strCity(10) = "Belgrade"
24.
25. strDate = Month(Date) & "-" & Day(Date) & "-" _
& Year(Date)
26. strPath = "f:Daily Data"
27. strLogName = strPath & "ReportsLog for " _
& strDate & ".docm"
28. strSummary = strPath & "ReportsSummary for " _
& strDate & ".docm"
29. Documents.Add
30. ActiveDocument.SaveAs strSummary
31.
32. For i = 1 To 10
33. strFile = strPath & strCity(i) & " " & strDate & ".docm"
34. If Dir(strFile) <> "" Then
35. Documents.Open strFile
36. Documents(strFile).Paragraphs(1).Range.Copy
37. Documents(strFile).Close _
38. SaveChanges:=wdDoNotSaveChanges
39. With Documents(strSummary)40. Selection.EndKey Unit:=wdStory
41. Selection.Paste
42. .Save
43. End With
44. strLogText = strLogText & strCity(i) _
& vbTab & "OK" & vbCr
45. Else
46. strLogText = strLogText & strCity(i) _
& vbTab & "No file" & vbCr
47. End If
48. Next i
49.
50. Crash:
51.
52. Documents.Add
53. Selection.TypeText strLogText
54. ActiveDocument.SaveAs strLogName
55. Documents(strLogName).Close
56. Documents(strSummary).Close
57.
58. End SubThe procedure in Listing 18.1 creates a new document that will contain 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:
Lines 3 through 9 declare six String variables—
strDate, strPath, strLogText, strLogName, strSummary, andstrFile—and one String array,strCity, containing 10 items. (The procedure uses anOption Base 1statement that doesn't appear in the listing, sostrCity(10)produces 10 items in the array rather than 11.) Line 10 declares the Integer variablei, which the procedure will use as a counter.Line 11 is a spacer. Line 12 uses an
On Error GoTostatement to start error handling and direct execution to the labelCrash:in the event of an error. Line 13 is a spacer.Lines 14 through 23 assign the names of the company's 10 offices to the
strCityarray. Line 24 is a spacer.Line 25 assigns to
strDatea string created by concatenating the month, the day, and the year for the current date (with a hyphen between each part) by using theMonth, Day, andYearfunctions, respectively. For example, January 21, 2007, will produce a date string of1-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.)Line 26 sets
strPathto thef:Daily Datafolder. Line 27 then builds a filename for the log file in theReportssubfolder, and line 28 creates a filename for the summary file, also in theReportssubfolder.Line 29 creates a new document based on
Normal.dotm, and line 30 saves this document under the name stored in thestrSummaryvariable. Line 31 is a spacer.Line 32 begins a
For... Nextloop that runs fromi = 1toi = 10. Line 33 assigns to the String variablestrFilethe filename for the first of the cities stored in thestrCityarray:strPath & strCity(i) & " " & strDate & ".docm".Line 34 then begins an
Ifstatement that checks whetherDir(strFile)returns an empty string. If not, line 35 opens the document specified bystrFile, 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 hot fields (such as date fields or links that automatically update themselves when the document is opened), it may have become dirty (modified). Including theSaveChangesargument 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 theSavedproperty of the document toTrueand then close it without using theSaveChangesargument.)Lines 39 through 43 contain a
Withstatement that works with theDocumentobject specified bystrSummary. Line 40 uses theEndKeymethod with theUnitargumentwdStoryto move the selection to the end of the document. Line 41 pastes in the material copied from the document just opened, and line 42 saves the document. Line 43 ends theWithstatement.Line 44 adds to
strLogTextthe contents ofstrCity(i), a tab, the textOK, and a carriage return, which will produce a simple tabbed list of the cities and the status of their reports.If the condition posed in line 34 isn't met, execution branches to the
Elsestatement in line 45, and line 46 adds tostrLogTextthe contents ofstrCity(i), a tab,No file, and a carriage return. Line 47 ends theIfstatement, and line 48 ends theFor... Nextloop, returning execution to line 32.Line 49 is a spacer. Line 50 contains the
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. Line 51 is a spacer.Line 52 creates a new document based on the default template; line 53 types the contents of
strLogTextinto the new document; and line 54 saves it under the namestrLogName. Line 55 closes this new document (alternatively, you could leave the document open so that the user could view it). Line 56 closes the summary document (which has remained open since it was created; again, you might want to leave this open so that the user might view it or offer the user the option of keeping it open). Line 57 is a spacer, and line 58 ends the procedure.
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:
Make sure a file is open in a procedure that needs a file to be open—otherwise, you'll get an error every time. For example, in Excel, you might check the
Countproperty of theWorkbookscollection 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"Check that the procedure is referencing an appropriate item, if the procedure has definable requirements. For example, in an Excel procedure that applies intricate formatting to a chart the user has selected, make sure the user has, in fact, actually selected a chart. Trying to manipulate another object with chart-related commands is likely to cause an error or at least unwanted side effects.
Make sure a file contains the element required by the procedure. (If it doesn't, an error will likely result.) Alternatively, trap the error that will result from the element's absence.
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 Word, you might need to apply some formatting to half of a table but not to the rest of it. In this case, it may 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
Setstatement to define a range for the break and then return to the range and remove the break.In Excel, you may need to define named ranges in a workbook so that you can easily reference them from the code. (Usually, you'll do better to use ranges via VBA, which won't leave unwanted named ranges in the workbook.) Delete these named ranges when you've finished with them.
During a complex procedure, you may need to create scratch files in which to temporarily store or manipulate information or scratch folders in which to store 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, is a bit 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 mess that you've made on the drive, and remove both scratch files and scratch folders that you've created. 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 poor 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 a procedure goes wrong or is interrupted during execution.
Another approach is to use the API (application programming interface) commands GetTempDir and GetTempFileName to return 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 temporary folder, you should delete any files that you create in it when you've finished using them. 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 IfFor 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 the 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"
- Understand the characteristics of well-behaved procedures
Well-behaved procedures don't annoy or alarm the user either during or after their execution.
- Master It
Name two ways programmers can write procedures that don't annoy users.
- Retain and restore the user environment
Users quite rightly don't appreciate it if your procedure modifies the state of their application's or operating system's environment. Find ways to restore the user environment before your procedure finishes execution.
- Master It
Assume that you are writing a procedure that employs Word's Search and Replace feature. This feature retains its settings between uses so the user can repeatedly trigger the same search or replace actions. How can you temporarily store the status of the user's last search or replace so that you can restore this data after your procedure is finished executing?
- Let the user know what's happening
Particularly when a procedure is doing a lengthy "batch job" such as updating dozens of files, it's important to let the user know that the computer hasn't frozen. People need to be told that execution is continuing as expected even though nothing appears to be happening.
- Master It
Describe a way to let the user know that a procedure isn't frozen—that activity is taking place during execution.
- Check that the procedure is running under suitable conditions
Another important element of 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.
- Master It
If a procedure accesses data from a file, name an error that could occur and thus should be trapped.
- Clean up after a procedure
A well-behaved procedure avoids leaving unneeded files or other temporary items behind. In other words, a procedure should clean up after itself.
- Master It
Cleaning up involves three major tasks. Name one.