Language issues

Consider yourself very fortunate if all your end users have the English language version of Excel. Non-English versions of Excel aren't always 100-percent compatible, so that means additional testing on your part. In addition, keep in mind that two users can both be using the English language version of Excel yet use different Windows regional settings. In some cases, you may need to be aware of potential problems. CROSS- I briefly discuss language issues in Chapter 26.

An Introductory Pivot Table Example

This section gets the ball rolling with a simple example of using VBA to create a pivot table. Figure 17-1 shows a very simple worksheet range. It contains four fields SalesRep, Region, Month, and Sales. Each record describes the sales for a particular sales representative in a particular month. Figure 17-1 This simple table is a good candidate for a pivot table. CD- This workbook, named simple pivot table.xlsm, is available on the companion ROM CD-ROM. Figure 17-1 This simple table is a good...

Dialog boxes

Some of the Ribbon commands display a dialog box. In many cases, these dialog boxes contain additional controls that aren't available in the Ribbon. You'll find two general classes of dialog boxes in Excel Modal dialog boxes When a modal dialog box is displayed, it must be closed in order to execute the commands. An example is the Format Cells dialog box. None of the options you specify are executed until you click OK. Use the Cancel button to close the dialog box without making any changes....

Learning more about objects and properties

If this is your first exposure to VBA, you're probably a bit overwhelmed by objects, properties, and methods. I don't blame you. If you try to access a property that an object doesn't have, you get a runtime error, and your VBA code grinds to a screeching halt until you correct the problem. Fortunately, there are several good ways to learn about objects, properties, and methods. READ THE REST OF THE BOOK Don't forget, the name of this chapter is Introducing Visual Basic for Applications. The...

Add Ins

An add-in is a program that's attached to an application to give it additional functionality. To attach an Excel add-in, use the Add-Ins tab in the Excel Options dialog box. In addition to the add-ins that ship with Excel, you can download additional add-ins from Microsoft's Web site (http www.office.microsoft.com), and you can purchase or download many third-party add-ins from online services. You can use the coupon in the back of the book to acquire a free copy of the Power Utility Pak...

Viewing templates

Excel 2007 gives you access to many templates. To explore the Excel templates, choose Office New to display the New Workbook dialog box. The template categories appear as tabs in the New Workbook dialog box. In addition, the right side of the dialog box displays a list of templates that you've used recently. The Microsoft Office Online section contains a number of categories. Click a category, and you'll see the available templates. To use a template, select it and click Download. Figure 4-3...

Selecting Objects

Selecting objects in Excel conforms to standard Windows practices. You can select a range of cells by clicking and dragging (it's more efficient to learn the keyboard shortcuts, however). Clicking an object that has been placed on the drawing layer selects the object. To select multiple objects or noncontiguous cells, press Ctrl while you select the objects or cells. Note Clicking a chart selects a specific object within the chart. To select the chart object itself, press Ctrl while you click...

Customizing or combining controls

A very handy feature lets you customize a control and then save it for future use. You can, for instance, create a CommandButton control that's set up to serve as an OK button. For example, you can set the following properties Width, Height, Caption, Default, and Name. Then drag the customized CommandButton to the Toolbox. This will create a new control. Right-click the new control to rename it or change its icon. You can also create a new Toolbox entry that consists of multiple controls. For...

About VBA

Excel 5 was the first application on the market to feature Visual Basic for Applications (VBA). VBA is best thought of as Microsoft's common application scripting language, and it's included with most Office 2007 applications and even in applications from other vendors. Therefore, if you master VBA by using Excel, you'll be able to jump right in and write macros for other Microsoft (and some non-Microsoft) products. Even better, you'll be able to create complete solutions that use features...

Learning more about API functions

Working with the Windows API functions can be tricky. Many programming reference books list the declarations for common API calls and often provide examples. Usually, you can simply copy the declarations and use the functions without really understanding the details. In reality (at least the reality that I've seen), most Excel programmers take a cook-book approach to API functions. The Internet has hundreds of examples that can be copied and pasted and that work quite reliably. CD- The...

Activating the VBE

When you're working in Excel, you can switch to the VBE by using either of the following techniques Choose Developer' Code Visual Basic. In addition, you can access two special modules as follows. (These special VBA modules are used for event handler procedures, which I describe in Chapter 19.) Excel 2007 has dozens of significant new features, including a brand-spanking-new user interface. If you're expecting new things in the VBE, you're out of luck. The Excel 2007 VBE is exactly like the...

The Basics of VBA

Before I get into the meat of things, I suggest that you read through the material in this section to get a broad overview of where I'm heading. These are the topics that I cover in the remainder of this chapter. Following is a quick-and-dirty summary of what VBA is all about Code You perform actions in VBA by executing VBA code. You write (or record) VBA code, which is stored in a VBA module. Module VBA modules are stored in an Excel workbook, but you view or edit a module by using the Visual...

Msg Box emulation MyMsg Box code

The MyMsgBox function uses a UserForm named MyMsgBoxForm. The function itself, which follows, is very short. The bulk of the work is done in the UserForm_Initialize procedure. CD- The complete code for the MyMsgBox function is too lengthy to list here, but it's available in ROM a workbook named msgbox emulation.xlsm, available on the companion CD-ROM. The workbook is set up so you can easily try various options. Public Prompt1 As String Public Buttons1 As Integer Public Title1 As String Public...

Distributing the application to the user

You've completed your project, and you're ready to release it to the end users. How do you go about doing this You can choose from many ways to distribute your application, and the method that you choose depends on many factors. You could just hand over a CD-ROM, scribble a few instructions, and be on your way. Or, you may want to install the application yourself - but this is not always feasible. Another option is to develop an official setup program that performs the task automatically. You...

Determining the Most Appropriate User Interface

When you develop spreadsheets that others will use, you need to pay special attention to the user interface. By user interface, I mean the method by which the user interacts with the application and executes your VBA macros. Excel 2007 makes some of these decisions irrelevant. Custom menus and toolbars are, for all intents and purposes, obsolete. This means that developers must learn how to work with the Ribbon. Excel provides several features that are relevant to user interface design Shortcut...

Using the function in a VBA procedure

In addition to using custom functions in worksheet formulas, you can also use them in other VBA procedures. The following VBA procedure, which is defined in the same module as the custom RemoveVowels function, first displays an input box to solicit some text from the user. Then the procedure uses the VBA built-in MsgBox function to display the user input after it's processed by the RemoveVowels function (see Figure 10-2). The original input appears as the caption in the message box. Figure 10-2...

Variables Data Types and Constants

VBA's main purpose in life is to manipulate data. Some data resides in objects, such as worksheet ranges. Other data is stored in variables that you create. A variable is simply a named storage location in your computer's memory. Variables can accommodate a wide variety of data types - from simple Boolean values (True or False) to large, double-precision values (see the following section). You assign a value to a variable by using the equal sign operator (more about this in the upcoming...

Other Topics

The six chapters in Part VII cover additional topics. Chapter 26 presents information regarding compatibility. In Chapter 27, I discuss various ways to use VBA to work with files. In Chapter 28, I explain how to use VBA to manipulate Visual Basic components such as UserForms and modules. Chapter 29 covers the topic of class modules. Chapter 30 explains how to work with color in Excel. I finish the part with a useful chapter that answers many common questions about Excel programming.

Background for Text Tools

Excel has many worksheet functions that can manipulate text strings in useful ways. For example, you can uppercase the text in a cell (UPPER), add characters to text (CONCATENATE), remove spaces (TRIM), and so on. But to perform any of these operations, you need to write formulas, copy them, convert the formulas to values, and then paste the values over the original text. In other words, Excel doesn't make it particularly easy to modify text. Wouldn't it be nice if Excel had some text...

Displaying the Help file

I created a simple compiled HTML Help file named texttools. chm for this utility. Clicking the HelpButton on the UserForm executes this procedure Application.Help (ThisWorkbook.Path & & texttools.chm) End Sub Figure 16-7 shows one of the help screens. Figure 16-7 A help screen for the Text Tools utility. CD-ROM Figure 16-7 A help screen for the Text Tools utility. CD-ROM The companion CD-ROM includes all of the source files that were used to create the Help file. These files are in a...

The Macro Recorder

Earlier in this chapter, I discuss the macro recorder, which is a tool that converts your Excel actions into VBA code. This section covers the macro recorder in more detail. Note This is another reminder to make sure that Excel displays the Developer tab in the Ribbon. If you don't see this tab, refer to Displaying Excel's Developer tab earlier in this chapter. The macro recorder is an extremely useful tool, but remember the following points The macro recorder is appropriate only for simple...

Excels Role in Microsofts Strategy

Currently, most copies of Excel are sold as part of Microsoft Office - a suite of products that includes a variety of other programs. (The exact programs that you get depend on which version of Office you buy.) Obviously, it helps if the programs can communicate well with each other. Microsoft is at the forefront of this trend. All the Office products have extremely similar user interfaces, and all support VBA. Therefore, after you hone your VBA skills in Excel, you'll be able to put them to...

Displaying Excels Developer tab

The Excel 2007 Ribbon does not display the Developer tab by default. If you're going to be working with VBA, it's essential that you turn on the Developer tab 1. Choose Office Excel Options. 2. In the Excel Options dialog box, click the Popular tab. 3. Place a checkmark next to Show Developer Tab in the Ribbon. After you perform these steps, Excel displays a new tab, as shown in Figure 7-1. Hk 3 liMiNi 1 lpi v ijf r iJD Lia gUtMri Mnnca CfciYwCwfe . Hi X _ IMC IteHilMM A -** Figure 7-1 By...

Why Excel Is Great for Developers

Excel is a highly programmable product, and it's easily the best choice for developing spreadsheet-based applications. For developers, Excel's key features include the following File structure The multisheet orientation makes it easy to organize an application's elements and store them in a single file. For example, a single workbook file can hold any number of worksheets and chart sheets. UserForms and VBA modules are stored with a workbook but are invisible to the end user. Visual Basic for...

The users installed version of Excel

With every new release of Excel, the issue of compatibility rears its head. As I write this, Excel 2007 has just been released - yet many large corporations are still using Excel 2000 and some use even earlier versions. Unfortunately, there is no guarantee that an application developed for, say, Excel 2000 will work perfectly with later versions of Excel. If you need your application to work with a variety of Excel versions, the best approach is to work with the lowest version - and then test...

Microsoft Excel

Most people don't realize that Microsoft's experience with spreadsheets extends back to the early '80s. Over the years, Microsoft's spreadsheet offerings have come a long way, from the barely adequate MultiPlan to the powerful Excel 2007. In 1982, Microsoft released its first spreadsheet, MultiPlan. Designed for computers running the CP M operating system, the product was subsequently ported to several other platforms, including Apple II, Apple III, XENIX, and MS-DOS. MultiPlan essentially...

Quickanddirty spreadsheets

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

Basic Spreadsheet Types

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

Smart Tags

A Smart Tag is a small icon that appears automatically in your worksheet after you perform certain actions. Clicking a Smart Tag reveals several options. For example, if you copy and paste a range of cells, Excel generates a Smart Tag that appears below the pasted range (see Figure 2-12) and provides you with several options regarding the formatting of the pasted data. Figure 2-12 This Smart Tag appears when you paste a copied range. Figure 2-12 This Smart Tag appears when you paste a copied...

Excel commands

Excel 2007 features a brand new menu-less user interface. In place of a menu system, Excel uses a context-sensitive Ribbon system. The words along the top (such as Insert, View, and so on) are known as tabs. Click a tab, and the Ribbon of icons displays the commands that are most suited to the task at hand. Each icon has a name that is (usually) displayed next to or below the icon. The icons are arranged in groups, and the group name appears below the icons. The convention I use in this book is...

Looping blocks of instructions

Looping is the process of repeating a block of instructions. You might know the number of times to loop, or the number could be determined by the values of variables in your program. The following code, which enters consecutive numbers into a range, demonstrates what I call a bad loop. The procedure uses two variables to store a starting value (Startval) and the total number of cells to fill (NumToFill). This loop uses the GoTo statement to control the flow. If the Cnt variable, which keeps...

Excels settings

Information used by Excel 2007 is stored in this Registry section HKEY CURRENT You can use the regedit.exe program to change anything in the Registry, including information that is critical to your system's operation. In other words, if you change the wrong piece of information, Windows may no longer work properly. Get into the habit of choosing the File Export command in Regedit. This command enables you to save an ASCII version of the entire Registry or just a specific branch of the Registry....

Passing Arguments to Procedures

A procedure's arguments provide it with data that it uses in its instructions. The data that's passed by an argument can be any of the following The use of arguments by procedures is very similar to worksheet functions in the following respects require a fixed number of arguments. accept an indefinite number of arguments. require some arguments, leaving others optional For example, a few of Excel's worksheet functions, such as RAND and NOW, use no arguments. Others, such as COUNTIF, require two...

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

An Enhanced Data Form

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

Foryoureyesonly spreadsheets

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

Data storage and access spreadsheets

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

Classifying spreadsheet users

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

Creating Megaformulas

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

Text file formats

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

Chart sheets

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

Testing the application

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

About Excel Utilities

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

Defining data types

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

Spreadsheet Applications

For the purposes of this book, a spreadsheet application is a spreadsheet file (or group of related files) that is designed so that someone other than the developer can perform useful work without extensive training. According to this definition, most of the spreadsheet files that you've developed probably don't qualify as spreadsheet applications. You may have dozens or hundreds of spreadsheet files on your hard drive, but it's a safe bet that most of them aren't really designed for others to...

The audience for spreadsheet applications

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

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

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

Selecting Ranges from a User Form

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

Other file formats

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

Starting Excel

Macro Script Clear Boxes

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

Displaying a data form by using VBA

Use the showDataForm method to display Excel's data form. The only requirement is that the active cell must be within a range. The following code activates cell A1 which is in a table and then displays the data form Sub DisplayDataForm Range A1 .Select ActiveSheet.ShowDataForm End Sub CD- a workbook with this example is available on the companion CD-ROM. The file is named

Executing a procedure from another procedure

One of the most common ways to execute a procedure is from another VBA procedure. You have three ways to do this Enter the procedure's name, followed by its arguments if any separated by commas. Use the Call keyword followed by the procedure's name and then its arguments if any enclosed in parentheses and separated by commas. Use the Run method of the Application object. The Run method is useful when you need to run a procedure whose name is assigned to a variable. You can then pass the...

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

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

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

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

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

GoTo statements

The most straightforward way to change the flow of a program is to use a GoTo statement. This statement simply transfers program execution to a new instruction, which must be preceded by a label a text string followed by a colon, or a number with no colon . VBA procedures can contain any number of labels, but a GoTo statement cannot branch outside of a procedure. The following procedure uses the VBA InputBox function to get the user's name. If the name is not Howard, the procedure branches to...

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

Declaring a Sub procedure

A procedure declared with the Sub keyword must adhere to the following syntax Private Public Static Sub name arglist instructions Exit Sub instructions End Sub Private Optional Indicates that the procedure is accessible only to other procedures in the same module. Public Optional Indicates that the procedure is accessible to all other procedures in all other modules in the workbook. If used in a module that contains an Option Private Module statement, the procedure is not available outside 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...

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

Toggle Button

A ToggleButton control has two states on and off. Clicking the button toggles between these two states, and the button changes its appearance. Its value is either True pressed or False not pressed . I never use this control because I think a CheckBox is much clearer. Many of the UserForm controls can be embedded directly into a worksheet. These controls are accessible by using Excel's Developer Controls Insert command. Adding such controls to a worksheet requires much less effort than creating...

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

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

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