Using VBA to specify the data used by a chart

The examples in this section describe VBA techniques that enable you to change the data used by a chart. CHANGING CHART DATA BASED ON THE ACTIVE CELL Figure 18-4 displays a chart based on the data in the row of the active cell. When the user moves the cell pointer, the chart is updated automatically. Figure 18-4 This chart always displays the data from the row of the active cell. Figure 18-4 This chart always displays the data from the row of the active cell. This example uses an event handler...

Determining whether a chart is activated

A common type of macro performs some manipulations on the active chart (the chart selected by a user). For example, a macro might change the chart's type, apply colors, or change the font size. The question is, how can your VBA code determine whether the user has actually selected a chart By selecting a chart, I mean either activating a chart sheet or activating an embedded chart by clicking it. Your first inclination might be to check the TypeName property of the Selection, as in this...

Quickanddirty spreadsheets

This is probably the most common type of spreadsheet. Most of the spreadsheets in this category are fairly small and are developed to quickly solve a problem or answer a question. Here's an example You're about to buy a new car, and you want to figure out your monthly payment for various loan amounts. Or perhaps you need to generate a chart that shows your company's sales by month, so you quickly enter 12 values and whip out a chart, which you paste into your word processor. In both of the...

Basic Spreadsheet Types

In this section, I classify spreadsheets into several basic types to provide a better perspective on how spreadsheet applications fit into the overall scheme of things. This is all quite arbitrary, of course, and is based solely on my own experience. Moreover, there is quite a bit of overlap between the categories, but they cover most of the spreadsheets that I've seen and developed. My names for these categories are as follows Add-ins that contain worksheet functions I discuss each of these...

Spreadsheet newsgroups

The primary Usenet newsgroup for general spreadsheet users is This newsgroup is intended for users of any spreadsheet brand, but about 90 percent of the postings deal with Excel. My advice Skip this one and head directly for the Microsoft newsgroups. Microsoft has an extensive list of newsgroups, including quite a few devoted to Excel. If your Internet service provider doesn't carry the Microsoft newsgroups, you can access them directly from Microsoft's news server. You'll need to configure...

What Is a Spreadsheet Application

In this chapter, I attempt to clarify how people use spreadsheets in the real world. This is a topic that's germane to this entire book because it can help you determine how much effort you should devote to a particular development project. By the time you finish this chapter, you should have a pretty good idea of what I mean by a spreadsheet application. And after you've made it through the rest of the book, you'll be well on your way to developing your own spreadsheet applications with Excel....

The audience for spreadsheet applications

The remaining segments in the preceding table comprise spreadsheet end users, whom you can think of as the consumers of spreadsheet applications. When you develop a spreadsheet application for others to use, you need to know which of these groups of people will actually be using your application. Users with little experience and no interest comprise a large percentage of all spreadsheet users, which is probably the largest segment of all. These are the people who need to use a spreadsheet for...

Spreadsheet Applications

Programming, as it relates to spreadsheet use, is essentially the process of building applications that use a spreadsheet rather than a traditional programming language such as C, Pascal, or BASIC. In both cases, however, these applications will be used by other people not the developer of the application. For purposes of this book, a spreadsheet application is a spreadsheet file (or group of related files) that is designed so that someone other than the developer can perform useful work...

Working with ADO

ADO (ActiveX Data Objects) is an object model that enables you to access data stored in a variety of database formats. Importantly, this methodology allows you to use a single object model for all your databases. This is currently the preferred data access methodology and should not be confused with DAO (Data Access Objects). This section presents a simple example that uses ADO to retrieve data from an Access database. ADO programming is a very complex topic. If you have a need to access...

With End With constructs

The With-End With instruction construct enables you to perform multiple operations on a single object. To start understanding how the With-End With construct works, examine the following procedure, which modifies five properties of a selection's formatting (the selection is assumed to be a Range object) Selection.Font.Name Times New Roman Selection.Font.FontStyle Bold Italic Selection.Font.Size l2 Selection.Font.Underline xlUnderlineStyleSingle Selection.Font.Colorlndex 5 End Sub This procedure...

Adding and deleting menus automatically

If you need a menu to be created when a workbook is opened, use the Workbook_Open event. The following code, stored in the code module for the ThisWorkbook object, executes the CreateMenu procedure (not shown here) To delete the menu when the workbook is closed, use a procedure such as the following. This procedure is executed before the workbook closes, and it executes the DeleteMenu procedure (not shown here). Private Sub Workbook_BeforeClose(Cancel As Boolean) A problem could arise, however,...

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 You can even mix and match the argument types. For example, the following example uses three arguments the first is a range, the second is a value, and the third is an expression. You can create Function...

Modifying Your Userform For A Progress Indicator With A Multipage Control

This step assumes that you have a UserForm all set up. You'll add a MultiPage control. The first page of the MultiPage control will contain all your original controls. The second page will contain the controls that display the progress indicator. When the macro begins executing, VBA code will change the Value property of the MultiPage control. This will effectively hide the original controls and display the progress indicator. The first step is to add a MultiPage control to your UserForm. Then...

Disabling or hiding menus

When a menu or menu item is disabled, its text appears in a faint shade of gray, and clicking it has no effect. Excel disables its menu items when they are out of context. For example, the Links menu item on the Edit menu is disabled when the active workbook does not contain any links. You can write VBA code to enable or disable both built-in and custom menus or menu items. Similarly, you can write code to hide menus or menu items. The key, of course, is tapping into the correct event. The...

Determining the last nonempty cell in a column or row

In this section, I present two useful functions LASTINCOLUMN returns the contents of the last nonempty cell in a column LASTINROW returns the contents of the last nonempty cell in a row. Each function accepts a range as its single argument. The range argument can be a complete column (for LASTINCOLUMN) or a complete row (for LASTINROW). If the supplied argument is not a complete column or row, the function uses the column or row of the upper-left cell in the range. For example, the following...

Associating a Help Topic with a VBA Function

If you create custom worksheet functions with VBA, you might want to associate a Help file and context ID with each function. After these items are assigned to a function, the Help topic can be displayed from the Insert Function dialog box by pressing F1. To specify a context ID for a custom worksheet function, follow these steps 1. Create the function as usual. 2. Make sure that your project has an associated Help file (refer to the preceding section). 3. In the VBE, press F2 to activate the...

Menu Programming That Works with Events

Suppose that you want to create a menu when a workbook opens. You'll also want to delete the menu when the workbook closes because menu modifications remain in effect between Excel sessions. Or suppose that you want a menu to be available only when a particular workbook or worksheet is active. These sorts of things are relatively easy to program, thanks to Excel's event handlers. The examples in this section demonstrate various menu-programming techniques used in conjunction with events. I...

Class modulelevel variables for the CSVFile Class

A class module must maintain its own private variables that mirror the property settings for the class. The CSVFileClass class module uses two variables to keep track of the two property settings. These variables are declared at the top of the class module Private RangeToExport As Range Private ImportToCell As Range RangeToExport is a Range object that represents the range to be exported. ImportToCell is a Range object that represents the upper-left cell of the range into which the file will be...

Prompting for a cell value

The following procedure demonstrates how to ask the user for a value and then insert it into cell A1 of the active worksheet Range(A1).Value InputBox(Enter the value) End Sub Figure 11-3 shows how the input box looks. This procedure has a problem, however. If the user clicks the Cancel button in the input box, the procedure deletes any data already in the cell. The following modification checks for the Cancel button clicks but takes no action Dim UserEntry As String UserEntry InputBox(Enter the...

Using a List Box in a User Form

Figure 14-2 shows another example that uses a ListBox as a menu. Before the UserForm is displayed, its Initialize event handler procedure is called. This procedure, which follows, uses the Addltem method to add six items to the ListBox Figure 14-2 This dialog box uses a ListBox as a menu. Figure 14-2 This dialog box uses a ListBox as a menu. Private Sub UserForm_Initialize() With ListBoxl .Addltem Macrol .Addltem Macro2 .AddItem .AddItem .AddItem .AddItem With Macro3 Macro4 Macro5 Macro6 The...

Manipulating the Command Bars Collection

The CommandBars collection, contained in the Application object, is a collection of all CommandBar objects. Each CommandBar object has a collection of Controls. All these objects have properties and methods that enable you to control toolbars with VBA procedures. In this section, I provide some key background information that you should know about before you start writing code to manipulate toolbars. As always, a thorough understanding of the object model will make your task much easier. You...

Determining whether a cell has a comment

The following statement will display the comment in cell A1 of the active sheet MsgBox Range(A1).Comment.Text If cell A1 does not have a comment, executing this statement will generate a cryptic error message Object variable or With block variable not set. To determine whether a particular cell has a comment, you can write code to see whether the Comment object is Nothing. (Yes, Nothing is a valid keyword.) The following statement displays True if cell A1 does not have a comment MsgBox...

Activating a Microsoft Office application

If the application that you want to start is one of several Microsoft applications, you can use the ActivateMicrosoftApp method of the Application object. For example, the following procedure starts Word Application.ActivateMicrosoftApp xlMicrosoftWord End Sub If Word is already running when the preceding procedure is executed, it is activated. The other constants available for this method are

The IDE Object Model

Programming the IDE requires an understanding of its object model. The top object in the object hierarchy is the VBE (Visual Basic Environment). As with Excel's object model, the VBE contains other objects. A simplified version of the IDE object hierarchy is as follows VBProject VBComponent CodeModule Designer Property Reference Window CommandBar This chapter ignores the Extensibility Library's Windows collection and CommandBars collection, which aren't all that useful for Excel developers....

Excel Resources Online

If I've done my job, the information provided in this book will be useful to you. It is, however, by no means comprehensive. In addition, new issues tend to crop up, so you'll want to make sure that you're up-to-date. Therefore, I've compiled a list of additional resources that could help you become more proficient in Excel application development. I've classified these resources into three categories Microsoft technical support

Solving Problems with a Spreadsheet

I've covered the basic concept of a spreadsheet application, discussed the end users and developers of such applications, and even attempted to figure out why people use spreadsheets at all. Now it's time to take a look at the types of tasks that are appropriate for spreadsheet applications. You might already have a pretty good idea of the types of tasks for which you can use a spreadsheet. Traditionally, spreadsheet software has been used for numerical applications that are largely interactive...

Listing all Command Bar objects

If you're curious about the objects in the CommandBars collection, the following procedure should be enlightening. Executing this procedure generates a list (as shown in Figure 22-6) of all CommandBar objects in the CommandBars collection. For Excel 2003, it lists a total of 126 built-in command bars, plus any custom menu bars or toolbars. For each command bar, the procedure lists its Index, Name, and Type property settings (displayed as Toolbar, Menu Bar, or Shortcut) as well as whether it's a...

Help for Your Excel Applications

If you develop a nontrivial application in Excel, you might want to consider building in some sort of help for end users. Doing so makes the users feel more comfortable with the application and could eliminate many of those time-wasting phone calls from users with basic questions. Another advantage is that help is always available That is, the instructions can't be misplaced or buried under a pile of books. You can add user help to your applications in a number of ways, ranging from simple to...

Chart creation macro recorder output

I turned on the macro recorder while I created a chart (shown in Figure 18-1) and then performed some simple customizations to it. The chart uses the data in range A1 F2. Following in Listing 18-1 is the code generated by the macro recorder Listing 18-1 Code Generated by the Macro Recorder Range(A1 F2).Select Charts.Add ActiveChart.ChartType xlColumnClustered ActiveChart.SetSourceData _ _ PlotBy xlRows ActiveChart.Location _ Where xlLocationAsObject, _ Name Sheet1 ActiveChart.HasLegend False...

Sending EMail Attachments from Excel

As you probably know, Excel has commands to send worksheets or workbooks via e-mail. And, of course, you can use VBA to automate these types of tasks. The procedure below sends the active workbook (as an attachment) to joeblow anydomain.com. The e-mail message has the subject My Workbook. ActiveWorkbook.SendMail joeblow anydomain.com, My Workbook End Sub If you would like to e-mail only a single sheet from a workbook, you need to copy the sheet to a new (temporary) workbook, send that workbook...

The Chart object model

To get a feel for the number of objects involved when working with charts, turn on the macro recorder, create a chart, and perform some routine chart-editing tasks. You might be surprised by the amount of code that Excel generates. When you first start exploring the object model for a Chart object, you'll probably be very confused . . . which is not surprising because the object model is very confusing. It's also very deep. For example, assume that you want to change the title displayed in an...

Associating a Help File with Your Application

You can associate a particular HTML Help file with your Excel application in one of two ways by using the Project Properties dialog box or by writing VBA code. In the Visual Basic Editor (VBE), choose Tools xxx Properties (where xxx corresponds to your project's name). In the Project Properties dialog box, click the General tab and specify a compiled HTML Help file for the project. This file should have a CHM extension. It's a good practice to keep your application's Help file in the same...

Making the application bulletproof

If you think about it, it's fairly easy to destroy a spreadsheet. Erasing one critical formula or value often causes errors throughout the entire worksheet and perhaps even other dependent worksheets. Even worse, if the damaged workbook is saved, it replaces the good copy on disk. Unless a backup procedure is in place, the user of your application could be in trouble, and you'll probably be blamed for it. Obviously, it's easy to see why you need to add some protection when users especially...

Transferring onedimensional arrays

The example in the preceding section involves a two-dimensional array, which works out nicely for row-and-column-based worksheets. When transferring a one-dimensional array to a range, the range must be horizontal that is, one row with multiple columns. If you have to use a vertical range instead, you must first transpose the array to make it vertical. You can use Excel's TRANSPOSE function to do this. The following example transfers a 100-element array to a vertical worksheet range (A1 A100)...

Microsoft Knowledge Base

Your best bet for solving a problem could be the Microsoft Knowledge Base. This is the primary Microsoft product information source an extensive, searchable database that consists of tens of thousands of detailed articles containing technical information, bug lists, fix lists, and more. 923 As you know, the Internet is a dynamic entity that tends to change rapidly. Web sites are often reorganized (especially those at the microsoft.com domain). Therefore, a particular URL listed in this Appendix...

Common properties

Although each control has its own unique set of properties, many controls have some common properties. For example, every control has a Name property and properties that determine its size and position (Height, Width, Left, and Right). If you're going to manipulate a control by using VBA, it's an excellent idea to provide a meaningful name for the control. For example, the first OptionButton that you add to a UserForm has a default name of OptionButton1. You refer to this object in your code...

S

Sachs, Jonathan (Lotus 1-2-3 inventor), 4 sales commission rate calculator based on years, 292 one-argument, 289-291 all workbooks, 340-341 chart as a file, 499-500 and closing all workbooks, 341 Loan Amortization Wizard default settings, 794-796 schedule, macro running, 895 scoping formula names, 51 Function procedure, 283-284 variables, 199-204, 895-896 VBA procedures, 239-240 screen. See display Script Editor, 82, 136 scrolling label, Help display, 775-776 UserForm sheet, 451-454 seamless...

Adding An Item To The Addins Collection

The add-in files that make up the AddIns collection can be stored anywhere. Excel maintains a partial list of these files and their locations in the Windows Registry. For Excel 2003, this list is stored at Manager You can use the Windows Registry Editor (regedit.exe) to view this Registry key. Note that the standard add-ins that are shipped with Excel do not appear in this Registry key. In addition, add-in files stored in the following directory will also appear in the list but will not be...

Programming dependencies in a wizard

In most wizards, a user's response on a particular step can affect what's displayed in a subsequent step. In the CD-ROM example, the user indicates which products he or she uses in Step 3 and then rates those products in Step 4. The OptionButtons for a product's rating are visible only if the user has indicated a particular product. Programmatically, this is accomplished by monitoring the MultiPage's Change event. Whenever the value of the MultiPage is changed (by clicking the Back or Next...

Setting up the Multi Page control for the wizard

Start with a new UserForm and add a MultiPage control. By default, this control contains two pages. Right-click the MultiPage tab and insert enough new pages to handle your wizard (one page for each wizard step). The example on the CD-ROM is a four-step wizard, so the MultiPage control has four pages. The names of the MultiPage tabs are irrelevant. The MultiPage control's Style property will eventually be set to 2 - fmTabStyleNone. While working on the UserForm, you'll want to keep the tabs...

Removing An Item From The Addins Collection

Oddly, there is no direct way to remove an add-in from the Addlns collection. The Addlns collection does not have a Delete or Remove method. One way to remove an add-in from the Add-Ins dialog box is to edit the Windows Registry database (using regedit.exe). After you do this, the add-in will not appear in the Add-Ins dialog box the next time that you start Excel. Note that this method is not guaranteed to work with all add-in files. Another way to remove an add-in from the Addlns collection is...

Using array formulas to count and sum

If none of the standard counting techniques fits the bill, you may be able to construct an array formula (see Array Formulas earlier in this chapter). Don't forget When you enter an array formula, press Ctrl+Shift+Enter. To count the number of numerical values (skipping text and blanks), use this array formula To count the number of cells that contain an error value, use this array formula To count the number of unique numeric values (skipping text, blanks not allowed), use this array formula...

The Loan Amortization Wizard workbook structure

The Loan Amortization Wizard consists of the following components FormMain A UserForm that serves as the primary user interface. FormHelp A UserForm that displays online help. HelpSheet A worksheet that contains the text used in the online help. ModMain A Visual Basic for Applications (VBA) module that contains a procedure that displays the main UserForm. ThisWorkbook The code module for this object contains the event handler procedures Workbook_Open and Workbook_BeforeClose, which create and...

The Excel Get SaveAsFilename Method

The GetSaveAsFilename method is very similar to the GetOpenFilename method. It displays a Save As dialog box and lets the user select (or specify) a file. It returns a filename and path but doesn't take any action. The syntax for this method is FileFilter, Filterlndex, Title, ButtonText) InitialFilename Optional. Specifies the suggested filename. FileFilter Optional. A string specifying file-filtering criteria. Filterlndex Optional. The index number of the default file-filtering criteria. Title...

Listing Excel menu information

The ListMenuInfo procedure, which follows, might be instructive for those who will be customizing Excel menus. It displays the caption for each item (menu, menu item, and submenu item) on the Worksheet Menu Bar. Dim SubMenuItem As CommandBarControl For Each Menu In CommandBars(1).Controls For Each MenuItem In Menu.Controls For Each SubMenuItem In MenuItem.Controls Cells(row, 1) Menu.Caption Cells(row, 2) MenuItem.Caption Cells(row, 3) SubMenuItem.Caption row row + 1 Next SubMenuItem Next...

Displaying All Components in a VBA Project

The ShowComponents procedure, shown in Listing 28-1, loops through each VBA component in the active workbook and writes the following information to a worksheet The number of lines of code in the code module for the component Listing 28-1 Displaying Each Active VBA Component in a Worksheet Dim VBP As VBProject Set VBP ActiveWorkbook.VBProject NumComponents VBP.VBComponents.Count Cells.ClearContents For i 1 To NumComponents Name Cells(i, 1) VBP.VBComponents(i).Name Type Select Case...

Creating an addin

To create an add-in, do the following 1. Activate the VBE and select the future add-in workbook in the Project window. 2. Choose Debug Compile. This step forces a compilation of the VBA code and also identifies any syntax errors so that you can correct them. When you save a workbook as an add-in, Excel creates the add-in even if it contains syntax errors. 3. Choose Tools xxx Properties to display the Project Properties dialog box (where xxx represents the name of the project). Click the General...

Cleaning up recorded macros

Earlier in this section, you saw how recording your actions while you issued a single command the File Page Setup command can produce an enormous amount of VBA code. In many cases, the recorded code includes extraneous commands that you can delete. It's also important to understand that the macro recorder doesn't always generate the most efficient code. If you examine the generated code, you'll see that Excel generally records what is selected that is, an object and then uses the Selection...

The User Form for the Text Tools utility

When I create a utility, I usually begin by designing the user interface. In this case, it's the dialog box that's displayed to the user. Creating the dialog box forces me to think through the project one more time. Figure 16-2 shows the UserForm for the Text Tools utility. Notice that the configuration of the controls varies, depending on which option is selected. BS text tools.xla - UserForml UserForm Figure 16-2 The UserForm for the Text Tools utility. Figure 16-2 The UserForm for the Text...

Entering event handler code

Every event handler procedure has a predetermined name. Following are some examples of event handler procedure names You can declare the procedure by typing it manually, but a much better approach is to let the VBE do it for you. Figure 19-2 shows the code module for the ThisWorkbook object. To insert a procedure declaration, select Workbook from the objects list on the left. Then select the event from the procedures list on the right. When you do so, you get a procedure shell that contains the...

Worksheet Events

The events for a Worksheet object are some of the most useful. As you can see, monitoring these events can make your applications perform feats that would otherwise be impossible. Table 19-2 lists the worksheet events, with a brief description of each. The worksheet is calculated or recalculated . Cells on the worksheet are changed by the user or by an A hyperlink on the sheet is clicked. A pivot table on the sheet is updated. The selection on the worksheet is changed. This event was added in...

Controlling Word from Excel

The example in this section demonstrates an Automation session by using Word. The MakeMemos procedure creates three customized memos in Word and then saves each document to a file. The information used to create the memos is stored in a worksheet, as shown in Figure 20-6. The monthly sales data for your region is listed below. This information was obtained from the central database. Please call if you have any questions._ Figure 20-6 Word automatically generates three memos based on this Excel...

Saving and closing all workbooks

The following procedure loops through the Workbooks collection. The code saves and closes all workbooks. Sub CloseAllWorkbooks Dim Book As Workbook For Each Book In Workbooks If Book.Name lt gt ThisWorkbook.Name Then Book.Close savechanges True End If Next Book ThisWorkbook.Close savechanges True End Sub Notice that the procedure uses an If statement to determine whether the workbook is the workbook that contains the code. This is necessary because closing the workbook that contains the...

Passing Arguments to Procedures

A procedure's arguments provide it with data that it uses in its instructions. The data that's passed by an argument can be any of the following With regard to arguments, procedures are very similar to worksheet functions in the following respects A procedure may not require any arguments. A procedure may require a fixed number of arguments. A procedure may accept an indefinite number of arguments. A procedure may require some arguments, leaving others optional. A procedure may have all...

Adding A Menu Take

Listing 23-1 presents a better demonstration. It uses the FindControl method to attempt to locate the Help menu. If the Help menu is not found, the code adds the new menu item to the end of the Worksheet Menu Bar. Listing 23-1 Adding the Budgeting Menu to Excel's Main Menu Bar Dim HelpMenu As CommandBarControl Dim NewMenu As CommandBarPopup Set HelpMenu If HelpMenu Is Nothing Then Add the menu to the end Set NewMenu CommandBars 1 .Controls _ .Add Type msoControlPopup, Temporary True Add the...

For Each Next constructs

Recall from the preceding chapter that a collection is a group of related objects. For example, the Workbooks collection is a collection of all open Workbook objects. There are many other collections that you can work with. You don't have to know how many elements are in a collection to use the For Each-Next construct. Suppose that you want to perform some action on all objects in a collection. Or suppose that you want to evaluate all objects in a collection and take action under certain...

Adding a new menu to a menu bar

In this section, I describe how to use VBA to add a new menu to the Worksheet Menu Bar. The Worksheet Menu Bar is the first item in the CommandBars collection, so you can reference it one of two ways By its name CommandBars Worksheet Menu Bar By its index CommandBars l In VBA terms, you use the Add method to append a new control to the Controls collection. The new control is a pop-up control of type msoControlPopup. You can specify the new control's position if you don't, the new menu is added...

Inserting a New User Form

To insert a new UserForm, activate the VBE press Alt F11 , select your workbook's project from the Project window, and then choose Insert UserForm. UserForms have names like UserForml, UserForm2, and so on. You can change the name of a UserForm to make it easier to identify. Select the form and use the Properties window to change the Name property. Press F4 if the Properties window is not displayed. Figure 13-1 shows the Properties window when an empty UserForm is selected. Figure 13-1 The...

Listing all controls on all toolbars

The following procedure loops through all command bars in the collection. If the command bar is a toolbar that is, if its Type property is set to msoBarTypeNormal another loop displays the Caption for each toolbar button. msoBarTypeNormal is a built-in constant that has the value of 0. Sub ShowAllToolbarControls Dim row As Integer Dim Cbar As CommandBar Dim ctl As CommandBarControl If Cbar.Type msoBarTypeNormal Then Cells row, 1 Cbar.Name For Each ctl In Cbar.Controls Cells row, 2 ctl.Caption...

Some useful application properties

When you're working with Excel, only one workbook at a time can be active. And if the sheet is a worksheet, one cell is the active cell even if a multicell range is selected . VBA knows this and lets you refer to these active objects in a simplified manner. This is often useful because you won't always know the exact workbook, worksheet, or range that you want to operate on. VBA handles this by providing properties of the Application object. For example, the Application object has an ActiveCell...

How the Msg Box emulation works

Notice the use of four Public variables. The first three Promptl, Buttonsl, and Titlel represent the arguments that are passed to the function. The other variable UserClick represents the values returned by the function. The UserForm_ Initialize procedure needs a way to get this information and send it back to the function, and using Public variables is the only way to accomplish that. The UserForm shown in Figure 15-7 contains four Image controls one for each of the four possible icons , three...

Msg Box emulation MyMsg Box code

The MyMsgBox function makes use of a UserForm named MyMsgBoxForm. The function itself, which follows, is very short. The bulk of the work is done in the The complete code for the MyMsgBox function is too lengthy to list here, but it's available in a workbook on the companion CD-ROM. Public Promptl As String Public Buttonsl As Integer Public Titlel As String Public UserClick As Integer Function MyMsgBox ByVal Prompt As String, _ Optional ByVal Buttons As Integer, _ Optional ByVal Title As String...

Saving the Text Tools utility settings

The Text Tools utility has a very useful feature It remembers the last settings that you used. This is handy because most people tend to use the same option each time that they invoke it. As I mentioned, the most recent settings are stored in the Windows Registry. When the user clicks the Close button, the code uses VBA's SaveSetting function to save the value of each control. When the Text Tools utility is started, it uses the GetSetting function to retrieve those values and set the controls...

Fixing a menu that has been reset

Consider this scenario You write VBA code that creates a new menu when your workbook application is opened. The user opens another workbook containing a macro that resets Excel's menu bar. Or consider this The user plays around with the Customize dialog box, selects the Workbook Menu Bar from the list of that dialog box, and then clicks the Reset button. In both cases, your custom menu is destroyed. Your menu-making code is probably triggered by the Workbook_Open event, so the only way that the...

Replacing a Module with an Updated Version

The example in this section demonstrates how to replace a VBA module with a different VBA module. Besides demonstrating three VBComponent methods Export, Remove, and Import , the procedure also has a practical use. For example, you might distribute a workbook to a group of users and then later discover that a macro contains an error or needs to be updated. Because the users could have added data to the workbook, it's not practical to replace the entire workbook. The solution, then, is to...

How Excel Handles Custom Dialog Boxes

A custom dialog box is created on a UserForm, and you access UserForms in the Visual Basic Editor VBE . Following is the typical sequence that you will follow when you create a UserForm 1. Insert a new UserForm into your workbook's VBProject. 2. Write a procedure that will display the UserForm. This procedure will be located in a VBA module not in the code module for the UserForm . 3. Add controls to the UserForm. 4. Adjust some of the properties of the controls that you added. 5. Write event...

Cleaning up the recorded pivot table code

As with most recorded macros, the preceding example is not as efficient as it could be. It can be simplified to make it more understandable. Listing 17-1 generates the same pivot table as the procedure previously listed. Listing 17-1 A More Efficient Way to Generate a Pivot Table in VBA Dim PTCache As PivotCache Dim PT As PivotTable Set PTCache ActiveWorkbook.PivotCaches.Add _ SourceType xlDatabase, _ Set PT PTCache.CreatePivotTable _ TableDestination , _ TableName PivotTable1 xlPageField...

Disabling shortcut menu items

As an alternative to removing menu items, you might want to disable one or more items on certain shortcut menus while your application is running. When an item is disabled, it appears in a light gray color, and clicking it has no effect. The following procedure disables the Hide menu item from the Row and Column shortcut menus False False End Sub

Returning an array of nonduplicated random integers

The function in this section, RANDOMINTEGERS, returns an array of nonduplicated integers. The function is intended to be used in a multicell array formula. Select a range and then enter the formula by using Ctrl Shift Enter. The formula returns an array of nonduplicated integers, arranged randomly. For example, if you enter the formula into a 50-cell range, the formulas will return nonduplicated integers from 1 to 50. The code for RANDOMINTEGERS follows Function RANDOMINTEGERS Dim FuncRange As...

Applying names to existing references

When you create a new name for a cell or a range, Excel doesn't automatically use the name in place of existing references in your formulas. For example, assume that you have the following formula in cell F10 If you define the names Income for A1 and Expenses for A2, Excel won't automatically change your formula to Income-Expenses. It's fairly easy to replace cell or range references with their corresponding names, however. Start by selecting the range that you want to modify. Then choose the...

Error Handling Techniques

When a VBA procedure is running, errors can occur, as you undoubtedly know. These include either syntax errors which you must correct before you can execute a procedure or runtime errors which occur while the procedure is running . This section deals with runtime errors. For error-handling procedures to work, the Break on All Errors setting must be turned off.In the VBE, choose Tools Options and click the General tab in the Options dialog box. If Break on All Errors is selected, VBA ignores...

List Box item transfer

Some applications require a user to select several items from a list. It's often useful to create a new list of the selected items. For an example of this situation, check out the Attach Toolbars dialog box that appears when you click the Attach button in the Customize dialog box which appears when you choose View Toolbars Customize . Figure 14-12 shows a dialog box with two ListBoxes. The Add button adds the item selected in the left ListBox to the right ListBox. The Delete button removes the...

Determining file associations

In Windows, many file types are associated with a particular application. This association makes it possible to double-click the file to load it into its associated application. The following function, named GetExecutable, uses a Windows API call to get the full path to the application associated with a particular file. For example, your system has many files with a .txt extension one named Readme.txt is probably in your Windows directory right now. You can use the GetExecutable function to...

An array formula example

Excel's array formulas enable you to perform individual operations on each cell in a range in much the same way that a program language's looping feature enables you to work with elements of an array. If you've never used array formulas before, this section will get your feet wet with a hands-on example. Figure 3-5 shows a worksheet with text in A1 A5. The goal of this exercise is to create a single formula that returns the sum of the total number of characters in the range. Without the single...

Displaying Help from an input box

VBA's InputBox function can also display a Help button if its sixth argument contains the Help filename. The following example produces the InputBox shown in Figure 24-11 Msg Enter a value DefaultVal 0 HFile ThisWorkbook.Path amp AppHelp.chm ContextID 1002 x InputBox _ Prompt Msg, _ Default DefaultVal, _ HelpFile HFile, _ Context ContextID Figure 24-11 An InputBox with a Help button. Figure 24-11 An InputBox with a Help button.

Creating A New Toolbar

To create a new toolbar, click the New button of the Customize dialog box and then enter a name in the New Toolbar dialog box that appears. Excel creates and displays an empty toolbar. You can then add buttons or menu commands to the new toolbar. Figure 22-2 shows a custom toolbar that I created manually. This toolbar, called Custom Formatting, contains the formatting tools that I use most frequently. Notice that this toolbar includes drop-down menus as well as standard toolbar buttons. Bor.lei...

Naming columns and rows

With Excel, you can also name complete rows and columns. In the preceding example, the name Qtrl is assigned to the range C3 C6. Alternatively, Qtr1 could be assigned to all of column C, Qtr2 to column D, and so on. You also can do the same horizontally so that North refers to row 3, South to row 4, and so on. The intersection operator works exactly as before, but now you can add more regions or quarters without having to change the existing names. When naming columns and rows, make sure that...

Ensuring that an addin is installed

In some cases, you might need to ensure that your add-in is installed properly that is, opened via the Tools Add-Ins command and not the File Open command. This section describes a technique that determines just that. If the add-in isn't properly installed, the code displays a message see Figure 21-6 , and the file is closed. In other words, the add-in remains open only if it is properly installed. Figure 21-6 When attempting to open the add-in incorrectly, the user sees this message. Figure...

Template files

You can save any workbook as a template file XLT extension . Doing so is useful if you tend to create similar files on a regular basis. For example, you might need to generate a monthly sales report. You can save some time by creating a template that holds the necessary formulas and charts for your report. When you start new files based on the template, you need only plug in the values. To create a new workbook that's based on an existing template, choose the File New command and then select...

Adding menu items to shortcut menus

Adding a menu item to a shortcut menu works just like adding a menu item to a regular menu. The following example demonstrates how to add a menu item to the Cell shortcut menu that appears when you right-click a cell. This menu item is added to the end of the shortcut menu, with a separator bar above it. Set NewItem .Caption Toggle Word Wrap .OnAction ToggleWordWrap .BeginGroup True End With End Sub Selecting the new menu item executes a procedure named ToggleWordWrap not shown here . Figure...

Adding a new Comment object

You might have noticed that the list of methods for the Comment object doesn't include a method to add a new comment. This is because the AddComment method belongs to the Range object. The following statement adds a comment an empty comment to cell A1 on the active worksheet If you consult the Help system, you'll discover that the AddComment method takes an argument that represents the text for the comment. Therefore, you can add a comment and then add text to the comment with a single...

Importing XML data to a list

The example in the preceding section used XML files that contained only a single record. XML files often contain multiple records, called repeating elements. Examples include a customer list or data for all employees in an organization. You can use the Excel File Open command to open an XML file that contains repeating elements. After you specify the filename, Excel presents the Open XML dialog box, as shown in Figure 4-6. This dialog box has three options As an XML List The file opens, and...

Assigning A Custom Macro To A Builtin Button

Each of Excel's built-in toolbar buttons executes a specific internal procedure. You can assign your own macro to a built-in button. To do so, just use the OnAction property. The following instruction assigns a macro to the Sort Ascending toolbar button. Ascending .OnAction ShowMsg After executing the instruction, clicking the Sort Ascending button will no longer work. Rather, it will execute the ShowMsg VBA procedure. To return the button to its normal functionality, assign an empty string to...

Workspace files

A workspace file is a special file that contains information about an Excel workspace. For example, if you have a project that uses two workbooks and you like to have the workbook windows arranged in a particular way, you can save an XLW file choose the File Save Workspace command to save this window configuration. Then, whenever you open the XLW file, Excel restores the desired workspace. Which Version Created That XLS File Unfortunately, there is no direct way to determine which version of...

About the List Box control

Following are a few points to keep in mind when working with ListBox controls. Examples in the sections that follow demonstrate many of these points. The items in a ListBox can be retrieved from a range of cells specified by the RowSource property , or they can be added by using VBA code using the Addltem method . A ListBox can be set up to allow a single selection or a multiple selection. This is determined by the MultiSelect property. If a ListBox is not set up for a multiple selection, the...

Naming cells and ranges

You create names for cells or ranges by choosing the Insert Name Define command or by pressing Ctrl F3 . An even faster way to create names is to use the Name Box the drop-down list at the left side of the formula bar . When using the Name Box, just select the cell or range, type the name into the Name Box, and press Enter. You can choose the Insert Name Create command to create names automatically for cells or ranges based on row or column titles on your worksheet. In Figure 3-2, for example,...

Spreadsheet File Formats Supported

Although Excel's default file format is an XLS workbook file, it can also open and save a wide variety of files generated by several other applications. An important consideration is whether a particular file type can survive a round trip. In other words, do you lose any information if you save a file in a particular format and then reopen it in the same application As you might expect, using Excel's native file format XLS files ensures that you'll lose absolutely nothing as long as you use the...

Understanding Class Modules

This chapter presents an introduction to class modules and includes several examples that might help you better understand this feature and give you ideas for using class modules in your own projects. An introduction to class modules A list of some typical uses for class modules Examples that demonstrate some key concepts related to class modules For many VBA programmers, the concept of a class module is a mystery. This feature has been available in Visual Basic for several years and was added...

Removing a VBA module

If you need to remove a VBA module or a class module from a project, select the module's name in the Project Explorer window and choose File Remove xxx where xxx is the name of the module . Or you can right-click the module's name and choose Remove xxx from the shortcut menu. You'll be asked whether you want to export the module before removing it. See the next section for details. Remember You cannot remove code modules associated with the workbook the ThisWorkbook code module or with a sheet...

About Command Bars

Beginning with Excel 97, Microsoft introduced a completely new way of handling toolbars. Technically, a toolbar is known as a CommandBar object. In fact, what's commonly called a toolbar is actually one of three types of command bars Toolbar This is a bar with one or more clickable controls. This chapter focuses on this type of command bar. Menu bar The two built-in menu bars are Worksheet Menu Bar and Chart Menu Bar see Chapter 23 . Shortcut menu This is the menu that pops up when you...

Emulating the Msg Box Function

VBA's MsgBox function is a bit unusual because unlike most functions, it displays a dialog box. But, like other functions, it also returns a value namely, an integer that represents which button the user clicked. This example discusses a custom function that I created that emulates VBA's MsgBox function. On first thought, creating such a function might seem rather easy. Think again The MsgBox function is extraordinarily versatile because of the arguments that it accepts. Consequently, creating...

Minimizing and maximizing windows

At any given time, the VBE can have lots of code windows, and things can get a bit confusing. Code windows are much like worksheet windows in Excel. You can minimize them, maximize them, hide them, rearrange them, and so on. Many people find it most efficient to maximize the Code window that they're working in. Doing so enables you to see more code and keeps you from getting distracted. To maximize a Code window, click the maximize button in its title bar or just double-click its title bar. To...

Using a Windows API function to select a directory

In this section, I present a function named GetDirectory that displays the dialog box shown in Figure 12-7 and returns a string that represents the selected directory. If the user clicks Cancel, the function returns an empty string. This technique will work with Excel 97 and later versions. The GetDirectory function takes one argument, which is optional. This argument is a string that will be displayed in the dialog box. If the argument is omitted, the dialog box displays Select a folder as the...

Working with Text Files

VBA contains a number of statements that allow low-level manipulation of files. These Input Output I O statements give you much more control over files than Excel's normal text file import and export options. A file can be accessed in any of three ways Sequential access By far the most common method. This allows reading and writing individual characters or entire lines of data. Random access Used only if you're programming a database application, which you shouldn't be doing in VBA because...

Toggling a Boolean property

A Boolean property is one that is either True or False. The easiest way to toggle a Boolean property is to use the Not operator, as shown in the following example, which toggles the WrapText property of a selection. Toggles text wrap alignment for selected cells If TypeName Selection Range Then Selection.WrapText Not ActiveCell.WrapText End If End Sub Note that the active cell is used as the basis for toggling. When a range is selected and the property values in the cells are inconsistent for...

Early Binding

To use early binding, you create a reference to the object library by choosing the Tools References command in the Visual Basic Editor VBE , which brings up the dialog box shown in Figure 20-4. Figure 20-4 Attaching a reference to an object library file. Figure 20-4 Attaching a reference to an object library file. After the reference to the object library is established, you can use the Object Browser shown in Figure 20-5 to view the object names, methods, and properties. To access the Object...

Attaching A Toolbar To A Workbook

To store a toolbar in a workbook file, choose View Toolbars Customize to display the Customize dialog box. Click the Attach button to bring up the Attach Toolbars dialog box, as shown in Figure 22-5. This dialog box lists all the custom toolbars in the Toolbars collection in the list box on the left. Toolbars already stored in the workbook are shown in the list box on the right. Figure 22-5 The Attach Toolbars dialog box. Figure 22-5 The Attach Toolbars dialog box. To attach a toolbar, select...

Determining the Windows directory

Following is an example of an API function declaration Declare Function GetWindowsDirectoryA Lib kernel32 _ ByVal lpBuffer As String, ByVal nSize As Long As Long This function, which has two arguments, returns the name of the directory in which Windows is installed something that is not normally possible using VBA . After calling the function, the Windows directory is contained in lpBuffer, and the length of the directory string is contained in nSize. After inserting the Declare statement at...

Contents

Part I Some Essential Background Chapter 1 Excel 2003 Where It Came From 3 A Brief History of It all started with Lotus Quattro Microsoft Excel Why Excel Is Great for Developers 11 Excel's Role in Microsoft's Strategy 13 Chapter 2 Excel in a Nutshell 15 Thinking in Terms of Objects 15 Worksheets Chart XLM macro sheets 18 Excel 5 95 dialog sheets 18 Excel's User Menus Shortcut menus Toolbars Dialog boxes Keyboard Smart Tags Task Data Formulas, Functions, and Names 25 Customizing the Display 27...

Why Create Custom Functions

You are undoubtedly familiar with Excel worksheet functions even novices know how to use the most common worksheet functions, such as SUM, AVERAGE, and IF. By my count, Excel contains more than 300 predefined worksheet functions, plus additional functions available through the Analysis ToolPak add-in. If that's not enough, however, you can create custom functions by using VBA. With all the functions available in Excel and VBA, you might wonder why you would ever need to create new functions....