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...
Executing the Sub procedure directly
The quickest way to execute this procedure is by doing so directly from the VBA module in which you defined it. Follow these steps 1. Activate the VBE and select the VBA module that contains the procedure. 2. Move the cursor anywhere in the procedure's code. 3. Press F5 or choose RunORun Sub UserForm . 4. Respond to the input box and click OK. The procedure displays the cube root of the number you entered. You can't use the RunORun Sub UserForm command to execute a Sub procedure that uses...
Dialog Box Controls The Details
In the following sections, I introduce you to each type of control you can use in custom dialog boxes and discuss some of the more useful properties. I don't discuss every property for every control because that would require a book that's about four times as thick and it would be a very boring book . The Help system for controls and properties is thorough. To find complete details for a particular property, select the property in the Properties window and press F1. Figure 17-4 shows the online...
Handling Errors Another Way
How can you identify and handle every possible error The answer is that often you can't. Fortunately, VBA provides another way to deal with errors. Revisiting the EnterSquareRoot procedure Examine the following code. I modified the routine from the previous section by adding an On Error statement to trap all errors and then checking to see whether the InputBox was cancelled. Sub EnterSquareRoot5 Dim Num As Variant Dim Msg As String ' Set up error handling On Error GoTo BadEntry Num InputBox...
DoWhite loop
VBA supports another type of looping structure known as a Do-While loop. Unlike a For-Next loop, a Do-While loop continues until a specified condition is met. Here's the Do-While loop syntax Do While condition statements Exit Do statements The following example uses a Do-While loop. This routine uses the active cell as a starting point and then travels down the column, multiplying each cell's value by 2. The loop continues until the routine encounters an empty cell. Do While ActiveCell.Value lt...
DoUntil loop
The Do-Until loop structure is similar to the Do-While structure. The two structures differ in their handling of the tested condition. A program continues to execute a Do-While loop while the condition remains true. In a Do-Until loop, the program executes the loop until the condition is true. Do Until condition statements Exit Do statements The following example is the same one presented for the Do-While loop but recoded to use a Do-Until loop ActiveCell.Value ActiveCell.Value 2...
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...
Adding descriptive information
I recommend entering a description of your add-in, but this isn't required. 1. Activate the change case.xlsm workbook. 2. Choose OfficeOPrepareOProperties command. Excel displays the Document Properties panel above the Formula bar. See Figure 21-3. 3. Enter a title for the add-in in the Title field. This text appears in the list of add-ins in the Add-Ins dialog box. For this example, enter Change Case. 4. In the Comments field, enter a description. This information appears at the bottom of the...
The Copy and Paste methods
You can perform copy and paste operations in VBA by using the Copy and Paste methods. The Copy method is applicable to the Range object, but the Paste method is applicable to the Worksheet object. This short macro copies range A1 A12 and pastes it to the range beginning at cell C1 Range A1 A12 .Select Selection.Copy Range C1 .Select ActiveSheet.Paste End Sub Notice that in the preceding example, which the macro recorder generated, the ActiveSheet object is used with the Paste method. This is a...
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...
Selecting multiple files
If the MultiSelect argument for the GetOpenFilename method is True, the user can select multiple files in the dialog box. In this case, the GetOpenFilename method returns an array of filenames. Your code must loop through the array to identify each selected filename, as the following example demonstrates Display full path and name of the files Msg You selected amp vbNewLine For I LBound FileNames To UBound FileNames Msg Msg amp FileNames i amp vbNewLine MsgBox No files were selected. Figure...
Using a UserForm as a progress indicator
One of the most common Excel programming questions I hear is How can I make a UserForm display the progress of a lengthy macro Answer Use a UserForm to create an attractive progress indicator, as shown in Figure 18-9. Such a use of dialog boxes does, however, require a few tricks which I'm about to show you. This UserForm functions as a progress indicator for a lengthy macro. This UserForm functions as a progress indicator for a lengthy macro. Creating the progress indicator dialog box The...
Dynamic arrays
You can also create dynamic arrays. A dynamic array doesn't have a preset number of elements. Declare a dynamic array with a blank set of parentheses Before you can use this array, you must use the ReDim statement to tell VBA how many elements the array has. Usually, the number of elements in the array is determined while your code is running. You can use the ReDim statement any number of times, changing the array's size as often as you need. The following example demonstrates how to change the...
Assignment statement examples
In the assignment statement examples that follow, the expressions are to the right of the equal sign x y 2 z 2 HouseCost 375000 FileOpen True Range TheYear .Value 2 007 Expressions can be as complex as you need them to be. Use the line continuation character a space followed by an underscore to make lengthy expressions easier to read. Often, expressions use functions VBA's built-in functions, Excel's worksheet functions, or functions that you develop with VBA. I discuss functions in Chapter 9.
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....
The BeforeRightClick event
The BeforeRightClick event is similar to the BeforeDoubleClick event, except that it consists of right-clicking a cell. The following procedure checks to see whether the cell that was right-clicked contains a numeric value. If so, the code displays the Format Number dialog box and sets the Cancel argument to True avoiding the normal shortcut menu display . If the cell does not contain a numeric value, nothing special happens the shortcut menu is displayed as usual. Private Sub...
Auto Syntax Check option
The Auto Syntax Check setting determines whether the VBE pops up a dialog box if it discovers a syntax error while you're entering your VBA code. The dialog box tells roughly what the problem is. If you don't choose this setting, VBE flags syntax errors by displaying them in a different color from the rest of the code, and you don't have to deal with any dialog boxes popping up on your screen. I usually keep this setting turned off because I find the dialog boxes annoying and I can usually...
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,...
RefEdit 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.
The Select method
Use the Select method to select a range of cells. The following statement selects a range on the active worksheet Before selecting a range, make sure that you've activated the range's worksheet otherwise, you get an error or the wrong range is selected. For example, if Sheet1 contains the range you want to select, use the following statements to select the range Sheets Sheet1 .Activate Range A1 C12 .Select Contrary to what you may expect, the following statement generates an error. In other...
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...
Figure Tyg
Listing all top-level controls in all shortcut menus. 55 Format Data Series 55 Format Axis 57 Format Legend Entry 53 Formula Bar 59 PivotTable Context Menu 50 Query amp Selected Object amp Selected Object amp Selected Object amp Selected Object amp Selected Object amp Selected Object amp Selected Object amp Selected Object Cu amp t amp Copy Cu amp t _ Fill aseries amp Options Sheet2 amp Copy amp Copy amp Copy amp Copy amp Copy amp Copy amp Delete amp Copy Save amp As aopen amp Copy Here amp...
Testing the macro 1
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 BeforeClose 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...
A function with a range argument
Using a worksheet range as an argument is not at all tricky Excel takes care of the behind-the-scenes details. Assume that you want to calculate the average of the five largest values in a range named Data. Excel doesn't have a function that can do this, so you would probably write a formula LARGE Data,4 LARGE Data,5 5 This formula uses Excel's LARGE function, which returns the nth largest value in a range. The formula adds the five largest values in the range named Data and then divides the...
Using Assignment Statements
An assignment statement is a VBA statement that assigns the result of an expression to a variable or an object. Excel's Help system defines the term expression as . . . a combination of keywords, operators, variables, and constants that yields a string, number, or object. An expression can be used to perform a calculation, manipulate characters, or test data. I couldn't have said it better myself. Much of your work in VBA involves developing and debugging expressions If you know how to create...
Life of Variables
The scope of a variable not only determines where that variable may be used, it also affects under which circumstances the variable is removed from memory. You can purge all variables from memory by using three methods l Click the Reset toolbar button the little blue square button on the Standard toolbar in the VBE . I Click End when a runtime error message shows up. I Include an End statement anywhere in your code. Otherwise, only procedure level variables will be removed from memory when the...
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
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...
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...
The Offset property
The Offset property provides another handy means for referring to ranges. This property, which operates on a Range object and returns another Range object, lets you refer to a cell that is a particular number of rows and columns away from another cell. Like the Cells property, the Offset property takes two arguments. The first argument represents the number of rows to offset the second represents the number of columns to offset. The following expression refers to a cell one row below cell A1...
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...
Argument descriptions
When you access a built-in function from the Insert Function dialog box, the Function Arguments dialog box displays a description of each argument. See Figure 20-7. Unfortunately, there is no direct way to provide such descriptions for custom functions. You can, however, make your argument names descriptive which is a good idea. The Function Arguments dialog box displays function argument descriptions for built-in functions only. a loan based on constant payments and a constant interest rate,...
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.
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...
Using VBA and Worksheet Functions
Using functions to make your VBA expressions more powerful Using the VBA built-in functions Using Excel worksheet functions in your VBA code Writing custom functions n previous chapters, I allude to the fact that you can use functions in your VBA expressions. There are two flavors of functions those built in to VBA and those built in to Excel. I provide a full explanation in this chapter. Functions can make your VBA code perform some powerful feats, with little or no programming effort...
Returning an array of month names
I'll start out with a simple example. The MonthNames functions returns a 12-element array of you guessed it, month names. MonthNames Array January, February, March, April, May, June, July, August, _ September, October, November, December End Function To use the MonthNames function in a worksheet, you must enter it as a 12-cell array formula. For example, select range A1 L1 and enter MonthNames . Then use Ctrl Shift Enter to enter the array formula in all 12 selected cells. Figure 20-3 shows the...
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...
The Column and Row properties
The Column property returns the column number of a single-cell range the Row property returns the row number of a single-cell range. Both are readonly properties. For example, the following statement displays 6 because the cell is in the sixth column MsgBox The next expression displays 3 because cell F3 is in the third row MsgBox If the Range object consists of more than one cell, the Column property returns the column number of the first column in the range, and the Row property returns the...
A GetOpenFilename 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....
OptionButton control
OptionButtons are useful when the user needs to select from a small number of items. OptionButtons are always used in groups of at least two. Figure 17-12 shows two sets of OptionButtons Report Destination and Layout . One set uses graphics images set with the Picture property . Two sets of Option Button controls, each contained in a Frame control. Two sets of Option Button controls, each contained in a Frame control. The following is a description of the most useful OptionButton control...
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...
Moving and resizing controls
After you place a control in a dialog box, you can move it and resize it by using standard mouse techniques. Or for precise control, you can use the Properties window to enter a value for the control's Height, Width, Left, or Top property. You can select multiple controls by Ctrl clicking the controls. Or you can click and drag to lasso a group of controls. When multiple controls are selected, the Properties window displays only the properties common to all selected controls. You can change...
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...