Jumping Right In

Vertex42 The Excel Nexus

Professional Excel Templates

Get Instant Access

In This Chapter

^ Developing a useful VBA macro: A hands-on, step-by-step example ^ Recording your actions using the Excel macro recorder ^ Examining and testing recorded code ^ Changing recorded macro f

'm not much of a swimmer, but I have learned that the best way to get into a cold body of water is to jump right in — no sense prolonging the agony. By wading through this chapter, you can get your feet wet immediately but avoid getting in over your head. By the time you reach the end of this chapter, you just might start feeling better about this whole programming business. This chapter provides a step-by-step demonstration of how to develop a simple but useful VBA macro.

What You'll Be Doing

In this example, you create an Excel macro that converts selected formulas to their current values. Sure, you can do this without a macro, but it's a multistep procedure.

To convert a range of formulas to values, you normally complete the following steps:

1. Select the range that contains the formulas to be converted.

2. Copy the range to the Clipboard.

3. Choose EditOPaste Special.

4. Click the Values option button in the Paste Special dialog box, which is shown in Figure 2-1.

This clears the cut-copy mode indicator (the moving border) in the worksheet.

Figure 2-1:

Use the Values option in the Paste Special dialog box to copy formulas as values.

; Special mm

OS

O Validation

O Formulas

O All except borders

GiVaiuesj

O Column widths

O Formats

O Formulas and number formats

O Comments

O Values and number formats

Operation

® None

O Multiply

O Add

O Divide

O Subtract

l~~l Skip blanks

l~~l Transpose

Paste Link

OK 1 [ Cancel ]

The macro you create in this chapter accomplishes all these steps in a single action. As I describe in the following sections, you start by recording your actions as you go through these steps. Then you test the macro to see whether it works. Finally, you edit the macro to add some finishing touches. Ready?

Taking the First Steps

This section describes the preparations you take prior to recording the macro. In other words, you need to take the following steps before the fun begins:

1. Start Excel if it's not already running.

2. Open a new workbook.

3. Enter some values and formulas into the worksheet.

It doesn't matter what you enter. This step simply provides something to work with.

Figure 2-2 shows how my workbook looks at this point. I created this data by using the RAND function, which generates random numbers. Specifically, the cells in the range A3: D10 contain this formula:

So, cell A1 contains a value and the other cells contain a formula.

Figure 2-2:

My worksheet's sample values and formulas.

Figure 2-2:

My worksheet's sample values and formulas.

Recording the Macro

Here comes the hands-on part. Follow these instructions carefully:

1. Select the range of cells that contains your formulas.

The selection can include both values and formulas. In my case, I chose the range A1:D10.

2. Choose ToolsOMacroORecord New Macro.

The Record Macro dialog box appears, as shown in Figure 2-3.

3. Enter a name for the macro.

Excel provides a default name, but it's better to use a more descriptive name. ConvertFormulas is a good name for this one.

Figure 2-3:

The Record Macro dialog box appears when you're about to record a macro.

Figure 2-3:

The Record Macro dialog box appears when you're about to record a macro.

4. Enter Shift+C (for an uppercase C) as the shortcut key combo.

Click in the Shortcut Key box to enter a shortcut key. Specifying one lets you execute the macro by pressing a key combination — in this case, Ctrl+Shift+C.

The dialog box closes and Excel's macro recorder is turned on. From this point, Excel monitors everything you do and converts it to VBA code. Notice that a two-button toolbar appears and the status bar displays Recording.

6. Choose EditOCopy (or press Ctrl+C).

This copies the selected range of cells to the Clipboard.

7. Choose EditOPaste Special.

Excel displays the Paste Special dialog box.

8. Click the Values option.

9. Click OK to close the dialog box.

10. Press Esc to cancel the Excel cut-copy mode indicator.

The moving border (which is Excel's way of telling you that data is ready to be copied) is removed from the selection.

11. Choose ToolsOMacroOStop Recording, or click the Stop Recording button on the mini-toolbar that's floating on your screen.

The macro recorder is turned off.

Congratulations! You just created your first Excel VBA macro. You might want to phone your mother and tell her the good news.

Testing the Macro

Now you can try out this macro and see whether it works properly. To test your macro, add some more formulas to the worksheet. (You wiped out the original formulas while recording the macro.)

1. Enter some new formulas in the worksheet. Again, any formulas will do.

2. Select the range that contains the formulas.

3. Press Ctrl+Shift+C.

In a flash, Excel executes the macro. The macro converts all the formulas in the selected range to their current values.

Another way to execute the macro is to choose the ToolsOMacroOMacros command (or press Alt+F8) to display the Macros dialog box. Select the macro from the list (in this case, ConvertFormulas) and click Run. Make sure you select the range to be converted before executing the macro.

Examining the Macro

So far, you've recorded a macro and tested it. And if you're a curious type, you're probably wondering what this macro looks like.

Excel stores the recorded macro in the workbook, but you can't actually view the macro in Excel. To view or modify a macro, you must activate the Visual Basic Editor (VBE, for short).

Follow these steps to see the macro:

1. Choose ToolsOMacroO Visual Basic Editor (or press Alt+F11).

The Visual Basic Editor program window appears, as shown in Figure 2-4. This window is highly customizable, so your VBE window may look different. The VBE program window contains several other windows and is probably very intimidating. Don't fret; you'll get used to it.

2. In the VBE window, locate the window called Project.

The Project window (also known as the Project Explorer window) contains a list of all workbooks that are currently open. Each project is arranged as a tree and can be expanded (to show more information) or contracted (to show less information).

Figure 2-4:

The Visual Basic Editor is where you view and edit VBA code.

■öN Microsoft Visual Basic - Bookl

- Qü

i File Edit View ¡nsert Format Debug Run Tools Add-lns Window Help

Type a question for help -r

: H J - A I & I -n I «

j. j KI^'J'ï 1*1

Project VBAProject x|

pjfli ft

- JgTvBAProject (Bookl)

a & Microsoft Excel Objects a] Sheet 1 (Sheet 1) ® ThisWorkbook Q Modules

Immediate

»

<

The VBE uses quite a few different windows, any of which can be either open or closed. If a window isn't immediately visible in the VBE, you can choose an option from the View menu to display the window. For instance, if the Project window is not visible, you can choose ViewOProject Explorer (or press Ctrl+R) to display it. You can display any other VBE window in a similar manner.

3. Select the project that corresponds to the workbook in which you recorded the macro.

If you haven't saved the workbook, the project is probably called VBAProject (Book1).

4. Click the plus sign (+) to the left of the folder named Modules.

The tree expands to show Module1, which is the only module in the project.

5. Double-click Module1.

The VBA code in that module is displayed in a Code window. Figure 2-5 shows how it looks on my screen. Because the VBE program window is highly customizable, your screen may not look exactly the same.

The code in Module1 should look like this:

Sub ConvertFormulas() ' ConvertFormulas Macro

' Macro recorded 3/24/2004 by John Walkenbach ' Keyboard Shortcut: Ctrl+Shift+C Selection.Copy

Selection.PasteSpecial Paste:=xlValues, _ Operation:=xlNone, SkipBlanks:=False, _ Transpose:=False Application.CutCopyMode = False End Sub

Notice that this listing looks just a bit different than what you see on your screen and from what is shown in Figure 2-5. (Take a look at the lines beginning with Selection.PasteSpecial.) The lines have simply been reformatted to fit on the printed page. The only difference is where each line of the macro is divided.

At this point, the macro probably looks like Greek to you. Don't worry. Travel a few chapters down the road and all will be as clear as the view from Olympus.

The ConvertFormulas macro (also known as a Sub procedure) consists of several statements. Excel executes the statements one by one, from top to bottom. A statement preceded by an apostrophe (') is a comment.

Comments are included only for your information and are essentially ignored. In other words, Excel doesn't execute comments.

The first actual VBA statement (which begins with the word Sub) identifies the macro as a Sub procedure and gives its name — you provided this name before you started recording the macro. The next statement tells Excel to copy the cells in the selection. The next statement corresponds to the options you selected in the Paste Special dialog box. (This statement occupies two lines, using VBA's line continuation character — a space followed by an underscore.) The next statement cancels the Excel cut-copy mode indicator. The last statement simply signals the end of the macro subroutine.

Figure 2-5:

The VBE displays the VBA code in Modulel of Bookl.

Figure 2-5:

The VBE displays the VBA code in Modulel of Bookl.

Hey, I didn't record that!

I've noted that the macro recorder is like recording sound on a tape recorder. When you play back an audio tape and listen to you own voice, you invariably say "I don't sound like that." And when you look at your recorded macro, you may see some actions that you didn't think you recorded.

When you recorded the ConvertFormulas example, you didn't change the Operation, SkipBlanks, or Transpose options in the Paste Special dialog box, yet the recorder still recorded them. Don't worry, it happens all the time. When you record an action that includes a dialog box, Excel records all of the options in the dialog box, not just the ones that you change. In later chapters, you'll learn how to remove the extra stuff from a recorded macro.

Modifying the Macro

JUNG/

The macro you've created is fairly useful, saving you a few seconds every time you need to convert formulas to values — but it's also dangerous. After you execute this macro, you may notice that you can't choose the EditOUndo command. In other words, if you execute this macro accidentally, you have no way to convert the values back to the original formulas. (Actually, you can develop macros that can be undone with the EditOUndo command. That, however, is a bit beyond the scope of this book.)

In this section, you make a minor addition to the macro to prompt users to verify their intentions before the formula-to-value conversion takes place. Issuing such a warning isn't completely foolproof, but it's better than nothing.

You need to provide a pop-up message asking the user to confirm the macro by clicking Yes or No. Fortunately, a VBA statement exists that lets you do this quite easily.

Working in a VBA module is much like working in a word-processing document (except there's no word wrap). You can press Enter to start a new line and the familiar editing keys work as expected.

Here's how you modify the macro to include the warning:

1. In the VBE, activate Module1.

2. Place the cursor at the beginning of the Selection.Copy statement.

3. Press Enter to insert a new line and then type the following VBA statements:

Answer = MsgBox("Convert formulas to values?", vbYesNo) If Answer <> vbYes Then Exit Sub

To make the new statements line up with the existing statements, press Tab before typing the new statements. Indenting text is optional but it makes your macros easier to read. Your macro should now look like the example in Figure 2-6.

These new statements cause Excel to display a message box with two buttons: Yes and No. The user's button click is stored in a variable named Answer. If the Answer is not equal to Yes, Excel exits the subroutine with no further action (<> represents not equal to). Figure 2-7 shows this message box in action.

Activate a worksheet and try out the revised macro to see how it works. To test your macro, you may need to add some more formulas to your worksheet.

Just as you can press Alt+F11 to display the VBE, you can again press Alt+F11 to switch back to Excel.

Figure 2-6:

The Convert-Formulas macro looks like this after you add to the statements.

Figure 2-6:

The Convert-Formulas macro looks like this after you add to the statements.

Figure 2-7:

The macro displays this message box.

Figure 2-7:

The macro displays this message box.

& ' Bookl

. ÖM

A

B

B*' 1 HS

Ë

F 1 s

1

108

2

8

:4e. DOSÉS?

4

14.840215

10:259323

■&.0.1;05ttl0 64.773435

5

71 836413

77.734973

37:550294 84007933

R

65,767111

feilte

'm asfflsîâl isnnanu

8

37 331826

74.E3509,3

Mlgij

Microsoft Exnel

88.196!

C r. tr.:"'>:tt

S

10-

i-,: ¡ssl

11

■ ■.

1 fi?

1?

n

14

IS

IS

17

-

IS-

iq

i » » 1 : '.h'M't 1

1*1

-■"" 1

> 1

You'll find that clicking the No button cancels the macro, and that the formulas in the selection remain intact. When you click the No button, Excel executes the Exit Sub part of the statement. If you click Yes, the macro continues its normal course of action.

If you find this macro useful, save the workbook file.

More about the ConVertFormulas Macro

By the time you finish this book, you'll completely understand how the ConvertFormulas macro works — and you'll be able to develop more-sophisticated macros. For now, I wrap up the example with a few additional points about the macro:

1 For this macro to work, its workbook must be open. If you close the workbook, the macro doesn't work (and the Ctrl+Shift+C shortcut has no effect).

1 As long as the workbook containing the macro is open, you can run the macro while any workbook is active. In other words, the macro's own workbook doesn't have to be active.

1 The macro isn't perfect. One of its flaws is that it generates an error if the selection isn't a range. For instance, if you select a chart and then press Ctrl+Shift+C, the macro grinds to a halt and you see the error message shown in Figure 2-8. In Chapter 14, I show you how to correct this type of problem.

Figure 2-8:

The error message is VBA's way of telling you something's wrong.

Figure 2-8:

The error message is VBA's way of telling you something's wrong.

1 Before you started recording the macro, you assigned it a new shortcut key. This is just one of several ways to execute the macro.

1 You could enter this macro manually rather than record it. To do so, you need a good understanding of VBA. (Be patient, you'll get there.)

1 The two statements you added after the fact are examples of VBA statements that you cannot record.

1 You could store this macro in your Personal Macro Workbook. If you were to do so, the macro would be available automatically whenever you start Excel. See Chapter 6 for details about your Personal Macro Workbook.

1 You could also convert the workbook to an add-in file. (More about this in Chapter 22.)

You've been initiated into the world of Excel programming. (Sorry, there's no secret handshake or decoder ring.) I hope this chapter helps you realize that Excel programming is something you can actually do — and even live to tell about it. Keep reading. Subsequent chapters almost certainly answer any questions you have, and you'll soon understand exactly what you did in this hands-on session.

Was this article helpful?

0 0

Post a comment