Formula Errors

It's not uncommon to enter a formula and receive an error in return. One possibility is that the formula you entered is the cause of the error. Another possibility is that the formula refers to a cell that has an error value. The latter scenario is known as the ripple effect - a single error value can make its way to lots of other cells that contain formulas that depend on the cell. The tools in the Formulas Formula Auditing group can help you trace the source of formula errors. Table 3-2 lists...

Creating Wizards

Many applications incorporate wizards to guide users through an operation. Excel's Text Import Wizard is a good example. A wizard is essentially a series of dialog boxes that solicit information from the user. Usually, the user's choices in earlier dialog boxes influence the contents of later dialog boxes. In most wizards, the user is free to go forward or backward through the dialog box sequence or to click the Finish button to accept all defaults. You can create wizards by using VBA and a...

Solving Problems with Excel

In the previous sections, I cover the basic concept of a spreadsheet application, discuss the end users and developers of such applications, and even attempt 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 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...

An Enhanced Data Form

Next is one of the more complex UserForms that you'll encounter. I designed it as a replacement for Excel's built-in Data Form, which is shown in Figure 15-26. Note Displaying Excel's Data Form is not easy in Excel 2007. You need to use the Excel Options dialog box, click the Customization tab, and add the Form command from the Commands Not in the Ribbon group. Then, the Form command will appear on your Quick Access Toolbar. Like Excel's Data Form, my Enhanced Data Form works with a list in a...

Foryoureyesonly spreadsheets

As the name implies, no one except you - the creator - will ever see or use the spreadsheets that fall into this category. An example of this type might be a file in which you keep information relevant to your income taxes. You open the file whenever a check comes in the mail, you incur an expense that can be justified as business, you buy tax-deductible Girl Scout cookies, and so on. Another example is a spreadsheet that you use to keep track of your employees' time records (sick leave,...

Data storage and access spreadsheets

A large percentage of Excel workbooks consist of one or more database tables (sometimes known as lists). These are used to track just about anything you can think of. Most people find that it's much easier to view and manipulate data in a spreadsheet than it is using normal database software. If the tables are set up properly, they can be summarized with a pivot table. In Chapter 10, you'll find everything you need to know about creating custom worksheet functions, including lots of examples.

Classifying spreadsheet users

Over the years, I've found that it's often useful to classify people who use spreadsheets (including both developers and end users) along two dimensions their degree of experience with spreadsheets and their interest in learning about spreadsheets. To keep things simple, each of these two dimensions has three levels. These levels can be combined in nine combinations, which are shown in Table 5-1. In reality, only seven segments are worth thinking about because both moderately experienced and...

Creating Megaformulas

Often, spreadsheets require intermediate formulas to produce a desired result. In other words, a formula may depend on other formulas, which in turn depend on other formulas. After you get all these formulas working correctly, it's often possible to eliminate the intermediate formulas and use what I refer to as a single megaformula instead. The advantages You use fewer cells (less clutter), the file size is smaller, and recalculation may even be a bit faster. The main disadvantage is that the...

Text file formats

When you attempt to load a text file into Excel, the Text Import Wizard might kick in to help you specify how you want the file retrieved. Tip To bypass the Text Import Wizard, press Shift when you click OK in the Open dialog box. Table 4-3 lists the text file types supported by Excel 2007. All text file formats are limited to a single worksheet. Columns are delimited with a comma, and rows are delimited with a carriage return. Excel supports subtypes for Macintosh and MS-DOS. Columns are...

Chart sheets

A chart sheet normally holds a single chart. Many users ignore chart sheets, preferring to store charts on the worksheet's drawing layer. Using chart sheets is optional, but they make it a bit easier to print a chart on a page by itself, and they are especially useful for presentations. Figure 2-1 shows a pie chart on a chart sheet. Figure 2-1 A pie chart on a chart sheet.

Testing the application

How many times have you used a commercial software application, only to have it bomb out on you at a crucial moment Most likely, the problem was caused by insufficient testing that didn't catch all the bugs. All nontrivial software has bugs, but in the best software, the bugs are simply more obscure. As you'll see, you sometimes must work around the bugs in Excel to get your application to perform properly. After you create your application, you need to test it. This is one of the most crucial...

About Excel Utilities

A utility isn't an end product, such as a quarterly report. Rather, it's a tool that helps you produce an end product. An Excel utility is (almost always) an add-in that enhances Excel with new features or capabilities. Excel is a great product, but many users soon develop a wish-list of features that they would like to see added to the software. For example, some users prefer to turn off the dotted-line page break display, and they want a feature that toggles this attribute so that they don't...

Defining data types

VBA makes life easy for programmers because it can automatically handle all the details involved in dealing with data. Not all programming languages make it so easy. For example, some languages are strictly typed, which means that the programmer must explicitly define the data type for every variable used. Data type refers to how data is stored in memory - as integers, real numbers, strings, and so on. Although VBA can take care of data typing automatically, it does so at a cost slower...

Spreadsheet Applications

For the 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 without extensive training. According to this definition, most of the spreadsheet files that you've developed probably don't qualify as spreadsheet applications. You may have dozens or hundreds of spreadsheet files on your hard drive, but it's a safe bet that most of them aren't really designed for others to...

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 in learning more about spreadsheets make up a large percentage of all spreadsheet users, probably the largest group of all. These are the people who need to...

About the Registry

The Windows Registry is essentially a central hierarchical database that is used by the operating system and by application software. The Registry first appeared in Windows 95 and replaces the old INI files that stored Windows and application settings. CROSS- Your VBA macros can also read and write information to the Registry. Refer to REFERENCE Chapter 11 for details. You can use the Registry Editor program to browse the Registry - and even edit its contents if you know what you're doing. The...

Array formula pros and cons

The advantages of using array formulas rather than single-cell formulas include the following They can sometimes use less memory. They can make your work much more efficient. They can eliminate the need for intermediate formulas. They can enable you to do things that would be difficult or impossible otherwise. A few disadvantages of using array formulas are the following Using many complex array formulas can sometimes slow your spreadsheet recalculation time to a crawl. They can make your...

Selecting Ranges from a User Form

Many of Excel's built-in dialog boxes allow the user to specify a range. For example, the Goal Seek dialog box asks the user to select two single-cell ranges. The user can either type the range names directly or use the mouse to point and click in a sheet to make a range selection. Your UserForms can also provide this type of functionality, thanks to the RefEdit control. The RefEdit control doesn't look exactly like the range selection control used in Excel's built-in dialog boxes, but it works...

Other file formats

Table 4-5 lists the other file types supported by Excel 2007. Excel 2007 no longer supports round-tripping for HTML files. Also known as Archived Web Page. The only browser that can display these files is Microsoft Internet Explorer. The file format originated by Adobe. Requires a free add-in from Microsoft. Microsoft's alternative to Adobe's PDF. Requires a free add-in from Microsoft. A workspace file is a special file that contains information about an Excel workspace. For example, if you...

Starting Excel

Excel can be started in various ways, depending on how it's installed. These include clicking an icon on the Desktop, using the Windows Start button, and double-clicking a file associated with the Excel application. All methods ultimately launch the excel.exe executable file. When Excel starts, it performs the following actions It reads its settings stored in the Windows Registry. It opens the .xlb menu toolbar customization file. It opens all add-ins that are installed that is, those that are...

Using the Editor Format tab

Figure 7-9 shows the Editor Format tab of the Options dialog box. The options on this tab control the appearance of the VBE itself. Breeipwtt Tem Comment Text KeywddTert Figure 7-9 The Editor Format tab of the Options dialog box. The Code Colors option lets you set the text color foreground and background and the indicator color displayed for various elements of VBA code. This is largely a matter of individual preference. Personally, I find the default colors to be just fine. But for a change...

Looping through a selected range efficiently

A common task is to create a macro that evaluates each cell in a range and performs an operation if the cell meets a certain criterion. The procedure that follows is an example of such a macro. The ColorNegative procedure sets the cell's background color to red for cells that contain a negative value. For non-negative value cells, it sets the background color to none. Note This example is for educational purposes only. Using Excel's conditional formatting is a much better approach. Sub...

Referencing User Form Controls

When working with controls on a UserForm, the VBA code is usually contained in the code window for the UserForm. You can also refer to UserForm controls from a general VBA module. To do so, you need to qualify the reference to the control by specifying the UserForm name. For example, consider the following procedure, which is located in a VBA module. It simply displays the UserForm named userForml. Assume that UserForml contains a text box named TextBoxi , and you want to provide a default...

Processing a series of files

One common use for macros, of course, is to repeat an operation a number of times. The example in this section demonstrates how to execute a macro on several different files stored on disk. This example -which may help you set up your own routine for this type of task - prompts the user for a file specification and then processes all matching files. In this case, processing consists of importing the file and entering a series of summary formulas that describe the data in the file. In previous...

Dissecting a file

In this section, I describe the various parts within a typical Excel XLSM macro-enabled workbook file. The workbook, named sample. xlsm, is shown in Figure 4-4. It has one worksheet, one chart sheet, and a simple VBA macro. The worksheet contains a table, a button from the Forms controls , a SmartArt diagram, and a photo of a flower. CD The sample. xlsm workbook is available on the companion CD-ROM. To view the innards of an Excel 2007 file, you need to open an Explorer window and add a ZIP...

Determining default printer information

The example in this section uses a Windows API function to return information about the active printer. The information is contained in a single text string. The example parses the string and displays the information in a more readable format. Private Declare Function GetProfileStringA Lib kernel32 ByVal lpAppName As String, ByVal lpKeyName As String, ByVal lpDefault As String, ByVal lpReturnedString As _ String, ByVal nSize As Long As Long Dim strLPT As String 255 Dim Result As String Call...

Documenting the development effort

Putting a spreadsheet application together is one thing. Making it understandable for other people is another. As with traditional programming, it's important that you thoroughly document your work. Such documentation helps you if you need to go back to it and you will , and it helps anyone else whom you might pass it on to. Tip You might want to consider a couple of things when you document your project. For example, if you were hired to develop an Excel application, you might not want 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 VB Project. 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...

The Offset property

The Offset property, like the Range and Cells properties, also returns a Range object. But unlike the other two methods that I discussed, the Offset property applies only to a Range object and no other class. Its syntax is as follows obj ect.Offset rowOffset, columnOffset The Offset property takes two arguments that correspond to the relative position from the upper-left cell of the specified Range object. The arguments can be positive down or to the right , negative up or to the left , or...

Thinking in Terms of Objects

When you are developing applications with Excel especially when you are dabbling with Visual Basic for Applications - VBA , it's helpful to think in terms of objects, or Excel elements that you can manipulate manually or via a macro. Here are some examples of Excel objects A range or a table in a worksheet A ListBox control on a UserForm a custom dialog box A chart embedded in a worksheet A particular data point in a chart You may notice that an object hierarchy exists here The Excel object...

Randomizing a range

The RangeRandomize function, which follows, accepts a range argument and returns an array that consists of the input range - in random order Dim V As Variant, ValArray As Variant Dim Temp1 As Variant, Temp2 As Variant Dim RCount As Integer, CCount As Integer ' Return an error if rng is too large CellCount rng.Count If CellCount gt 1000 Then RangeRandomize CVErr xlErrNA Exit Function ReDim ValArray 1 To 2, 1 To CellCount Fill ValArray with random numbers and values from rng For i 1 To CellCount...

Writing the Sort procedure

It was time to sort the SheetNames array. One option was to insert the sorting code in the SortSheets procedure, but I thought a better approach was to write a general-purpose sorting procedure that I could reuse with other projects sorting arrays is a common operation . You might be a bit daunted by the thought of writing a sorting procedure. The good news is that it's relatively easy to find commonly used routines that you can use or adapt. The Internet, of course, is a great source for such...

Using a List Box to activate a sheet

The example in this section is just as useful as it is instructive. This example uses a multicolumn ListBox to display a list of sheets within the active workbook. The columns represent The type of sheet worksheet, chart, or Excel 5 95 dialog sheet The number of nonempty cells in the sheet Whether the sheet is visible Figure 14-17 shows an example of the dialog box. Figure 14-17 This dialog box lets the user activate a sheet. Figure 14-17 This dialog box lets the user activate a sheet. The code...

Lotus

Envious of VisiCalc's success, a small group of computer freaks at a start-up company in Cambridge, Massachusetts, refined the spreadsheet concept. Headed by Mitch Kapor and Jonathan Sachs, the company designed a new product and launched the software industry's first full-fledged marketing blitz. I remember seeing a large display ad for 1-2-3 in The Wall Street Journal. It was the first time that I'd ever seen software advertised in a general interest publication. Released in January 1983,...

Methods of a Comment object

Table 7-2 shows the methods that you can use with a Comment object. Again, these methods perform common operations that you may have performed manually with a comment at some point but you probably never thought of these operations as methods. Table 7-2 METHODS OF A COMMENT OBJECT Table 7-2 METHODS OF A COMMENT OBJECT Returns a Comment object that represents the next comment in the worksheet. Returns a Comment object that represents the previous comment in the worksheet. Returns or sets the...

Charts

Excel is perhaps the most commonly used application in the world for creating charts. As I mention earlier in this chapter, you can store charts on a chart sheet or float them on a worksheet. NEW Excel 2007 still hasn't introduced any new chart types, but charts are easier to create, and they most definitely look much better. Figure 2-22 shows an Excel 2007 chart that uses some of the new formatting options. w w m t i im m V gt hhjhwh WW who ust in Figure 2-22 Excel 2007 charts have improved in...

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 about active worksbooks, worksheets, and cells, 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...

A function with one argument

This section describes a function for sales managers who need to calculate the commissions earned by their sales forces. The calculations in this example are based on the following table Note that the commission rate is nonlinear and also depends on the month's total sales. Employees who sell more earn a higher commission rate. There are several ways to calculate commissions for various sales amounts entered into a worksheet. If you're not thinking too clearly, you might waste lots of time and...

Emulating Excels SUM Function

In this section, I present a custom function called MySum. Unlike the simplesum function listed in the previous section, the MySum function emulates Excel's SUM function almost perfectly. Before you look at the code for MySum, take a minute to think about the Excel SUM function. It is, in fact, very versatile. It can have as many as 255 arguments even missing arguments , and the arguments can be numerical values, cells, ranges, text representations of numbers, logical values, and even embedded...

The Module VBA module

Vbmodeless

The Module1 VBA module contains the declarations, a simple procedure that kicks off the utility, and a procedure that handles the undo operation. DECLARATIONS IN THE MODULE1 VBA MODULE Following are the declarations at the top of the Module1 module Public Const APPNAME As String Text Tools Utility Public Const PROGRESSTHRESHOLD 2000 Public UserChoices 1 To 8 As Variant 'stores user's last choices Public UndoRange As Range ' For undoing Public UserSelection As Range 'For undoing I declare a...

Planning an Application That Meets User Needs

After you determine the end users' needs, it's very tempting to jump right in and start fiddling around in Excel. Take it from someone who suffers from this problem Try to restrain yourself. Builders don't construct a house without a set of blueprints, and you shouldn't build a spreadsheet application without some type of plan. The formality of your plan depends on the scope of the project and your general style of working, but you should spend at least some time thinking about what you're...

Using the File Dialog object to select a directory

If users of your application all use Excel 2002 or later, you might prefer to use a much simpler technique that uses the FileDialog object. The following procedure displays a dialog box see Figure 12-10 that allows the user to select a directory. The selected directory name or Canceled is then displayed by using the MsgBox function. Figure 12-10 Using the FileDialog object to select a directory. Figure 12-10 Using the FileDialog object to select a directory. Sub GetAFolder2 ' For Excel 2002 and...

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

Errorhandling examples

The first example demonstrates an error that can safely be ignored. The SpecialCells method selects cells that meet a certain criterion. The SpecialCells method is equivalent to choosing the Home Editing Find amp Select lt Go To Special command. The Go To Special dialog box provides you with a number of choices. For example, you can select cells that contain a numeric constant non-formula . In the example that follows, the SpecialCells method selects all the cells in the current range selection...

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

Text Tools The Anatomy of a Utility

In this section, I describe an Excel utility that I developed and that is part of my Power Utility Pak add-in . The Text Tools utility enables the user to manipulate text in a selected range of cells. Specifically, this utility enables the user to do the following Change the case of the text uppercase, lowercase, proper case, sentence case, or toggle case . Add characters to the text at the beginning, to the end, or at a specific character position . Remove characters from the text from the...

Counting visible cells in a range

The CountVisible function that follows accepts a range argument and returns the number of nonempty visible cells in the range. A cell is not visible if it's in a hidden row or a hidden column. Function CountVisible rng ' Counts visible cells Dim CellCount As Long Dim cell As Range Application.Volatile CellCount 0 Set rng Intersect rng.Parent.UsedRange, rng For Each cell In rng Not cell.EntireColumn.Hidden Then _ CellCount CellCount 1 End If Next cell CountVisible CellCount End Function This...

Selecting cells by value

The example in this section demonstrates how to select cells based on their value. Oddly enough, Excel does not provide a direct way to perform this operation. My selectByValue procedure follows. In this example, the code selects cells that contain a negative value, but this can be changed easily. Dim Cell As Object Dim FoundCells As Range Dim WorkRange As Range If TypeName Selection lt gt Range Then Exit Sub Set WorkRange ActiveSheet.UsedRange Set WorkRange Application.Intersect Selection,...

Modeless Dialog

Most dialog boxes that you encounter are modal dialog boxes, which must be dismissed from the screen before the user can do anything with the underlying application. Some dialogs, however, are modeless, which means the user can continue to work in the application while the dialog box is displayed. To display a modeless UserForm, use a statement such as The word vbModeless is a built-in constant that has a value of 0. Therefore, the following statement works identically Figure 15-1 shows a...

Overview

In this chapter, I attempt to describe 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...

Adding new ActiveX controls

UserForms can use other ActiveX controls developed by Microsoft or other vendors. To add an additional ActiveX control to the Toolbox, right-click the Toolbox and select Additional Controls. This will display the dialog box shown in Figure 13-15. Q MiciiosdH Anirstion Conlral tO P4 ciosotl Corwnon C'sloc CciM. vnfEHn-S MiamiA Caofiiir CnrKiol. t skir. fi 0 Hici sifl EJfffctAniratior. SpnLe Munosoll flreclAn racier, Slructned Qiapti MeiOitf D eelAnnij KnWp JJi.veiflCirtlro k 1 u gt tficiosoft...

Select Case constructs

The Select Case construct is useful for choosing among three or more options. This construct also works with two options and is a good alternative to If-Then-Else. The syntax for Select Case is as follows Select Case testexpression Case expressionlist-n The following example of a Select Case construct shows another way to code the GreetMe examples that I presented in the preceding section Dim Msg As String Select Case Time Case Is lt 0.5 Msg Good Morning Case 0.5 To 0.75 Msg Good Afternoon Case...

Creating Multiple Pivot Tables

The final example creates a series of pivot tables that summarize data collected in a customer survey. That data is stored in a worksheet database see Figure 17-6 and consists of 150 rows. Each row contains the respondent's sex plus a numerical rating using a 1-5 scale for each of the 14 survey items. Figure 17-6 Creating a series of pivot tables will summarize this survey data. CD-ROM Figure 17-6 Creating a series of pivot tables will summarize this survey data. CD-ROM This workbook, named...

Creating an embedded chart

In Excel 2007, a ChartObject is a special type of Shape object. Therefore, it's a member of the Shapes collection. To create a new chart, use the AddChart method of the Shapes collection. The following statement creates an empty embedded chart The AddChart method can use five arguments all are optional Type The type of chart. If omitted, the default chart type is used. Constants for all of the chart types are provided for example, xlArea, xlColumnClustered, and so on . Left The left position of...

Declaring dynamic arrays

A dynamic array doesn't have a preset number of elements. You declare a dynamic array with a blank set of parentheses Before you can use a dynamic array in your code, however, you must use the ReDim statement to tell VBA how many elements are in the array. This is often done by using a variable, the value of which isn't known until the procedure is executing. For example, if the variable contains a number, you can define the array's size by using this statement You can use the ReDim statement...

Database front ends

Increasingly, spreadsheet products are used to access external databases. Spreadsheet users can access data stored in external files, even if they come in a variety of formats, by using tools that Excel provides. When you create an application that does this, it's sometimes referred to as an executive information system, or EIS. This sort of system combines data from several sources and summarizes it for users. Accessing external databases from a spreadsheet often strikes fear in the hearts of...

Spaghetti applications

An all-too-common type of spreadsheet is what I call a spaghetti application. The term stems from the fact that the parts of the application are difficult to follow, much like a plate of spaghetti. Most of these spreadsheets begin life as a reasonably focused, singleuser application. But over time, they are passed along to others who make their own modifications. As requirements change and employees come and go, new parts are added and others are ignored. Before too long, the original purpose...

About the Dialogs collection

The Dialogs collection of the Application object consists of more than 200 members that represent most of Excel's built-in dialog boxes. Each has a predefined constant to make it easy to specify the dialog box that you need. For example, Excel's Go To dialog box is represented by the constant Use the Show method to actually display the dialog box. Here's an example that displays the Go To dialog box see Figure 12-11 Figure 12-11 This dialog box was displayed with a VBA statement. Figure 12-11...

Creating a Reverse Pivot Table

A pivot table is a summary of data in a table. But what if you have a summary table, and you'd like to create a table from it Figure 17-8 shows an example. Range B2 F14 contains a summary table - similar to a very simple pivot table. Columns I K contain a 48-row table created from the summary table. In the table, each row contains one data point, and the first two columns describe that data point. Figure 17-8 The summary table on the left will be converted to the table on the right. Figure 17-8...

Making the application bulletproof

If you think about it, it's fairly easy to destroy a spreadsheet. Erasing one critical formula or value can cause 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...

Selecting a Color in a User Form

The example in this section is a function that displays a dialog box similar in concept to the MyMsgBox function, presented earlier . The function, named GetAColor, returns a color value Function GetAColor As Variant UserForml.Show GetAColor ColorValue End Function You can use the GetAColor function with a statement like the following Executing this statement displays the UserForm. The user selects a color and clicks OK. The function then assigns the user's selected color value to the UserColor...

Introducing the Ribbon

If you've used Excel 2007 for more than a minute, you know that it has an entirely new UI. Menus and toolbars are gone, replaced with a brand new tab and Ribbon UI. Click a tab along the top that is, a word such as Home, Insert, Page Layout , and the Ribbon displays the commands for that tab. Office 2007 is the first software in history to use this new interface, so the jury is still out regarding how it will be accepted. The appearance of the commands on the Ribbon varies, depending on the...

The Chart object model

When you first start exploring the object model for a Chart object, you'll probably be very confused -which is not surprising the object model is very confusing. It's also very deep. For example, assume that you want to change the title displayed in an embedded chart. The top-level object, of course, is the Application object Excel . The Application object contains a Workbook object, and the Workbook object contains a Worksheet object. The Worksheet object contains a ChartObject object, which...

Using an External Control

The example in this section uses the Microsoft Date and Time Picker Control. Although this is not an Excel control it's installed with Windows , it works fine in a UserForm. To make this control available, add a UserForm to a workbook and follow these steps 2. Right-click the Toolbox and choose Additional Controls. Select View Toolbox if the Toolbox is not visible. 3. In the Additional Controls dialog box, scroll down and place a check mark next to Microsoft Date and Time Picker Control 6.0....

The Cells property

Another way to reference a range is to use the Cells property. You can use the Cells property, like the Range property, on Worksheet objects and Range objects. Check the Help system, and you see that the Cells property has three syntaxes object.Cells rowIndex, columnlndex object.Cells rowIndex object.Cells I'll give you some examples that demonstrate how to use the Cells property. The first example enters the value 9 into cell A1 on Sheet1. In this case, I'm using the first syntax, which...

Task pane

Excel 2002 introduced a new UI element known as the task pane. This is a multipurpose user interface element that is normally docked on the right side of Excel's window but you can drag it anywhere . The task pane is used for a variety of purposes, including displaying the Office Clipboard, displaying a pivot table field list, inserting clip art, providing research assistance, and mapping eXtensible Markup Language XML data. Figure 2-13 shows the Clip Art task pane. CtpJtm Office onine foi Tps...

Making the data form accessible

For some reason, the command to access the data form is not in the Excel 2007 Ribbon. In order to access the data form from Excel's user interface, you must add it to your Quick Access Toolbar QAT 1. Right-click the QAT and select Customize Quick Access Toolbar. This displays the Customization panel of the Excel Options dialog box. 2. In the Choose Commands From drop-down, select Commands Not in the Ribbon. 3. In the list box on the left, select Form. 4. Click the Add button to add the selected...

A function that returns a VBA array

VBA includes a useful function called Array. The Array function returns a variant that contains an array that is, multiple values . If you're familiar with array formulas in Excel, you have a head start on understanding VBA's Array function. You enter an array formula into a cell by pressing Ctrl Shift Enter. Excel inserts curly braces around the formula to indicate that it's an array formula. CROSS- See Chapter 3 for more details on array formulas. Note It's important to understand that the...

Creating templates

Excel supports three types of templates The default workbook template Used as the basis for new workbooks. This file is named The default worksheet template Used as the basis for new worksheets that are inserted into a workbook. This file is named sheet.xltx. Custom workbook templates Usually, these are ready-to-run workbooks that include formulas, but they can be as simple or as complex as you like. Typically, these templates are set up so that a user can simply plug in values and get...

Creating workbook templates

The book.xltx and sheet.xltx templates discussed in the preceding section are two special types of templates that determine default settings for new workbooks and new worksheets. This section discusses other types of templates, referred to as workbook templates, which are simply workbooks that you set up as the basis for new workbooks or worksheets. Why use a workbook template The simple answer is that it saves you from repeating work. Assume that you create a monthly sales report that consists...

Adding sound to your applications

The example in this section adds some sound capability to Excel. Specifically, it enables your application to play WAV or MIDI files. For example, you might like to play a short sound clip when a dialog box is displayed. Or maybe not. In any case, if you want Excel to play WAV or MIDI files, this section has what you need. CD- The examples in this section are available on the companion CD-ROM in a file named The following example contains the API function declaration plus a simple procedure to...

Excel file formats

Excel 2007 uses a new default file format. However, it can still read and write older Excel file formats. Iip To change the default file save setting, choose Office Excel Options and click the Save tab in the Excel Options dialog box. You'll find a drop-down list that lets you select the default file format. Table 4-2 lists the Excel file types that Excel 2007 supports. Keep in mind that an Excel workbook or add-in file can have any extension that you like. In other words, these files don't...

User Form Checklist

Before you unleash a UserForm on end users, be sure that everything is working correctly. The following checklist should help you identify potential problems. Are similar controls the same size Are the controls evenly spaced Is the dialog box too overwhelming If so, you may want to group the controls by using a MultiPage control. Can every control be accessed with a hot key Are any of the hot keys duplicated Is the tab order set correctly Will your VBA code take appropriate action if the user...

Functions with no argument

Like Sub procedures, Function procedures need not have arguments. Excel, for example, has a few built-in functions that don't use arguments, including RAND, TODAY, and NOW. You can create similar functions. This section contains examples of functions that don't use an argument. CD- A workbook that contains these functions is available on the companion CD-ROM. The file ROM s named ' no argument, xlsm. Here's a simple example of a function that doesn't use an argument. The following function...

Displaying Excels BuiltIn Dialog Boxes

Code that you write in VBA can execute many of Excel's Ribbon commands. And, if the command leads to a dialog box, your code can make choices in the dialog box although the dialog box itself isn't displayed . For example, the following VBA statement is equivalent to choosing the Home Editing Find amp Select Go To command, specifying range A1 C3, and clicking OK. But the Go To dialog box never appears which is what you want . Application.Goto Reference Range A1 C3 In some cases, however, you may...

Scoping variables

A variable's scope determines in which modules and procedures the variable can be used. Table 8-2 lists the three ways in which a variable can be scoped. How a Variable with This Scope Is Declared Include a Dim or Static statement within the procedure. Include a Dim or Private statement before the first procedure in a module. Include a Public statement before the first procedure in a module. I discuss each scope further in the following sections. A Note about the Examples in This Chapter A Note...

Summing formula examples

Table 3-4 shows a number of formula examples that demonstrate a variety of summing techniques. Sum of Sales in which Month Jan SUMIF Month,Jan,Sales SUMIF Month,Feb,Sales Sum of Sales in which Month Jan or Feb Sum of Sales in which Month Jan and Region North Sum of Sales in which Month Jan and Region North Excel 2007 only An array formula that returns the sum of Sales in which Month Jan and Region North Sum of Sales in which Month Jan and Region lt gt North Excel 2007 only An array formula that...

A function that returns an error value

In some cases, you might want your custom function to return a particular error value. Consider the RemoveVowels function, which I presented earlier in this chapter Function RemoveVowels Txt As String ' Removes all vowels from the Txt argument Dim i As Long RemoveVowels For i 1 To Len Txt If Not UCase Mid Txt, i, 1 Like AEIOU Then RemoveVowels RemoveVowels amp Mid Txt, i, 1 End If Next i End Function When used in a worksheet formula, this function removes the vowels from its single-cell...

Adjusting User Form Controls

After a control is placed in a UserForm, you can move and resize the control by using standard mouse techniques. Tip You can select multiple controls by Shift-clicking or by clicking and dragging to lasso a group of controls. A UserForm can contain vertical and horizontal grid lines displayed as dots that help you align the controls that you add. When you add or move a control, it snaps to the grid to help you line up the controls. If you don't like to see these grid lines, you can turn them...

Working with constants

A variable's value may change while a procedure is executing that's why it's called a variable . Sometimes, you need to refer to a named value or string that never changes a constant. Using constants throughout your code in place of hard-coded values or strings is an excellent programming practice. For example, if your procedure needs to refer to a specific value such as an interest rate several times, it's better to declare the value as a constant and use the constant's name rather than its...

The Range property

The Range property returns a Range object. If you consult the Help system for the Range property, you learn that this property has two syntaxes object.Range cell1 object.Range cell1, cell2 Working with merged cells can be tricky. If a range contains merged cells, you may need to take some special action with the macros. For example, if cells A1 D1 are merged, the statement that follows selects columns A through D not just column B, as you might expect I don't know if this unexpected behavior is...

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. In previous editions of this book, I presented a technique that read the font names from the Font control on the Formatting toolbar. Excel 2007 no longer has a Formatting toolbar, and it's impossible to access the controls on the Ribbon using VBA. For compatibility purposes, however, Excel 2007 still supports the old CommandBar properties and methods - it's just...

Counting selected cells

You can create a macro that works with the selected range of cells. Use the Count property of the Range object to determine how many cells are contained in a range selection or any range, for that matter . For example, the following statement displays a message box that contains the number of cells in the current selection With the larger worksheet size in Excel 2007, the Count property can generate an error. The Count property uses the Long data type, so the largest value that it can store is...

Determining the type of selected range

Excel supports several types of range selections A contiguous range of cells Any combination of the above that is, a multiple selection As a result, when your VBA procedure processes a user-selected range, you can't make any presumptions about what that range might be. In the case of a multiple range selection, the Range object comprises separate areas. To determine whether a selection is a multiple selection, use the Areas method, which returns an Areas collection. This collection represents...

The Excel Input Box method

Using Excel's InputBox method offers three advantages over VBA's InputBox function You can specify the data type returned. The user can specify a worksheet range by dragging in the worksheet. Input validation is performed automatically. The syntax for the Excel InputBox method is InputBox Prompt ,Title ,Default ,Left ,Top , HelpFile, HelpContextID ,Type Prompt Required. The text displayed in the input box. Title Optional. The caption in the input box window. Default Optional. The default value...

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 and display the new list in another ListBox. For an example of this situation, check out the Customization tab of the Excel Options dialog box. 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 selected item from the list on the right. A check box...

The Comments collection

Recall that a collection is a group of like objects. Every worksheet has a Comments collection, which consists of all Comment objects on the worksheet. If the worksheet has no comments, this collection is empty. Comments appear in the collection based on their position in the worksheet Left-to-right and then top-to-bottom. For example, the following code refers to the first comment on Sheetl of the active workbook The following statement displays the text contained in the first comment on...

Installing the Enhanced Data Form addin

To try out the Enhanced Data Form, install the add-in 1. Copy the dataform2.xla file from the CD-ROM to a directory on your hard drive. 2. In Excel, choose Office Excel Options. 3. In the Excel Options dialog box, click the Add-Ins tab. 4. Select Excel Add-Ins from the Manage drop-down list and click Go to display the Add-Ins dialog box. 5. In the Add-Ins dialog box, click Browse and locate the dataform2 . xla in the directory from Step 1. After performing these steps, you can access the...

VBA Language Elements An Overview

In Chapter 7, I present an overview of objects, properties, and methods, but I don't tell you much about how to manipulate objects so that they do meaningful things. This chapter gently nudges you in that direction by exploring the VBA language elements, which are the keywords and control structures that you use to write VBA routines. To get the ball rolling, I start by presenting a simple VBA Sub procedure. The following code, which is stored in a VBA module, calculates the sum of the first...

Why is the file format important

The new open XML file formats for Microsoft Office represent a significant step for the computing community. For the first time, it's relatively easy to read and write Excel workbooks using software other than Excel. For example, it's possible to write a program to modify thousands of Excel workbook files without even opening Excel. Such a program could insert a new worksheet into every file. The programmer, of course, would need to have excellent knowledge of the XML file structures, but such...

Creating a standalone progress indicator

This section describes how to set up a standalone progress indicator - that is, one that is not initiated by displaying a UserForm - to display the progress of a macro. The macro simply clears the worksheet and writes 20,000 random numbers to a range of cells ' Inserts random numbers on the active worksheet Const RowMax As Integer 500 Const ColMax As Integer 40 Dim r As Integer, c As Integer If TypeName ActiveSheet lt gt Worksheet Then Exit Sub Cells r, c Int Rnd 1000 Next c Next r End Sub...

Determining multiple selections in a List Box

A ListBox's MultiSelect property can be any of three values 0 fmMultiSelectSingle Only one item can be selected. This is the default setting. 1 fmMultiSelectMulti Pressing the Space key or clicking selects or deselects an item in the list. 2 fmMultiSelectExtended Shift-clicking extends the selection from the previously selected item to th current item. You can also use Shift and one of the arrow keys to extend the selected items. If the ListBox allows multiple selections that is, if its...

The VBA Msg Box Function

VBA's MsgBox function is an easy way to display a message to the user or to get a simple response such as OK or Cancel . I use the MsgBox function in many of this book's examples as a way to display a variable's value. The official syntax for MsgBox is as follows context prompt Required. The text displayed in the message box. buttons Optional. A numeric expression that determines which buttons and icon are displayed in the message box. See Table 12-2. Table 12-2 CONSTANTS USED FOR BUTTONS IN...

Adding items to a List Box control

Before displaying a UserForm that uses a ListBox control, you'll probably need to fill the ListBox with items. Yo can fill a ListBox at design time using items stored in a worksheet range or at runtime using VBA to add the items to the ListBox. The two examples in this section presume that You have a UserForm named userForm1 . This UserForm contains a ListBox control named ListBox1 . The workbook contains a sheet named Sheet1 , and range A1 A12 contains the items to be displayed in th ListBox....

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. Like the GetOpenFilename method, all of the GetSaveAsFilename method's arguments are optional. FileFilter, FilterIndex, Title, ButtonText InitialFilename Optional. Specifies the suggested filename. FileFilter Optional. A string specifying file-filtering criteria. FilterIndex Optional....

Writing code to display the dialog box

Next, you add an ActiveX CommandButton to the worksheet. This button will execute a procedure that displays the UserForm. Here's how 1. Activate Excel. Alt F11 is the shortcut key combination. 2. Choose Developer Controls Insert and click CommandButton from the ActiveX Controls section. 3. Drag in the worksheet to create the button. If you like, you can change the caption for the worksheet CommandButton. To do so, right-click the button and choose CommandButton Object Edit from the shortcut...

Counting and Summing Techniques

I spend quite a bit of time reading the Excel newsgroups on the Internet, and it seems that many of the questions deal with conditional counting or summing. In an attempt to answer most of these questions, I present a number of formula examples that deal with counting various things on a worksheet, based on single or multiple criteria. You can adapt these formulas to your own needs. NEW Excel 2007 includes two new counting and summing functions that aren't available in previous versions...

Executing Ribbon commands

In previous versions of Excel, programmers created custom menus and toolbars by using the CommandBar object. In Excel 2007, the CommandBar object is still available, but it doesn't work like it has in the past. CROSS- Refer to Chapter 22 for more information about the CommandBar object. The CommandBar object has also been enhanced in Excel 2007. You can use the CommandBar object to execute Ribbon commands using VBA. Many of the Ribbon commands display a dialog box. For example, the statement...

Referencing other sheets or workbooks

When a formula refers to other cells, the references need not be on the same sheet as the formula. To refer to a cell in a different worksheet, precede the cell reference with the sheet name followed by an exclamation point. Here's an example of a formula that uses a cell reference in a different worksheet Sheet2 You can also create link formulas that refer to a cell in a different workbook. To do so, precede the cell reference with the workbook name in square brackets , the worksheet name, and...

Entering a value in the next empty cell

A common requirement is to enter a value into the next empty cell in a column or row. The following example prompts the user for a name and a value and then enters the data into the next empty row see Figure 11-5 . Figure 11-5 A macro for inserting data into the next empty row in a worksheet. Figure 11-5 A macro for inserting data into the next empty row in a worksheet. Dim Entry1 As String, Entry2 As String 'Determine next empty row NextRow Cells Rows.Count, 1 .End xlUp .Row 1 Entry1 InputBox...