Controlling Excel from Word
As you might expect, you can also control Excel from another application such as another programming language or a Word VBA procedure . For example, you might want to perform some calculations in Excel and return the result to a Word document. You can create any of the following Excel objects with the adjacent functions Application object Workbook object CreateObject Excel.Sheet Chart object An Excel VBA procedure created this Word document. An Excel VBA procedure created this Word document....
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 EditOGo 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 you want to happen you...
The Top Ten Questions about VBA
I created a custom VBA function. When I try to use it in a formula, the formula displays NAME . What's wrong You probably have your function code in the wrong location. VBA code for worksheet functions must be in a standard VBA module not in a module for a sheet or in ThisWorkbook. Can I use the VBA macro recorder to record all of my macros No. Normally you use it only to record simple macros or as a starting point for a more complex macro. It cannot record macros that use variables, looping,...
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...
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 toolbar. To display the Forms toolbar, right-click any toolbar and choose Forms from the shortcut menu. 3. Click the Button tool on the Forms toolbar. 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...
Controlling Word from Excel
The example in Figure 23-2 demonstrates an automation session by using Word. The MakeMemos procedure creates three customized memos in Word and then saves each memo to a separate file. The information used to create the memos is stored in a worksheet. Word automatically generates three memos based on this Excel data. Create Word Memos Using Data on This Worksheet The monthly sales data for your region is listed below. This information was obtained From the central database. Please call if you...
Identifying specific errors
All errors are not created equal. Some are serious and some are less serious. Although you may ignore errors you consider inconsequential, you must deal with other, more serious errors. In some cases, you need to identify the specific error that occurred. When an error occurs, Excel stores the error number in an Error object named Err. This object's Number property contains the error number. You can get a description of the error by using the VBA Error function. For example, the following...
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. A control can hide...
A Custom Dialog Box Example
This section's UserForm example is an enhanced version of the ChangeCase macro from the beginning of the chapter. Recall that the original version of this macro changes the text in the selected cells to uppercase. This modified version uses a custom dialog box to ask the user which type of change to make uppercase, lowercase, or proper case. This dialog box needs to obtain one piece of information from the user the type of change to make to the text. Because the user has three choices, your...
Using the MsgBox function
A common problem in many programs involves one or more variables not taking on the values you expect. In such cases, monitoring the variable s while your code runs is a helpful debugging technique. Do this by inserting An error message like this often means that your VBA code contains a bug. temporary MsgBox functions in your routine. For example, if you have a variable named CellCount, you can insert the following statement When you execute the routine, the MsgBox function displays CellCount's...
ComboBox control
A ComboBox control is similar to a ListBox control described later, in this chapter's ListBox control section . A ComboBox, however, is a drop-down box and displays only one item at a time. Another difference is that the user may be allowed to enter a value that does not appear in the list of items. Figure 17-6 shows two ComboBox controls. The following is a description of some useful ComboBox control properties i BoundColumn If the list contains multiple columns, this property determines which...
Examples of Activation Events
Another category of events consists of activating and deactivating objects specifically, sheets and windows. 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. 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...
Displaying a custom dialog box
You can display a custom dialog box by using the UserForm's Show method in a VBA procedure. The macro that displays the dialog box must be in a VBA module not in the Code window for the UserForm. The following procedure displays the dialog box named UserForm1 UserForml.Show ' Other statements can go here End Sub When Excel displays the dialog box, the macro halts until the user closes the dialog box. Then VBA executes any remaining statements in the procedure. Most of the time, you won't have...
Simplifying object references 1
As you probably already know, references to objects can become very lengthy. For example, a fully qualified reference to a Range object may look like this You can instruct Excel to not display these types of alerts while running a macro. .Range InterestRate If your macro frequently uses this range, you may want to create an object variable by using the Set command. For example, the following statement assigns this Range object to an object variable named Rate Set Rate WorkbooksCMyBook.xls _...
Is This Thing Efficient
You might think that recording a macro would generate some award-winning VBA code better than you could ever write manually. Think again. In many cases, the recorder spits out lots of extraneous garbage, and it often generates code that's less than efficient. Don't get me wrong. I'm a staunch supporter of the macro recorder. It's a great tool for helping you learn VBA. Except for simple macros, however, I've never used a recorded macro without fixing it up a bit usually quite a bit . To...
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. The Stop Recording toolbar, which consists of only two buttons, is displayed when you are recording a macro. You can change the manner in which Excel records your actions by clicking the Relative Reference button on the Stop Recording toolbar. This button is a toggle. When the...
CheckBox 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 1 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...
Adding a menu item
The example in the preceding section demonstrates how to create a new menu. The following example adds a menu item to the Excel Format menu. This menu item, when clicked, executes a macro named ToggleWordWrap. The ToggleWordWrap procedure changes the WrapText property of the selected cells. After creating the menu item, I change the Caption, OnAction, and BeginGroup properties. Setting BeginGroup to True displays a separator bar before the new menu item. Figure 20-5 shows the modified Format...
SpinButton control
The SpinButton control lets the user select a value by clicking the control, which has two arrows one to increase the value and the other to decrease the value . Figure 17-15 shows a dialog box that uses two SpinButton controls. Each control is linked to the Label control on the right by using VBA procedures . The following descriptions explain the most useful properties of a SpinButton control t Value The control's current value. t Min The control's minimum value. t Max The control's maximum...
Using other toolbar button operations
When Excel is in customization mode that is, when the Customize dialog box is displayed , you can right-click a toolbar control to display a shortcut menu of additional actions. Figure 19-6 shows the shortcut menu that appears when you right-click a button in customization mode. These commands are described in the following list. Note that some of these commands are unavailable for certain toolbar controls. 1 Reset Resets the control to its original state. 1 Delete Deletes the control. 1 Name...
Essential VBA Language Elements
Knowing when, why, and how to use comments in your code Using variables and constants Telling VBA what type of data you're using Knowing why you may need to use labels in your procedures ecause VBA is a real, live programming language, it uses many elements common to all programming languages. In this chapter, I introduce you to several of these elements comments, variables, constants, data types, arrays, and a few other goodies. If you've programmed using other languages, some of this...
The OnTime event
The OnTime event occurs when a particular time of day occurs. The following example demonstrates how to program Excel so that it beeps and then displays a message at 3 00 p.m. In this example, I use the OnTime method of the Application object. This method takes two arguments the time 0.625 or 3 00 p.m. and the code to execute when the time occurs DisplayAlarm . This procedure is quite useful if you tend to get so wrapped up in your work that you forget about meetings and appointments. Just set...
Displaying a toolbar when a worksheet is activated
Assume that you have a workbook named Budget that holds your budget information. In addition, assume that you've developed a custom toolbar named Budget Tools that you use with this workbook. The toolbar should be visible when you work on the Budget sheet otherwise, it should remain hidden and out of the way. The following procedures, which are stored in the code window for the ThisWorkbook object, display the Budget Tools toolbar when the Budget workbook is active and hide the toolbar when the...
Why Create dialog Boxes
Some of the VBA macros you create behave the same every time you execute them. For example, you may develop a macro that enters a list of your employees into a worksheet range. This macro always produces the same result and requires no additional user input. You might develop other macros, however, that behave differently under various circumstances or that offer the user options. In such cases, the macro may benefit from a custom dialog box. A custom dialog box provides a simple means for...
CurentRate
This misspelled variable, which is difficult to spot, will probably cause your routine to give incorrect results. If you use Option Explicit at the beginning of your module and declare the CurrentRate variable , Excel generates an error if it encounters a misspelled variation of that variable. To ensure that the Option Explicit statement is inserted automatically whenever you insert a new VBA module, turn on the Require Variable Definition option. You'll find it in the Editor tab of the Options...
CubeRoot
The cell displays 12, which is indeed the cube root of 1728. As you might expect, you can use a cell reference as the argument for the CubeRoot function. For example, if cell A1 contains a value, you can enter CubeRoot A1 . In this case, the function returns the number obtained by calculating the cube root of the value in A1. You can use this function any number of times in the worksheet. As with Excel's built-in functions, your custom functions also appear in the Insert Function dialog box....
Setting breakpoints in your code
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 midexecution, and clicking the OK button resumes execution. Wouldn't it be nice if you could halt a routine's execution, take a look at any of your variables, and then continue execution Well, that's exactly what you can do by setting a breakpoint. You can set a breakpoint in your VBA code in several ways 1 Move the cursor to...
A function with an indefinite number of arguments
Some Excel worksheet functions take an indefinite number of arguments. A familiar example is the SUM function, which has the following syntax The first argument is required, but you can have as many as 29 additional arguments. Here's an example of a SUM function with four range arguments Here's a function that can have any number of single-value arguments. This function doesn't work with multicell range arguments. Function Concat string1, ParamArray string2 ' Demonstrates indefinite number of...
ScrollBar control
The ScrollBar control is similar to a SpinButton control described later . The difference is that the user can drag the ScrollBar's button to change the control's value in larger increments. Figure 17-14 shows a ScrollBar control. Its Value is displayed in a Label control. A ScrollBar control, with a Label control below it. A ScrollBar control, with a Label control below it. The following is a description of the most useful properties of a ScrollBar control 1 Value The control's current value....
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 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 words,...
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. Report Destination Printer C File Email The following is a description of the most useful OptionButton control properties Accelerator A letter that...
Save the file from the VBE by choosing FileOSave
If you create an add-in that stores information in a worksheet, you must set the workbook's IsAddIn property to False to view the workbook. You do this in the Property window when the ThisWorkbook objects is selected see Figure 22-5. After you've made your changes, make sure that you set the IsAddIn property back to True before you save the file. You now know how to work with add-ins and why you might want to create your own add-ins. One example in this chapter shows you the steps for creating...
Ensuring that an attached toolbar is displayed
As I explained earlier in this chapter, you can attach any number of toolbars to a workbook. But I also noted that the attached toolbar won't replace an existing toolbar that has the same name. In some cases, the failure to display a toolbar can present a problem. For example, assume that you distribute a workbook to your coworkers, and this workbook has an attached toolbar that executes your macros. Later, you update the workbook and add some new controls to your attached toolbar. When you...
Moving a range
You move a range by cutting it to the Clipboard and then pasting it in another area. If you record your actions while performing a move operation, the macro recorder generates code like the following As with the copying example earlier in this chapter, this is cient way to move a range of cells. In fact, you can move a Range A1 C6 .Cut Range A10 This macro takes advantage of the fact that the Cut method can use an argument that specifies the destination. Notice also that the range was not...
The NumberFormat property
The NumberFormat property represents the number format expressed as a text string of the Range object. This is a read-write property, so your VBA code can change the number format. The following statement changes the number format of column A to percent with two decimal places 0.00 Follow these steps to see a list of other number formats 2. Access the Format Cells dialog box by pressing Ctrl 1. 4. Select the Custom category to view some additional number format strings.
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 Use Excel's custom dialog box to easily 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...
Adding the OptionButtons
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 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...
Using multiple sets of OptionButtons
Figure 18-7 shows a custom dialog box with three sets of OptionButtons. If your UserForm contains more than one OptionButtons set, make sure that each set of OptionButtons works as a set. You can do so in either of two ways 1 Enclose each set of OptionButtons in a Frame control. This approach is the best and also makes the dialog box look better. It's easier to add the Frame before adding the OptionButtons. You can, however, also drag existing OptionButtons into a Frame. 1 Make sure that each...
Set MItem OutlookAppCreateltemO
The code sets the To, Subject, and Body properties, and then uses the Send method to send each message. Figure 23-7 shows one of the e-mails created by Excel. Create a personalized e-mail by using Excel. Create a personalized e-mail by using Excel. This example is available on this book's Web site. To use this example you must have Microsoft Outlook installed.
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...
The Address property
Address, a read-only property, displays the cell address for a Range object in absolute notation a dollar sign before the column letter and before the row number . The following statement displays the message box shown in Figure 8-1. MsgBox Range Cells 1, 1 , Cells 5, 5 .Address This message box displays the Address property of a 1-by-5 range.
Diving into Object Properties and Methods
Although knowing how to refer to objects is important, you can't do anything useful by simply referring to an object as in the examples in the preceding sections . To accomplish anything meaningful, you must do one of two things 1 Read or modify an object's properties 1 Specify a method of action to be used with an object Another slant on McObjects, McProperties, and McMethods Here's an analogy that may help you understand the relationships between objects, properties, and methods in VBA. In...
Creating a tabbed dialog box
Tabbed dialog boxes are useful because they let you present information in small, organized chunks. The Excel Options dialog box which is displayed when you choose ToolsOOptions is a good example. This dialog box uses a whopping 13 tabs to add some organization to an overwhelming number of options. Creating your own tabbed dialog boxes is relatively easy, thanks to the MultiPage control. Figure 18-11 shows a custom dialog box that uses a MultiPage control with three pages, or tabs. When the...
Commanding the CommandBars collection
You manipulate Excel toolbars and menus, for that matter by using objects located in the CommandBars collection. The CommandBars collection consists of 1 All Excel built-in toolbars 1 Any other custom toolbars that you create 1 A built-in menu bar named Worksheet menu bar, which appears when a worksheet is active 1 A built-in menu bar named Chart menu bar, which appears when a chart sheet is active 1 Any other custom menu bars that you create As I mention at the beginning of this chapter, the...
Looping through a range efficiently
Many macros perform an operation on each cell in a range, or they might perform selected actions based on each cell's content. These macros usually include a For-Next loop that processes each cell in the range. The following example demonstrates how to loop through a range of cells. In this case, the range is the current selection. A variable named Cell refers to the cell being processed. Within the For-Next loop, the single statement evaluates the cell and changes its interior color if the...
Getting a response from a message box
Your VBA code can also determine which button was clicked in a message box. You can assign the result of the MsgBox function to a variable. In the following code, I use some built-in constants which I describe later in Table 15-2 that make it easy to work with the values returned by MsgBox Ans MsgBox Continue , vbYesNo When you execute this procedure, the Ans variable is assigned a value of either vbYes or vbNo, depending on which button the user clicks. The Select Case statement uses the Ans...
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 i Workbooks A collection of all currently open Workbook objects i Worksheets A collection of all Worksheet objects contained in a particular Workbook object i Charts A collection of all Chart objects chart sheets contained in a particular Workbook object i Sheets A...
CommandButton control
CommandButton is simply a clickable button. It is of no use unless you provide an event-handler procedure to execute when the button is clicked. Figure 17-7 shows a dialog box with a few CommandButtons. One of these buttons features a picture specified using the Picture property . When a CommandButton is clicked, it executes a macro with a name that consists of the CommandButton's name, an underscore, and the word Click. For example, if a command button is named MyButton, clicking it executes...
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 21-5. Unfortunately, you can't 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. The Function Arguments dialog box displays function argument descriptions for...
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 ToolsOMacroO Record New 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...