Working with Ranges

Most of your VBA programming probably involves worksheet ranges. (For a refresher course on Range objects, refer to Chapter 8.) When you work with Range objects, keep the following points in mind 11 Your VBA doesn't need to select a range to work with it. 1 If your code does select a range, its worksheet must be active. 1 The macro recorder doesn't always generate the most efficient code. Often, you can create your macro by using the recorder and then edit the code to make it more efficient. 1...

Changing Excel Settings

Some of the most useful macros are simple procedures that change one or more of Excel's settings. For example, simply changing the recalculation mode from automatic to manual requires numerous steps. You can save yourself some keystrokes and menu choices (not to mention time) by creating a macro that automates this task. This section presents two examples that show you how to change settings in Excel. You can apply the general principles demonstrated by these examples to other operations that...

What Is the Visual Basic Editor

The Visual Basic Editor is a separate application where you write and edit your VBA macros. It works seamlessly with Excel. By seamlessly, I mean that Excel takes care of opening the VBE when you need it. You can't run the VBE separately Excel must be running in order for the VBE to run. The quickest way to activate the VBE is to press Alt F11 when Excel is active. To return to Excel, press Alt F11 again. You can also activate the VBE by using the DeveloperOCodeOVisual Basic command. If you...

Creating a Custom Toolbar

Custom toolbars are not as versatile in Excel 2007 as they are in Excel 2003 and before because they always appear in the Add-InsOCustom Toolbars tab and they can't be moved . But custom toolbars are still a viable option if you're willing to put up with the limitations. For this reason, I include a simple example of creating a custom toolbar. The code that follows creates a new toolbar with two buttons that is displayed in the Add-ins tab in the Custom Toolbars group of the Ribbon Set cBar...

Executing Sub procedures

Although you may not know much about developing Sub procedures at this point, I'm going to jump ahead a bit and discuss how to execute these procedures. This is important because a Sub procedure is worthless unless you know how to execute it. By the way, executing a Sub procedure means the same thing as running or calling a Sub procedure. You can use whatever terminology you like. You can execute a VBA Sub in many ways that's one reason you can do so many useful things with Sub procedures....

Adding a new item to the Cell shortcut menu

In Chapter 16 I describe the Change Case utility. You can enhance that utility a bit by making it available from the Cell shortcut menu. This example is available at this book's Web site. The AddToShortcut procedure adds a new menu item to the Cellshortcut menu. Recall that Excel has two Cell shortcut menus. This procedure modifies the normal right-click menu, but not the right-click menu that appears in Page Break Preview mode. Set Bar .Caption amp Change Case When you modify a shortcut menu,...

Ref Edit control

The RefEdit control is used when you need to let the user select a range in a worksheet. Figure 17-13 shows a custom dialog box with two RefEdit controls. Its Value property holds the address of the selected range. The RefEdit control sometimes causes trouble on more complex userforms. For best results, do not place a RefEdit control inside a Frame or MultiPage control.

Testing the macro

How Uppercase Excel 2007

Finally, you need to test the macro and dialog box to make sure they work properly 1. Activate a worksheet any worksheet in any workbook . 2. Select some cells that contain text. The UserForm appears. Figure 16-8 shows how it should look. 4. Make your choice and click OK. If you did everything correctly, the macro makes the specified change to the text in the selected cells. Figure 16-9 shows the worksheet after converting the text to uppercase. Figure 16-9 shows the worksheet after converting...

Handling Errors The Details

You can use the On Error statement in three ways, as shown in Table 12-1. You can use the On Error statement in three ways, as shown in Table 12-1. resumes execution at the specified line. You must include a colon after the label so that After executing this statement, VBA simply ignores all errors and resumes execution resumes its normal error-checking behavior. Use this statement after using one of the other On Error statements or when you want to remove error handling in your procedure. In...

Climbing the Object Hierarchy

The Application object contains other objects. Following is a list of some of the more useful objects contained in the Excel Application il Addin I CommandBar i Window I Workbook i WorksheetFunction Each object contained in the Application object can contain other objects. For example, the following is a list of objects that can be contained in a Workbook object In turn, each of these objects can contain still other objects. Consider a Worksheet object which is contained in a Workbook object,...

Communicating with Your Users

A-b YE.1AA, employees otten use Excel's The five chapters in this part show you how to develop custom dialog boxes also known as UserForms . This VBA feature is fairly easy to use, after you get a few basic concepts under your belt. And, if you're like me, you may actually enjoy creating dialog boxes.

Using Worksheet Functions in VBA

Although VBA offers a decent assortment of built-in functions, you might not always find exactly what you need. Fortunately, you can also use most of Excel's worksheet functions in your VBA procedures. The only worksheet functions that you cannot use are those that have an equivalent VBA function. VBA makes Excel's worksheet functions available through the WorksheetFunction object, which is contained in the Application object. Remember, the Application object is Excel. Therefore, any statement...

Excel Ribbon Customization

Excel 2007 offers its very own way of customizing the Ribbon, which is far more complex than manipulating the CommandBars collection. The new approach involves writing XML code in a text editor, copying that XML file into the workbook file all outside of Excel , editing a bunch of XML files which also are stashed away inside the new Excel file format, which is really nothing more than a zipped container of individual but related files , and then writing VBA procedures to handle the clicking of...

Working with Charts

Charts are packed with different objects, so manipulating charts with VBA can be a bit of a challenge. The challenge increases with Excel 2007, because Microsoft decided to omit recording macros for all the new and fancy formatting stuff. To get a feel for this, turn on the macro recorder, create a chart, and perform some routine chart-editing tasks. You may be surprised by the amount of code Excel generates. And at the same time, you'll be disappointed about how much does not get recorded....

An Addin Example

In this section, I discuss the basic steps involved in creating a useful add-in. The example is based on the Change Case text conversion utility that I describe in Chapter 16. The XLSM version of this example is available at this book's Web site. You can create an add-in from this workbook. The workbook consists of one blank worksheet, a VBA module, and a UserForm. In Chapter 19, I already added code to the workbook that creates a new menu item on the Cell right-click shortcut menu. The...

Get OpenFilename example

The fileFilter argument determines what appears in the dialog box's Files of Type drop-down list. This argument consists of pairs of file filter strings followed by the wild card file filter specification, with commas separating each part and pair. If omitted, this argument defaults to the following Notice that this string consists of two parts The first part of this string is the text displayed in the Files of Type dropdown list. The second part determines which files the dialog box displays....

Introduction

Reetings, prospective Excel programmer . . . Thanks for buying my book. I think you'll find that it offers a fast, enjoyable way to discover the ins and outs of Microsoft Excel programming. Even if you don't have the foggiest idea of what programming is all about, this book can help you make Excel jump through hoops in no time well, it will take some time . Unlike most programming books, this one is written in plain English, and even normal people can understand it. Even better, it's filled...

Auto List Members option

If the Auto List Members option is set, VBE provides some help when you're entering your VBA code. It displays a list that would logically complete the statement you're typing. I like this option and always keep it turned on. Figure 3-6 shows an example which will make lots more sense when you start writing VBA code .

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 extension rather than the normal XLSX extension. For example, if you try to 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 2-5. You need to click No, and then...

Debugging Techniques

In this section, I discuss the four most common methods for debugging Excel VBA code 1 Inserting MsgBox functions at various locations in your code 1 Inserting Debug.Print statements 1 Using the Excel built-in debugging tools Perhaps the most straightforward debugging technique is simply taking a close look at your code to see whether you can find the problem. If you're lucky, the error jumps right out and you can quickly correct it. Notice I said, If you're lucky. That's because often you...

An Intentional Error

Sometimes you can use an error to your advantage. For example, suppose you have a macro that works only if a particular workbook is open. How can you determine whether that workbook is open Perhaps the best solution is to write a general-purpose function that accepts one argument a workbook name and returns True if the workbook is open, False if it's not. This function takes advantage of the fact that Excel generates an error if you refer to a workbook that is not open. For example, the...

The Msg Box Function

You're probably already familiar with the VBA MsgBox function I use it quite a bit in the examples throughout this book. The MsgBox function, which accepts the arguments shown in Table 15-1, is handy for displaying information and getting simple user input. Here's a simplified version of the syntax for the MsgBox function Here's a simplified version of the syntax for the MsgBox function Supplies the text Excel displays in the message box Specifies which buttons and what icon appear in the...

The Before DoubleClick event

You can set up a VBA procedure to be executed when the user double-clicks a cell. In the following example which is stored in the Code window for a Sheet object , double-clicking a cell makes the cell bold if it's not bold or not bold if it is bold Private Sub Worksheet_BeforeDoubleClick _ ByVal Target As Excel.Range, Cancel As Boolean Target.Font.Bold Not Target.Font.Bold Cancel True End Sub The Worksheet_BeforeDoubleClick procedure has two arguments Target and Cancel. Target represents the...

The IfThen structure

Okay, I'll say it If-Then is VBA's most important control structure. You'll probably use this command on a daily basis at least I do . As in many other aspects of life, effective decision making is the key to success in writing programs. If this book has the effect I intend, you'll soon share my philosophy that a successful Excel application boils down to making decisions and acting upon them. The If-Then structure has this basic syntax If condition Then statements Else elsestatements Use the...

How VBA Works with Excel

Rich Tennant

No, that's not the icon or Excel, it's the icon tor Excuse, the database o reasons vihy you haven't learned the other programs in Oiiice 7 he next four chapters provide the necessary foundation for discovering the ins and outs of VBA. You find out about modules the sheets that store your VBA code and are introduced to the Excel object model something you won't want to miss . You also discover the difference between subroutines and functions, and you get a crash course in the Excel macro...

VBA function examples

In this section, I present a few examples of using VBA functions in code. In many of these examples, I use the MsgBox function to display a value in a message box. Yes, MsgBox is a VBA function a rather unusual one, but a function nonetheless. This useful function displays a message in a pop-up dialog box. For more details about the MsgBox function, see Chapter 15. A workbook that contains all the examples is available at this book's Web site. The first example uses VBA's Date function to...