Adding eventhandler procedures

In this section, I explain how to write the procedures that handle the events that occur when the dialog box is displayed. 1. Press Alt+F11 to activate the VBE. 2. Make sure the UserForm is displayed double-click the Close button. The VBE activates the Code window for the UserForm and provides an empty procedure named CloseButton_Click. 3. Modify the procedure as follows Private Sub CloseButton_Click() Unload UserForml This procedure, which is executed when the user clicks the Close button,...

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

An Erroneous Example

To get things started, I developed a short VBA macro. Activate the VBE, insert a module, and enter the following code Sub EnterSquareRoot Dim Num As Double ' Prompt for a value Num lnputBox Enter a value As shown in Figure 12-1, this procedure asks the user for a value. It then enters the square root of that value into the active cell. The InputBox function displays a dialog box that asks the user for a value. The InputBox function displays a dialog box that asks the user for a value. You can...

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

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

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.

Adding controls to a User Form

When you activate a UserForm, the VBE displays the Toolbox in a floating window, as shown in Figure 16-3. You use the tools in the Toolbox to add controls to your UserForm. Just click the desired control in the Toolbox and drag it into the dialog box to create the control. After you add a control, you can move and resize it by using standard techniques. You use the tools in the Toolbox to add controls to a UserForm. You use the tools in the Toolbox to add controls to a UserForm. Table 16-1...

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

Looping through a Collection

VBA supports yet another type of looping looping through each object in a collection of objects. Recall that a collection consists of a number of the same type of object. For example, each workbook has a collection of worksheets the Worksheets collection , and Excel has a collection of all open workbooks the Workbooks collection . When you need to loop through each object in a collection, use the For Each-Next structure. The syntax is For Each element In collection statements Exit For...

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

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 Input Box Function

The VBA InputBox function is useful for obtaining a single value from the user. This is a good alternative to developing a UserForm when you need to get only one value. Here's a simplified version of the syntax for the InputBox function InputBox prompt , title , default The InputBox function accepts the arguments listed in Table 15-4. Supplies the text displayed in the input box Specifies the text displayed in the input box's title bar optional Defines the default value optional Here's an...

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