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 MsgBox 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....
Executing a procedure from a custom menu
As I describe in Chapter 23, Excel provides two ways for you to customize its menus choosing the View Toolbars Customize command or writing VBA code. The latter method is preferable if you create applications, but you can use either technique to assign a macro to a new menu item. Following are the steps required to display a new menu item on a menu and to assign a macro to the menu item. It assumes that the new menu item is on the Data menu, that the menu item text is Open Customer File, and...
Using VBA filerelated commands
The VBA commands that you can use to work with files are summarized in Table 27-1. Table 27-1 VBA FILE-RELATED COMMANDS Changes the current directory Changes the current drive Returns a filename or directory that matches a specified pattern or file attribute Returns the date and time when a file was last modified Returns the size of a file, in bytes Returns a value that represents an attribute of a file The remainder of this section consists of examples that demonstrate some of the file...
Making the application aesthetically appealing and intuitive
If you've used many different software packages, you've undoubtedly seen examples of poorly designed user interfaces, difficult-to-use programs, and just plain ugly screens. If you're developing spreadsheets for other people, you should pay particular attention to how the application looks. How a computer program looks can make all the difference in the world to users, and the same is true with the applications that you develop with Excel. Beauty, however, is in the eye of the beholder. If your...
Using arguments with builtin dialog boxes
Most of the built-in dialog boxes also accept arguments, which usually correspond to the controls on the dialog box. For example, the Cell Protection dialog box invoked by using the xlDialogCellProtection constant uses two arguments locked and hidden. If you want to display that dialog box with both of these options checked, use the following statement True, True The arguments for each of the built-in dialog boxes are listed in the Help system. To locate the help topic, search for Built-In...
Deleting menu items from shortcut menus
The following procedure uses the Delete method to remove the menu item added by the procedure in the preceding section Sub RemoveItemFromShortcut On Error Resume Next Word Wrap .Delete End Sub The On Error Resume Next statement avoids the error message that appears if the menu item is not on the shortcut menu. The following procedure removes the Hide menu item from two shortcut menus the one that appears when you right-click a row header and the one that appears for a column header End Sub
Adding A Menu Item To The Tools Menu
The example in Listing 23-2 adds several menu items to a custom menu on the Worksheet Menu Bar. Often, you'll simply want to add a menu item to one of Excel's built-in menus, such as the Tools menu. With Excel 5 and Excel 95, assigning a macro to a new menu item on the Tools menu was easy. For some reason, this feature was removed, beginning with Excel 97. This section demonstrates how to write VBA code to add a menu item to Excel's Tools menu. Listing 23-3 adds the menu item Clear All But...
Adding A Toolbar Button That Executes A Macro
To create a new toolbar button to which you will attach a macro, activate the Commands tab of the Customize dialog box, and then choose Macros from the Categories list. Drag the command labeled Custom Button to your toolbar by default, this button has a smiley face image. After adding the button, right-click it and select your options from the menu shown in Figure 22-4. You'll want to change the name, assign a macro, and I hope change the image. Figure 22-4 Customizing a toolbar button. Figure...
Comments
A comment is descriptive text embedded within your code. The text of a comment is completely ignored by VBA. It's a good idea to use comments liberally to describe what you're doing because an instruction's purpose is not always obvious. You can use a complete line for your comment, or you can insert a comment after an instruction on the same line. A comment is indicated by an apostrophe. VBA ignores any text that follows an apostrophe except when the apostrophe is contained within quotation...
Programming properties of objects
Most objects have at least one property, and you can give them as many as you need. After a property is defined, you can use it in your code, using the standard dot syntax The VBE Auto List Members option works with objects defined in a class module. This makes it easier to select properties or methods when writing code. Properties for the object that you define can be read-only, write-only, or read write. You define a read-only property with a single procedure using the Property Get keyword....
Modifying an addin
If you need to modify an add-in, first open it and then unlock it. To unlock it, activate the VBE and then double-click its project's name in the Project window. You'll be prompted for the password. Make your changes and then save the file from the VBE choose File Save . If you create an add-in that stores its information in a worksheet, you must set its IsAddIn property to False before you can view that workbook in Excel. You do this in the Properties window as shown in Figure 21-4 when the...
The Deactivate event
The following example demonstrates the Deactivate event. This procedure is executed whenever the workbook is deactivated, and essentially never lets the user deactivate the workbook. When the Deactivate event occurs, the code reactivates the workbook and displays a message. Private Sub Workbook_Deactivate Me.Activate MsgBox Sorry, you may not leave this workbook End Sub I do not recommend using procedures, such as this one, that attempt to take overExcel.It can be very frustrating and confusing...
Using VBA to deactivate a chart
When you record a macro that deactivates a chart, you'll find that the macro recorder generates a statement such as this This statement does indeed deactivate the chart, but it is certainly not very clear as to why the chart is deactivated. When writing macros that work with charts, you might prefer to use the Deselet method to deactivate a chart These two statements have slightly different effects. When an embedded chart is the active chart, executing Deselect method does not select any cells...
Deleting A Menu Item From The Tools Menu
To delete a menu item, use the Delete method of the Controls collection. The following example deletes the Clear All But Formulas menu item on the Tools menu. Note that it uses the FindControl method to handle the situation when the Tools menu has a different caption. _ Controls amp Clear All But Formulas .Delete End Sub ois I Data Window Help Type a question for help -.Sx Spelling F7 Research Alt Click Shared Workspace Share Workbook Compare and Merge Workbooks Protection Online Collaboration...
The Excel GetOpenFilename Method
If your application needs to ask the user for a filename, you can use the InputBox function. But this approach often leads to typographical errors. A better approach is to use the GetOpenFilename method of the Application object, which ensures that your application gets a valid filename as well as its complete path . This method displays the normal Open dialog box displayed when you choose the File Open command but does not actually open the file specified. Rather, the method returns a string...
Importing XML data by using a Map
This example uses the worksheet shown in Figure 4-4. This worksheet uses data in column B to generate a loan amortization schedule. Assume that a back-end system generates XML files and that each file contains data for a customer. An example of such a file is shown below lt xml version 1.0 gt lt Customer gt lt Name gt Joe Smith lt Name gt lt AcctNo gt 32374-94 lt AcctNo gt lt LoanAmt gt 325,983 lt LoanAmt gt lt IntRate gt 6.25 lt IntRate gt lt Term gt 30 lt Term gt lt Customer gt Figure 4-4...
Assignment Statements
An assignment statement is a VBA instruction that makes a mathematical evaluation and assigns the result to a variable or an object. Excel's Help system defines expression as a combination of keywords, operators, variables, and constants that yields a string, number, or object. An expression can perform a calculation, manipulate characters, or test data. I couldn't have said it better myself. Much of the work done in VBA involves developing and debugging expressions. If you know how to create...
Determining or setting the file position
For sequential file access, it's rarely necessary to know the current location in the file. If for some reason you need to know this, you can use the Seek function. Excel's Text File Import and Export Features Excel supports three types of text files CSV Comma-Separated Value files Columns of data are separated by a comma, and each row of data ends in a carriage return. For some non-English versions of Excel, a semi-colon rather than a comma is used. PRN Columns of data are aligned by character...
Storing multiple charts on a chart sheet
Most Excel users who take the time to think about it would agree that a chart sheet holds a single chart. Most of the time, that's a true statement. However, it's certainly possible to store multiple charts on a single chart sheet. In fact, Excel lets you do this directly. If you activate an embedded chart and then choose Chart Location, Excel displays its Chart Location dialog box. If you select the As New Sheet option and specify an existing chart sheet as the location, the chart will appear...
The Applybuttonclick Procedure In The Userform Code Module
This procedure is executed when the Apply button is clicked. It does some error checking and then calls the CreateWorkRange function to ensure that empty cells are not included in the cells to be processed. See the upcoming section, Making the Text Tools utility efficient. The ApplyButton_Click procedure also calls the SaveForUndo procedure, which saves the current data in case the user needs to undo the operation. See Implementing Undo, later in this chapter. The procedure then uses a Select...
Background for Text Tools
Excel has many text functions that can manipulate text strings in useful ways. For example, you can uppercase the text in a cell, delete characters from text, remove spaces, and so on. But to perform any of these operations, you need to write formulas, copy them, convert the formulas to values, and then paste the values over the original text. In other words, Excel doesn't make it particularly easy to modify text. Wouldn't it be nice if Excel had some text manipulation tools that didn't require...
Toolbar files
Excel stores toolbar and menu bar configurations in an XLB file. When you exit Excel 2003, the current toolbar configuration is saved in a file named Excelll. xlb. The exact location and name of this file varies with the version of Excel, so search your hard drive for .xlb, and you'll find it. This file contains information regarding the position and visibility of all custom toolbars and custom menu bars, plus modifications that you've made to built-in toolbars or menu bars. The Excel 2003 Help...
Using a text box for help
Using a text box to display help information is also easy to implement. Simply create a text box by using the Text Box button on the Drawing toolbar, enter the help text, and format it to your liking. Figure 24-1 shows an example of a text box set up to display help information. Current Record 2 irst Record to Print 3 Last Record to Print 6 t easy to print form letters for Elephants R Us. Specifying What to Print Enter the first record to be printed in cell C4, and the last record to be printed...
Recording Chart Macros
Perhaps the best way to become familiar with the Chart object model is to turn on the macro recorder while you create and manipulate charts. Even though the macro recorder tends to generate lots of extraneous and inefficient code, the recorded code will still give you insights regarding the objects, properties, and methods that you need to know. Excel's macro recorder always activates a chart and then uses the ActiveChart property to return the actual Chart object. In Excel, it's not necessary...
How can I print the workbooks full path and filename in a page header
If you use Excel 2002 or later, you can take advantage of a new feature in the Page Setup dialog box. When this dialog is displayed, click the Header Footer tab and click Custom Header. You'll find a new icon that inserts the code to print the full path and filename of the workbook. Note, however, that if the workbook has not been saved, the path name might be incorrect. It uses the default workbook path. For older versions of Excel, you need to use a VBA macro and take advantage of the...
Protecting formulas from being overwritten
In many cases, you might want to protect your formulas from being overwritten or modified. To do so, perform the following steps 1. Select the cells that may be overwritten. 2. Choose Format Cells and then click the Protection tab of the Format Cells dialog box. 3. In the Protection tab, clear the Locked check box. 4. Click OK to close the Format Cells dialog box. 5. Choose Tools Protection Protect Sheet to display the Protect Sheet dialog box, as shown in Figure 2-14. If you use a version...
Deleting a command bar using VBA
To delete a custom toolbar, use the Delete method of the CommandBar object. The following instruction deletes the toolbar named MyToolbar If the toolbar doesn't exist, the instruction generates an error. To avoid the error message when you attempt to delete a toolbar that might or might not exist, the simplest solution is to ignore the error. The following code deletes MyToolbar if it exists. If it doesn't exist, no error message is displayed. On Error Resume Next On Error GoTo 0 Another...
Counting cells between two values
The following function, named COUNTBETWEEN, returns the number of values in a range first argument that fall between values represented by the second and third arguments Function COUNTBETWEENCInRange, numl, num2 As Long ' Counts number of values between numl and num2 With Application.WorksheetFunction If numl lt num2 Then COUNTBETWEEN .CountIf InRange, gt amp numl -.CountIf InRange, gt amp num2 COUNTBETWEEN .CountIfCInRange, gt amp num2 -.CountIfCInRange, gt amp numl Note that this function...
Pausing a macro to get a userselected range
You can create a macro that pauses while the user specifies a range of cells. The procedure in this section describes how to do this with Excel's InputBox method. Do not confuse Excel's InputBox method with VBA's InputBox function. Although these two functions have the same name, they are not the same. The Sub procedure that follows demonstrates how to pause a macro and let the user select a cell Prompt Select a cell for the output. Title Select a cell ' Display the Input Box On Error Resume...
Using the FileSystemObject object
The FileSystemObject object is a member of the Windows Scripting Host and provides access to a computer's file system. This object is often used in script-oriented Web pages for example, VBScript and JavaScript and can be used with Excel 2000 and later versions. The Windows Scripting Host is often used as a way to spread computer viruses. Consequently, the Windows Scripting Host is disabled on many systems. Therefore, use caution if you are designing an application that will be used on many...
Accommodating keyboard users
Many users prefer to navigate through a dialog box by using the keyboard The Tab and Shift Tab keystrokes cycle through the controls, and pressing a hot key operates the control. To make sure that your dialog box works properly for keyboard users, you must be mindful of two issues tab order and accelerator keys. The tab order determines the sequence in which the controls are activated when the user presses Tab or Shift Tab. It also determines which control has the initial focus. If a user is...
Adding a control to a command bar
To add a new control to a CommandBar object, use the Add method of the Controls collection object. The following instruction adds a new control to a toolbar named MyToolbar. Its Type property is set to the msoControlButton constant, which creates a standard button. Type msoControlButton The toolbar button added in the preceding instruction is just a blank button clicking it has no effect. Most of the time, you'll want to set some properties when you add a new button to a toolbar. The following...
Adding menu items to a menu
In the previous example under Adding a new menu to a menu bar, I demonstrate how to add a menu to a menu bar. Listing 23-2 adds to the original procedure and in doing so, demonstrates how to add menu items to the new menu. Listing 23-2 Adding Selections and Submenu Items to the Budgeting Menu Dim HelpMenu As CommandBarControl Dim NewMenu As CommandBarPopup Dim Menultem As CommandBarControl Dim Submenuitem As CommandBarButton ' Delete the menu if it already exists Call DeleteMenu Set HelpMenu If...
Showing a progress indicator without using a MultiPage control
The example in this section is similar to the example in the previous section. However, this technique is simpler because it doesn't use a MultiPage control. Rather, the progress indicator is stored at the bottom of the UserForm but the UserForm's height is reduced such that the progress indicator controls are not visible. When it's time to display the progress indicator, the UserForm's height is increased, which makes the progress indicator visible. The companion CD-ROM contains an example...
Frequently Asked Questions about Excel Programming
If you like to cruise the Internet, you're undoubtedly familiar with FAQs lists of frequently asked questions and their answers about a particular topic. FAQs are prevalent in the Usenet discussion groups and are posted in an attempt to reduce the number of messages that ask the same questions over and over again. They rarely serve their intended purpose, however, because the same questions keep appearing despite the FAQs. I've found that people tend to ask the same questions about Excel...
Getting a list of fonts
If you need to get a list of all installed fonts, you'll find that Excel does not provide a direct way to retrieve that information. One approach is to read the font names from the Font control on the Formatting toolbar. The following procedure displays a list of the installed fonts in column A of the active worksheet. It uses the FindControl method to locate the Font control on the Formatting toolbar. If this control is not found for example, it was removed by the user , a temporary CommandBar...
Where to put event handler procedures
VBA newcomers often wonder why their event handler procedures aren't being executed when the corresponding event occurs. The answer is almost always because these procedures are located in the wrong place. In the Visual Basic Editor VBE window, each project is listed in the Projects window. The project components are arranged in a collapsible list, as shown in Figure 19-1. Each of the following components has its own code module Sheet objects for example, Sheet1, Sheet2, and so on . Chart...