Searching newsgroups

Many people don't realize that you can perform a keyword search on past newsgroup postings. Often, this is an excellent alternative to posting a question to the newsgroup because you can get the answer immediately. The best source for searching newsgroup postings is Google, at the following Web address A Dozen Tips for Posting to a Newsgroup 1. Make sure that your question has not already been answered. Check the newsgroup's FAQ (if one exists) and also perform a Google search. (See the...

Can I use Excels builtin worksheet functions in my VBA code

Excel's worksheet functions are accessed via the WorksheetFunction method of the Application object. For example, you could access the SUM worksheet functions with a statement such as the following Ans This example assigns the sum of the values in A1 A3 (on the active sheet) to the Ans variable. Generally, if VBA includes an equivalent function, you cannot use Excel's worksheet version. For example, because VBA has a function to compute square roots (Sqr), you cannot use the...

Using ActiveX controls on a worksheet

Excel also lets you add the UserForm ActiveX controls to a worksheet's drawing layer (an invisible layer on top of a sheet that holds pictures, charts, and other objects). Figure 6-1 shows a simple worksheet model with several UserForm controls inserted directly on the worksheet. This sheet contains some examples of an OptionButton, a ScrollBar, a CommandButton, and a CheckBox. This workbook, which also includes a few simple macros,is available on the companion CD-ROM. Perhaps the most common...

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. Combining them results in nine combinations, which are shown in Table 5-1. In reality, only seven segments are worth thinking about because both moderately experienced and very...

Showing a progress indicator by using a Multi Page control

In the preceding example, the macro was not initiated by a UserForm. If your lengthy macro is kicked off when a UserForm is displayed, however, the technique that I describe in this section is a better solution. It assumes the following Your project is completed and debugged. Your project uses a UserForm (without a MultiPage control) to initiate a lengthy macro. You have a way to gauge the progress of your macro. The companion CD-ROM contains an example that demonstrates this technique.

Activating the VBE

When you're working in Excel, you can switch to the VBE by using any of the following techniques Choose Tools Macro Visual Basic Editor. Click the Visual Basic Editor button, which is located on the Visual Basic toolbar. 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.) Right-click a sheet tab and then select View Code to access the code module for the active sheet. Right-click the Excel...

Adding The Menu Item

The AddMenuItem procedure shown in Listing 23-5 is executed when the workbook is opened. It creates a new GridLines menu item on the View menu. Listing 23-5 Augmenting a Built-in Excel Menu Dim ViewMenu As CommandBarPopup Dim NewMenultem As CommandBarButton ' Delete the menu if it already exists Call DeleteMenultem Set ViewMenu CommandBars(1).FindControl(ID 30004) If ViewMenu Is Nothing Then MsgBox Cannot add menu item. Exit Sub Set NewMenuItem ViewMenu.Controls.Add _ (Type msoControlButton)...

Using VBA to display arbitrary data labels on a chart

One of the most frequent complaints about Excel's charting is its inflexible data labeling feature. For example, consider the XY chart in Figure 18-7. It might be useful to display the associated name for each data point. However, you can search all day, and you'll never find the Excel command that lets you do this automatically. (Hint Such a command doesn't exist.) Data labels are limited to the values only . . . unless you want to edit each data label manually and replace it with text of your...

Creating a selfexpanding chart

One of the most common questions related to charting is How can I create a chart that will expand automatically when I add new data to the worksheet To understand this issue, examine Figure 18-25, which shows a worksheet set up to store sales information that is updated daily. The chart displays all the data in the worksheet. When new data is entered, the chart series must be expanded to include the new data. On the other hand, if data is deleted, the chart series should also be contracted to...

Another function with no argument

I used to use Excel's RAND( ) function to quickly fill a range of cells with values. But I didn't like the fact that the random numbers change whenever the worksheet is recalculated. So I usually had to convert the formulas to values by choosing the Edit Paste Special command (with the Values option). Then I realized that I could create a custom function that returned random numbers that didn't change. I used the VBA built-in Rnd function, which returns a random number between 0 and 1. The...

Understanding Excels AddIn Manager

The most efficient way to load and unload add-ins is with Excel's Add-Ins dialog box, which you access by choosing Tools Add-Ins. This command displays the Add-Ins dialog box, as shown in Figure 21-1. The list box contains the names of all add-ins that Excel knows about, and check marks identify add-ins that are open. You can open and close add-ins from this dialog box by clearing or marking the check boxes. Figure 21-1 The Add-Ins dialog box. Figure 21-1 The Add-Ins dialog box. Beginning with...

What the Icons Mean

Throughout the book, I've used icons to call your attention to points that are particularly important. I use this icon to indicate that the material discussed is new to Excel 2003. If you're developing an application that will be used for earlier versions of Excel, pay particular attention to these icons. I use Note icons to tell you that something is important perhaps a concept that could help you master the task at hand or something fundamental for understanding subsequent material. Tip icons...

The Developer and the End User

I've already used the terms developer and end user, which are terms that you see frequently throughout this book. Because you've gotten this far, I think I can safely assume that you're either a spreadsheet application developer or a potential developer. My definitions regarding developers and end users are simple. The person who creates the spreadsheet application is the developer. For joint projects, there are multiple developers a development team. The person who uses the results of the...

About the Companion CDROM

The inside back cover of this book contains a CD-ROM that holds many useful examples that I discuss in the text. When I write about computer-related material, I emphasize learning by example. I know that I learn more from a well-thought-out example than from reading a dozen pages in a book. I assume that this is true for many other people. Consequently, I spent more time developing the examples on the CD-ROM than I did writing chapters. The files on the companion CD-ROM are not compressed, so...

XLS files

The XLS workbook files produced by Excel 2003 use the same file format as Excel 97, Excel 2000, Excel 2002, and Excel 2003. These files cannot be opened by any version of Excel prior to Excel 97. You can, however, save a workbook using any of the older Excel file formats. You might lose some information that is specific to the later file format. An Excel workbook or add-in file can have any extension that you like. In other words, these files don't need to be stored with an XLS (for workbooks)...

Data Entry

Data entry in Excel is quite straightforward. Excel interprets each cell entry as one of the following A numeric value (including date and time values) A Boolean value (True or False) Formulas always begin with an equal sign ( ). Excel is accommodating to habitual 1-2-3 users, however, and accepts an each-at symbol ( ), a plus sign (+), or a minus sign (-) as the first character in a formula. It automatically adjusts the entry after you press Enter. The following data entry tips are especially...

Scoping names

A named cell or range normally has a workbook-level scope in other words, you can use the name in any worksheet in the workbook. Another option is to create names that have a worksheet-level scope. To create a worksheet-level name, define the name by preceding it with the worksheet name followed by an exclamation point for example, SheetllSales. If the name is used on the sheet in which it is designed, you can omit the sheet qualifier when you reference the name. The Define Name dialog box...

Copying a variably sized range

In many cases, you need to copy a range of cells, but you don't know the exact row and column dimensions of the range. For example, you might have a workbook that tracks weekly sales, and the number of rows changes weekly when you add new data. Figure 11-1 shows a very common type of worksheet. This range consists of several rows, and the number of rows changes each week. Because you don't know the exact range address at any given time, writing a macro to copy the range requires some additional...

Properties of command bar controls

Command bar controls have a number of properties that determine how the controls look and work. Following is a list of a few of the more useful properties for command bar controls BeginGroup If True, a separator bar appears before the control. BuiltIn Read-only. True if the control is one of Excel's built-in controls. Caption The text that is displayed for the control. If the control shows only an image, the caption appears when you move the mouse pointer over the control. Enabled If True, the...

Essentials of Spreadsheet Application Development

My goal in this chapter is to provide you with some general guidelines that you may find useful while you learn to create effective applications with Excel. A discussion of the basic steps involved in spreadsheet application development Determining end user needs and planning applications to meet those needs Guidelines for developing and testing your applications Documenting your development efforts and writing user documentation There is no simple, sure-fire recipe for developing an effective...

Importing a text file to a range

The following subroutine in Listing 27-4 reads the text file created in the previous example and then stores the values beginning at the active cell. The code reads each character and essentially parses the line of data, ignoring quote characters and looking for commas to delineate the columns. Listing 27-4 Reading the Text File from Listing 27-3 and Storing the Values Beginning at the Active Cell Dim txt As String, Char As String * 1 Set ImpRng ActiveCell On Error Resume Next Filename Open...

Smart Tags

A Smart Tag is a small icon that appears automatically in your worksheet. Clicking a Smart Tag reveals several clickable 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-3). Excel features several other Smart Tags, and additional Smart Tags can be provided by third-party providers. Not everyone likes Smart Tags but fortunately, they can be turned off by using the Smart Tags tab in the AutoCorrect dialog...

Data storage and access spreadsheets

It's not surprising that spreadsheets are often used for keeping lists or modest database manipulations. Most people find that it's much easier to view and manipulate data in a spreadsheet than it is using normal database software. Beginning with Excel 97, each worksheet consists of 65,536 rows, which is a size increase that greatly extends the potential for database work. Spreadsheets in this category are often candidates for applications, especially if end users need to perform moderately...

Whatif models

Many consider the what-if model category to be the epitome of spreadsheets at their best. The ability to instantly recalculate thousands of formulas makes spreadsheet software the ideal tool for financial modeling and other models that depend on the values of several variables. If you think about it, just about any spreadsheet that contains formulas is a what-if model (which are often distributed as templates). Changing the value of a cell used in a formula is akin to asking what if . . . My...

Singleblock budgets

By a single-block budget, I mean a spreadsheet (not necessarily a budget model) that essentially consists of one block of cells. The top row might contain names that correspond to time (months, quarters, or years), and the left column usually contains categories of some type. Typically, the bottom row and right column contain formulas that add the numbers together. There may or may not be formulas that compute subtotals within the block. This is a very common type of spreadsheet. In fact,...

Utility applications

No one is ever completely satisfied with his or her spreadsheet product. Good as it is, I still find quite a bit lacking in Excel. This brings me to the next category of spreadsheets utility applications. Utilities are special tools designed to perform a single recurring task. For example, if you often import text into Excel, you may want some additional text-handling commands, such as the ability to convert selected text to uppercase (without using formulas). The solution Develop a...

Singleuser applications

This is a spreadsheet application that only the developer uses, but its complexity extends beyond the spreadsheets in the for-your-eyes-only category. For example, I developed a workbook to keep track of registered users for my shareware applications. It started out as a simple worksheet database (for my eyes only), but then I realized that I could also use it to generate mailing labels and invoices. One day I spent an hour or so writing macros and then realized that I had converted this...

Video modes

As you probably know, users' video displays vary widely. A video resolution of 1024 x 768 is most common, but many systems are set up with an 800 x 600 display. And higher resolution displays are becoming increasingly common. Just because you have a super-high resolution monitor, you can't assume that everyone else does. I discuss compatibility issues in Chapter 26. I briefly discuss language issues in Chapter 26. Video resolution can be a problem if your application relies on specific...

Declarations In The Module Vba Module

Following are the declarations at the top of the Modulel module Public Const APPNAME As String Text Tools Utility Public Const PROGRESSTHRESHHOLD 2000 Public UserChoices(1 To 8) As Variant 'stores user's last choices Public UndoRange As Range ' For undoing Public UserSelection As Range 'For Udoing I declare a Public constant containing a string that stores the name of the application. This string is used in the UserForm caption and in various message boxes. It is also used as the caption for...

Builtin Functions

Like most programming languages, VBA has a variety of built-in functions that simplify calculations and operations. Often, the functions enable you to perform operations that are otherwise difficult or even impossible. Many VBA functions are similar (or identical) to Excel worksheet functions. For example, the VBA function UCase, which converts a string argument to uppercase, is equivalent to the Excel worksheet function UPPER. Appendix B contains a complete list of VBA functions, with a brief...

Creating the User Form

Figure 13-7 shows the finished UserForm for this example. For best results, start with a new workbook with only one worksheet in it. Then follow these steps Figure 13-7 This dialog box asks the user to enter a name and a sex. Figure 13-7 This dialog box asks the user to enter a name and a sex. 1. Press Alt+F11 to activate the VBE. 2. In the Project window, select the workbook's project and choose Insert UserForm to add an empty UserForm. 3. The UserForm's Caption property will have its default...

Developing Excel Utilities with VBA

A utility, in general, is something that enhances software, adding useful features or making existing features more accessible. This chapter is about Excel utilities About Excel utilities and utilities in general Why use VBA to develop utilities What you need to know to develop good utilities Step-by-step details for developing a useful Excel utility to manipulate text in cells Where to go for more Excel utilities As you'll see, creating utilities for Excel is an excellent way to make a great...

C

Analysis tools, handling through, 34 code type returned by Excel InputBox method, 388 counting and summing techniques, 59-62 creating, 908-909 dates and times, 62-64 described, 25-27, 43-44 errors, 55-56 first character, acceptable, 24 HTML worksheet, activating, 81-82 linking, 48 megaformulas, 64-67 monitoring range to make bold, 623 names, 48-55 overwrite protection, 37-38 ranges and cells, 54 results, containing in cell, 17 self-expanding chart, 596, 599 worksheet, executing Function add-in,...

Using the Editor tab

Figure 7-7 shows the options that you access by clicking the Editor tab of the Options dialog box. Editor Editor Format General Docking V Auto Syntax Check Auto Indent f Require Variable Declaration . ,. r Drag-and-Drop Test Editing DeFault to Full Module View Procedure Separator Figure 7-7 The Editor tab of the Options dialog box. The Auto Syntax Check setting determines whether the VBE pops up a dialog box if it discovers a syntax error while you're entering your VBA code. The dialog box...

Adding sound to your applications

By itself, Excel doesn't have much to offer in the area of sound VBA's Beep command is about as good as it gets. However, with a few simple API calls, your application can play WAV or MIDI files. The text-to-speech feature, which debuted in Excel 2002, extends the sound capability quite a bit. In fact, Excel 2002 and later can speak text by using the Speak method of the Speech object.The examples in this section focus on playing sound files, not speech. Not all systems support sound. To...

IfThen constructs

Perhaps the most commonly used instruction grouping in VBA is the If-Then construct. This common instruction is one way to endow your applications with decision-making capability. Good decision making is the key to writing successful programs. A successful Excel application essentially boils down to making decisions and acting on them. The basic syntax of the If-Then construct is If condition Then true_instructions Else false_instructions The If-Then construct is used to execute one or more...

A multifunctional function

This example describes a technique that may be helpful in some situations making a single worksheet function act like multiple functions. For example, the following VBA listing is for a custom function called StatFunction. It takes two arguments the range (rng) and the operation (op). Depending on the value of op, the function returns a value computed using any of the following worksheet functions AVERAGE, COUNT, MAX, MEDIAN, MIN, MODE, STDEV, SUM, or VAR. For example, you can use this function...

Adding event handler procedures

In this section, I explain how to write the procedures that will handle the events that occur when the UserForm is displayed. To continue the example, do the following 1. Press Alt+F11 to activate the VBE. 2. Make sure the UserForm is displayed and double-click its Cancel button. This will activate the Code window for the UserForm and insert an empty procedure named CancelButton_Click. Notice that this procedure consists of the object's name, an underscore character, and the event that it...

Importing more than columns of data

It's not uncommon to need to import a text file that exceeds Excel's 256-column capacity. If you attempt to open such a file with the File Open command, Excel simply ignores any data past column 256 (and doesn't even warn you about it). Listing 27-5 is a variation of the ImportRange procedure presented earlier in this chapter. It reads a text file and then imports the data into a new workbook. If the data contains more than 256 columns of data, additional sheets are added to the workbook....

Vba Excel Numlock

Charts, using in a SERIES formula, 561-563 cells and ranges, setting (Ctrl+F3), 48-49 columns and rows, 51 constants, 51-53 entering directly into Define Name dialog box, 53-54 to existing references, 49-50 hidden, 50 intersecting, 50-51 natural language, 52 objects, 54-55 scoping, 51 understanding cell and range names, 54 fullname property of add-in object, 685 functions declaring, 283 finding, 26 Macro Recorder, 160 month, returning array, 295-298 in object hierarchy, 163 prompting to add to...

Controlling Excel from another application

You can, of course, also control Excel from another application (such as another programming language or a Word VBA procedure). For example, you might want to perform some calculations in Excel and return the result to a Word document. You can create any of the following Excel objects with the adjacent functions Application object Workbook object Chart object Listing 20-4 shows a procedure that is located in a VBA module in a Word 2003 document. This procedure creates an Excel Worksheet object...

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, such as a quarterly report. An Excel utility is (almost always) an add-in that enhances Excel with new features or capabilities. Excel is a decent 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 who turn off the grid-line display want a feature that toggles this attribute so that they don't...

Designtime versus runtime User Form manipulations

It's important to understand the distinction between manipulating UserForms or controls at design time and manipulating these objects at runtime. Runtime manipulations are apparent when the UserForm is shown, but the changes made are not permanent. For example, you might write code that changes the Caption property of the UserForm before the form is displayed. The new caption appears when the UserForm is shown, but when you return to the VBE, the UserForm displays its original caption. Part IV...

Creating a command bar

In VBA, you create a new toolbar by using the Add method of the CommandBars collection. The following instruction creates a new toolbar with a default name, such as Custom 1. The created toolbar is initially empty (has no controls) and is not visible (its Visible property is False). More often, you'll want to set some properties when you create a new toolbar. The following example demonstrates one way to do this Dim TBar As CommandBar Set TBar CommandBars.Add With TBar .Name MyToolbar .Top 0...

Creating Custom Menus the Easy

When Excel 97 was released, I was a bit frustrated with the amount of code required to create a custom menu, so I developed a technique that simplifies the process considerably. My technique uses a worksheet, shown in Figure 23-6, to store information about the new menu. A VBA procedure reads the data in the workbook and creates the menu, menu items, and submenu items. The worksheet consists of a table with five columns Level This is the location of the particular item relative to the hierarchy...

Installing an addin

To avoid confusion, close the XLS workbook before installing the add-in created from that workbook. To install an add-in, do the following 1. Choose the Tools Add-Ins command. Excel displays the Add-Ins dialog box. 2. Click the Browse button and locate the add-in that you just created. After you find your new add-in, the Add-Ins dialog box displays the add-in in its list. As shown in Figure 21-3, the Add-Ins dialog box also displays the descriptive information that you provided in the...

Processing a series of files

One common use for macros, of course, is to repeat an operation a number of times. The example in Listing 11-8 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. Listing 11-8 A...

D

DAO (Data Access Objects), 12, 33 data analysis options, 12 chart, changing based on active cell, 563-564 using ComboBox, 565-566 entry, spreadsheet, 24-25, 98 file formats, surviving round trip importing VBA text files, 828 interactive charts, getting, 602 Pivot Table, 535-536 range, creating interactive charts, 603-604 series control with Autofiltering, 592-593 storage and access spreadsheet, 103 structure, deciding on, 108 summarizing in table 539-541 described, 36, 531-532 modifying,...

Description

By default, Excel inserts five lines of comments (three of them blank) that list the macro name, the user's name, and the date. You can put anything you like here or nothing at all. As far as I'm concerned, typing anything in is a waste of time because I always end up deleting this in the module. In versions of Excel prior to Excel 97, the Record Macro dialog box provided an option that let you assign the macro to a new menu item on the Tools menu. This If you create some VBA macros that you...

Determining the current video mode

The example in this section uses Windows API calls to determine a system's current video mode. If your application needs to display a certain amount of information on one screen, knowing the display size helps you scale the text accordingly. Declare Function GetSystemMetrics Lib user32 _ (ByVal nIndex As Long) As Long Public Const SM_CXSCREEN 0 Public Const SM_CYSCREEN 1 vidWidth GetSystemMetrics(SM_CXSCREEN) vidHeight GetSystemMetrics(SM_CYSCREEN) Msg The current video mode is Msg Msg &...

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 Often, it's critical that you choose the appropriate event for your event handler procedure...

Using VBA to specify the data used by a chart

The examples in this section describe VBA techniques that enable you to change the data used by a chart. CHANGING CHART DATA BASED ON THE ACTIVE CELL Figure 18-4 displays a chart based on the data in the row of the active cell. When the user moves the cell pointer, the chart is updated automatically. Figure 18-4 This chart always displays the data from the row of the active cell. Figure 18-4 This chart always displays the data from the row of the active cell. This example uses an event handler...

Determining whether a chart is activated

A common type of macro performs some manipulations on the active chart (the chart selected by a user). For example, a macro might change the chart's type, apply colors, or change the font size. The question is, how can your VBA code determine whether the user has actually selected a chart By selecting a chart, I mean either activating a chart sheet or activating an embedded chart by clicking it. Your first inclination might be to check the TypeName property of the Selection, as in this...

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

Spreadsheet newsgroups

The primary Usenet newsgroup for general spreadsheet users is This newsgroup is intended for users of any spreadsheet brand, but about 90 percent of the postings deal with Excel. My advice Skip this one and head directly for the Microsoft newsgroups. Microsoft has an extensive list of newsgroups, including quite a few devoted to Excel. If your Internet service provider doesn't carry the Microsoft newsgroups, you can access them directly from Microsoft's news server. You'll need to configure...

What Is a Spreadsheet Application

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

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 comprise a large percentage of all spreadsheet users, which is probably the largest segment of all. These are the people who need to use a spreadsheet for...

Spreadsheet Applications

Programming, as it relates to spreadsheet use, is essentially the process of building applications that use a spreadsheet rather than a traditional programming language such as C, Pascal, or BASIC. In both cases, however, these applications will be used by other people not the developer of the application. For 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...

Working with ADO

ADO (ActiveX Data Objects) is an object model that enables you to access data stored in a variety of database formats. Importantly, this methodology allows you to use a single object model for all your databases. This is currently the preferred data access methodology and should not be confused with DAO (Data Access Objects). This section presents a simple example that uses ADO to retrieve data from an Access database. ADO programming is a very complex topic. If you have a need to access...

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 Times New Roman Selection.Font.FontStyle Bold Italic Selection.Font.Size l2 Selection.Font.Underline xlUnderlineStyleSingle Selection.Font.Colorlndex 5 End Sub This procedure...

Adding and deleting menus automatically

If you need a menu to be created when a workbook is opened, use the Workbook_Open event. The following code, stored in the code module for the ThisWorkbook object, executes the CreateMenu procedure (not shown here) To delete the menu when the workbook is closed, use a procedure such as the following. This procedure is executed before the workbook closes, and it executes the DeleteMenu procedure (not shown here). Private Sub Workbook_BeforeClose(Cancel As Boolean) A problem could arise, however,...

A function with an indefinite number of arguments

Some Excel worksheet functions take an indefinite number of arguments. A familiar example is the SUM function, which has the following syntax The first argument is required, but you can have as many as 29 additional arguments. Here's an example of a SUM function with four range arguments You can even mix and match the argument types. For example, the following example uses three arguments the first is a range, the second is a value, and the third is an expression. You can create Function...

Modifying Your Userform For A Progress Indicator With A Multipage Control

This step assumes that you have a UserForm all set up. You'll add a MultiPage control. The first page of the MultiPage control will contain all your original controls. The second page will contain the controls that display the progress indicator. When the macro begins executing, VBA code will change the Value property of the MultiPage control. This will effectively hide the original controls and display the progress indicator. The first step is to add a MultiPage control to your UserForm. Then...

Disabling or hiding menus

When a menu or menu item is disabled, its text appears in a faint shade of gray, and clicking it has no effect. Excel disables its menu items when they are out of context. For example, the Links menu item on the Edit menu is disabled when the active workbook does not contain any links. You can write VBA code to enable or disable both built-in and custom menus or menu items. Similarly, you can write code to hide menus or menu items. The key, of course, is tapping into the correct event. The...

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 nonempty cell in a column LASTINROW returns the contents of the last nonempty 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...

Associating a Help Topic with a VBA Function

If you create custom worksheet functions with VBA, you might want to associate a Help file and context ID with each function. After these items are assigned to a function, the Help topic can be displayed from the Insert Function dialog box by pressing F1. To specify a context ID for a custom worksheet function, follow these steps 1. Create the function as usual. 2. Make sure that your project has an associated Help file (refer to the preceding section). 3. In the VBE, press F2 to activate the...

Menu Programming That Works with Events

Suppose that you want to create a menu when a workbook opens. You'll also want to delete the menu when the workbook closes because menu modifications remain in effect between Excel sessions. Or suppose that you want a menu to be available only when a particular workbook or worksheet is active. These sorts of things are relatively easy to program, thanks to Excel's event handlers. The examples in this section demonstrate various menu-programming techniques used in conjunction with events. I...

Class modulelevel variables for the CSVFile Class

A class module must maintain its own private variables that mirror the property settings for the class. The CSVFileClass class module uses two variables to keep track of the two property settings. These variables are declared at the top of the class module Private RangeToExport As Range Private ImportToCell As Range RangeToExport is a Range object that represents the range to be exported. ImportToCell is a Range object that represents the upper-left cell of the range into which the file will be...

Prompting for a cell value

The following procedure demonstrates how to ask the user for a value and then insert it into cell A1 of the active worksheet Range(A1).Value InputBox(Enter the value) End Sub Figure 11-3 shows how the input box looks. This procedure has a problem, however. If the user clicks the Cancel button in the input box, the procedure deletes any data already in the cell. The following modification checks for the Cancel button clicks but takes no action Dim UserEntry As String UserEntry InputBox(Enter the...

Using a List Box in a User Form

Figure 14-2 shows another example that uses a ListBox as a menu. Before the UserForm is displayed, its Initialize event handler procedure is called. This procedure, which follows, uses the Addltem method to add six items to the ListBox Figure 14-2 This dialog box uses a ListBox as a menu. Figure 14-2 This dialog box uses a ListBox as a menu. Private Sub UserForm_Initialize() With ListBoxl .Addltem Macrol .Addltem Macro2 .AddItem .AddItem .AddItem .AddItem With Macro3 Macro4 Macro5 Macro6 The...

Manipulating the Command Bars Collection

The CommandBars collection, contained in the Application object, is a collection of all CommandBar objects. Each CommandBar object has a collection of Controls. All these objects have properties and methods that enable you to control toolbars with VBA procedures. In this section, I provide some key background information that you should know about before you start writing code to manipulate toolbars. As always, a thorough understanding of the object model will make your task much easier. You...

Determining whether a cell has a comment

The following statement will display the comment in cell A1 of the active sheet MsgBox Range(A1).Comment.Text If cell A1 does not have a comment, executing this statement will generate a cryptic error message Object variable or With block variable not set. To determine whether a particular cell has a comment, you can write code to see whether the Comment object is Nothing. (Yes, Nothing is a valid keyword.) The following statement displays True if cell A1 does not have a comment MsgBox...

Activating a Microsoft Office application

If the application that you want to start is one of several Microsoft applications, you can use the ActivateMicrosoftApp method of the Application object. For example, the following procedure starts Word Application.ActivateMicrosoftApp xlMicrosoftWord End Sub If Word is already running when the preceding procedure is executed, it is activated. The other constants available for this method are

The IDE Object Model

Programming the IDE requires an understanding of its object model. The top object in the object hierarchy is the VBE (Visual Basic Environment). As with Excel's object model, the VBE contains other objects. A simplified version of the IDE object hierarchy is as follows VBProject VBComponent CodeModule Designer Property Reference Window CommandBar This chapter ignores the Extensibility Library's Windows collection and CommandBars collection, which aren't all that useful for Excel developers....

Excel Resources Online

If I've done my job, the information provided in this book will be useful to you. It is, however, by no means comprehensive. In addition, new issues tend to crop up, so you'll want to make sure that you're up-to-date. Therefore, I've compiled a list of additional resources that could help you become more proficient in Excel application development. I've classified these resources into three categories Microsoft technical support

Solving Problems with a Spreadsheet

I've covered the basic concept of a spreadsheet application, discussed the end users and developers of such applications, and even attempted 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 pretty 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 interactive...

Listing all Command Bar objects

If you're curious about the objects in the CommandBars collection, the following procedure should be enlightening. Executing this procedure generates a list (as shown in Figure 22-6) of all CommandBar objects in the CommandBars collection. For Excel 2003, it lists a total of 126 built-in command bars, plus any custom menu bars or toolbars. For each command bar, the procedure lists its Index, Name, and Type property settings (displayed as Toolbar, Menu Bar, or Shortcut) as well as whether it's a...

Help for Your Excel Applications

If you develop a nontrivial application in Excel, you might want to consider building in some sort of help for end users. Doing so makes the users feel more comfortable with the application and could eliminate many of those time-wasting phone calls from users with basic questions. Another advantage is that help is always available That is, the instructions can't be misplaced or buried under a pile of books. You can add user help to your applications in a number of ways, ranging from simple to...

Chart creation macro recorder output

I turned on the macro recorder while I created a chart (shown in Figure 18-1) and then performed some simple customizations to it. The chart uses the data in range A1 F2. Following in Listing 18-1 is the code generated by the macro recorder Listing 18-1 Code Generated by the Macro Recorder Range(A1 F2).Select Charts.Add ActiveChart.ChartType xlColumnClustered ActiveChart.SetSourceData _ _ PlotBy xlRows ActiveChart.Location _ Where xlLocationAsObject, _ Name Sheet1 ActiveChart.HasLegend False...

Sending EMail Attachments from Excel

As you probably know, Excel has commands to send worksheets or workbooks via e-mail. And, of course, you can use VBA to automate these types of tasks. The procedure below sends the active workbook (as an attachment) to joeblow anydomain.com. The e-mail message has the subject My Workbook. ActiveWorkbook.SendMail joeblow anydomain.com, My Workbook End Sub If you would like to e-mail only a single sheet from a workbook, you need to copy the sheet to a new (temporary) workbook, send that workbook...

The Chart object model

To get a feel for the number of objects involved when working with charts, turn on the macro recorder, create a chart, and perform some routine chart-editing tasks. You might be surprised by the amount of code that Excel generates. When you first start exploring the object model for a Chart object, you'll probably be very confused . . . which is not surprising because the object model is very confusing. It's also very deep. For example, assume that you want to change the title displayed in an...

Associating a Help File with Your Application

You can associate a particular HTML Help file with your Excel application in one of two ways by using the Project Properties dialog box or by writing VBA code. In the Visual Basic Editor (VBE), choose Tools xxx Properties (where xxx corresponds to your project's name). In the Project Properties dialog box, click the General tab and specify a compiled HTML Help file for the project. This file should have a CHM extension. It's a good practice to keep your application's Help file in the same...

Making the application bulletproof

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

Transferring onedimensional arrays

The example in the preceding section involves a two-dimensional array, which works out nicely for row-and-column-based worksheets. When transferring a one-dimensional array to a range, the range must be horizontal that is, one row with multiple columns. If you have to use a vertical range instead, you must first transpose the array to make it vertical. You can use Excel's TRANSPOSE function to do this. The following example transfers a 100-element array to a vertical worksheet range (A1 A100)...

Microsoft Knowledge Base

Your best bet for solving a problem could be the Microsoft Knowledge Base. This is the primary Microsoft product information source an extensive, searchable database that consists of tens of thousands of detailed articles containing technical information, bug lists, fix lists, and more. 923 As you know, the Internet is a dynamic entity that tends to change rapidly. Web sites are often reorganized (especially those at the microsoft.com domain). Therefore, a particular URL listed in this Appendix...

Common properties

Although each control has its own unique set of properties, many controls have some common properties. For example, every control has a Name property and properties that determine its size and position (Height, Width, Left, and Right). If you're going to manipulate a control by using VBA, it's an excellent idea to provide a meaningful name for the control. For example, the first OptionButton that you add to a UserForm has a default name of OptionButton1. You refer to this object in your code...

S

Sachs, Jonathan (Lotus 1-2-3 inventor), 4 sales commission rate calculator based on years, 292 one-argument, 289-291 all workbooks, 340-341 chart as a file, 499-500 and closing all workbooks, 341 Loan Amortization Wizard default settings, 794-796 schedule, macro running, 895 scoping formula names, 51 Function procedure, 283-284 variables, 199-204, 895-896 VBA procedures, 239-240 screen. See display Script Editor, 82, 136 scrolling label, Help display, 775-776 UserForm sheet, 451-454 seamless...

Adding An Item To The Addins Collection

The add-in files that make up the AddIns collection can be stored anywhere. Excel maintains a partial list of these files and their locations in the Windows Registry. For Excel 2003, this list is stored at Manager You can use the Windows Registry Editor (regedit.exe) to view this Registry key. Note that the standard add-ins that are shipped with Excel do not appear in this Registry key. In addition, add-in files stored in the following directory will also appear in the list but will not be...

Programming dependencies in a wizard

In most wizards, a user's response on a particular step can affect what's displayed in a subsequent step. In the CD-ROM example, the user indicates which products he or she uses in Step 3 and then rates those products in Step 4. The OptionButtons for a product's rating are visible only if the user has indicated a particular product. Programmatically, this is accomplished by monitoring the MultiPage's Change event. Whenever the value of the MultiPage is changed (by clicking the Back or Next...

Setting up the Multi Page control for the wizard

Start with a new UserForm and add a MultiPage control. By default, this control contains two pages. Right-click the MultiPage tab and insert enough new pages to handle your wizard (one page for each wizard step). The example on the CD-ROM is a four-step wizard, so the MultiPage control has four pages. The names of the MultiPage tabs are irrelevant. The MultiPage control's Style property will eventually be set to 2 - fmTabStyleNone. While working on the UserForm, you'll want to keep the tabs...

Removing An Item From The Addins Collection

Oddly, there is no direct way to remove an add-in from the Addlns collection. The Addlns collection does not have a Delete or Remove method. One way to remove an add-in from the Add-Ins dialog box is to edit the Windows Registry database (using regedit.exe). After you do this, the add-in will not appear in the Add-Ins dialog box the next time that you start Excel. Note that this method is not guaranteed to work with all add-in files. Another way to remove an add-in from the Addlns collection is...

Using array formulas to count and sum

If none of the standard counting techniques fits the bill, you may be able to construct an array formula (see Array Formulas earlier in this chapter). Don't forget When you enter an array formula, press Ctrl+Shift+Enter. To count the number of numerical values (skipping text and blanks), use this array formula To count the number of cells that contain an error value, use this array formula To count the number of unique numeric values (skipping text, blanks not allowed), use this array formula...

The Loan Amortization Wizard workbook structure

The Loan Amortization Wizard consists of the following components FormMain A UserForm that serves as the primary user interface. FormHelp A UserForm that displays online help. HelpSheet A worksheet that contains the text used in the online help. ModMain A Visual Basic for Applications (VBA) module that contains a procedure that displays the main UserForm. ThisWorkbook The code module for this object contains the event handler procedures Workbook_Open and Workbook_BeforeClose, which create and...

The Excel Get SaveAsFilename Method

The GetSaveAsFilename method is very similar to the GetOpenFilename method. It displays a Save As dialog box and lets the user select (or specify) a file. It returns a filename and path but doesn't take any action. The syntax for this method is FileFilter, Filterlndex, Title, ButtonText) InitialFilename Optional. Specifies the suggested filename. FileFilter Optional. A string specifying file-filtering criteria. Filterlndex Optional. The index number of the default file-filtering criteria. Title...

Listing Excel menu information

The ListMenuInfo procedure, which follows, might be instructive for those who will be customizing Excel menus. It displays the caption for each item (menu, menu item, and submenu item) on the Worksheet Menu Bar. Dim SubMenuItem As CommandBarControl For Each Menu In CommandBars(1).Controls For Each MenuItem In Menu.Controls For Each SubMenuItem In MenuItem.Controls Cells(row, 1) Menu.Caption Cells(row, 2) MenuItem.Caption Cells(row, 3) SubMenuItem.Caption row row + 1 Next SubMenuItem Next...

Displaying All Components in a VBA Project

The ShowComponents procedure, shown in Listing 28-1, loops through each VBA component in the active workbook and writes the following information to a worksheet The number of lines of code in the code module for the component Listing 28-1 Displaying Each Active VBA Component in a Worksheet Dim VBP As VBProject Set VBP ActiveWorkbook.VBProject NumComponents VBP.VBComponents.Count Cells.ClearContents For i 1 To NumComponents Name Cells(i, 1) VBP.VBComponents(i).Name Type Select Case...

Creating an addin

To create an add-in, do the following 1. Activate the VBE and select the future add-in workbook in the Project window. 2. Choose Debug Compile. This step forces a compilation of the VBA code and also identifies any syntax errors so that you can correct them. When you save a workbook as an add-in, Excel creates the add-in even if it contains syntax errors. 3. Choose Tools xxx Properties to display the Project Properties dialog box (where xxx represents the name of the project). Click the General...

Cleaning up recorded macros

Earlier in this section, you saw how recording your actions while you issued a single command the File Page Setup command can produce an enormous amount of VBA code. In many cases, the recorded code includes extraneous commands that you can delete. It's also important to understand that the macro recorder doesn't always generate the most efficient code. If you examine the generated code, you'll see that Excel generally records what is selected that is, an object and then uses the Selection...

The User Form for the Text Tools utility

When I create a utility, I usually begin by designing the user interface. In this case, it's the dialog box that's displayed to the user. Creating the dialog box forces me to think through the project one more time. Figure 16-2 shows the UserForm for the Text Tools utility. Notice that the configuration of the controls varies, depending on which option is selected. BS text tools.xla - UserForml UserForm Figure 16-2 The UserForm for the Text Tools utility. Figure 16-2 The UserForm for the Text...