Vertex42 The Excel Nexus

Professional Excel Templates

Get Instant Access

The Power of Excel 11

Barriers to Entry 11

Knowing Your Tools—The Visual Basic Toolbar 13

Macro Security 14

Overview of Recording, Storing,and Running a Macro 15

Running a Macro 16

Understanding the Visual Basic Editor (VBE) 18

Case Study 20

Examining Code in the Programming Window 22

Running the Macro on Another Day Produces Undesired Results 24

Case Study 25

Frustration 28

Next Steps: Learning VBA Is the Solution____28

To make matters worse, even in 1995, I was the spreadsheet wizard in my office. My company had just forced everyone to convert from Lotus 1-2-3 to Excel. I was now faced with a macro recorder that didn't work and a language that I could not understand. This was not a good combination of events.

My assumption in writing this book is that you are pretty talented with a spreadsheet. You probably know more than 90% of the people in your office. I will assume that you are not a programmer, but that you might have taken a class in BASIC in high school. This isn't a requirement—it actually is a barrier to entry into the ranks of being a successful VBA programmer. There is a pretty good chance that you've recorded a macro in Excel and a similar chance that you were not happy with the results.

The Good News—It Is Easy to Climb the Learning Curve

Even if you've been frustrated with the macro recorder before, it is really just a small speed bump on your road to writing powerful programs in Excel. This book will teach you why the macro recorder fails, but also will show how to easily change the recorded code into something useful. For all the former BASIC programmers in the audience, I will decode this bizarre-looking language so that you can easily pick through recorded macro code and understand what is going on.

The Great News—Excel with VBA Is Worth the Effort

Although you've probably been frustrated with Microsoft over your inability to record macros in Excel, the great news is that Excel VBA is powerful. Absolutely anything that you can do in the Excel interface can be duplicated with stunning speed in Excel VBA. If you find yourself routinely creating the same reports manually day after day or week after week, Excel VBA will greatly streamline those tasks.

The authors work for MrExcel Consulting. In this role, we get to help automate reports for hundreds of clients. The stories are often similar: The M.I.S. department has a several-month backlog of requests. Someone in accounting or engineering discovers that he or she can import some data into Excel and get the reports necessary to run the business. This is a liberating event—you no longer need to wait months for the I.T. department to write a program. However, the problem is that after you import the data into Excel and win accolades

With Lotus 1-2-3, you could record a macro today, play it back tomorrow, and it would faithfully work. When you attempt the same feat in Microsoft Excel, the macro might work today but not tomorrow. I was horribly frustrated in 1995 when I tried to record my first Excel macro.

Visual Basic Is Not Like BASIC

The code generated by the macro was unlike anything that I had ever seen. It said this was "Visual Basic." I had the pleasure of learning half of dozen programming languages at various times; this bizarre-looking language was horribly unintuitive and did not at all resemble the BASIC language that I had learned in high school.

from your manager for producing the report, you then find yourself producing the same report every month or every week. This becomes very tedious.

Again, the great news is that with a few hours of VBA programming, you can automate the reporting process and turn it into a few button clicks. The reward is great. Hang with me as we cover a few of the basics.

This chapter is going to expose why the macro recorder does not work. I will walk through a simple example of recorded code and demonstrate why it will work today but fail tomorrow. You will be seeing code here, and I realize that this code may not be familiar to you yet. That's OK. The point of this chapter is to demonstrate the fundamental problem with the macro recorder. We'll also cover the fundamentals of the Visual Basic Environment.

Knowing Your Tools—The Visual Basic Toolbar

We'll start with a basic overview of the tools needed to get around with VBA. In Excel, select View, Toolbars, Visual Basic to display the Visual Basic toolbar (see Figure 1.1).

Figure 1.1

The Visual Basic toolbar provides an interface for running and recording macros.

Control Toolbox Security Settings

Control Toolbox Security Settings

Microsoft Script Editor

Record Macro Design Mode Visual Basic Editor

Microsoft Script Editor

Record Macro Design Mode Visual Basic Editor

Excel's Visual Basic toolbar provides these seven actions:

■ Run Macro—Displays a list of available macros.

■ Record Macro—Begins the process of recording a macro and displays the Stop Recording toolbar as shown in Figure 1.2.

Figure 1.2

The Stop Recording toolbar is one of the smallest toolbars in Excel, yet the relative reference button on the right is one of the most important in getting your recorded macros close to working.

■ Visual Basic Editor—Opens up the Visual Basic Editor.

■ Control Toolbox—Displays the controls that can be added to a worksheet.

■ Design Mode—Turns on the design mode, enabling the user to edit controls on the worksheet. If you attempt to edit a control before you are in design mode, you will cause the action associated with that control to be performed.

■ Microsoft Script Editor—Opens the Script Editor, useful for creating scripts for Web sites. This isn't related to VBA, so we won't be discussing it.

When you click the Record button, the Stop Recording toolbar appears. As shown in Figure 1.2, it provides these two actions:

• Stop Recording—Ends the current recording session.

• Relative Reference—Tells Excel to record with relative reference rather than the absolute reference.

Macro Security

After VBA macros were used as the delivery method for some high-profile viruses, Microsoft changed the default security settings to prevent macros from running. Thus, before we can begin discussing the recording of a macro, we need to show you how to adjust the default settings.

The macro security settings can be found under Tools, Macro, Security. There are four levels of security: Very High, High, Medium, and Low. By default, Excel sets the security to High (see Figure 1.3). This setting will prevent all unsigned macros from running or even being edited. You need to adjust this setting to Medium to begin writing code.

Figure 1.3

Excel macro security settings are set to High by default.

Figure 1.3

Excel macro security settings are set to High by default.

Very High Security

This is the first option of using Microsoft's "Sandbox" paradigm for security. The paradigm says that your network administrator can set up a highly protected network directory and define it as a trusted location. This area is called the Sandbox. You are allowed to play any macros located in the Sandbox. Anything that is installed outside the Sandbox is off limits. The theory is that viruses will not be able to locate themselves into the secured trusted area

High Security

The High security setting allows only trusted macros to run. A macro is trusted when it has been digitally signed and you choose to trust the source. Because macro signing requires that you buy a digital certificate from an agency such as VeriSign, this setting effectively dis allows all macros that you write yourself. If a workbook contains an unsigned macro, the workbook opens but the macros are automatically disabled—no prompt appears informing you of this.

Medium Security

With Medium security, you get to choose whether you want to Enable or Disable macros each time that you open each workbook. This is the setting I recommend. On the one hand, it is annoying to be asked constantly to enable the macro every time you open a workbook. However, this question is the last line of defense when someone has sent you a workbook with malicious virus code, and being able to disable macros when you did not expect code to be in a received workbook is the safest setting.

Low Security

With Low security, all macros enable automatically. This can be dangerous if you ever receive a workbook with a malicious macro. I do not recommend this setting.

If you are considering this setting because you're tired of enabling your personal macros, then look into adding a digital signature to your macros.

Overview of Recording, Storing, and Running a Macro

Recording a macro is very useful when you do not have enough experience in writing lines of code in a macro. As you gain more knowledge and experience, you will begin to record lines of code less and less frequently.

To begin recording a macro, select Tools, Macro, Record New Macro, or press the Record Macro button on the VBA toolbar. Before recording begins, Excel displays the Record Macro dialog box, as shown in Figure 1.4.

Filling Out the Record Macro Dialog

In the Macro Name field, type a name for the macro. Be sure to type continuous characters; for example, type Macrol and not Macro 1 (with a space). Assuming you will soon be creating many macros, use a meaningful name for the macro. A name such as "FormatReport" is more useful than "Macrol."

Figure 1.4

Use the Record Macro dialog box to assign a name and a shortcut key to the soon-to-be-recorded macro.

Figure 1.4

Use the Record Macro dialog box to assign a name and a shortcut key to the soon-to-be-recorded macro.

The second field in the Record Macro dialog box is a shortcut key. If you type j in this field, then pressing Ctrl+j causes this macro to run.

In the Record Macro dialog box, you can choose where you want to save a macro when you record it: Personal Macro Workbook, New Workbook, This Workbook. I recommend storing macros related to a particular workbook in This Workbook.

The Personal Macro Workbook (Personal.xls) is not an open workbook; it is created if you choose to save the recording in the Personal Macro Workbook. This workbook is used to save a macro in a workbook that will open automatically when you start Excel, thereby enabling you to use the macro. After Excel is started, the workbook is hidden. If you want to display it, select Unhide from the Window menu.

It is not recommended you use the personal workbook for every macro you save. Save only those macros that will assist you in general tasks—not in tasks that are performed in a specific sheet or workbook.

After you select the location where you want to store the macro, click OK. Record your macro. When you are finished recording the macro, click the Stop button on the Stop Recording dialog box.

Running a Macro

If you assigned a shortcut key to your macro, you can play it by pressing the key combination. Macros can also be assigned to toolbar buttons, forms controls, or you can run them from the Visual Basic toolbar.

Creating a Macro Button

If you want to create a macro to assist in your Excel work, store the macro in a personal workbook and attach it to a toolbar button:

1. Right-click on a toolbar and select Customize.

2. Select the Commands tab (see Figure 1.5).

Figure 1.5

Use Customize to insert a Macro button into a toolbar.

Figure 1.5

Use Customize to insert a Macro button into a toolbar.

3. From Categories, select Macros.

4. Select the Custom button (with the yellow smiley face) and drag it onto a toolbar.

5. Right-click on the icon (do not close the Customize dialog box).

6. Select Assign Macro, select the macro, and click OK.

7. Close the Customize dialog box.

Assigning a Macro to a Form Control

If you want to create a macro specific to a workbook, store the macro to the workbook and attach it to a form control on the sheet, as shown in Figure 1.6.

Figure 1.6

Assigning a macro to a form control is appropriate for macros stored in the same workbook as the control.

Figure 1.6

Assigning a macro to a form control is appropriate for macros stored in the same workbook as the control.

Follow these steps to attach a macro to a form control on the sheet:

1. Display the Forms toolbar by selecting View, Toolbars, Forms.

2. Click the Button image.

3. Draw a button on the sheet by clicking and holding the left mouse button while drawing a box shape. Release the button when you are finished.

4. Select a macro from the Assign Macro dialog box and click OK.

5. Click the button to run the macro.

Understanding the Visual Basic Editor (VBE)

Figure 1.7 shows an example of the typical VBE screen. You can see three windows: Project Explorer, the Properties window, and the Programming window. Don't worry if your window doesn't look exactly like this; as we review the editor, I'll show you how to display the windows you'll need.

Figure 1.7

The VBE window.

Figure 1.7 shows an example of the typical VBE screen. You can see three windows: Project Explorer, the Properties window, and the Programming window. Don't worry if your window doesn't look exactly like this; as we review the editor, I'll show you how to display the windows you'll need.

VBE Settings

Several settings in the VBE enable you to customize it as you want. I'm going to go over only the ones that can help you with your programming.

Customizing VBE Options Settings

Under Tools, Options, Editor, there are several useful settings. All settings except for one are set correctly by default. The remaining setting requires some consideration on your part. This setting is Require Variable Declaration. By default, Excel doesn't require you to declare variables. Bill prefers this setting. It can save you time in creating a program. Tracy prefers changing this setting to require variable declaration. This forces the compiler to stop if it finds a variable that it does not recognize. This cuts down on misspelled variable names. It is a matter of your personal preference if you turn this on or keep it off.

Enabling Digital Signatures

If you're like me, you write a lot of personal macros. After a while, it gets tiresome having to approve the enabling of macros you wrote. That's where digital signatures come in.

The Project Explorer

The Project Explorer lists any open workbooks and add-ins that are loaded. If you click the + icon next to the VBA Project, you will see that there is a folder with Microsoft Excel Objects. There can also be folders for Forms, Class Modules, and (standard) Modules. Each folder includes one or more individual components.

Right-clicking on a component and selecting View Code, or just double-clicking on the components, brings up any code in the Programming Window (except for UserForms, where double-clicking displays the UserForm in Design View).

¿„ To display this window, select View, Project Explorer from the menu, press Ctrl+R, or click the Project Explorer icon on the toolbar.

The Project Explorer pane is shown in Figure 1.8. This pane can show Microsoft Excel Objects, Userforms, Modules, and/or Class Modules.

Figure 1.8

The Project Explorer,dis-playing different types of modules.

Project - VBAProjcct o Jäl

■ ; YBAPtoJect (MdcroTolmportliwoL

hi & Microsoft Excel Objects B[] Sheet 1 (SheetJ) ® ThlsWorkbook d <±* ModJes

Module 1

& VfirtProjecl (KeporlMatro.Hls)

Id {hi Microsoft Excel Objects ■[] Sheet 1 (Sheet!) ■] Sheet2 (Data) ■] sheets (Report) ® ThlsWorfcbock a ¿3 Forms

Module 1 Module? 3 Class Modules CIsReglon OsStyle

To insert a module, right-click your project, select Insert, and then select the type of module you want.

Microsoft Excel Objects

By default, a project consists of sheet modules for each sheet in the workbook and a single ThisWorkbook module. Code specific to a sheet, such as controls or sheet events, is placed on the corresponding sheet. Workbook events are placed in the ThisWorkbook module. We'll learn more about events in Chapter 8, "Event Programming."


Excel allows you to design your own forms to interact with the user. We'll learn more about these forms in Chapter 9, "UserForms—An Introduction."


When you record a macro, Excel automatically creates a module to place the code. It is in these types of modules that most of your code will reside.

Class Modules

Class modules are Excel's way of letting you create your own objects. Also, class modules allow pieces of code to be shared among programmers without the programmer needing to understand how it works. We'll learn more about class modules in Chapter 20, "Creating Classes, Records, and Collections."

The Properties Window

The Properties Window enables you to edit the properties of various components—sheets, workbooks, modules, or form controls. Its property list varies according to what component is selected.

jTl To display this window, select View, Properties Window from the menu, press F4, or click the Project Properties icon on the toolbar.

Was this article helpful?

0 0

Post a comment