Displaying Excels Builtin Dialog Boxes

You can write VBA code that performs the equivalent of selecting an Excel menu command and making choices in the resulting dialog box although Excel doesn't actually display the dialog box. For example, the following statement has the same effect as choosing the HomeOEditingOFind & SelectOGo To command, specifying a range named InputRange, and clicking OK Application.Goto Reference InputRange When you execute this statement, the Go To dialog box does not appear. This is almost always what...

For Next loops

The simplest type of loop is a For-Next loop. Here's the syntax for this structure For counter start To end Step stepval statements Exit For statements Next counter The looping is controlled by a counter variable, which starts at one value and stops at another value. The statements between the For statement and the Next statement are the statements that get repeated in the loop. To see how this works, keep reading. The following example shows a For-Next loop that doesn't use the optional Step...

In This Chapter

Introducing the concept of objects Finding out about the Excel object hierarchy Understanding object collections Referring to specific objects in your VBA code Accessing or changing an object's properties Performing actions with an object's methods veryone is familiar with the word object. Well, folks, forget the definition you think you know. In the world of programming, the word object has a different meaning. You often see it used as part of the expression object-oriented programming, or...

Working with a Code Window

As you become proficient with VBA, you spend lots of time working in Code windows. Macros that you record are stored in a module, and you can type VBA code directly into a VBA module. Just to make sure you're straight with the concept, remember that a VBA module holds your VBA code, and a VBA module is displayed in a Code window. If you have several projects open, the VBE may have lots of Code windows at any given time. Figure 3-3 shows an example of what I mean. Code windows are much like...

About the Debugger

In this section, I discuss the gory details of using the Excel debugging tools. These tools are much more powerful than the techniques I discuss in the previous section. But along with power comes responsibility. Using the debugging tools takes a bit of setup work. Earlier in this chapter, I discuss using MsgBox functions in your code to monitor the values of certain variables. Displaying a message box essentially halts your code in mid-execution, and clicking the OK button resumes execution....

Contents at a Glance

Part I Introducing Chapter 1 What Is Chapter 2 Jumping Right Part II How VBA Works with Chapter 3 Working In the Visual Basic Chapter 4 Introducing the Excel Object Chapter 5 VBA Sub and Function Chapter 6 Using the Excel Macro Part III Programming Chapter 7 Essential VBA Language Chapter 8 Working with Range Chapter 9 Using VBA and Worksheet Chapter 10 Controlling Program Flow and Making Chapter 11 Automatic Procedures and Chapter 12 Error-Handling Chapter 13 Bug Extermination Chapter 14 VBA...

Using the Object Browser

The VBE includes another tool, known as the Object Browser. As the name implies, this tool lets you browse through the objects available to you. To access the Object Browser, press F2 when the VBE is active (or choose ViewOObject Browser). You see a window like the one shown in Figure 4-4. The drop-down list at the top contains a list of all currently available object libraries. Figure 4-4 shows All Libraries. If you want to browse through Excel's objects, select Excel from the drop-down list....

Recording in relative mode

In some cases you want your recorded macro to work with cell locations in a relative manner. You may want the macro to start entering the month names in the active cell. In such a case, you need to use relative recording. You can change the manner in which Excel records your actions by clicking the Use Relative References button on the Code group on the Developer tab. This button is a toggle button. When the button appears in a pressed state, the recording mode is relative. When the button...

First Things First

Before you can call yourself an Excel programmer, you must go through the initiation rites. That means you need to make a small change so Excel will display a new tab at the top of the screen Developer. When you click the Developer tab, the Ribbon displays information that is of interest to programmers (that's you ). Figure 2-1 shows how the Ribbon looks when the Developer tab is selected. The Developer tab is normally hidden, but it's easy to unhide. The Developer tab is normally hidden, but...

Executing a macro by using a shortcut key

Another way to execute a macro is to press its shortcut key. But before you can use this method, you have to set things up. Specifically, you must assign a shortcut key to the macro. You have the opportunity to assign a shortcut key in the Record Macro dialog box when you begin recording a macro. If you create the procedure without using the macro recorder, you can assign a shortcut key (or change an existing shortcut key) by using the following procedure 1. Choose DeveloperOCodeOMacros. 2....

Writing an Event Handler Procedure

The VBE helps you out when you're ready to write an event-handler procedure it displays a list of all events that Excel can recognize. Figure 11-2 shows a Code window for the ThisWorkbook object (the code window is maximized to fill the entire code window area). To display this empty Code window, double-click the ThisWorkbook object in the Project window. This Code window has two drop-down lists at the top. An empty Code window for the This Workbook object. An empty Code window for the This...

Table of Contents

Is This the Right So You Want to Be a Why What I Assume about Obligatory Typographical Conventions Check Your Security How This Book Is Part I Introducing Part II How VBA Works with Part III Programming Part IV Communicating with Your Part V Putting It All Part VI The Part of Marginal Get the Sample Now Part 1 Introducing Chapter 1 What Is VBA Okay, So What Is What Can You Do with Inserting a bunch of text Automating a task you perform Automating repetitive Creating a custom Creating a custom...

Is It Live or Is It VBA

Recording a macro is sort of like using a tape recorder. Turn it on, do your thing, and then turn it off. This analogy, however, goes only so far. Table 6-1 compares tape recording with macro recording. Tape Recording versus Macro Recording Choose DeveloperOCodeO Macros (or other methods). Yes, if you have the proper equipment. Yes, if you have a second tape recorder. Yes (no additional equipment required). Depends on the situation and the equipment quality. Depends on how you set things up...

Adding eventhandler procedures

Here's how to add an event-handler procedure for the Cancel and OK buttons 1. Double-click the Cancel button. VBE activates the Code window for the UserForm and inserts an empty procedure The procedure named CancelButton_Click is executed when the Cancel button is clicked, but only when the dialog box is displayed. In other words, clicking the Cancel button when you're designing the dialog box won't execute the procedure. Because the Cancel button's Cancel property is set to True, pressing Esc...

Navigating through the hierarchy

If you want to work with the Application object, it's easy you start by typing Application. Every other object in Excel's object model is under the Application object. You get to these objects by moving down the hierarchy and connecting each object on your way with the dot (.) operator. To get to the Workbook object named Bookl.xlsx, start with the Application object and navigate down to the Workbooks collection object. To navigate further to a specific worksheet, add a dot operator and access...

Wrapping Your Mind around Collections

Collections are another key concept in VBA programming. A collection is a group of objects of the same type. And to add to the confusion, a collection is itself an object. Here are a few examples of commonly used collections 1 Workbooks A collection of all currently open Workbook objects 1 Worksheets A collection of all Worksheet objects contained in a particular Workbook object 1 Charts A collection of all Chart objects (chart sheets) contained in a particular Workbook object 1 Sheets A...

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

Using Custom Functions

I've covered VBA functions and Excel worksheet functions. The third category of functions you can use in your VBA procedures is custom functions. A custom function also known as User Defined Function, UDF is one you develop yourself by using what else VBA. To use a custom function, you must define it in the workbook in which you use it. Here's an example of defining a simple Function procedure and then using it in a VBA Sub procedure MultiplyTwo num1 num2 End Function Dim n1 As Double, n2 As...

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

Executing the procedure from the Macro dialog box

Most of the time, you execute Sub procedures from Excel, not from the VBE. The steps below describe how to execute a macro by using Excel's Macro dialog box. Alt F11 is the express route of course you can skip this step if Excel is already active . 2. Choose DeveloperOCodeOMacros or press Alt F8 . Excel displays the dialog box shown in Figure 5-3. 4. Click Run or double-click the macro's name in the list box . The Macro dialog box lists all available Sub The Macro dialog box lists all available...

The Open event for a workbook

One of the most commonly used events is the Workbook Open event. Assume that you have a workbook that you use every day. The Workbook_Open procedure in this example is executed every time the workbook is opened. The procedure checks the day of the week if it's Friday, the code displays a reminder message for you. To create the procedure that is executed whenever the Workbook Open event occurs, follow these steps 2. Press Alt F11 to activate the VBE. 3. Locate the workbook in the Project window....

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.

Getting a Folder Name

Sometimes, you don't need to get a filename, you need to get a folder name. If that's the case, the FileDialog object is just what the doctor ordered. The following procedure displays a dialog box that allows the user to select a directory. The selected directory name or Canceled is then displayed by using the MsgBox function. With .InitialFileName Application.DefaultFilePath amp Please select a location for the backup The FileDialog object lets you specify the starting directory by specifying...

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

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.

Executing the procedure from a button or shape

Chunks Con Sus Opciones Excel 2007

You can create still another means for executing the macro by assigning the macro to a button or any other shape on a worksheet. To assign the macro to a button, follow these steps 2. Add a button from the Forms group. To display the Forms group, select DeveloperOControlsOInsert See Figure 5-5 3. Click the Button tool in the Forms group. 4. Drag in the worksheet to create the button. After you add the button to your worksheet, Excel jumps right in and displays the Assign Macro dialog box shown...

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

Get the Sample Files

This book has its very own Web site where you can download the example files discussed and view Bonus Chapters. To get these files, point your Web browser to Having the sample files will save you a lot of typing. Better yet, you can play around with them and experiment with various changes. In fact, I highly recommend playing around with these files. Experimentation is the best way to master VBA.

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

An Excursion into Versions

If you plan to develop VBA macros, you should have some understanding of Excel's history. I know you weren't expecting a history lesson when you picked up this book, but bear with me. This is important stuff. Here are all the major Excel for Windows versions that have seen the light of day, along with a few words about how they handle macros i Excel 2 The original version of Excel for Windows was called Version 2 rather than 1 so that it would correspond to the Macintosh version. Excel 2 first...

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

Recording in absolute mode

Follow these steps to record a simple macro in absolute mode. This macro simply enters three month names into a worksheet 1. Choose DeveloperOCodeORecord Macro. 2. Type Absolute as the name for this macro. 3. Click OK to begin recording. 4. Activate cell B1 and type Jan in that cell. 5. Move to cell C1 and type Feb. 6. Move to cell D1 and type Mar. 7. Click cell B1 to activate it again. 9. Press Alt F11 to activate the VBE. 10. Examine the Module1 module. When executed, this macro selects cell...

The GetSaVeAsFilename Method

The Excel GetSaveAsFilename method works just like the GetOpenFilename method, but it displays the Excel Save As dialog box rather than its Open dialog box. The GetSaveAsFilename method gets a path and filename from the user but doesn't do anything with it. fileFilter , filterIndex , title , buttonText The GetSaveAsFilename method takes Table 15-6's arguments, all of which are optional. Specifies a default filename that appears in the File Determines the types of files Excel displays in the...

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

Activate and deactivate events in a Workbook

The previous examples use events associated with a worksheet. The ThisWorkbook object also handles events that deal with sheet activation and deactivation. The following procedure, which is stored in the Code window for the ThisWorkbook object, is executed when any sheet in the workbook is activated. The code displays a message with the name of the activated sheet. Private Sub Workbook_SheetActivate ByVal Sh As Object The Workbook_SheetActivate procedure uses the Sh argument. Sh is a variable...

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

Working with constants

A variable's value may and usually does change while your procedure is executing. That's why they call it a variable. Sometimes, you need to refer to a value or string that never changes a constant. A constant is a named element whose value doesn't change. As shown in the following examples, you declare constants by using the Const statement Const ModName As String Budget Macros Public Const AppName As String Budget Application Using constants in place of hard-coded values or strings is 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 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...

Check Box control

A CheckBox control is useful for getting a binary choice yes or no, true or false, on or off, and so on. Figure 17-5 shows some examples of CheckBox controls. The following is a description of a CheckBox control's most useful properties i Accelerator A letter that lets the user change the value of the control by using the keyboard. For example, if the accelerator is A, pressing Alt A changes the value of the CheckBox control from checked to unchecked, or from unchecked to checked . i...

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

The Get OpenFilename Method

If your VBA procedure needs to prompt the user for a filename, you could use the InputBox function. An InputBox usually isn't the best tool for this job, however, because most users find it difficult to remember paths and directory names, and it's far too easy to make a typographic error. For a better solution to this problem, use the GetOpenFilename method of the Application object, which ensures that your application gets a valid filename, including its complete path. The GetOpenFilename...

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