Excel

Worksheets and Chart Sheets

2009-08-01T02:24:00.000-07:00

Because the CodeNames of worksheets and chart sheets in your project are treated by VBA as intrinsic object variables that reference those sheets, the CodeNames given to worksheets and chart sheets should follow variable naming conventions. Worksheet CodeNames are prefixed with wks to identify them in code as references to Worksheet objects. Similarly, chart sheets are prefixed with cht to identify them as references to Excel Chart objects.

For both types of sheets, the prefix should be followed by a descriptive term indicating the sheet's purpose in the application. Figure 3-4, for example, shows a wksCommandBars worksheet that contains a table defining the command bars created by the application. For sheets contained within an add-in or hidden in a workbook and not designed to be seen by the user, the sheet tab name should be identical to the CodeName. For sheets that are visible to the user, the sheet tab name should be a friendly name, and one that you should be prepared for the user to change. As discussed later, you should always rely on sheet CodeNames rather than sheet tab names within your VBA code.

Modules, Classes and Userforms

2009-07-31T22:20:00.000-07:00

In our sample naming convention, the names of standard code modules should be prefixed with an uppercase M, class modules with an uppercase C and userforms with an upper case F. This has the advantage of neatly sorting these objects in the VBE Project window if you don't care for the folder view.

This convention also makes code that uses classes and userform objects much clearer. In the following code sample, for example, this naming convention makes it very clear that you are declaring an object variable of a certain class type and then creating a new instance of that class:

Dim clsMyClass As CMyClass
Set clsMyClass = New CMyClass

In each case, the name on the left is a class variable, and the object on the right is a class.

Procedures

2009-06-21T19:14:00.000-07:00

Subroutines and functions are grouped under the more general term procedure. Always give your procedures very descriptive names. Once again, you are allowed up to 255 characters for your procedure names, and procedure names appear in the Ctrl+Spacebar auto-complete list, so don't sacrifice a name that makes the purpose of a procedure obvious for one that's simply short.

It is not a common practice to do so, but we find that giving functions a prefix indicating the data type of their return value to be very helpful in understanding code. When calling a function, always place open and closed parenthesis after the function name to distinguish it from a variable or subroutine name, even if the function takes no arguments. Listing 3-1 shows a well-named Boolean function being used as the test for an If...Then statement.

Listing 3-1. An Example of Naming Conventions for Function Names

If bValidatePath("C:\Files") Then
   ' The If...Then block is executed
   ' if the specified path exists.
End If

Subroutines should be given a name that describes the task they perform. For example, a subroutine named ShutdownApplication leaves little doubt as to what it does. Functions should be given a name that describes the value they return. A function named sGetUnusedFilename() can reasonably be expected to return a filename.

The naming convention applied to procedure arguments is exactly the same as the naming convention for procedure-level variables. For example, the bValidatePath function shown in Listing 3-1 would be declared in the following manner:

Function bValidatePath(ByVal sPath As String) As Boolean

Naming Convention Examples

2009-06-04T20:12:00.000-07:00

Naming convention descriptions in the abstract are difficult to connect to real-world names, so we show some real-world examples of our naming convention in this section. All of these examples are taken directly from commercial-quality applications written by the authors.

Variables

gsErrMsg A public variable with the data type String used to store an error message
mauSettings() A module-level array of user-defined type that holds a list of settings
cbrMenu A local variable with the data type CommandBar that holds a reference to a menu bar

Constants

gbDEBUG_MODE A public constant of type Boolean that indicates whether the project is in debug mode
msCAPTION_FILE_OPEN A module-level constant of data type String that holds the caption for a user-defined file open dialog (Application.GetOpenFilename in this instance)
lOFFSET_START A local constant of data type Long holding the point at which we begin offsetting from some Range object

User-Defined Types

The following is a public user-defined type that is used to store the dimensions and location of an object. It consists of four variables of data type Double that store the top, left, width and height of the object and a variable of data type Boolean used to indicate whether the settings have been saved.

Public Type DIMENSION_SETTINGS
   bSettingsSaved As Boolean
   dValTop As Double
   dValLeft As Double
   dValHeight As Double
   dValWidth As Double
End Type

The variables within a user-defined type definition are called member variables. These can be declared in any order. However, our naming convention suggests you sort them alphabetically by data type unless there is a strong reason to group them in some other fashion.

Enumerations

The following is a module-level enumeration used to describe various types of days. The sch prefix in the name of the enumeration stands for the application name. This enumeration happens to come from an application called Scheduler. DayType in the enumeration name indicates the purpose of the enumeration and each of the individual enumeration members has a unique suffix that describes what it means.

Private Enum schDayType
   schDayTypeUnscheduled
   schDayTypeProduction
   schDayTypeDownTime
   schDayTypeHoliday
End Enum

If you don't indicate what values you want to give your enumeration members, VBA automatically assigns a value of zero to the first member in the list and increments that value by one for each additional member. You can easily override this behavior and assign a different starting point from which VBA will begin incrementing. For example, to make the enumeration above begin with one instead of zero, you would do the following:

Private Enum schDayType
   schDayTypeUnscheduled = 1
   schDayTypeProduction
   schDayTypeDownTime
   schDayTypeHoliday
End Enum

VBA will continue to increment by one for each member after the last member for which you've specified a value. You can override automatic assignment of values to all of your enumeration members by simply specifying values for all of them.

Figure 3-3 shows one of the primary advantages of using enumerations. VBA provides you with an auto-complete list of potential values for any variable declared as a specific enumeration.

Procedures

Listing 3-1. An Example of Naming Conventions for Function Names

If bValidatePath("C:\Files") Then
   ' The If...Then block is executed
   ' if the specified path exists.
End If

Function bValidatePath(ByVal sPath As String) As Boolean

A Sample Naming Convention

2009-06-03T20:00:00.000-07:00

A good naming convention applies not just to variables, but to all the elements of your application. The sample naming convention we present here covers all the elements in a typical Excel application. We begin with a discussion of variables, constants and related elements, because these are the most common elements in any application. Table 3-1 shows the general format of the naming convention. The specific elements of the naming convention and their purposes are described afterward.

Table 3-1. A Naming Convention for Variables, Constants, User-Defined Types and Enumerations
Element	Naming Convention
Variables	`DescriptiveName`
Constants	`DESCRIPTIVE_NAME`
User-defined types	Type DESCRIPTIVE_NAME DescriptiveName End Type
Enumerations	Enum GeneralDescr GeneralDescrSpecificName1 GeneralDescrSpecificName2 End Enum

The Scope Specifier ()

g Public

m Module level

(nothing) Procedure level

The Array Specifier ()

a Array

(nothing) Not an array

The Data Type Specifier ()

There are so many data types that it's difficult to provide a comprehensive list of prefixes to represent them. The built-in types are easy. The most frequently used built-in types get the shortest prefixes. Problems arise when naming object variables that refer to objects from various applications. Some programmers use the prefix obj for all object names. This is not acceptable. However, devising consistent, unique and reasonably short prefixes for every object type you will ever use is also probably too much to ask. Try to find reasonably meaningful one- to three-letter prefixes for the object variables you use most frequently and reserve the obj prefix for objects that appear infrequently in your code.

Make your code clear, and above all, be consistent. Keep data type prefixes to three characters or fewer. Longer prefixes, in combination with scope and array specifiers, make for unwieldy variable names. Table 3-2 shows some suggested prefixes for the most commonly used data types.

Table 3-2. Suggested Naming Convention Prefixes
Prefix	Data Type	Prefix	Data Type	Prefix	Data Type
b	Boolean	cm	ADODB.Command	cbo	MSForms.ComboBox^[*]
byt	Byte	cn	ADODB.Connection	chk	MSForms.CheckBox
cur	Currency	rs	ADODB.Recordset	cmd	MSForms.CommandButton
dte	Date			ddn	MSForms.ComboBox^[**]
dec	Decimal	cht	Excel.Chart	fra	MSForms.Frame
d	Double	rng	Excel.Range	lbl	MSForms.Label
i	Integer	wkb	Excel.Workbook	lst	MSForms.ListBox
l	Long	wks	Excel.Worksheet	mpg	MSForms.MultiPage
obj	Object			opt	MSForms.OptionButton
sng	Single	cbr	Office.CommandBar	spn	MSForms.SpinButton
s	String	ctl	Office.CommandBarControl	txt	MSForms.TextBox
u	User-defined type
v	Variant	cls	User-defined class variable	ref	RefEdit Control
		frm	Userform variable	col	VBA.Collection

^[*] Used for ComboBox controls with a DropDownCombo Style setting.

^[**] Used for ComboBox controls with a DropDownList Style setting.

Using Descriptive Names

VBA gives you up to 255 characters for each of your variable names. Use a few of them. Don't try to save yourself a little effort by making your variable names very short. Doing so will make your code difficult to understand in the long run, both for you and for anyone else who has to work on it.

The Visual Basic IDE provides an auto-complete feature for identifiers (all the names used in your application). You typically need to type only the first few characters to get the name you want. Enter the first few characters of the name and press Ctrl+Spacebar to activate an auto-complete list of all names that begin with those characters. As you type additional characters, the list will continue to narrow down. In Figure 3-1, the Ctrl+Spacebar shortcut has been used to display a list of message string constants available to add to a message box.

A Few Words About Enumerations

Enumerations are a special type of constant available in Excel 2000 and higher. They enable you to take a list of related values and package them up with similar, logical friendly names. VBA and the Excel object model make extensive use of enumerations. You can see these in the auto-complete list that VBA provides for the values of many properties. For example if you type:

Sheet1.PageSetup.PaperSize =

into a VBA module, you'll be prompted with a long list of XlPaperSize enumeration members that represent the paper sizes available to print on. Figure 3-2 shows this in action.

These names actually represent numeric constants whose values you can examine if you look them up in the Object Browser, discussed in Chapter 16 VBA Debugging. Notice the structure of these enumeration names. First, they all begin with a prefix identifying the application they are associated with, in this case xl, which obviously stands for Excel. Second, the first part of their name is a descriptive term that ties them together visually as belonging to the same enumerated type, in this case Paper. The last part of each enumeration name is a unique string describing the specific value. For example, xlPaper11x17 represents 11x17 paper and xlPaperA4 represents A4 paper. This system for naming enumerated constants is very common and is the one we use in this book.

Naming Conventions

2009-06-01T19:54:00.000-07:00

What Is a Naming Convention and Why Is It Important

The term naming convention refers to the system you use to name the various parts of your application. Whenever you declare a variable or create a userform, you give it a name. You implicitly name objects even when you do not give them a name directly by accepting the default name provided when you create a userform, for example. One of the hallmarks of good programming practice is the consistent use of a clearly defined naming convention for all parts of your VBA application.

Let's look at an example that may help demonstrate why naming conventions matter. In the following line of code, what do you know about x?

x = wksDataSheet.Range("A1").Value

From its usage you can reasonably assume it is a variable. But what data type is it designed to hold? Is its scope public, module level, or private? What is its purpose in the program? As it stands, you cannot answer any of these questions without spending some time searching through the rest of the code. A good naming convention conveys the answers to these questions with a simple visual inspection of the variable name. Here's a revised example. (We cover the specifics in detail in the next section.)

glListCount = wksDataSheet.Range("A1").Value

Now you know the scope of the variable (g stands for global or public scope), what data type it was designed to hold (l stands for the Long data type) and have a rough idea of the purpose of the variable (it holds the number of items in a list).

A naming convention helps you to immediately recognize the type and purpose of the building blocks used in an application. This enables you to concentrate on what the code is doing rather than having to figure out how the code is structured. Naming conventions also help make your code self-documenting, reducing the number of comments required to make the purpose of your code clear.

We present an example of a well-structured naming convention in the following section. However, the most important thing about naming conventions is that you pick one and use it consistently. As long as everyone involved in a project understands the naming convention, it doesn't really matter exactly what prefixes you use or how your names are capitalized. When it comes to naming conventions, consistency rules, both across projects and over time.

Dictator Applications

2009-05-23T19:13:00.000-07:00

All of the architectures we have considered so far have sought to enhance Excel in some way to improve the end user's experience when using our application. In contrast, dictator applications seek to completely take over the Excel user interface, replacing Excel's menus with their own and exercising a very high level of control over the user. In the ideal dictator application, users cannot tell they are inside Excel.

These applications are created in Excel to use the features Excel provides, but those features are entirely controlled by the application and rarely (if ever) exposed to the user. Instead, the user interface is made up of tightly controlled data-entry worksheets and/or userforms, designed to appear like any other Windows application. These applications typically require large amounts of code to be able to have that level of control, but that degree of control enables us to write full-scale, fully functional Windows applications, on a par with any that can be written in Visual Basic or other "mainstream" application-development platforms. Indeed, by building our application within Excel, we are immediately able to utilize the incredible amount of functionality Excel provides.

If there is a requirement for our dictator applications to work in Excel 97, we are not able to have userforms and menu items available at the same time, because userforms are always modal. We therefore have the choice to base our user interface on using worksheets and menus, using userforms with buttons to navigate between the forms or by building modeless forms using VB6. Excel 2000 introduced modeless userforms, enabling us to make both userforms and menus available and allowing us much more flexibility when deciding whether to use a form or worksheet for the user interface. For this reason, we recommend that, if possible, Excel 2000 should be set as the lowest-level version dictator applications are designed to work within.

As dictator applications get more and more complex, they will often start to use functionality that only exists in the most recent versions of Excel (such as the XML import/export introduced in Excel 2003), so youas the designer of the applicationneed to decide what should happen if the application is opened in an older version. If the functionality being used is a core part of the application, it is unlikely the application will be usable at all in older versions. If the use of the new features can be limited to a small part of the application, it might make more sense to just disable that menu item, or provide separate routines for older versions to use. Making use of new Excel features will often result in compile errors if the workbook were to be opened in an older version, so many dictator applications use a "front-loader" workbook to do an initial version check, check whether all external dependencies (such as Outlook) are available, and then open and run the main application workbook if all the checks are okay. If the checks fail, we can provide meaningful error messages to the end user (such as "This application requires Excel 2000 or above and will not work in Excel 97").

There's no escaping the fact that dictator applications are much more complicated than either self-automated workbooks or application-specific add-ins and will require an intermediate to advanced-level Excel/VBA developer to create and maintain them. Although this type of architecture can get very complicated, the complexity can be mitigated by following the best-practices advice discussed in Chapter 3 Excel and VBA Development Best Practices (general advice) and Chapter 6 Dictator Applications (specific advice for dictator applications).

After the decision to build a dictator application has been made, we have an incredible amount of flexibility in terms of physically creating the application. The data could be stored in one or more separate workbooks, local databases (for example, Access databases), or a central database (for example, SQL Server). We could decide to include all the code in a single workbook or have a small "core" add-in, with numerous little applets that plug in to the core to provide the functionality, where each applet performs a single, specific task. The decision will probably be a trade-off between (at least) the following considerations:

A single-workbook structure is easier for a single developer to maintain, because everything is in the one place.
A multiple-workbook structure is easier for a team of developers to create, because each developer can work on his own applet without conflicting with another team member.
If a multiple-workbook structure is built so each plug-in applet is not loaded until it is first used, the initial opening of the core add-in will be quicker than loading the full application of the single-workbook structurealthough modern PCs might make that difference appear immaterial.
A single-workbook structure must be updated in its entirety, but the applets of a multiple-workbook structure can be updated and deployed independently.
The code required to implement a multiple-workbook plug-in architecture is quite complex, and might be too complex for the intermediate VBA developer to fully understandalthough we explain it in Chapter 11 Interfaces.

Requirements of a Dictator Application

To look and operate like a standalone Windows application, a dictator application needs to modify many Application properties, from turning on IgnoreOtherApplications (so double-clicking an XLS file in Explorer will not use our instance of Excel) to turning off ShowWindowsInTaskBar in Excel 2000 and above (because we may have multiple workbooks to be managed under program control), as well as hiding all the command bars. Unfortunately, Excel will remember many of those settings, to reuse them the next time Excel is started, so every dictator application must start by recording the existing state of all the settings that will be changed and restore them all when it closes. If the code to do this is written as two separate routines and assigned shortcut keys, they also provide an easy way to switch between the application's display and Excel's during development.

After a snapshot of the user's settings has been taken, the dictator application can set the application properties it requires; it then needs to lock down Excel to prevent users from doing things we do not want them to do, including the following:

Hiding and disabling all the command bars (including the shortcut command bars), then setting up our own.
Protecting our command bars and disabling access to the command bar customization dialog.
Disabling all the shortcut key combinations that Excel provides, and then optionally re-enabling the few we want to be exposed to the user.
Setting Application.EnableCancelKey to xlDisabled at the start of every entry point, to prevent users stopping the code.
When using worksheets as data-entry forms, we don't want the user to be able to copy and paste entire cells, because that includes the formatting, data validation, and so on. Therefore we need to turn off drag and drop (which does a cut and paste); trap both Ctrl+X and Shift+Delete to do a copy rather than a cut; and trap Ctrl+V, Shift+Insert, and the Enter keys to ensure we only ever paste values.

Having locked down the Excel environment while our application is running, we need to provide a mechanism for the developers to access the code, to enable them to debug the application. One method is to set a global IsDevMode Boolean variable to True if a particular file exists in the application directory or (more securely) depending on the Windows username. This Boolean can then be used throughout the application to provide access points, such as enabling the Alt+F11 shortcut to switch to the VBE, adding a Reset menu item and/or shortcut key to switch back to the Excel environment and not setting the EnableCancelKey property, to allow the developer to break into the code. The Boolean can also be used within error handlers, to control whether to display a user- or developer-oriented error message.

Structure of a Dictator Application

A typical dictator application uses the following logical structure:

A front-loader/startup routine to perform version and dependency checks and so on
A main "core" set of routines, to do the following:

Take a snapshot of the Excel environment settings and to restore those settings

Configure and lock down the Excel application

Create and remove the application's command bars

Handle copying and pasting data within the worksheet templates

Provide a library of common helper routines and classes

(Optionally) Implement a plug-in architecture using class modules, as described in Chapter 11 Interfaces
A backdrop worksheet, to display within the Excel window while userforms are being shown, usually with some form of application-specific logo (if we are primarily using forms for the user interface)
Multiple independent applets that provide the application's functionality
Multiple template worksheets used by the applets, such as data-entry forms or preformatted report templates

Application-Specific Add-ins

2009-05-22T19:12:00.000-07:00

As mentioned previously, the main problem with both codeless and self-automated workbooks is that the "program" is physically stored in the same file as the data the end user types in or otherwise works with. It is very difficult to reliably update the program part of those workbooks without affecting or in most cases destroying the end-user's work. The alternative is to structure the application such that all the code is contained within one workbook, with a separate workbook for the end user to use for data entry, analysis, and so forth. One such architecture is that of an application-specific add-in. These are very similar to normal add-ins, but instead of immediately setting up their menu items, keyboard hooks, and so on, they stay invisible until the user opens a workbook the add-in can identify as one for which it should make itself available.

Typically, the user would be supplied with at least two workbooksthe XLA add-in workbook and a template workbook to use for data entry. The template workbook(s) will contain some kind of indicator the add-in can use to identify it, usually either a hidden defined name or a custom document property.

The key benefit of using an application-specific add-in is that we can safely distribute updates to the code, knowing we will not be destroying the user's data. There is, however, a small price to pay for this convenience:

Splitting the application into two (or more) workbooks makes it slightly harder to manage, because we have to keep the correct versions of both workbooks synchronized during the development process. Simple version control is discussed in more detail in Chapter 3 Excel and VBA Development Best Practices.
The application is slightly harder for other developers to understandparticularly if they are used to single-workbook applications or do not understand the technique of using class modules to hook application-level events, as explained in Chapter 7 Using Class Modules to Create Objects.
Deployment is more complicated because we need to distribute multiple files. Deployment strategies are discussed in Chapter 24 Providing Help, Securing, Packaging, and Distributing.

Structure of an Application-Specific Add-in

Application-specific add-ins are very similar in structure to general-purpose add-ins, but with extra code to identify when to enable or disable the menu items:

A class module used to trap the application-level events.
Code in an Auto_Open or Workbook_Open routine adds the add-in's menu items. Each menu item has its OnAction property set to call the appropriate routine in the add-in file, but they are all initially either disabled or hidden. It then creates an instance of the class module and initializes application event hooks.
Separate routines for each menu item, located in a standard module.
(Optionally) Public functions, located in a standard module, exposed for use in worksheet functions.
Code in the class module hooks the application-level Workbook Activate event, checks whether the new workbook has the correct custom document property, and if so enables the menu items and sets up the keyboard hooks.
Code in the class module hooks the application-level Workbook Deactivate event, disables the menu items, and removes the keyboard hooks.
Code in an Auto_Close or Workbook_Close routine removes the add-in's menu items.

Function and General-Purpose Add-ins

2009-05-21T19:11:00.000-07:00

An add-in is a specific type of application, usually used to add features to Excel. The worksheets in an add-in are hidden from the user, so they never interact directly with the workbook. Instead, the add-in exposes its routines by adding items to Excel's menus and toolbars, hooking key combinations and/or exposing functions to be used within a worksheet. The routines in an add-in can also be executed by typing their fully qualified name (for example, MyAddin.xla!MyRoutine) in the Tools > Macro > Macros dialog, although they do not appear in the list of available macros.

The routines in a general-purpose add-in will always be available to the Excel user, so they are most appropriate when used for utility functions that are designed to work with any file, typically using the ActiveWorkbook, ActiveSheet or Selection objects to identify the items to operate on. Care should be taken to tidily handle "user error" issues, where the add-in's routines may be called from a context in which they will not work. For example, if your add-in changes the case of the text in the selected cell, you must check that there is a cell selected (and not a drawing object, for example), it isn't locked and it isn't the result of a formula. Similarly, if your code applies some custom formatting to the active worksheet, you must check that there is an active sheet (because there may be no workbooks open), it is a worksheet (and not a chart or macro sheet) and it is not protected.

An add-in is just a very hidden workbook, so it doesn't appear in the list of workbooks or the VBA Workbooks collection, and its macros are not listed in the Tools > Macro > Macros dialog. It is, however, just like any other workbook in almost every other way and should therefore be very easy for an intermediate Excel/VBA developer to understand and maintain. Indeed, you can toggle between having the workbook behave like an add-in or not by just changing the IsAddin property of the ThisWorkbook object in the VBE's Properties window.

Because add-ins never expose their worksheets to the user, all the user interaction is done through userforms (although VBA's InputBox and MsgBox functions can be used in simple situations). This gives us a high level of control over the user's entries, enabling us to create applications that are totally robust and reliableassuming we include data-validation code and good error handling.

If the add-in needs to store any information, such as remembering a user's choices, that information should be kept separate from the add-in file, by storing it either in the registry (using SaveSetting/ GetSetting) or in a separate file (such as an INI file). If that is done, the add-in will never need to be saved by the end user and can be simply replaced by a new version if an update is required.

If you are willing to trust the end user to install the add-in correctly, it is also very easy to deployjust send the XLA file with instructions to either copy it to their Library folder or to use the Browse button in the Tools > Add-ins dialog to locate the file. The alternative is to use an installation routine to write the required registry entries Excel uses to maintain its add-ins list, such that the add-in is automatically opened and installed when the client next starts Excel. These registry entries are covered in detail in Chapter 24 Proving Help, Securing, Packaging and Distributing.

Structure of a Function or General-Purpose Add-in

Most general-purpose add-ins use the same structure:

Code in an Auto_Open or Workbook_Open routine that adds the add-in's menu items and sets up the keyboard hooks. Each menu item has its OnAction property set to call the appropriate routine in the add-in file.
Separate routines for each menu item, located in a standard module.
Public functions, located in a standard module, exposed for use in worksheet formulas. Dedicated function add-ins often contain only functions and do not add menu items.
Code in an Auto_Close or Workbook_Close routine that removes the add-in's menu items and clears the keyboard hooks.

Self-Automated Workbooks

2009-05-20T19:10:00.000-07:00

Self-Automated Workbooks

A self-automated workbook is one in which the VBA code is physically contained within the workbook it acts upon. Probably the most common type of application, the automation code can be as simple as ensuring the workbook always opens with Sheet1 active or be as complex as an entire application. This is usually the first type of application a beginning VBA developer produces, built by adding numerous helper routines to the workbook that get progressively more complex (and usually cumbersome) over time.

After VBA is introduced to the workbook, we have much more flexibility in providing the required functionality and can make a considered choice whether to use Excel's built-in functions or write our own equivalents to avoid some of Excel's pitfalls. For example, Excel's data validation might not fire correctly when entries are made in multiple cells at the same time and is usually cleared when data is pasted onto the range. We can work around both these limitations by trapping the Worksheet_Change event and performing our own validation in code, making the worksheet more robust, reliable, and secure.

The workbook and worksheet code modules are provided for us by Excel to hook whichever events we want to use, and any ActiveX controls we add to the worksheet are automatically exposed in the same code module. This is the simplest architecture to create and probably the simplest to understandmost VBA developers will have written an application of this type and will therefore understand, for example, how the code within the worksheet code module is triggered.

The clearest advantage this style of application has over all the others is in the ease of deployment. There is only one filethe workbookto distribute; there is no need to install or configure anything; and because the code is physically stored within the workbook, it is immediately available and working as soon as the workbook is opened.

Unfortunately, the self-automated workbook's clearest advantage is also its biggest problem. Because the code is physically inside the workbook, how do you issue updates to the code without affecting the data that has been entered on the worksheets? Although it is possible to write VBA that modifies the code within another workbook, the user has to specifically allow that to happen (in Excel 2002 and above), and it is only possible to unprotect and reprotect the VBA project using SendKeys, which cannot be relied on to work in foreign-language versions of Excel or if Excel does not have the focus. Even if the project could be unprotected and reprotected, saving the updated project would remove any digital signature that had been applied, resulting in macro virus warnings every time the workbook was subsequently opened. The only reliable way self-automated workbooks can be updated is to provide a completely new workbook with a routine (or instructions) to copy the data from the old workbook.

Self-automated workbooks are an ideal choice if the following conditions apply:

The routines contained within the workbook provide specific functionality for that workbook (as opposed to general-purpose utilities).
There will only be one copy of the workbook (so any changes can be done directly to that workbook), or

The workbook will have a short lifetime and/or will be distributed to a large (and maybe unknown) audience, in which case the ease of deployment becomes a significant consideration, and there is no intention to distribute any updates, or

The workbook does not contain any data that will need to be retained during an update, such as one that obtains its data from an external data source.

Codeless Applications

2009-05-19T19:09:00.001-07:00

Codeless Applications

The most basic application is one that only uses Excel's built-in functionality. Everybody creates this type of application without knowing it, simply by using Excel. They are typically created by beginning to intermediate Excel users who have not yet learned to use VBA. All the special formatting, validation, formulas and so on are placed directly on the same worksheet where data entry will be performed. There are some major issues with this approach, which together mean totally codeless applications are rarely a good choice.

Those who avoid using VBA may find that some of their worksheet functions and data-validation criteria get extremely complex and almost incomprehensible to anyone other than the author. The equivalent VBA will often be easier to understand.

The same worksheet is usually used for data entry, analysis, and presentation, often resulting in a cluttered appearance (usually with multitudes of differently colored cells to try to identify their purpose) that is difficult to understand, is unintuitive to use and is almost impossible for anyone except the author to modify reliably.

Such applications have to rely on Excel's cell protection and password-protecting the worksheet to prevent the users making unauthorized changes to the formulas, validation, formatting and so on. Worksheet passwords are notoriously easy to break, and a simple copy and paste will wipe out any data validation that has been set up. These applications are therefore neither secure nor robust.

Without any code, we are unable to provide much assistance to the user; we have to rely on them to do everything themselvesand do it correctlyinstead of providing reliable helper routines that automate some of their tasks. The more complex the application, the less likely it is that all the tasks will be performed correctly.

If we consider a definition of what constitutes a "program" to be "anything that isn't the data," we see that all the conditional formatting, data validation, worksheet functions and so forth are really part of the "program," so these applications break the basic tenet of keeping the program and data physically separate. After the end users have started to enter data into their worksheet, it is very difficult to distribute an updated worksheet to them, without losing the data they have already entered. In addition to the new version of the worksheet, you would have to either provide clear instructions explaining how to copy their data across or write a conversion program that will copy their data from the old to the new workbook.

Codeless applications are ideal for simple situations where most of the following conditions apply:

There will only be one copy of the workbook (so any changes can be done directly to that workbook), or each copy of the workbook will have a short lifetime, such as a survey or data-collection form that can be forgotten about after it has been completed and returned. In each case, the assumption is that the workbooks will not need up dating after they have been deployed.
The end users will be maintaining the workbook themselves (and don't know VBA), or the workbook will not require any maintenance at all.
The workbook might be copied and updated on different machines (although only one at a time), such as when the workbook is taken home for the weekendbecause it is self-contained, it can easily be copied to a floppy disk or e-mailed.
There are relatively few routine or complex tasks to be performed to maintain or analyze the data.
There are only a small number of end users, who can be well trained to ensure the application is used correctly and is not inadvertently broken.

A good example of a codeless application is a simple survey or data-collection form that requires the end user to fill in the details and e-mail the completed workbook to a central address for consolidation and analysis. The main benefit of a codeless application in such a situation is the avoidance of Excel's macro security warnings and the corresponding assurance that there is nothing malicious in the file.

Excel as an Application Development Platform

2009-05-15T18:20:00.000-07:00

If we look at Excel as a development platform and not just a spreadsheet, we can break it down into five fundamental components we can use for our applications:

The worksheet, charts and so on, used as a user interface and presentation layer for data entry and reporting
The worksheet, used as a simple data store for lists, tables and other information used by our application
VBA, Excel's programming language and forms engine
The worksheet, used as a declarative programming language for high-performance numeric processing
The Excel object model, allowing programmatic control of (nearly) all of Excel's functionality, from both within Excel and from outside it

The Worksheet as a Presentation Layer for Data Entry and Reporting

When most people think about Excel, they think in terms of typing numbers into cells, having some calculations update and seeing a result displayed in a different cell or on a chart. Without necessarily thinking in such terms, they are using the worksheet as a user interface for their data entry and reporting and are generally comfortable with these tasks. The in-cell editing, validation and formatting features built in to Excel provide an extremely rich and compelling data-entry experience, while the charting, cell formatting and drawing tools provide a presentation-quality reporting mechanism. It is hard to imagine the code that would be required if we tried to reproduce the experience using the form design tools available in most other development environments, yet it's there waiting for us to use in our Excel-based applications. The biggest problem we face is how to add some structure to the free-form grid of the worksheet, to present a simple and easy-to-use interface, while leveraging the rich functionality Excel provides. Chapter 4 Worksheet Design introduces some techniques and best practices for developing worksheet-based data-entry forms, and Chapter 15 Advanced Charting Techniques discusses using Excel's charting capabilities.

The Worksheet as a Simple Data Store

What is a worksheet when it is never intended to be shown to the end user? At its simplest, it is no more than a large grid of cells in which we can store just about anything we want tonumbers, text, lists, tables or pictures. Most applications use some amount of static data or textual or graphical resources; storing that information in a worksheet makes it both extremely easy to access using VBA and simple to maintain. Lists and tables in worksheets can directly feed Excel's data validation (as shown in Chapter 4 Worksheet Design), greatly simplify the creation and maintenance of command bars (Chapter 8 Advanced Command Bar Handling), and enable us to construct dynamic userforms (Chapter 10 Userform Design and Best Practices).

VBA: Excel's Programming Language

We expect most readers of this book to have at least some familiarity with VBA. If not, we suggest you read either our Excel 2000/2002 VBA Programmer's Reference or John Walkenbach's Excel Power Programming before continuing much further. Many people see the A in VBA as meaning the language is somehow less than Visual Basic itself. In fact, both VB6 and Office 2000 and above use exactly the same DLL to provide the keywords, syntax and statements we program with. The only differences are the objects provided by the runtimes (the VB runtime vs. the Excel objects), the forms packages (VB's "Ruby" forms vs. Office UserForms) and that VB6 includes a compiler to create EXEs and DLLs, whereas VBA is always interpreted at runtime. Indeed, the Office Developer Edition (pre-Excel 2003) includes the same compiler VB6 uses, enabling us to compile (simple) DLLs from within the Office Visual Basic Editor.

Most beginner and intermediate VBA developers use VBA as a purely procedural language, with nearly all their code residing in standard modules. VBA also enables us to create applications using an object-oriented programming (OOP) approach, in which class modules are used to create our own objects. Chapter 7 Using Class Modules to Create Objects and Chapter 11 Interfaces explain how to use VBA in this manner, while basic OOP concepts (such as encapsulation) are used throughout the book.

Most of this book is dedicated to explaining advanced VBA techniques and a professional approach to application design and development that can put using VBA in Excel on a par with, and sometimes in front of, using VB6 or VB.Net for application development. We also show in Chapter 20 Combining Excel and Visual Basic 6 and Chapter 22 Using VB.NET and the Visual Studio Tools for Office that the Excel developer can use the best of both worlds, by combining Excel, VB6 and/or VB.Net in a seamless application.

The Worksheet as a Declarative Programming Language

Consider the following code:

dSales = 1000
dPrice = 10.99
dRevenue = dSales * dPrice

This code could quite easily be a few lines of VBA. We give the variable dSales a value of 1000, the variable dPrice a value of 10.99 and then calculate the revenue as sales times price. If we change the names of the variables and adjust the spacing, the same code could also be written as follows:

D1   =1000
D2   =10.99
D3   =D1*D2

This preceding code looks much more like worksheet cell addresses and formulas than lines of VBA code, showing that a worksheet is in fact a programming language of its own, if we choose to think of it in those terms. The IF() worksheet function is directly equivalent to the If...Then...Else VBA statement, and the judicious use of circular references and iteration can be equivalent to either the For...Next or Do...Loop structures.

Instead of stating a set of operations that are executed line by line, we "program" in this language by stating a set of declarations (by typing formulas and values into worksheet cells), in any order we want to:

"D3 is the product of D1 and D2"
"D1 has the value 1000"
"D2 has the value 10.99"

To "run" this program, Excel first examines all the declarations and builds a "precedence tree" to identify which cells depend on the results of which other cells and thereby determine the most efficient order in which the cells must be calculated. The same precedence tree is also used to identify the minimum set of calculations that must be performed whenever the value in a cell is changed. The result is a calculation engine that is vastly more efficient than an equivalent VBA program, and one that should be used whenever complex numeric computations are required in our applications.

Microsoft Excel (and other spreadsheet programs) is unique among application development platforms in providing both a procedural (VBA) and a declarative (the worksheet) programming language. The most efficient Excel application is one that makes appropriate use of both these languages.

It is assumed the reader of this book has some knowledge of Excel and worksheet functions, so Chapter 14 Data Manipulation Techniques focuses on using advanced worksheet functions (including best-practice suggestions for handling circular references) and Excel's other data-analysis features.

The Excel Object Model

Although the other four components of the Excel platform are invaluable in the development of applications, it is probably the richness of the Excel object model that provides the most compelling reason to base our application development on Excel. Almost everything that can be done through the user interface can also be done programmatically by using the objects in the Excel object modelaccessing the list of number formats and applying a digital signature to a workbook are perhaps the most notable exceptions. The vast array of functionality exposed by these objects makes highly complex applications fairly simple to developit becomes more an issue of when and how to efficiently plug the functionality together than to develop the functionality from scratch. This book does not attempt to explore and document all the backwaters of the object model, but instead makes continual use of the objects in our application development.

The Excel Developer

2009-05-15T18:19:00.000-07:00

Excel developers can be divided into five general categories, based on their experience and knowledge of Excel and VBA. To varying degrees, this book has something to offer each of them, but with a focus on the more advanced topics. Putting yourself into one of these categories might help you decide whether this is the right book for you.

The basic Excel user probably doesn't think of himself as a developer at all. To basic users, Excel is no more than a tool to help them get on with their job. They start off using Excel worksheets as a handy place to store lists or perform simple repetitive calculations. As they discover more of Excel's functionality, their workbooks become more complex and start to include lots of worksheet functions, pivot tables and charts. There is little in this book for these people; although Chapter 4 Worksheet Design details the best practices to use when designing and laying out a worksheet for data entry, Chapter 14 Data Manipulation Techniques explains how to structure a worksheet and which functions and features to use to manipulate their lists, and Chapter 15 Advanced Charting Techniques, explains how to get the most from Excel's chart engine. The techniques suggested in these chapters should help the basic Excel user avoid some of the pitfalls often encountered as their experience and the complexity of their spreadsheets increases.

The Excel power user has a wide understanding of Excel's functionality, knows which tool or function is best to use in a given situation, creates complex spreadsheets for his own use and is often called on to help develop colleagues' spreadsheets or to identify why colleagues' spreadsheets do not work as intended. Occasionally power users include small snippets of VBA they found on the Internet or created using the macro recorder, but struggle to adapt the code to their needs. As a result, they produce code that is untidy, slow and hard to maintain. Although this book is not a VBA tutorial, the power user has much to gain from following the best practices we suggest for both worksheets and code modules. Most of the chapters in the book are relevant to power users who have an interest in improving their Excel and VBA development skills.

The VBA developer makes extensive use of VBA code in his workbooks (often too much). VBA developers are typically either power users who have started to learn VBA too early or Visual Basic 6 developers who have switched to Excel VBA development. Although they might be very proficient at VBA, they believe every problem must have a VBA solution and lack sufficient knowledge of Excel to make the best use of its features. Their solutions are often cumbersome, slow and make poor use of Excel's object model. This book has much to offer VBA developers to improve their use of Excel itself, including explaining how to architect Excel-based applications, the best practices for designing worksheets and how to use Excel's features for their data entry, analysis and presentation. This book also seeks to improve their Excel VBA development skills by introducing advanced coding techniques, detailing VBA best practices and explaining how to improve their code's performance.

The Excel developer has realized the most efficient and maintainable applications are those that make the most of Excel's own functionality, augmented by VBA when appropriate. Excel developers are confident developing Excel-based applications for their colleagues to use or as part of an in-house development team. Their undoubted knowledge of Excel is put to good use in their applications, but it also constrains their designs, and they are reluctant to use other languages and applications to augment their Excel solutions. They have probably read John Walkenbach's Excel Power Programming and/or our own Excel 2000/2002 VBA Programmer's Reference and need a book to take them to the highest level of Excel application developmentthat of the professional developer. This is that book.

The professional Excel developer designs and develops Excel-based applications and utilities for clients or employers that are robust, fast, easy to use, maintainable and secure. Excel forms the core of their solutions, but they include any other applications and languages that are appropriate. For example, they might use third-party ActiveX controls; automate other applications; use Windows API calls; use ADO to connect to external databases, C/C++ for fast custom worksheet functions, VB6 or VB.Net for creating their own object models and securing their code; and XML for sharing data over the Internet. This book teaches all those skills. If you are already a professional Excel developer, you know that learning never stops and will appreciate the knowledge and best practices presented in this book by three of your peers.

Repairing corrupted files in Excel

2009-01-15T18:25:00.000-08:00

Applies to
Microsoft Office Excel 2003 Microsoft Excel 2002

Microsoft Excel provides automatic recovery for a corrupted file by attempting to reopen and simultaneously repair the file. Excel identifies what it changed while repairing the file. If the repair fails, Excel again tries to open the file but instead of attempting a repair, Excel extracts cell values and formulas leaving only the data. Under some circumstances, however, Excel won't automatically go into recovery mode, so users may find it necessary to recover file data manually.

To manually repair a file

On the File menu, click Open.
In the Open dialog box, select the file you want to open, and click the arrow next to the Open button.
Click Open and Repair, and then choose which method you want to use to recover your workbook.

Methods for recovering data from damaged workbooks

The following are additional methods you can use to recover data manually from a file that has been corrupted. Under some circumstances a disk error or network error may make it impossible to open a file. In those situations you should move your file to a different hard disk drive or from the network to a local disk before spending time implementing the recovery options outlined here.

The following methods are opportunities to save data that might otherwise be lost, so if one method is not successful, try another. You can also try third-party software solutions to recover file data if you can't recover your data using these methods.

If you can open the file in Excel

Use the Revert To Saved Document command

If you are editing a Microsoft Excel worksheet and the file becomes corrupted before you have saved changes to the file, you can recover the original worksheet by doing the following:

On the File menu, click Open and select the name of the file that you are editing.
A dialog box appears with the message "Revert to Saved Document?"
Click OK. The file you are editing reverts to the last saved version of the file.

Save the file in SYLK format

Saving the file in SYLK format is typically used to remove printer corruption. If you can open the corrupted Microsoft Excel file, you can "filter" it if you save it in SYLK format, close the file, and then reopen it as follows:

On the File menu, click Save As.
In the Save as type list, click SYLK (Symbolic Link), and then click Save.
Note Only the active sheet in the workbook is saved when you use the SYLK file format. Click OK when the message tells you the selected file type does not support workbooks. Click Yes when the message tells you the file may contain features that aren't compatible with the SYLK format.
On the File menu, click Close.
On the File menu, click Open.
Select the .slk file you saved, and click Open.
Note To see the .slk file, you may need to click All Files in the Files of type list.
On the File menu, click Save As.
In the Save as type box, click Microsoft Excel Workbook, and then click Save.

Note Because this format saves only the active worksheet, you must open the corrupted file repeatedly and save each worksheet separately.

Save the file in Hypertext Markup Language (HTML) format

If you can open the corrupted Microsoft Excel file, you can "filter" it if you save it in HTML format, close the file, and then reopen it as follows:

On the File menu, click Save As. In the Save as type list, click Web Page (*.htm, *.html).
Under Save, click Entire Workbook, and then click Save.
Close the file.
Open the file again in Excel.
On the File menu, click Save As. In the Save as type list, click Microsoft Excel Workbook. Change the name of the file to create a new "filtered" copy without replacing the original.

Note Some features may be lost when you save in HTML format.

If you can't open the file in Excel

Set the recalculation option in Excel to manual

Start Excel and open a blank workbook if one is not open on the screen.
On the Tools menu, click Options.
Click the Calculation tab.
In the Calculation section, click Manual, and then click OK.
On the File menu, click Open and try to open the file.

Use external references to link to the corrupted file

This method retrieves only data and not formulas or values from the workbook.

On the File menu, click Open. Then, browse from the current folder to the folder that contains the damaged file, and click Cancel.
Click New on the File menu, click File, and then click OK.
Type =File Name!A1 in cell A1 of the new workbook, where File Name is the name of the damaged workbook. If the Select Sheet dialog box appears, select the appropriate sheet, and click OK.
Select cell A1, and on the Edit menu, click Edit. Then, select an area that is approximately the same size as the range of cells that contain data in the damaged file, and then click Paste on the Edit menu.
With the range of cells still selected, click Copy on the Edit menu.
On the Edit menu, click Paste Special, select Values, and click OK.
This step removes the links to the damaged file and leaves only the data.

Open the file in Microsoft Word or WordPad

If you have the Microsoft Excel converter installed, you might be able to open your Excel workbook in Microsoft Word. If the file does open in Word, you will not be able to recover module sheets, dialog sheets, chart sheets, macro sheets, or any embedded charts. Also, you will not recover any cell formulas, only the results of those formulas that are currently in the cells.

You can also open your Excel workbook in WordPad. If the file does open, you might recover Microsoft Visual Basic code in your modules and class modules. Search for the words "Sub" or "Function" to find your code.

Open the file in Microsoft Excel Viewer

If you have Microsoft Excel Viewer installed, you may be able to open the Microsoft Excel workbook in Microsoft Excel Viewer, copy the cells, and paste the cells into a new workbook. However, you cannot recover module sheets, dialog sheets, chart sheets, or macro sheets. Also, you will not recover any cell formulas, only the results of those formulas that are currently in the cells.

To download Excel Viewer 2003, or for more information, see Downloads on Microsoft Office Online.

If a chart is linked to the corrupted file, use a macro to extract the data

Enter the following macro code in a module sheet:

 Sub GetChartValues97()
Dim NumberOfRows As Integer
Dim X As Object
Counter = 2

' Calculate the number of rows of data.
NumberOfRows = UBound(ActiveChart.SeriesCollection(1).Values)

Worksheets("ChartData").Cells(1, 1) = "X Values"

' Write x-axis values to worksheet.
With Worksheets("ChartData")
.Range(.Cells(2, 1), _
.Cells(NumberOfRows + 1, 1)) = _
Application.Transpose(ActiveChart.SeriesCollection(1).XValues)
End With

' Loop through all series in the chart and write their values to


' the worksheet.
For Each X In ActiveChart.SeriesCollection
Worksheets("ChartData").Cells(1, Counter) = X.Name

With Worksheets("ChartData")
.Range(.Cells(2, Counter), _
.Cells(NumberOfRows + 1, Counter)) = _
Application.Transpose(X.Values)
End With

Counter = Counter + 1
Next

End Sub

Insert a new worksheet into your workbook and rename it ChartData.
Select the chart from which you want to extract the underlying data values.
Note The chart can either be embedded on a worksheet or on a separate chart sheet.
Run the GetChartValues97 macro.
The data from the chart will be placed in the ChartData worksheet.

Saving a backup copy of your file

As a preventative measure, you may want to save your file often and create a backup copy every time you save. In the Save As dialog box (File menu, Save As command), click Tools, click General Options, and then select the Always create backup check box. This way, you'll have access to a good copy of the file, should the original be accidentally deleted or become corrupted.

You can also make sure that Excel automatically creates a recovery file at specific intervals. In the Options dialog box (Tools menu, Options command), on the Save tab, select the Save AutoRecover info every check box, and then specify a number of minutes. In the AutoRecover save location box, specify the location where you want to save the recovery file. Make sure that the Disable AutoRecover check box (under Workbook options) is not selected.

More ways to use HLookup and VLookup functions

2009-01-12T18:29:00.000-08:00

This column provide more information about ways to use the HLOOKUP and VLOOKUP functions in Excel, along with instructions for using the Lookup Wizard, an add-in that comes with Excel. The tips include shortcuts and advice on using a mix of absolute and relative cell references to return multiple search results.

Applies to
Microsoft Office Excel 2003 Microsoft Excel 2000 and 2002

See all Power User columns
See all columns

The last Power User column, Use HLookup and VLookup to find records in large worksheets, explained the basics of using the HLOOKUP and VLOOKUP functions in Microsoft Excel to find records in a large worksheet. This column explains several other ways to use those functions.

To refresh your memory from last time: My friend uses Microsoft Access to store and manage data about the number of hits that her Web site receives. She imports the data into Excel for analysis. She'd heard that lookup functions could save time by finding related data in the various sheets.

To follow the steps in this column, you need the sample worksheet from the last column, called LookupFunctions.xls. If you haven't already, follow the steps in the last column to get ready to do the steps in this one. The sample worksheet uses fictitious Web site data that demonstrates my friend's problem. The spreadsheet contains a worksheet called Page Views (with a set of IDs that uniquely identify each site page and the hits it receives) and another called Pages (with the page IDs and the names of the pages that correspond to each ID).

Let ToolTips help you write functions

When you enter a formula in Excel, a ToolTip appears and lists the arguments available for that function. (If a ToolTip does not appear, select Options on the Tools menu, and on the General tab, under Settings, select the Function tooltips check box.) Those ToolTips contain hyperlinks, and you can use the links to speed up the process of selecting cell ranges and embedding the correct arguments in your formulas. Follow these steps:

In the sample worksheet, click the Page Views tab, and then clear the VLOOKUP and HLOOKUP formulas and all text in Columns E and F.
In Cell F4, type:
=VLOOKUP(

When you type the opening parenthesis, a ToolTip appears.
Click lookup_value in the ToolTip, and then click Cell F3. Excel writes the value F3 next to the opening parenthesis.
Type a comma after F3 in the formula, and then click the Pages tab.
In the Pages worksheet, highlight the cell range A2 to B39. The string Pages!A2:B39 appears in the formula bar.
In the formula bar, type a comma after Pages!A2:B39. Entering a comma moves you to the next option in the ToolTip.
Enter the rest of the formula as shown: 2,false), and then press ENTER. Excel returns you to the Page Views worksheet, and #N/A appears in Cell F4, indicating that you need to enter a search value in Cell F3. Typically, the #N/A error appears when your function tries to reference an empty cell. For more information about fixing #N/A errors, see Correct a #N/A error.
Copy one of the page ID values from Column A to Cell F3, and then press ENTER. The corresponding page name appears in Cell F4.

You can follow these steps whenever you get stuck on any formula: Type an equal sign, the formula name, and the opening parenthesis, and then follow the ToolTip. Remember to separate your arguments with commas.

Use relative and absolute cell references to return multiple results

The instance of VLOOKUP that you created in the previous section contains a reference to Cell F3. Excel calls that type of reference a relative reference, meaning that if you copy the formula down or across a range of cells, Excel automatically increases the cell reference by 1 for each new instance of the formula. If any of those instances of the formula reference empty cells, Excel displays the #N/A error.

In contrast, absolute cell references do not change when you copy them down or across a range of cells, so using them can help you avoid #N/A errors. The following steps demonstrate how relative cell references can cause #N/A errors, and how absolute references can fix them.

On the Page Views worksheet, copy and paste or type this formula into Cell D4:
=VLOOKUP(A4,Pages!A2:B39,2,false)
Click Cell D4, rest your mouse pointer on the lower-right corner of the cell until it changes to a black plus sign (+), and then drag the mouse pointer down to Cell D41. The page names that correspond to each ID appear in Column D.

At this point, #N/A appears in several cells. What happened? If you click cells in Column D, you see that Excel also increased the cell references by one after the word Pages! in the formula. (In case you haven't read the previous article, "Pages!A2:B39" is called the table array argument. For more information about the argument, read the previous article.) In other words, the formula in Cell D4 references Cells A2 through B39 on the Pages worksheet, which is what you want. But look at the formula associated with Cell D5: It references Cells A3 through B40. If you go back to the Pages worksheet, you'll see that Cell B40 is empty. Excel returns the #N/A message because some instances of the formula contain references to empty cells.

To work around the problem, use absolute cell references. Absolute references prevent Excel from changing cell references in a formula when you copy that formula. Follow these steps:

In the Page Views worksheet, clear Cells D5 through D41.
In Cell D4 (or the formula bar, if that's easier), add dollar signs to the formula as shown:
=VLOOKUP(A4,Pages!$A$2:$B$39,2,FALSE)

The dollar signs make the cell references absolute.
Point to the lower-right corner of Cell D4 until your mouse pointer changes to a black plus sign, and then drag the pointer down to Cell D41. As you copy the formula, the dollar signs prevent Excel from changing any cell references in the table array argument. This time, the page names that correspond to each ID appear in Column D with no errors.

The formula in Step 2 uses absolute columns and rows (the dollar signs are a dead giveaway). You can use a mix of relative columns and absolute rows, or vice versa. For example:

If you need a relative column reference and an absolute row reference, use A$2.
If you need an absolute column reference and a relative row reference, use $A2.

You can mix absolute and relative references as needed. For example, you could use $A2:B$39, or any other combination of characters. Just make sure that you place the dollar sign before the column or row that you want to designate as an absolute reference. The function fails otherwise.

Finally, for more information about formula error messages, see Correct a #N/A error, Correct a #REF! error, and Correct a #VALUE! error.

Use formula auditing to find empty cells and fix broken functions

Typically, a function doesn't work because it references at least one empty cell. You can use the formula auditing tools in Excel to find the empty cell and fix the broken function. The formula auditing tools use arrows and icons to point to the cells from which a function tries to take the data it needs.

Important To follow the steps in this section, you must first enable an error checking option. On the Tools menu, click Options, click the Error Checking tab, and then select the Formulas referring to empty cells check box.

To see the auditing tools in action:

On the Page Views worksheet, remove the dollar signs from the formula in cell D4.
Clear cells D5 through D41, and then copy the changed formula down to Cell D41. Because all instances of the formula except the first one contain an error, Excel displays a green triangle in the upper-left corner of each cell:
Select Cell D5. The Trace Error icon appears.
Click the arrow to the right of the icon, and then click Trace Empty Cell. The following arrow and icon appear, indicating that the empty cell resides on another worksheet.
Double-click the black arrow that leads from the icon to the cell.
Double-click the entry in the Go to dialog box. Excel opens the Pages worksheet because that worksheet contains the empty cell.

At this point, you need to notice something subtle: In the Pages worksheet, Excel highlights the cell range A3 to B40, even though the data only resides in cells A2 to B39. The highlight is showing you that the formula is searching the wrong range of cells. Hence, the error.

To see a more pronounced example of this behavior, go back to the Page Views worksheet, select Cell D10, and repeat Steps 4 through 6. The highlight in the Pages worksheet extends even further down to indicate the greater number of empty cells referenced by the instance of the formula in Cell D10.

Use the Lookup Wizard to save time

If you don't have the time to write a function or if writing functions still frustrates you, you can use the Lookup Wizard. The Lookup Wizard comes with Excel, so you don't need to download it (but Excel may prompt you to install it the first time you try to use it). The wizard uses the INDEX and MATCH functions (instead of VLOOKUP and HLOOKUP) to return records.

Unlike the lookup functions, the INDEX function requires you to specify row and column labels. The INDEX function also returns values from unsorted lists.

The MATCH function determines the row that contains the desired value. You can use INDEX and MATCH to enter more than one search term and return the value that corresponds to the intersection of the two terms. For example, the following table contains shipping data for the first three months of 1994, 1995, and 1996.

Units Shipped
	January	February	March
1994	37	43	61
1995	40	60	52
1996	31	50	64

Using INDEX and MATCH, you can specify multiple search terms such as "1995" and "March." In that case, the functions would return 52, the value at the intersection of those terms.

The steps in this section explain how to configure Excel to run the Lookup Wizard, and how to use the wizard.

First, let's configure Excel.

On the Tools menu, click Add-Ins.
In the Add-Ins dialog box, click Lookup Wizard, and then click OK.

Now let's run the add-in. These steps explain how to duplicate the results you created earlier using the VLOOKUP function. We'll use values from the Page Views and Pages worksheets, and return a result to a blank cell on the Page Views worksheet.

Open the sample spreadsheet (LookupFunctions.xls) and select the Pages worksheet.
On the Tools menu, click Lookup. You should see the following text and cell range in the wizard.

If the text seems a bit cryptic, remember that you're defining the location and range of cells through which the function searches. When you see those values, click Next.
Ensure that Page Name appears in the top drop-down box, and then pick a page ID number from the bottom drop-down box and click Next.
Do one of the following:
- If you want your worksheet to display only the result of your search, click Copy just the formula to a single cell.
- If you want the worksheet to display the search result and the parameters used in the search, click Copy the formula and lookup parameters, and then click Next.
Do one of the following:
- If you chose to display only the search result, enter a reference to a blank cell in the box, and then click Finish.
- If you chose to display the result and the lookup parameters, enter references to three blank cells, and then click Finish.

The wizard performs the lookup and writes the result or results to the cell or cells you referenced in Step 6.

A final reminder

As you use lookup functions, remember that you're pointing to a data string in one location, telling Excel to find either a partial or absolute match for that data string in another location, and then telling Excel to display a third value that lies either next to or near that second value.

Excel

Worksheets and Chart Sheets

Modules, Classes and Userforms

Procedures

Listing 3-1. An Example of Naming Conventions for Function Names

Naming Convention Examples

Variables

Constants

User-Defined Types

Enumerations

Procedures

Listing 3-1. An Example of Naming Conventions for Function Names

A Sample Naming Convention

Table 3-1. A Naming Convention for Variables, Constants, User-Defined Types and Enumerations

The Scope Specifier ()

The Array Specifier ()

The Data Type Specifier ()

Table 3-2. Suggested Naming Convention Prefixes

Using Descriptive Names

A Few Words About Enumerations

Naming Conventions

What Is a Naming Convention and Why Is It Important

Dictator Applications

Requirements of a Dictator Application

Structure of a Dictator Application

Application-Specific Add-ins

Structure of an Application-Specific Add-in

Function and General-Purpose Add-ins

Structure of a Function or General-Purpose Add-in

Self-Automated Workbooks

Self-Automated Workbooks

Codeless Applications

Codeless Applications

Excel as an Application Development Platform

The Worksheet as a Presentation Layer for Data Entry and Reporting

The Worksheet as a Simple Data Store

VBA: Excel's Programming Language

The Worksheet as a Declarative Programming Language

The Excel Object Model

The Excel Developer

Repairing corrupted files in Excel

To manually repair a file

Methods for recovering data from damaged workbooks

If you can open the file in Excel

If you can't open the file in Excel

Saving a backup copy of your file

More ways to use HLookup and VLookup functions

Let ToolTips help you write functions

Use relative and absolute cell references to return multiple results

Use formula auditing to find empty cells and fix broken functions

Use the Lookup Wizard to save time

A final reminder