Demystifying Excel Macros

Many of us have automated tasks in Microsoft Excel by recording simple macros. But recorded macros can only do so much; for instance, they can't check the contents of a cell before performing an operation, and they can't pop up dialog boxes so that you can make choices. To do this, you will need to learn Excel's scripting language, Visual Basic for Applications. Despite having decent Help information, the VB Editor requires that you know something about structuring these mini-programs and forming commands. To help you get started, we will show you how to create a macro that offers your user some options and then performs the requested task. It introduces concepts that are common to all macros.

In creating the macro, you will see how actions such as clicking on an OK button can be used to trigger a piece of code. You will learn how to set properties for controls on a form, and how to check, for example, that a range of cells is selected—and how to manage the situation if this has not been done.

Our macro is useful when you have Excel data typed in a mix of cases: uppercase, lowercase, and a combination of the two. (Excel has a few functions, UPPER, LOWER, and PROPER, that can change the case of text, but unlike Microsoft Word, it can't do this in the cell where the text is entered.) It's a problem that is well suited to a custom-written macro. Our solution will display a user form offering the choice of the three possible cases—lowercase, uppercase, and proper case. The function that changes the case of the text is the StrConv function, which has the syntax StrConv(string, type of conversion).

The string is the contents of the cell in question, and the type of conversion will be either upper, lower, or proper (first letter of each word capitalized), as determined by the person who runs the macro. We'll offer the three choices as option buttons on a custom form. Because we want the user to choose one type of case, we'll use radio buttons instead of checkboxes, so that only one option can be selected at a time.

Create the Form

To create the form, begin with a new workbook and choose Tools | Macro | Visual Basic Editor. Select the new worksheet in the VBA Project Explorer and choose Insert | UserForm. Drag the lower right corner of the UserForm to expand it. If the toolbox doesn't appear, click on the Toolbox icon to display it.

Using the toolbox controls, drag a Frame control across the top two-thirds of the UserForm, then drag three OptionButton controls into the frame. Add two CommandButton controls across the bottom of the form. You will find options on the Format menu for adjusting the size and placement of controls to arrange them in the way you want.

Now, click on each element—UserForm, Frame, and each button—in turn, and set the properties in the Properties Window as shown in the table (the other properties can remain as they are).

The completed form looks like a typical Microsoft Office dialog. Naming the controls ensures that they will match the code we'll use. The Caption property defines the text that will appear with each button. The Accelerator property sets the character to press with the Alt key to select an option on the form.

We've given the Cancel command button two important properties; setting its Cancel property to True runs its code when the user presses the Esc key, and setting its Default property to True makes it the button selected by default when the form is run. This is important because any macro should be nondestructive; if you run it and hit Enter by accident, nothing should happen. This is particularly important here, as the case change can't be undone—the Edit | Undo option isn't available once the macro has run.

To set an option button so it is selected by default, you use its Value property. Because the option buttons' controls are all located inside a single frame, setting one option button's Value to True automatically sets the same property to False for all the others, ensuring that only one option button is selected at a time.

Add the Code

Before adding the code, we must decide which control will do the work. We don't want anything to happen if only an option button is chosen. The only two controls that should perform a task are the OK and Cancel buttons. Cancel should remove the form from the screen, and OK should change the case of all text items in the selected range of cells into the user's chosen case.

Double-click on the Cancel button; you'll see a code dialog appear. Your cursor will be placed between these two lines of code:

Private Sub cmdCancel_Click()

End Sub

And there you should type:

Unload Me

End

You can now test this portion of the form by clicking on the form and choosing Run Sub/User Form. When you select any option button, all the others should be deselected. The OK button should do nothing, and you can remove the form by pressing the Cancel button or the Escape key or by clicking on the form's Close button.

To continue, double-click on the OK button and then type the following code between the Sub and End Sub statements:

If optUpper.Value Then

convertChoice = vbUpperCase

ElseIf optLower.Value Then

convertChoice = vbLowerCase

Else 'optProper is selected

convertChoice = vbProperCase

End If

For Each cell In Selection

If VarType(cell.Value) = vbString

Then

cell.Value = StrConv(cell.Value,

convertChoice)

End If

Next cell

Unload Me

End

When the OK button is clicked on, the If function tests each option button in turn to see if it is selected. If the first is selected, then the variable convertChoice is set to a VBA constant, vbUpperCase. If the second is selected, then the variable is set to vbLowerCase. If neither the first or second are selected, then the third one must be selected, so the variable is set to vbProperCase. In this line of code, the apostrophe indicates a comment included to remind you that if neither of the other option buttons is selected, the optProper one must be selected, so there's no need to test it:

Else 'optProper is selected

The For statement processes each cell in the selected range, one at a time, checking to see if it contains a string. If so, the string is converted using the StrConv function into upper, lower, or proper case, depending on the VB constant stored in the variable convertChoice. These statements perform the test and conversion:

If VarType(cell.Value) = vbString Then

cell.Value = StrConv(cell.Value,

convertChoice)

End If

It is necessary to perform this test so you only convert a string value. Though attempting to convert a number doesn't affect the number, converting a date or formula destroys a cell's contents. The StrConv function is only applied to a cell if it contains a string value. When all cells have been processed, the dialog is removed from the screen.

To finish our macro, we'll write a short routine to display the form on the screen. While the code so far has been attached to the UserForm, the code to display the form must appear in a module. To create a module, choose Insert | Module and type these lines in the code window:

Sub caseChange()

If TypeName(Selection) = "Range" Then

frmCaseChange.Show

Else

msg = "Please select a range and run

the macro again"

Reply = MsgBox(msg, vbInformation,

"Case Change Macro")

End If

End Sub

This is the macro that runs the form. First, it checks to see if a range (one or more cells) is selected. Only if one is selected will the macro run and load the form. It is critical to write "Range" in mixed case, as shown, or the macro won't work correctly. A clip-art image or a chart is not a range, so if either is selected when the macro is run, a dialog will appear, suggesting that you select a range and run the macro again. This dialog displays the blue information icon, the message, and a single OK button.

To test the macro, enter a few pieces of text, dates, numbers, and formulas into cells in the workbook and save it. Now select the cells and run the macro caseChange using Tools | Macro | Macros. The text cells should change to the case that you select from the dialog.

Making It Available

To make this macro available to all worksheets, move it to your personal.xls file. If a personal.xls file does not appear in the Project Explorer, return to Excel and record a short macro by choosing Tools | Macro | Record New Macro, and store it in your Personal Macro Workbook. Recording a single macro to this workbook is all that you need do to create a personal.xls file.

To move your macro to your personal.xls file, drag the Forms entry and Module1 in the Project Explorer and drop them onto your Personal.xls file. The macro is now available to all Excel worksheets, and you can add a toolbar button to run it or create a keyboard shortcut by choosing Tools | Macro | Macros, selecting the Macro name, and clicking on Options.

More on Macros

To help you learn more about writing macros, there are many good books on VBA for Excel and VB in general. Any book by John Walkenbach is worth shelling out cold hard cash for; check out his Microsoft Office Excel 2003 Power Programming with VBA (Wiley, 2004). Those who need a little more hand-holding should try his Excel VBA Program-ming for Dummies (Wiley, 2004). He also has a Web site, the Spreadsheet Page ( http://j-walk.com/ss ). You will find plenty of information elsewhere online, including code snippets that you can use to create custom solutions. One good source is www.mrexcel.com/articles.shtml .