Microsoft® Excel 2000/Visual Basic® for Applications Fundamentals

Author Reed Jacobson Pages 368 Disk 1 Companion CD(s) Level Beg/Int Published 05/07/1999 ISBN 9780735605930 ISBN-10 0-7356-0593-9 Price(USD) $39.99 To see this book's discounted price, select a reseller below.	More Information About the Book Table of Contents Sample Chapter Related Series Related Books About the Author Support: Book & CD Rate this book

Chapter 1: Make a Macro Do Simple Tasks

• Chapter 1: Make a Macro Do Simple Tasks
• How Visual Basic for Applications Talks to Excel
  • Automation
  • Excel Objects and You
• Creating a Simple Macro
• Changing Multiple Properties at Once
• Editing a Recorded Macro
• Recording Actions in a Macro

Chapter Objectives

Estimated time: 40 minutes

In this chapter, you'll learn how to:

• Record and run a macro.

• Understand and edit simple recorded macros.

• Run a macro by using a shortcut key.

Last month we lost the remote control to our VCR. It was awful. I wanted to set the machine to record "Mystery Science Theater 3000" at 1:00 a.m. one night, but I couldn't do it because all the scheduling features were built into the remote control. Fortunately, after about two weeks, my wife detected a bulge in the cloth backing of the recliner and retrieved the precious controller. I'm so happy that I can now record old movies. Someday I might even watch some of them.

Microsoft Visual Basic for Applications (VBA) is Microsoft Excel 2000's remote control. Sure, you can use Excel without ever using VBA, but not only can the VBA "remote control" make your life more convenient, it also allows you to take advantage of features that you can't get to with the standard "front-panel" controls. And once you become acquainted with Excel's remote control, you'll wonder how you ever got along without it.

How Visual Basic for Applications Talks to Excel

The first spreadsheet macro languages mimicked the user interface. For example, if you typed R (for "Range"), N (for "Name"), and C (for "Create") in the user interface, you would enter RNC into the macro to automate the process. This approach had inherent weaknesses. Not only were keystroke macros difficult to read, but they didn't adapt well to the graphical user interface. What do you use to represent dragging a rectangle with the mouse?

To solve these problems, the early versions of Excel contained a new type of macro language that made the macro commands independent of the user interface. For example, in Excel version 4 you could copy a range in at least three different ways: press Ctrl-C, click the Copy toolbar button, and click Copy on the Edit menu. All those user interface sequences translated to a single macro function, =COPY(). These function-based macros had two major drawbacks: First, Excel macros were very specific to Excel; the language couldn't be adapted to other applications. Second, the number of functions kept increasing with each new version, and there was no good way to organize or group the thousands of possibilities.

Automation

Excel with VBA incorporates Automation (once known as OLE Automation), a powerful way of automating applications. Excel was the first major application to take advantage of this concept. In this approach, VBA acts as a general-purpose language that's independent of the application. Suddenly, anyone who knows how to work with any version of Visual Basic has a big head start in automating Excel, and anyone who learns how to write Excel macros in VBA can transfer that knowledge to other types of Visual Basic programming.

Even though Excel hosts VBA, VBA doesn't have any special "hooks" into Excel's internals. Rather, Excel exposes its capabilities to VBA by means of a special set of commands called an object library. VBA talks to Excel's object library.

Click to view graphic

VBA can control not only Excel, but also any application that provides an object library. All Microsoft Office applications provide object libraries, and several other Microsoft and non-Microsoft applications do, too.

Click to view graphic

The VBA that comes with Excel isn't the only language that can communicate with the object library. Any language that supports Automation can control Excel. You can control Excel not only with the VBA hosted by Excel, but also with a VBA project hosted by Microsoft Word, with the stand-alone version of Visual Basic, or even with C++ or Inprise Corporation's Delphi program.

Click to view graphic

Excel Objects and You

Not only does the object library expose Excel's capabilities to VBA, but even more important, the object library exposes Excel's capabilities to you. Once you know how to read and interpret an object library, you can discover new features and figure out quickly how to put them to work. The best way to start finding out about how VBA communicates with Excel's objects is to record some simple macros. Eventually, however, you'll want to move beyond the limitations of the macro recorder.

In Part 1 of this book, you'll learn how to record and modify simple macros. In Part 2, you'll learn how Excel objects work. In Part 3, you'll learn some secret powers of Visual Basic for Applications. And in Part 4, you'll learn how to make a macro easy to use.

Start the lesson

1. Start Excel.

2. On the Standard toolbar, click the Open button, and then in the Open dialog box, click the Favorites button.

3. Double-click the Excel VBA Practice folder on the companion CD, and then double-click the Budget workbook.

4. Save the Budget file as Chapter1.

Excel has a large collection of convenience tools readily available as shortcut keys and as buttons on toolbars. Sometimes a built-in convenience tool doesn't work quite the way you want. Enhancing a built-in tool is a good first macro to create.

Show the Visual Basic toolbar

Before you start creating the macros, take one small step that will make your work with macros much easier.

1. Point to any toolbar, and click the right mouse button. (This is called "right-clicking.") The toolbar shortcut menu appears, showing most of the available toolbars.

2. Select Visual Basic from the toolbar list. The Visual Basic toolbar appears. You can change the location and shape of this toolbar just as you can any other Excel toolbar.

   
   Click to view graphic

Now, when you're ready to record a macro, just click the circle on the toolbar. When you're ready to run a macro, click the triangle.

Format currency with a built-in tool

On the Formatting toolbar, Excel has a button that formats the current selection as currency: the Currency Style button.

1. In the Chapter1 workbook, select cells D3:F4 on the Budget2000 worksheet.

2. Click the Currency Style button on the Formatting toolbar. Excel reformats the selected cells as currency.

   
   Click to view graphic

The currency format that Excel applies when you click the Currency Style button has two decimal places. Sometimes you want to display currency with two decimal places—perhaps in your checkbook. But other times you don't want two decimal places—perhaps your budget doesn't warrant that kind of precision. You might want to create a macro to format a cell as currency with no decimal places instead.

Record a macro to format currency

1. On the Budget2000 worksheet, select cells D7:F8.

2. On the Visual Basic toolbar, click the Record Macro button.

3. Replace the default macro name with FormatCurrency, and then click OK.

   
   Click to view graphic

   The word Recording appears in the status bar, and a Stop Recording toolbar appears. You're recording.

4. On the Format menu, click the Cells command, and then, if necessary, click the Number tab.

5. Select Currency from the Category list.

6. Replace the value in the Decimal Places box with a zero, and then click OK.

   
   Click to view graphic

   Excel formats the selected cells as currency without decimal places.

7. Click the Stop Recording button.

8. Save the Chapter1 workbook.

That's it. You recorded a macro to format a selection with the currency format you want. Now you probably want to try out the macro to see how it works.

Run the macro

1. On the Budget2000 worksheet, select cells D9:F10.

2. On the Visual Basic toolbar, click the Run Macro button.

3. Select the FormatCurrency macro in the list, and click Run.

Your macro gives the selected cells your customized currency format. Running the macro from the Macro dialog box isn't much of a shortcut, though.

Assign a shortcut key to the macro

1. On the Visual Basic toolbar, click the Run Macro button.

2. Select the FormatCurrency macro in the list, and then click the Options button. The Macro Options dialog box allows you to change the macro's shortcut key assignment and its description.

3. You want to assign Ctrl-Shift-C as the shortcut key. If necessary, click in the box below the Shortcut key label and press Shift-C.

   
   Click to view graphic

4. Click OK to return to the Macro dialog box, and then click Cancel to get back to the worksheet.

5. Select cells D11:F13, and press Ctrl-Shift-C to run the macro.

6. Save the Chapter1 workbook.

Now you've successfully recorded, run, and enhanced a macro—all without seeing anything of the macro itself. Aren't you burning with curiosity to see what you've just created?

Look at the macro

The macro is actually hidden away in the workbook, but you need to open the Visual Basic Editor to be able to see it.

1. On the Visual Basic toolbar, click the Run Macro button.

2. Click FormatCurrency, and then click Edit. The Visual Basic Editor window appears. The Visual Basic Editor appears to be a separate program, but it is "owned" by Excel. If you quit Excel, Visual Basic automatically shuts down. Inside the Visual Basic Editor, a window captioned Module1 appears as well.

3. Maximize the Module1 window so that it fills the Visual Basic Editor, and then resize the Visual Basic Editor window so that you can see the Excel workbook in the background.

4. If any other windows are visible in the Visual Basic Editor, close them now.

   
   Click to view graphic

The window captioned Module1, a module, is the place where the recorder puts macros. Your macro is in the Module1 module. The macro looks like this:

Sub FormatCurrency()
'
' FormatCurrency Macro
' Macro recorded 11/15/98 by Reed Jacobson
'

'
    Selection.NumberFormat = "$#,##0"
End Sub

The five lines that start with apostrophes at the beginning of the macro are comments. The apostrophe at the beginning of the line indicates that the following text is a comment. (The blank line among the comments, without even an apostrophe, is where the recorder would have put the shortcut key combination if you had assigned it when you recorded the macro.) The recorder puts in the comments partly to remind you to add comments as you write a macro. You can add to them, change them, or delete them as you wish without changing how the macro runs. Comments are green to help you distinguish them from statements that do something.

The macro is written in VBA and follows standard Visual Basic rules. The macro itself begins with Sub, followed by the name of the macro. (Is Sub used because a macro is typically hidden, out of sight, like a submarine? Or does it stand for subroutine, for reasons you'll learn at the end of Chapter 2? Stay tuned.) The last line of a macro is always End Sub.

The Selection.NumberFormat statement does the real work. It is the body of the macro. Selection stands for "the current selection." NumberFormat refers to an attribute—or property—of the selection. To interpret a VBA instruction, read the statement from right to left, like this: "Let `$#,##0' be the number format of the selection."

Macro statements in Visual Basic work backward, the same as actions do in an Excel worksheet. In a macro statement, you state what you're going to work on, and then you do something to it.

Changing Multiple Properties at Once

The FormatCurrency macro changes a single attribute of the current selection—the number format. In Excel macros, an attribute is called a property. In the FormatCurrency macro, NumberFormat is a property of a cell. Many macro statements assign a value to a property. Whenever the macro recorder creates a statement containing an equal sign, the word in front of the equal sign is a property. Sometimes when you record an action, the macro changes multiple properties at the same time.

Merge text vertically with a command

Excel has a toolbar button that can merge and center several cells in a horizontal row: the Merge And Center button. But sometimes you might want to merge cells vertically along the edge of a report. Excel doesn't have a toolbar button that merges cells vertically along the edge and adjusts the position of text in those cells, but you can record a macro that does.

To better understand what's required, first walk through the steps to create this format using menu commands.

1. Activate the Budget2000 window.

2. Select the range A6:A12. The label, Variable, is at the top of the selected range.

   
   Click to view graphic

3. On the Format menu, click Cells, and then click the Alignment tab. The Alignment tab has several controls that control alignment, wrapping, orientation angle, shrinking, and merging.

4. Click the Merge Cells check box, and drag the red diamond in the orientation control up to the top of the arc to set the orientation to 90 degrees.

   
   Click to view graphic

5. Click OK to merge and tilt the label.

Putting a label to the side of a block of cells is extremely powerful. You can make it easy to do by recording a macro.

Record a macro to merge cells vertically

1. Rearrange your windows as necessary so that you can see both the Module1 window and the Excel window.

2. Select the range A15:A20, and then click the Record Macro button.

3. In the Record Macro dialog box, replace the default macro name with MergeVertical, replace the default description with Merge cells vertically, and set Ctrl-Shift-M as the shortcut key.

4. Click OK. In the module window, you can see that the recorder imme-diately puts the comment lines, the keyboard shortcut, and the Sub and End Sub lines into the macro.

5. On the Format menu in the Excel window, click Cells. In the Format Cells dialog box on the Alignment tab, select the Merge Cells check box, set the alignment to 90 degrees, and click OK. The recorder puts several lines into the macro all at once.

6. Click the Stop Recording button.

7. Save the Chapter1 workbook. The new macro in the Module1 window looks like this:

Sub MergeVertical()
'
' MergeVertical Macro
' Merge cells vertically
'
' Keyboard Shortcut: Ctrl+Shift+M
'
    With Selection
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 90
        .AddIndent = False
        .ShrinkToFit = False
        .MergeCells = True
    End With
End Sub

The macro shows seven different property settings for the cell alignment. Each property is followed by an equal sign. These properties correspond exactly to the controls you saw in the Format Cells dialog box.

Each of the property settings affects the current selection, just as the Number-Format property setting does in the FormatCurrency macro. In the FormatCurrency macro, however, the property name is attached directly to Selection with a period, to show that the property affects the cells in the current selection. In this macro, however, each property name just "hangs there," preceded only by a period.

A pair of statements beginning with With and ending with End With is called a With structure. It means that every time there is a period with nothing in front of it, you pretend that the word that followed the With is there. With structures make the code easier to read because you can tell instantly that all the properties relate to the current selection. You'll often see With structures in macros that you record.

Eliminate unnecessary lines from the macro

In many dialog boxes, the macro recorder records all the possible properties, even though you might change the values of only one or two of them. You can make your macro easier to understand if you eliminate unnecessary properties.

In the MergeVertical macro, you need to change the values of only the Orientation and MergeCells properties. You can therefore delete the other lines from the macro.

1. Activate the Visual Basic Editor window, and click as far to the left of the HorizontalAlignment statement as you can within the Editor window. (Your mouse pointer should turn into a right-pointing arrow before you click.) This action selects the entire line, including the indent that precedes the text.

2. Press the Delete key.

3. Repeat steps 1 and 2 for each of the properties except Orientation and MergeCells. If you delete too much, click the Undo button to restore what you deleted. The simplified macro (ignoring the comment lines, which you can delete if you want) should look like this:

   Sub MergeVertical()
       With Selection
           .Orientation = 90
           .MergeCells = True
       End With
   End Sub
   

4. Activate the Excel window, and select cells A25:A30.

5. Press Ctrl-Shift-M. The macro adjusts the label.

6. Save the Chapter1 workbook.

Now you've not only recorded a macro, but you've also deleted parts of it—and it still works. Next you'll record a macro and make additions to it.

Having macros in multiple modules shouldn't be a problem. When you use the Macro dialog box to select and edit a macro, it automatically takes you to the appropriate module.

Editing a Recorded Macro

A typical Excel worksheet has light gray gridlines that mark the boundaries of the cells. Sometimes, you might want to remove the gridlines. First walk through the process to remove the gridlines with menu commands, and then record a macro to make the change.

Remove gridlines with a command

1. On the Tools menu, click Options, and then, if necessary, click the View tab.

2. Clear the Gridlines check box at the bottom of the Window Options group.

3. Click OK. The gridlines disappear.

4. Repeat step 1, and select the Gridlines check box to turn the gridlines back on. Then click OK.

Gridlines are a property of the window. You can select the Gridlines check box so that the value of the property is True and the window displays the gridlines, or you can clear the check box so that the value of the property is False and the window doesn't display the gridlines. Now see how the recorder turns off the gridlines.

Record a macro to remove gridlines

1. Click the Record Macro button.

2. Replace the default macro name with RemoveGrid, and click OK. The recorder puts the shell of the macro (the comments and the Sub and End Sub lines) into the module.

3. On the Tools menu, click Options, clear the Gridlines check box on the View tab, and then click OK. The gridlines disappear.

4. Click the Stop Recording button, and then save the Chapter1 workbook.

5. Click the Run Macro button, select RemoveGrid, and then click Edit to look at the resulting code. Ignoring the comment lines, here's what it looks like:

Sub RemoveGrid()
    ActiveWindow.DisplayGridlines = False
End Sub

This macro is similar to the FormatCurrency macro. You can read it as "Let `False' be the DisplayGridlines property of the active window." This time you're not changing the selection but rather the active window. In both cases, you're changing an object, an Excel element that you can control with macros. However, this time the object isn't a range of cells, but a window.

Run the macro from the Visual Basic Editor

You can easily change the macro to make it restore the gridlines.

1. In the RemoveGrid macro, replace False with True. You can't use a shortcut key while you're in the Visual Basic Editor, but the Visual Basic Editor has its own shortcut for running whatever macro you're currently editing.

2. Press F5 to run the macro. The gridlines reappear in the current Excel worksheet. Pressing F5 from the Visual Basic Editor is a fast way to run a macro while you're testing it.

Toggle the value of a property with a macro

You could create one macro to turn the gridlines off and a second macro to turn them back on, but somehow, letting a single macro toggle the value of the property seems more natural. To toggle the value of a property, you first ask Excel for the current value, which you can store in a special container called a variable. You then change the value as you assign the variable back to the property. Here's how:

1. Insert a new blank line after the comments.

2. Select ActiveWindow.DisplayGridlines, and press and hold the Ctrl key as you drag it up to the blank line. This makes a copy of the statement.

3. At the beginning of the new line, type myGrid = ; the resulting statement is myGrid = ActiveWindow.DisplayGridlines. This statement stores the current value of DisplayGridlines, whether True or False, in the variable myGrid.

4. Double-click True in the original statement, and replace it with Not myGrid. The VBA keyword Not turns the value True into False and False into True.

5. Change the name RemoveGrid to ToggleGrid, to better reflect the macro's new capabilities. This is what the macro should look like now:

   Sub ToggleGrid()
       myGrid = ActiveWindow.DisplayGridlines
       ActiveWindow.DisplayGridlines = Not myGrid
   End Sub
   

6. Save the Chapter1 workbook, reactivate the Module1 window, and then press F5 several times to test the macro.

The macro reads the old value of the property, changes it to the opposite with the keyword Not, and assigns the newly inverted value back to the property.

Recording Actions in a Macro

By now, you should see a pattern to creating a simple convenience macro: Try out an action interactively. Once you know how to do the task, start the recorder. Do the task with the recorder on. Then stop the recorder.

So far, all the macros you've recorded have changed the value of one or more properties of an object. Some actions that you can record don't change the value of a property. Let's see what a macro looks like when it doesn't change a property.

Suppose you want to freeze the formulas of some cells in the Budget2000 worksheet at their current values. First change the formulas to values using menu commands, and then create a macro that can change any formula to a value.

Convert a formula to a value using menu commands

1. Activate the Budget2000 window, and then select cell D4.

   
   Click to view graphic

   Notice the formula in the formula bar: =D3-D68.

2. On the Edit menu, click the Copy command.

3. Don't change the selection. On the Edit menu, click the Paste Special command. The Paste Special dialog box appears.

4. Select the Values option from the Paste group, and click OK. Excel pastes the value from the cell over the top of the existing cell, eliminating the formula that was in it. The moving border is still visible around the cell, indicating that you could paste the value again somewhere else if you wanted.

5. Press the Esc key to get out of copy mode and clear the moving border.

   
   Click to view graphic

   Look at the formula bar: cell D4 now contains the value 26819.9.

As you carry out the copy and paste actions with the menus, notice that the Copy command doesn't bring up a dialog box. You see a moving border around the cells and a message in the status bar, but you don't need to tell Excel how to do the copying. The Paste Special command, on the other hand, does require additional information from you to carry out its job, so it displays a dialog box. Some actions in Excel require additional information about how to carry out the action, and some don't.

Convert a formula to a value with a macro

Watch how the macro recorder handles actions that display a dialog box, compared with how it handles actions that don't.

1. On the Budget2000 worksheet, select cell E4. Notice the formula in the formula bar: =E3-E68.

2. On the VBA Toolbar, click Record Macro.

3. Replace the default name with ConvertToValues.

4. Set the shortcut key to Ctrl-Shift-V, and click OK.

5. On the Edit menu, click Copy.

6. On the Edit menu, click Paste Special, click the Values option, and click OK.

7. Press the Esc key to get rid of the moving border.

8. Click the Stop Recording button, and save the Chapter1 workbook. Look at the formula bar. Cell E4 now contains the value 27057.9.

9. Switch to VBA to look at the recorded macro.

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

The basic structure of this macro is the same as that of the other macros you've seen in this chapter. The last line, for example, sets the value of the CutCopyMode property in much the same way that the ToggleGrid macro changed the DisplayGridlines property setting of the active window. The two lines that begin with Selection, however, are something new. Neither has a simple equal sign in it.

Selection.Copy looks similar to Selection.NumberFormat from the FormatCurrency macro. In that macro, NumberFormat was a property of the selection and you were assigning a new value to the NumberFormat property. Copy, however, isn't a property. That's why it doesn't have an equal sign after it. You don't assign anything to Copy; you just do it. Actions that don't use an equal sign to set the value of a property—that is, actions like Copy—are called methods. Like the names of properties, the names of methods are recorded by Excel and displayed at the end of the object's name.
When you use the Copy command from the menu, Excel doesn't ask you for any extra information. In the same way, when you use the Copy method in a macro, you don't give any extra information to the method.

PasteSpecial is also a method in Excel. PasteSpecial doesn't have an equal sign after it; it isn't a property that you assign a value to. The Paste Special command on the Edit menu displays a dialog box, but the dialog box doesn't show you properties to change; it just asks how to carry out the paste special action. When you execute the PasteSpecial method in a macro, you give the extra information to the method. The extra pieces of information you give to a method are called arguments.

Using a method with an object is like giving instructions to your nine-year-old. With some instructions—like, "Come eat"—you don't have to give any extra information. With other instructions—like, "Go to the store for me"—you have to tell what to buy (milk), how to get there (on your bike), and when to come home (immediately). Giving these extra pieces of information to your child is like giving arguments to an Excel method. (You call them arguments because whenever you tell your child how to do something, you end up with one.)

The four arguments you give to PasteSpecial correspond exactly to the four option groups in the Paste Special dialog box. Each argument consists of a name for the argument (for example, Paste) joined to the argument value (for example, xlValues) by a colon and an equal sign (:=).

Don't confuse an argument with a property. When you assign a new value to a property, you separate the value from the property with an equal sign, as in this statement:

ActiveWindow.DisplayWorkbookTabs = False

You read this statement as "Let `False' be the DisplayWorkbookTabs property of the active window."

Assigning a value to a property can appear superficially similar to using a named argument with a method. When you use a named argument with a method, you separate the method name from the argument name with a space, and you separate the argument name from the argument value with a colon and an equal sign. You must never confuse an equal sign with a colon and equal sign, any more than you would confuse beer with root beer.

When you have more than one argument, separate each one from the next with a comma and a space, as in this statement:

Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone

An argument looks a lot like a property, but an argument always follows a method name, whereas a property follows an object. Also, a property is followed by an equal sign, but an argument is followed by a colon and an equal sign.

Make a long statement more readable

When one of the statements in a macro gets to be longer than about 70 characters, the macro recorder puts a space and an underscore ( _ ) after a convenient word and continues the statement on the next line. The underscore tells the macro that it should treat the second line as part of the same statement. You can manually break long statements into several lines, as long as you break the line after a space. You can also indent related lines with tabs, to make the macro easier to read.

1. In the ConvertToValues macro, put each argument of the PasteSpecial statement on a separate line, using a space and an underscore character at the end of each line except the last.

   Sub ConvertToValues()
       Selection.Copy
       Selection.PasteSpecial __
           Paste:=xlValues, __
           Operation:=xlNone, __
           SkipBlanks:=False, __
           Transpose:=False
       Application.CutCopyMode = False
   End Sub
   

   Splitting a statement into several lines doesn't change the way the macro runs; it just makes it easier to read.

2. In Excel, select cell F4 and press Ctrl-Shift-V to run the macro. Look at the formula bar to make sure the formula changed to a value.

3. Save the Chapter1 workbook.

Most of the macros in this chapter change the settings of object properties, but this macro executes object methods. Properties and methods look very similar: both are separated from objects by periods. However, you assign new values to properties, whereas you execute methods, sometimes giving the method arguments along the way.

Create a personal signature

Included with your Microsoft Office 2000 CD is a program that you can use to create a personal signature.

1. On the Windows Start menu, click Find, and then click Files Or Folders. In the Named box, type selfcert. In the Look In list, select the drive containing the Microsoft Office 2000 CD or the network folder containing the Office installation files. Then click Find Now.

   
   Click to view graphic

2. When the program file Selfcert appears at the bottom of the dialog box, double-click its name to run the program.

   
   Click to view graphic

3. In the Your Name box, type your name, and click OK. Click OK to close the confirmation box.

4. Close the Find dialog box.

You've now created a personal signature that you can use to sign your macro projects.

If you have Microsoft Internet Explorer version 4 or later installed on your computer (even if it isn't your default browser), you can digitally sign your VBA project to avoid the warning message. Before you can sign a project, you need a digital signature. You might work at a company that can issue you a digital signature, or you can obtain a digital signature from a certificate authority such as VeriSign or Thawte, or you can create a self-signature for your own macros.

Add a signature to your project

You add a signature to the VBA project part of your workbook. If a virus—or anyone else—changes the VBA project, the signature becomes invalid. The signature applies only to the VBA project, not to the workbook data, so anyone can change the worksheets without invalidating the signature.

1. With the Chapter1 workbook open, activate the Visual Basic Editor, and on the Tools menu, click Digital Signature.

2. In the Digital Signature dialog box, click Choose.

   
   Click to view graphic

3. In the Select Certificate dialog box, select the certificate with your name and click OK.

4. Click OK to close the dialog box. Switch to Excel, and save and close the workbook.

The project in the Chapter1 workbook is now signed. If you need to modify a macro, as long as you do it on the machine that contains your digital signature, VBA automatically reapplies the signature. But no one else can reapply your signature to the project.

Trust a signature

Now that you have a workbook containing a signed project, you can tell Excel to trust workbooks that contain macros you have signed.

1. Open the Chapter1 workbook.

   
   Click to view graphic

   A message asks if you want to enable macros, and also if you want to trust macros from this source in the future.

2. Select the Always Trust Macros From This Author check box, and click the Enable Macros button.

3. Close the Chapter1 workbook, and then reopen it. No warning message appears. Excel recognizes you as a trusted source for macros.

4. Close the Chapter1 workbook, and quit Excel.

Security is important when you create any programs, including macros. Security is especially important when you share applications with others. If you create macros for others to use, you'll probably want to obtain a properly authenticated digital signature to protect your work.

Chapter Summary

To	Do this	Button
Show the Visual Basic toolbar	Right-click any toolbar, and click the Visual Basic Editor from the menu.
Turn on the recorder	On the Visual Basic toolbar, click the Record Macro button.
Turn off the recorder	Click the Stop Recording button.
Run a macro	Click the Run Macro button, select the name of the macro, and click Run.
Look at a macro	Click the Run Macro button, select the name of the macro, and click Edit.
Add a shortcut key	Click the Run Macro button, select the name of the macro, and click Options.
Save the value of a property	Make up a variable name and assign the property value to it.
Change the value of a property	Change the value that the recorded macro assigns to the property.
Split a long statement into multiple lines	Break lines after convenient words and put a space and an underscore ( _ ) at the end of each line except the last.
Create a digital signature for yourself	Run the Selfcert program, and type the name you want to use.
Apply a digital signature to a VBA project	In the VBA editor, on the Tools menu, click Digital Signatures. Click Choose, select your signature, and click OK.

For online information about	Ask the Assistant for help using the words
Recording macros	"Recording Macros"

Preview of the Next Chapter

In the next chapter, you'll learn how to combine small macros to automate whole tasks. You'll also learn how to find and fix problems when your macros don't work quite the way you want.