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

More about Using Worksheet Functions

Newcomers to VBA often confuse VBA's built-in functions and Excel's workbook functions. A good rule to remember is that VBA doesn't try to reinvent the wheel. For the most part, VBA doesn't duplicate Excel worksheet functions. Bottom line If you need to use a function, first determine whether VBA has something that meets your needs. If not, check out the worksheet functions. If all else fails, you may be able to write a custom function by using VBA. The WorksheetFunction object contains the...

Worksheet function examples

In this section, I demonstrate how to use worksheet functions in your VBA expressions. Finding the maximum value in a range Here's an example showing how to use the MAX worksheet function in a VBA procedure. This procedure displays the maximum value in the range named NumberList on the active worksheet You can use the MIN function to get the smallest value in a range. And, as you might expect, you can use other worksheet functions in a similar manner. For example, you can use the LARGE function...

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

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

The Before Close event for a workbook

Here's an example of the Workbook_BeforeClose event-handler procedure, which is automatically executed immediately before the workbook is closed. This procedure is located in the Code window for a ThisWorkbook object Sub Workbook_BeforeClose Cancel As Boolean Would you like to make a backup of this file FName F BACKUP amp ThisWorkbook.Name This routine uses a message box to ask the user whether he would like to make a backup copy of the workbook. If the answer is yes, the code uses the...

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

Multi Page control

Multiseiten Vba Punkte Excel

A MultiPage control lets you create tabbed dialog boxes, like the Format Cells Dialog the one that appears when you press Ctrl 1 . Figure 17-11 shows an example of a custom dialog box that uses a MultiPage control. This particular control has three pages, or tabs. Use a MultiPage control to create a tabbed dialog box. Use a MultiPage control to create a tabbed dialog box. Descriptions of the most useful MultiPage control properties follow 1 Style Determines the appearance of the control. The...

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

Selecting a range

In some cases, you may want the user to select a range while a dialog box is displayed. An example of this choice occurs in the second step of the Excel Chart Wizard. The Chart Wizard guesses the range to be charted, but the user is free to change it from the dialog box. To allow a range selection in your dialog box, add a RefEdit control. The following example displays a dialog box with the current region's range address displayed in a RefEdit control, as shown in Figure 18-6. The current...

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

Text Box control

A TextBox control lets the user enter text. Figure 17-16 shows a dialog box with two TextBox controls. The following is a description of the most useful TextBox control properties AutoSize If True, the control adjusts its size automatically, depending on the amount of text. ControlSource The address of a cell that contains the text in the TextBox. IntegralHeight If True, the TextBox height adjusts automatically to display full lines of text when the list is scrolled vertically. If False, 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...

Aligning and spacing controls

Image Control Excel Vba

The Format menu in the VBE window provides several commands to help you precisely align and space the controls in a dialog box. Before you use these commands, select the controls you want to work with. These commands work just as you might expect, so I don't explain them here. Figure 17-18 shows a dialog box with several CheckBox controls about to be aligned. Use the FormatO Align command to change the alignment of UserForm controls. Use the FormatO Align command to change the alignment of...

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