VBA Programming Series...4

Modifying the Macro

As you might expect, not only can you view your macro in the VBE, you can also change it. If you look at the code, some of it will actually make sense. Even though you have no idea what you’re doing at this point, I’ll bet you can make these changes to the code:

✓ Change the name that’s entered into the active cell. If you have a dog, use your dog’s name.

✓ Change the font name or size.

✓ See if you can figure out the appropriate location for a new statement:

Selection.Font.Italic = True

Working in a VBA code module is much like working in a word-processing document (except there’s no word wrap and you can’t format the text). On second thought, I guess it’s more like working in Windows Notepad. You can press Enter to start a new line, and the familiar editing keys work as expected.

After you’ve made your changes, jump back to Excel and try out the revised macro to see how it works. Just as you can press Alt+F11 in Excel to display the VBE, you can press Alt+F11 in the VBE to switch back to Excel.

Saving Workbooks That Contain Macros

If you store one or more macros in a workbook, the file must be saved with “macros enabled.” In other words, the file must be saved with an XLSM exten-sion rather than the normal XLSX extension.

For example, when you save the workbook that contains your NameAndTime macro, the file format in the Save As dialog box defaults to XLSX (a format that cannot contain macros!). Unless you change the file format to XLSM, Excel displays the warning shown in Figure . You need to click No, and then choose Excel Macro-Enabled Workbook (*.xlsm) from the Save As Type drop-down list.

Understanding Macro Security

Macro security is a key feature in Excel. The reason is that VBA is a powerful language — so powerful that even a simple macro can do serious damage to your computer. A macro can delete files, send information to other comput-ers, and even destroy Windows so that you can’t even start your system.

The macro security features in Excel 2007 and Excel 2010 were created to help prevent these types of problems.

Figure shows the Macro Settings section of the Trust Center dialog box.

To display this dialog box, choose Developer➪Codem➪Macro Security.

By default, Excel uses the Disable All Macros with Notification section. With this setting in effect, if you open a workbook that contains macros (and the file is not digitally “signed” or stored in a trusted location), Excel displays a warning like the one in Figure . If you are certain that the workbook comes from a trusted source, click Enable Macros, and the macros will be enabled.

You see the pop-up box in Figure  only if the VBE is open. Otherwise, Excel displays an eye-catching Security Warning above the Formula bar. You can click the button to enable the macros.

If you use Excel 2010, Excel will remember if you’ve designated a workbook to be safe. So, the next time you open it, you won’t see the Security Warning. That’s not the case with Excel 2007 though. You’ll get the security warning every time — unless you store that workbook in a trusted location.Perhaps the best way to handle macro security is to designate one or more folders as trusted locations. All the workbooks in a trusted location are opened without a macro warning. You designate trusted folders in the Trusted Locations section of the Trust Center dialog box.

If you want to find out what the other macro security settings imply, press F1 while the Macro Settings section of the Trust Center dialog box is in view. The Help screen opens and the subject “Enable or disable macros in Office documents” is shown in the Help window.

More about the NameAndTime Macro

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

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

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

✓ The macro isn’t “pro-quality” code. It will overwrite existing text with no warning — and its effects can’t be undone.

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

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

✓ You can store this macro in your Personal Macro Workbook. If you do so, the macro is available automatically whenever you start Excel. 

✓ You can also convert the workbook to an add-in file.

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