A function with an optional argument

Many Excel built-in worksheet functions use optional arguments. An example is the LEFT function, which returns characters from the left side of a string. Its official syntax follows The first argument is required, but the second is optional. If you omit the optional argument, Excel assumes a value of 1. Therefore, the following formulas return the same result The custom functions you develop in VBA also can have optional arguments. You specify an optional argument by preceding the argument's...

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

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

Handling Errors Another

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

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

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.

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

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

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

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

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

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

The Interior property

Here's another example of a property that returns an object. A Range object's Interior property returns an Interior object strange name, but that's what it's called . This type of object referencing works the same way as the Font property which I describe in the preceding section . For example, the following statement changes the Color property of the Interior object contained in the Range object Range A1 .Interior.Color 8421504 In other words, this statement changes the cell's background to...

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

Returning a sorted list

Suppose that you have a list of names you want to show in sorted order in another range of cells. Wouldn't it be nice to have a worksheet function do that for you The custom function in this section does just that It takes a single-column range of cells as its argument, and then returns an array of those cells sorted. Figure 20-4 shows how it works. Range A2 A13 contains some names. Range C2 C13 contains this multicell array formula Remember, the formula must be entered by pressing Ctrl Shift...

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

Adding the Option Buttons

In this section, you add three OptionButtons to the dialog box. Before adding the OptionButtons, you add a Frame object that contains the OptionButtons. The Frame isn't necessary, but it makes the dialog box look better. 1. In the toolbox, click the Frame tool and drag in the dialog box. This step creates a frame to hold the options buttons. 2. Use the Properties window to change the frame's caption to Options. 3. In the Toolbox, click the OptionButton tool and drag in the dialog box within the...

Activate and deactivate events in a sheet

Excel can detect when a particular sheet is activated or deactivated and execute a macro when either of these events occurs. These event-handler procedures go in the Code window for a Sheet object. You can quickly access a sheet's code window by right-clicking on the sheet's tab and selecting View Code. The following example shows a simple procedure that is executed whenever a particular sheet is activated. This code simply pops up a message box that displays the name of the active sheet MsgBox...

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

Preparing for the Big Event

What types of events am I talking about here Good question. An event is basically something that happens in Excel. Following are a few examples of the types of events that Excel can deal with A worksheet is activated or deactivated. Data is entered into a cell or the cell is An object, such as a button, is clicked. A particular key or key combination is Most Excel programmers never need to worry about most of the events in this list. You should, however, at least know that these events exist...

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

The Has Formula property

The HasFormula property which is read-only returns True if the single-cellrange contains a formula. It returns False if the cell does not have a formula. If the range consists of more than one cell, VBA returns True only if all cells in the range contain a formula, or False if all cells in the range don't have a formula. The property returns a Null if there is a mixture of formulas and nonformulas. Be careful with the type of variables you use to maintain the results returned by the HasFormula...

Understanding Macro Security

Macro security is a key feature in Excel 2007. The reason is that VBA is a powerful language so powerful that even a simple macro can do serious damage to your computer. A macro can delete files, send information to other computers, and even destroy Windows so that your system can't even be started. The macro security features in Excel 2007 were created to help prevent these types of problems. Figure 2-6 shows the Macro Settings section of the Trust Center dialog box. To display this dialog...

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