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

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

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

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

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

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

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

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

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

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, and there are many other collections that you can work with. 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 conditions. These are perfect occasions for the For Each-Next construct because you don't have to...

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

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

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 254 additional arguments in Excel 2007. 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...

Working with multicolumn List Box controls

Excel Vba Listbox Columns

A normal ListBox has a single column for its contained items. You can, however, create a ListBox that displays multiple columns and optionally column headers. Figure 14-14 shows an example of a multicolumn ListBox th gets its data from a worksheet range. Figure 14-14 This ListBox displays a three-column list with column headers. CD-ROM Figure 14-14 This ListBox displays a three-column list with column headers. CD-ROM This example, named listbox multicolumnl .xlsm , is available on the companion...

Implementing Undo

Unfortunately, Excel does not provide a direct way to undo an operation performed using VBA. Undoing a VBA macro is possible, but it takes quite a bit of work. And, unlike Excel's Undo feature, the undo technique used in the Text Tools utility is a single level. In other words, the user can undo only the most recent operation. Refer to the sidebar, Undoing a VBA Procedure , for additional information about using Undo with your applications. The Text Tools utility implements undo by saving the...

Adding the RibbonX code

The final task in creating this utility is to provide a way to execute it. Before Excel 2007, it was relatively easy to insert a new menu command or toolbar button. But, with the new Ribbon user interface, this once-simple job is significantly more challenging. To add a custom Ribbon interface to an Excel file which is actually a package of files , you must modify the file in two ways Create a new folder within the package and add a new part - an XML file that describes the Ribbon modification....

Sorting an array

Although Excel has a built-in command to sort worksheet ranges, VBA doesn't offer a method to sort arrays. One viable but cumbersome workaround is to transfer your array to a worksheet range, sort it by using Excel's commands, and then return the result to your array. But if speed is essential, it's better to write a sorting routine in VBA. In this section, I cover four different sorting techniques Worksheet sort transfers an array to a worksheet range, sorts it, and transfers it back to the...

IfThen constructs

Perhaps the most commonly used instruction grouping in VBA is the If-Then construct. This common instruction is one way to endow your applications with decision-making capability. Good decision making is the key to writing successful programs. The basic syntax of the If-Then construct is If condition Then true instructions Else false instructions The If-Then construct is used to execute one or more statements conditionally. The Else clause is optional. If included, the Else clause lets you...

Reading from and writing to the Registry

Most Windows applications use the Windows Registry database to store settings. See Chapter 4 for some additional information about the Registry. Your VBA procedures can read values from the Registry and write new values to the Registry. Doing so requires the following Windows API declarations Private Declare Function RegOpenKeyA Lib ADVAPI32.DLL _ ByVal hKey As Long, ByVal sSubKey As String, _ ByRef hkeyResult As Long As Long Private Declare Function RegCloseKey Lib ADVAPI32.DLL _ ByVal hKey As...

Builtin Functions

Like most programming languages, VBA has a variety of built-in functions that simplify calculations and operations. Many VBA functions are similar or identical to Excel worksheet functions. For example, the VBA function UCase, which converts a string argument to uppercase, is equivalent to the Excel worksheet function UPPER. CROSS- Appendix B contains a complete list of VBA functions, with a brief description of REFERENCE each. All are thoroughly described in the VBA Help system. Tip To get a...

Simulating a Toolbar with a User Form

Creating a custom toolbar in versions prior to Excel 2007 was relatively easy. With Excel 2007, it's impossible. More accurately, it's still possible to create a custom toolbar with VBA, but Excel ignores many of your VBA instructions. In Excel 2007, all custom toolbars are displayed in the Add-Ins Custom Toolbars Ribbon group. These toolbars cannot be moved, floated, resized, or docked. This section describes how to create a toolbar alternative A modeless UserForm that simulates a floating...

Handling Multiple User Form Controls with One Event Handler

Every CommandButton on a UserForm must have its own procedure to handle its events. For example, if you have two CommandButtons, you'll need two event handler procedures for the controls' click events Private Sub CommandButton1_Click ' Code goes here End Sub Private Sub CommandButton2_Click ' Code goes here End Sub In other words, you cannot assign a macro to execute when any CommandButton is clicked. Each Click event handler is hard-wired to its CommandButton. You can, however, have each event...