18.6. Cleaning Up after a Procedure

Like your children or housemates, your procedures should learn to clean up after themselves. Cleaning up involves the following:

• Undoing any changes that the procedure had to make to enable itself to run

• Closing any files that no longer need to be open

• Removing any scratch files or folders that the procedure has created to achieve its effects

18.6.1. Undoing Changes the Procedure Has Made

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 perform a procedure like 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.

• 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.

18.6.2. Removing Scratch Files and Folders

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 same 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 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. Before you ask — no, commercial applications don't always do this, not even Microsoft's applications — and no, 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 safely remove any scratch files left on their computer if a procedure goes wrong or is interrupted during execution.

Another possibility 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.

18.6.2.1. Building a Scratch Folder

You can use a MkDir statement 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 a Dir statement to check to see that the name isn't already in use. (If a folder with that name already exists, an error results.)

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 the Rnd function to generate a random number to use as part of the folder name.

18.6.2.2. Deleting the Scratch Folder

You can use the RmDir statement to remove an empty folder. (Make sure that the folder is empty 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"