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

Working with strings

Like Excel, VBA can manipulate both numbers and text (strings). There are two types of strings in VBA Fixed-length strings are declared with a specified number of characters. The maximum length is Variable-length strings theoretically can hold up to 2 billion characters. Each character in a string requires 1 byte of storage, plus a small amount of storage for the header of each string. When you declare a variable with a Dim statement as data type String, you can specify the length if you know...

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

Analyzing the custom function

Function procedures can be as complex as you need them to be. Most of the time, they are more complex and much more useful than this sample procedure. Nonetheless, an analysis of this example may help you understand what is happening. Function RemoveVowels(Txt) As String ' Removes all vowels from the Txt argument Dim i As Long RemoveVowels If Not UCase(Mid(Txt, i, 1)) Like AEIOU Then RemoveVowels RemoveVowels & Mid(Txt, i, 1) End If Next i End Function Notice that the procedure starts with...

Toggling a Boolean property

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

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

A function with an array argument

A Function procedure also can accept one or more arrays as arguments, process the array s , and return a single value. The array can also consist of a range of cells. The following function accepts an array as its argument and returns the sum of its elements Function SumArray List As Double Dim Item As Variant If WorksheetFunction.IsNumber Item Then SumArray SumArray Item Next Item End Function Excel's ISNUMBER function checks to see whether each element is a number before adding it to the...

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

With End With constructs

The With-End With instruction construct enables you to perform multiple operations on a single object. To start understanding how the With-End With construct works, examine the following procedure, which modifies five properties of a selection's formatting the selection is assumed to be a Range object Selection.Font.Name Cambria Selection.Font.Bold True Selection.Font.Italic True Selection.Font.Size l2 Selection.Font.Underline xlUnderlineStyleSingle Selection.Font.ThemeColor xlThemeColorAccentl...

Object models

The secret to using VBA with other applications lies in understanding the object model for each application. VBA, after all, simply manipulates objects, and each product Excel, Word, Access, PowerPoint, and so forth has its own unique object model. You can program an application by using the objects that the application exposes. Excel's object model, for example, exposes several very powerful data analysis objects, such as worksheets, charts, pivot tables, and numerous mathematical, financial,...

Applying names to existing references

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

Understanding User Form Events

Each UserForm control as well as the UserForm itself is designed to respond to certain types of events, and these events can be triggered by a user or by Excel. For example, clicking a CommandButton generates a Click event for the CommandButton. You can write code that is executed when a particular event occurs. Some actions generate multiple events. For example, clicking the upward arrow of a SpinButton control generates a SpinUp event and also a Change event. When a UserForm is displayed by...

Determining User Needs

When you undertake a new Excel project, one of your first steps is to identify exactly what the end users require. Failure to thoroughly assess the end users' needs early on often results in additional work later when you have to adjust the application so that it does what it was supposed to do in the first place. In some cases, you'll be intimately familiar with the end users - you might even be an end user yourself. In other cases for example, a consultant developing a project for a new...

The File NameOnly function

This function accepts one argument a path with filename and returns only the filename. In other words, it strips out the path. Private Function FileNameOnly pname As String ' Returns the filename from a path filename string Dim i As Integer, length As Integer, temp As String Dim Cnt As Integer ' Count the path separator characters Cnt 0 If Mid pname, i, 1 Application.PathSeparator Then FileNameOnly Split pname, Application.PathSeparator, Cnt End Function If the argument is c excel files 2007...

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

Duplicating rows a variable number of times

The example in this section demonstrates how to use VBA to create duplicates of a row. Figure 11-9 shows a worksheet for an office raffle. Column A contains the name, and column B contains the number of tickets purchased by each person. Column C contains a random number generated by the RAND function . The winner will be determined by sorting the data based on column 3 the highest random number wins . Figure 11-9 The goal is to duplicate rows based on the value in column B. Figure 11-9 The goal...

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

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

Removing a VBA module

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

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

Counting cells between two values

The following function, named CountBetween , returns the number of values in a range first argument that fall between values represented by the second and third arguments Function CountBetween InRange, num1, num2 As Long ' Counts number of values between num1 and num2 With Application.WorksheetFunction If num1 lt num2 Then CountBetween .CountIf InRange, gt amp .CountIf InRange, gt amp num2 CountBetween .CountIf InRange, gt amp .CountIf InRange, gt amp num1 Note that this function uses Excel's...

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

Copying a noncontiguous range

If you've ever attempted to copy a noncontiguous range selection, you discovered that Excel doesn't support such an operation. Attempting to do so brings up an error message That command cannot be used on multiple selections . When you encounter a limitation in Excel, you can often circumvent it by creating a macro. The example is this section is a VBA procedure that allows you to copy a multiple selection to another location. Sub CopyMultipleSelection Dim SelAreas As Range Dim PasteRange As...

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

Dealing with the Insert Function Dialog

Excel's Insert Function dialog box is a handy tool. When you are creating a worksheet formula, this tool lets you select a particular worksheet function from a list of functions see Figure 10-8 . These functions are grouped into various categories to make it easier to locate a particular function. The Insert Function dialog box also displays your custom worksheet functions, and the Function Arguments dialog box prompts you for a function's arguments. Tyj b lt J iri gt on of vtfu wartt Jo do...

Working with dates

You can use a string variable to store a date, but if you do, it's not a real date meaning you can't perform date calculations with it . Using the Date data type is a better way to work with dates. A variable defined as a date uses 8 bytes of storage and can hold dates ranging from January 1, 0100 to December 31, 9999. That's a span of nearly 10,000 years - more than enough for even the most aggressive financial forecast The Date data type is also useful for storing time-related data. In VBA,...

Displaying the date and time

If you understand the serial number system that Excel uses to store dates and times, you won't have any problems using dates and times in your VBA procedures. The DateAndTime procedure displays a message box with the current date and time, as depicted in Figure 11-14 . This example also displays a personalized message in the message box title bar. Figure 11-14 A message box displaying the date and time. Figure 11-14 A message box displaying the date and time. The procedure uses the Date...

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

Displaying the date when a file was saved or printed

An Excel workbook contains several built-in document properties, accessible from the BuiltinDocumentProperties property of the Workbook object. The following function returns the date and time that the workbook was last saved Application.Volatile LastSaved ThisWorkbook. _ BuiltinDocumentProperties Last Save Time End Function The following function is similar, but it returns the date and time when the workbook was last printed or previewed. If the workbook has never been printed or previewed,...

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

Array Formulas

In Excel terminology, an array is a collection of cells or values that is operated on as a group. An array formula is a special type of formula that works with arrays. An array formula can produce a single result, or it can produce multiple results - with each result displayed in a separate cell. For example, when you multiply a 1 5 array by another 1 5 array, the result is a third 1 5 array. In other words, the result of this kind of operation occupies five cells each element in the first...

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

Accommodating keyboard users

Many users prefer to navigate through a dialog box by using the keyboard The Tab and Shift Tab keystrokes cycle through the controls, and pressing a hot key an underlined letter operates the control. To make sure that your dialog box works properly for keyboard users, you must be mindful of two issues tab order and accelerator keys. The tab order determines the sequence in which the controls are activated when the user presses Tab or Shift Tab. It also determines which control has the initial...

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

What You Need to Know

This is not a book for beginning Excel users. If you have no experience with Excel, a better choice might be my Excel 2007 Bible, which provides comprehensive coverage of all the features of Excel. That book is meant for users of all levels. To get the most out of this book, you should be a relatively experienced Excel user. I didn't spend much time writing basic how-to information. In fact, I assume that you know the following How to create workbooks, insert sheets, save files, and so on How...

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

Determining the last nonempty cell in a column or row

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

Objects within a Comment object

Working with properties is confusing at first because some properties actually return objects. Suppose that you want to determine the background color of a particular comment on Sheet1. If you look through the list of properties for a Comment object, you won't find anything that relates to color. Rather, you must do this 1. Use the Comment object's Shape property to return the Shape object that's contained in the comment. 2. Use the Shape object's Fill property to return a FillFormat object. 3....

Relative or absolute

When recording your actions, Excel normally records absolute references to cells. In other words, when you select a cell, it will remember that exact cell not the cell relative to the current active cell . To demonstrate how this works, perform these steps and examine the code 1. Activate a worksheet and start the macro recorder. 4. Move to cell C1 and enter Feb. 5. Continue this process until you've entered the first six months of the year in B1 G1. 6. Click cell B1 to activate it again. 7....

Understanding object parents

As you know, Excel's object model is a hierarchy Objects are contained in other objects. At the top of the hierarchy is the Application object. Excel contains other objects, and these objects contain other objects, and so on. The following hierarchy depicts how a Range object fits into this scheme Application object Workbook object Worksheet object Range object In the lingo of object-oriented programming, a Range object's parent is the Worksheet object that contains it. A Worksheet object's...

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

Cleaning up recorded macros

Earlier in this chapter, you see how recording your actions while you issue a single command the Page Layout Page Setup' . Orientation command produces an enormous amount of VBA code. This is an example of how, in many cases, the recorded code includes extraneous commands that you can delete. Throughout this book, I present many small snippets of VBA code to make a point or to provide an example. Often, this code might consist of just a single statement. In some cases, the example consists of...

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

Calculating Formulas

You've probably noticed that the formulas in your worksheet get calculated immediately. If you change a cell that a formula uses, the formula displays a new result with no effort on your part. This is what happens when the Excel Calculation mode is set to Automatic. In this mode which is the default mode , Excel uses the following rules when calculating your worksheet When you make a change - enter or edit data or formulas, for example - Excel immediately calculates those formulas that depend...

About the Comment property

In this section, I've been discussing the Comment object. If you dig through the Help system, you'll find that a Range object has a property named Comment. If the cell contains a comment, the Comment property returns a Comment object. For example, the following statement refers to the Comment object in cell A1 If this were the first comment on the sheet, you could refer to the same Comment object as follows To display the comment in cell A1 in a message box, use a statement like this MsgBox...

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

Pairing a Spin Button with a Text Box

A SpinButton has a Value property, but this control doesn't have a caption in which to display its value. In many cases, however, you will want the user to see the SpinButton value. And sometimes you'll want the user to be able to change the SpinButton value directly instead of clicking the SpinButton repeatedly. The solution is to pair a SpinButton with a TextBox, which enables the user to specify a value either by typing it into the TextBox directly or by clicking the SpinButton to increment...

Declaring arrays

You declare an array with a Dim or Public statement, just as you declare a regular variable. You can also specify the number of elements in the array. You do so by specifying the first index number, the keyword To, and the last index number - all inside parentheses. For example, here's how to declare an array comprising exactly 100 integers Tip When you declare an array, you need specify only the upper index, in which case VBA assumes that 0 is the lower index. Therefore, the two statements...

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

A function with optional arguments

Many of Excel's built-in worksheet functions use optional arguments. An example is the LEFT function, which returns characters from the left side of a string. Its syntax is The first argument is required, but the second is optional. If the optional argument is omitted, Excel assumes a value of 1. Therefore, the following two formulas return the same result The custom functions that you develop in VBA also can have optional arguments. You specify an optional argument by preceding the argument's...

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

User Form events

Several events are associated with showing and unloading a UserForm Initialize Occurs before a UserForm is loaded or shown but does not occur if the UserForm was previously hidden. Activate Occurs when a UserForm is shown. Deactivate Occurs when a UserForm is deactivated but does not occur if the form is hidden. QueryClose Occurs before a UserForm is unloaded. Terminate Occurs after the UserForm is unloaded. Note Often, it's critical that you choose the appropriate event for your event handler...

Naming constants

Virtually every experienced Excel user knows how to create cell and range names although not all Excel users actually do so . But most Excel users do not know that you can use names to refer to values that don't appear in your worksheet - that is, constants. Suppose that many formulas in your worksheet need to use a particular interest rate value. One approach is to type the interest rate into a cell and give that cell a name, such as InterestRate. After doing so, you can use that name in your...

Some Useful Functions for Use in Your Code

In this section, I present some custom utility functions that you may find useful in your own applications and that may provide inspiration for creating similar functions. These functions are most useful when called from another VBA procedure. Therefore, they are declared by using the Private keyword and thus will not appear in Excel's Insert Function dialog box. CD- The examples in this section are available on the companion CD-ROM. The file is named

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