Excel Pages

This page describes the various pages on the web site.

This is the Page Index to the Excel Web Source site. It lists each page on the site, and describes its contents. The pages are listed in alphabetical order by title, not necessarily the file name of the page.

The icon indicates that the content is related to Excel itself (typically the user interface) or to formulas.

The icon indicates that the content is related to VBA.

The icon indicates that the content is related to Visual Basic 6.

The icon indicates that the content is related to Visual Basic NET.

The icon indicates that the page has been converted from HTML to ASP.NET 2.0

The icon indicates a download on the page.

Pages On The Web Site

About This Site
This page tells a bit about the site, along with acknowledgements, and miscellaneous information.

Activating Excel From Other Programs
If you are doing cross-application program (such as automating Excel from a Word document or template), you may find it useful for the user's convenience to activate Excel and set keyboard focus to the active sheet. The AppActivate statement is not entirely reliable. It sometimes properly activates an application, but sometimes returns an error. It also requires that you know the window's caption prior to calling AppActivate. The procedure on this page uses a few Windows API calls to reliably activate Excel and set keyboard focus to the Active Sheet.

Adding The COM Add-Ins Menu
THis page desribes how to add the COM Add-Ins menu item to the Tools menu. You'll need this menu item if you are working with COM Add-Ins.

Adding Menu Items To VBE
Adding menu items to the Visual Basic Editor (VBE) isn't as easy as it is with standard Excel toolbars. You've got to do everything though code, including using a class module. This page describes the procedures for adding your own menu items to the VBE menus.

Additional Resources For Excel Users
This page describes additional resources where Excel users and VBA programmers can find more detailed technical information.

Alert Message Component
If your application need to display messages to the user, you may find this component useful. It displays a modless for on which you can display a scrollable list of messages to the user. When a message is posted, no user action is required, as is the case with a MsgBox. The component also supports filtering messages by designer-defined priority levels.

Analysis Tool Pack
The Analysis Tool Pack (ATP) is an add-in module that is provided free with Excel, and provides dozens of addition worksheet functions related to Finance, Statistics, and Engineering. This page describes how to install the ATP add-in module, and call its functions from VBA procedures.

It should be noted that in Excel 2007, the functions that were in the separate ATP have been made native function within Excel itself, so you don't need the ATP to use the functions.

AnyXML - Allowing for optional and arbitrary XML in an XSD Schema
This page describes how to write an XSD XML Schema to allow for the inclusion of optional and arbitrary XML in an instance document. This allows any well-formed XML to be included in an XML document without the structure of that XML being described in a schema.

Application Events
Application Events are similar to standard Worksheet and Workbook events, but occur at the Application level. All Workbook and Worksheet events can be handled at the Application level, as can events such as adding or opening a new workbook, or installing an add-in module. This page describes how to use Application level events. This page assumes that you are familiar with Events and Classes. It is strongly recommended that you read the Events page before moving on to Applicaiton events.

Array Formulas
Array formulas are one of the most powerful tools in Excel. Many of the formulas on this web site are array formulas. This page describes what array formulas are, how they work, and how to use them.

Array To Column
This page describes formulas that you can use to transform an MxN range of data into a single column or row of data. This can be very useful for charting applications.
NOTE: This page has been replaced by the Matrix To Vector page.

Array Utility Functions
This page presents approximately 40 VBA functions for working with arrays. The procedures in this library call upon one another, so you should import the entire module into your project rather than bringing in only individual procedures. NOTE: This page has been replaced by the Extracting Vectors Form A Matrix page.

Automation Add Ins And Function Libraries
This page describes how to write Automation Add Ins in Visual Basic 6 to serve as function libraries available to all users on a machine. The functions defined in the classes of an Automation Add In can be called directly from a worksheet cell. This page also describes how to write Automation Add Ins whose functions can be called from your VBA code.

Averages Ignoring Zeros
This page describes formulas and code you can use to average a range of values, excluding zero values.

A Better NETWORKDAYS Function
The NETWORKDAYS is fine if your work week is Monday through Friday. However, if you work week begins and ends on different days of the week, NETWORKDAYS is of no use. This pages describes two formulas that allow you to specified days and holidays from the count.

A Better WORKDAY Function
The WORKDAY function suffers from a significant shortcoming: Saturday and Sunday are hard-coded into the function. You cannot specify other or additional days of the week. This page describes a function that allows you to exclude any number of days of the week.

A Better Union Function
The Union method has two problems. First, it errors out if a parameter is Nothing. Second, it double counts cells in overlapping ranges. This page describes code that fixes these problems.

Bitness, 32 and 64 bit Excel
This page describes the differences between Excel for 32 bits and 64 bits.

Blinking Text
Some users have expressed a need for blinking text. Excel doesn't natively support blinking text, but you can make a cell's text blink by using theOnTime method.

Browse Folder
This page describes two methods to display a "browse for folder" dialog box.

ByRef And ByVal Parameter Passing
This pages describes the difference between passing parameters to procedures by reference (ByRef) or by value (ByVal).

Break In Class Module Error Trapping Option
This page describes how and why you should always use the Break In Class Module debugger setting.

CALL Function
The page contains procedures for calling functions from a DLL directly from a worksheet cell.   This page appears compliments of Laurent Longre. NOTE: The CALL function was disabled in Excel 2000 because it exposes a series security risk. This page applies only to Excel 97.

Calling Worksheet Functions From VBA
This page describes how to call worksheet functions from your VBA code.

Cell References In A Range
This page describes a syntax and method for referencing various cells in a range without using the Offset property. This syntax is both faster and easier than either the Cells or Offset methods. The syntax references cells by the (hidden) "_Default" property of a Range. This page appears compliments of Alan Beban.

Cell Values And Displayed Text
When you are working in Excel, either with formulas or with Visual Basic For Applications (VBA) code, it is important to understand the difference between the actual value of a cell and what is displayed on the screen. The actual value of the cell is what Excel stores internally and what it uses in formulas and calculations. This is not necessarily the same as what you see displayed on the screen or printed on your reports. It is important that you understand the difference between the two -- otherwise, your formulas may not work as expected

Cell View
This add-in allows you to see the characters and their numeric codes in a cell. This makes it easy to find unprintable characters such as tabs that don't display but may affect formulas. This is particularly useful for finding special characters that may be present when you copy text from a web page and paste it into Excel.

Changing The Case Of Text
Excel does not have a format option to display text in upper or lower case. This page shows some VBA code that allows you to change a range of cells to upper or lower case, or to change the case of text automatically as it is entered.

Character Tests Of Strings
This page describes a number of formulas for testing text values in strings in worksheet cells.

Class Modules
Using class modules in a project can promote good design and streamline code. This page in a simple introduction to classes with examples. It is by no means a comprehensive treatment of classes.

Clipboard Functions
This page describes functions for working with the Windows Clipboard from within VBA.

Cloning A Folder
This page describes code that will make a copy of a folder, including all files and subfolder within the source folder. All files and subfolders of the source folder are copied to the destination folder, and the subfolder hierarchy (subfolders within subfolder within subfolders) is preserved in the destination folder.

Code Modules And Code Names
A frequent mistake of beginning VBA is programmers is that they put their code in the wrong code module. This page discusses the different types of modules in a VBProject, and what code should and should not be placed in which modules. It also discusses the Codename property of a module, and how to use (and misuse) this property.

Code Attributes For The Object Browser
You can create Attribute statement to provide descriptions of modules and procedures that will be displayed in the Object Browser. This page describes how to do that.

Code Notes
This provides information about code on the site. It is largely obsolete.

Collections And Dictionaries
This page contains procedures for working with Collection and Dictionary object, including sorting procedures.

Color Banding With Conditional Formatting
When creating worksheets with a large number of rows, especially if those rows have many columns, you may want to apply a style of alternating colored rows to make the worksheet easier to read. While Excel has built-in styles to do this, the colors will get botched if you sort the range or you insert or delete rows. This page describes a method you can use with Conditional Formatting to apply a format that will remain correct even during sorting or inserting or deleting rows.

Colors
Excel does not have any built-in worksheet functions for working with the colors of cells or fonts. If you want to read or test the color of a cell, you have to use VBA procedure. The Colors page describes several functions for counting and summing cells based on the color of the font or background.

Column To Table
It is not an uncommon task to take a column of data, grouped into logical blocks, and transform them into a two-dimensional table of rows and columns. This page describes how to do this with formulas and with VBA code.

Column To Table, Variable Block Lenghts
This page describes VBA code to create a table from a column of blocked data where the block sizes are variable, not fixed.

Creating A Function Library In NET
This article describes the procedures necessary to create a Class Library Automation Add-In using Visual Studio NET. You can download the example as a group of project files for Visual Studio 2005 and Visual Studio 2008.

COM Add Ins
This page describes how to write COM Add Ins and Automation Add Ins in Visual Basic 6. See also Automation Add Ins And Function Libraries.

COM Add-Ins In Excel 2007
This page describes some considerations regarding COM Add-Ins in Excel 2007.

COM Add-Ins And Automation Add Ins, Installing
This pages describes techniques and System Registry Keys and Values required to install your COM Add In for users that do not have administrative privileges on their acccount. This describes how to add a COM Add In for an individual user or to configure the add in so that it is available to all users of a machine. This pages assume you are somewhat familiar with the System Registry and the RegEdit Registry Editor program.

Conditional Formatting
This page describes how to use the Conditional Formatting tool, which was added to Excel in Excel97.

Conditional Formatting Colors
Unfortunately, the Color and ColorIndex properties of a Range don't return the color of a cell that is displayed if Conditional formatting is applied to the cell. Nor does it allow you to determine whether a conditional format is currently in effect for a cell. In order to determine these, you need code that will test the format conditions. This page describes three VBA functions that will do this for you.

Content Based Banding
This page describes how to use formulas and Conditional Formatting to apply styles based on cells' contents, changing from formatted to unformatted when a value changes in a list.

Consulting And Development Services
I am a professional Excel and VB/VBA developer. If you like what you see on the web site, you can hire me to develop projects for your own business, custom designed to meet your exact business needs. Contact Chip Pearson at (816) 214-6957.

CountType Function
There are few and rather limited built in Excel functions for determining what type of data is in a cell or how many cells in a range contain a particular type of content, such as formulas, date and times, Booleans, and so on. This page describes a VBA function named CountType that returns the count of cells in a range that are of a particular content type.

Creating An Add-In
This page describes how to create an Excel XLA add-in. If you know how to code in VBA, you know pretty much all you need to know about writing XLA Add-Ins. This page fills in the details you might overlook.

Date Add
It is sometimes useful to store years and months as numbers -- e.g., 5.06 for 5 years, 6 months. This page describes how to use the DOLLARDE and DOLLARFR functions to add years and months.

DATEDIF
All Excel versions since Excel5 have had a worksheet function called DATEDIF, which allows you to find the difference between two dates, in days, years, or months. Unfortunately, this function is documented only in Excel 2000, not in earlier or later versions. This page describes the syntax for the DATEDIF function.

Date Functions

This page describes about 20 useful worksheet formulas for working with dates.

Date Intervals
This page describes several formulas for working with date intervals. Specifically, it addresses the questions of whether a date falls within an interval, the number of days that two intervals overlap, and how many days are in one interval, excluding those days in another interval.

Dates And Times
One of the most common tasks in Excel involves working with dates and times. This page contains extensive information about how dates and times are stored in Excel, how to add and subtract them, and dozens of other related formulas. This is one of the most popular pages on the web site and is the starting point for many other pages that work with dates and times.

Date Time Entry
When you have a large number of dates of times to enter into a worksheet, you may want to be able to enter them without using the ":" or "/" separator characters -- e.g., type 1234 to get 12:34. This page describes the VBA code that allows you to do this.

Time Zones And Daylight Savings
This page describes VBA procedures to work with time zones and daylight savings time.

Day Of Week Functions
This page describes functions you can use when working with days of the week. The functions are presented both as worksheet formulas and as VBA functions.

Daylight Savings
Neither Excel, VBA, nor the Windows API library provide much support for working with Daylight Savings Time (DST) and Standard Time (STD). This page describes several procedure that you can use to work with DST. The rules used in this code to determine whether a date is within DST are the rules used in the USA and Canada. European and other countries have their own rules regarding when DST begins and ends. You can fairly easily adapt the code provided in these procedures to use the DST rules in effect in your locale.

Default Member Of A Class
This page describes how to specify a Sub, Function, or Property of a class as the default member. That allows you to write code like the following:

	Dim CC As New CMyClass
	CC.Value = 1234
	' is the same as:
	CC = 1234

Debugging VBA Code
This page provides an introduction to the debugging and diagnostic tools available in VBA to facilitate debugging your code.

Declaring Variables Properly In VBA Code
This page describes the best practices when declaring variables in VBA code.

Defined Names In Excel
Defined Names are a powerful tool in Excel. They allow you to assign a meaningful name to a cell or range of cells, and use those names where you would normally use a cell reference. This makes the formulas much easier to understand and maintain.

Defined Name Shortcut Key
Excel provides many shortcut keys, but one that is sorely lacking is a shortcut to the Defined Name box on the formula bar. You can use the macro on this page, assigned to a keystroke, to get directly to the name box.

Deleting Rows
Many worksheets are used to stored lists of data. This page describes some techniques for deleting duplicate rows in a list of data.

Deleting Duplicate Rows With Advanced Filter
This page describes how to use Excel's Advanced Filter tool using VBA code to delete duplicate rows of data.

Summing The Diagonal Elements Of A Range
This page describes several formulas for summing the diagonal elements of a range.

Delimited Text In A Cell
This page describes several formulas for working with delimited text in a cell.

Diagnosing Startup Problems In Excel
Occasionally, you may encounter errors when starting Excel that make it difficult or impossible to continue working. This page describes steps you can take to diagnose and fix errors that occur when Excel starts up.

Differences Between Functions And Macros
As a VBA programmer, you'll need to know the difference between Sub procedures and Function procedures. This page describes the difference, and the limitations of each type.

Direct Connection To MSNEWS
When you use the Microsoft Newsgroups (also called Communities), it is generally advantageous to connect directly to the MSNEWS server, rather than going through your local news server. This page explains why. This page was written by Leonard E Meads, and appears on this web site with his generous permission.

DistinctValues Worksheet Function
This page describes a VBA Function that will return an array of the distinct values from a range or an array. This function may be array entered into a range of cells on a worksheet, incorporated into an array formula in one or more cells, and can be called from other VB code, independent of a worksheet.

Directory Tree Listing




This page is a redirection page to the Folder Tree page that contains the documentation and download for the DirTree directory tree builder add-in

Distributed Dates
Often, Excel is used as a platform for scheduling applications. You may need to determine how many working days in an interval fall in each month or year in that interval. This page shows you how. For example, if a project starts on 19-Jan and ends on 8-June, these formulas will determine how many working days fall into each month.

Document Properties
This page contains some VBA procedures for retrieving or setting the Built-In or Custom properties of a Workbook.

This page has been replaced. Click here to go to the new page.

Document Properties
This page describes a number of VBA functions that you can use to modify the BuiltIn and Custom document properties of a workbook. It also contains code to read and modify BuiltIn and Custom properties of closed files.

Downloading A File From The Web
This page describes VBA code to download a file from the internet to your local PC.

Downloads
The Downloads page lists the example workbooks, code modules, and complete projects that you can download from the web site.

Duplicates
When you have lists of data, it is often useful to identify the duplicate or unique items in the list, or to compare two lists to one another. This page describes a variety of techniques to do both, using either formulas or VBA code. This is one of the most popular pages on my web site.

Easter Calculations
This page describes how the calculate the date of Easter for a given year. Both a worksheet formula and a VBA function are shown.

EMail From VBA
This page describes how to send email from VBA without using Outlook.

Empty Folder
This page describes code to delete the contents of a folder, but leave the folder itself intact. By default, it will send the contents (all files and subfolders) to the Windows Recycle Bin, but you can specify that you want to permanently delete the contents of the folder.

End User License Agreement (EULA) Questions And Answers
The EULA is the contract between you and Microsoft that governs how you may use Office.This page answers a number of frequently asked questions about the EULA.

Ensuring Macros Are Enabled, Technique 1 (Sheet Visibility)
The page describes how to use VBA code to ensure that your workbook or application is usuable by the end user only if macros are enabled. It is unusable if macros are disabled. An alternative technique, using Defined Names and Formula is describe on this page.

Enum Name Value List
This page describes how to get a list of the name values of an enum and put that list in the clipboard. This is useful for creating a Select Case statement to validate the value of an enum.

Ensuring Macros Are Enabled, Technique 2 (Errors In Formulas)
This page provides a second technique, using formulas and defined names, to make a workbook unusable if macros are disabled. This technique is simpler than the technique on the Ensuring Macros Are Enabled page.

Enum Data Types
This page introduces the Enum data type for VBA. Enums provide a way to group related values with a single name.

Error Handling
This page describes the basics of error handling in VBA.

Event Procedures
Event procedures are VBA procedures that are executed automatically by Excel when a specific event or action occurs, such as changing a cell value, changing the selection, or saving a workbook. This page describes the events that occur in workbooks and worksheets and charts. It also includes instructions and examples for creating your own events in your classes. For events at the application level, see the Application Events page.

Every Nth Row
This page describes how to retrieve every Nth row from a column of numbers, such as every other or every third row. It also describes how to use SUM and AVERAGE on these data sets.

Excel Functions
This page contains a large number or general use worksheet formulas, organized by category. This page is no longer updated, but there's still alot of interesting stuff to be found. The topics covered on this page are now on their own pages or within a related topic page.

Excel Macros
This page contains a large number or general use worksheet formulas, organized by category.  This page is no longer updated, but there's still alot of interesting stuff to be found. The topics covered on this page are now on their own pages or within a related topic page.

Excel Links
This page has links to some other Excel related sites, and to a few interesting non-Excel sites as well.

Excel Pages
This page.

Fixed Length Test Files, Exporting To A Text File
This page describes how to export worksheet data to a fixed field length text file.

File Dates And Times
Neither Excel nor VBA provide a mechanism for retrieving or setting the times associated with a file: Create Time, Last Accessed Time, and Last Modified Time. This page provides two functions, GetFileDateTime and SetFileDateTime, that provide this functionality. These functions are VBA-Friendly wrapper functions for the GetFileTime and SetFileTime Windows API functions. The GetFileDateTime and SetFileDateTime function handle the data format conversions and the translation between local time and GMT time.

File Descriptions In VBA
This page describes how to get file information from a file name. It shows code that allows you to read the system registry and get the ProgID and text description of a file.

File Extensions And Excel VBA Code
By default, Windows does not display file extenesions. For example, the file Book1.xls is displayed as just Book1, without the xls extension. The value of this Windows setting has implications in Excel VBA code. This page describes how to test this setting and behave accordingly.

Full File Names In Windows Captions
This page describes some VBA event code that you can use to display the full file name, with drive and path information, in the Excel application window caption and the captions of the individual workbook windows.

FindAll XLA Add-In
This pages introduces the FindAll.xla add-in that you can use to search one or more worksheets in a workbook and display all of the results together on the user interface. The page includes a download for the XLA add-in.

Finding Values On Multiple Worksheets
VBA does not provide a way to find all occurrences of a value on a single worksheet or multiple worksheets. This page contains a VBA function named FindAll that finds all occurrences of a value on a worksheet and returns a Range object comprised of the found cells. It also contains a function named FindAllOnWorksheets which allows you to search for a value on any number of sheets in a workbook.

Find Method Of Range In VBA
You can use VBA to find data in a range of cells using the Find method of a Range. Unfortunately, there is no FindAll method that will return a Range object that contains references to all the cells in which the searched-for text was found. This page provides such a procedure. Another shortcoming of the Find method is that it does not support wildcard strings. This page provides another method, WildCardMatchCells, which will return a Range object referencing each cell whose contents match the provided wildcard string. The wildcard string may be any string that is valid to use with the Like operator.

Flexible Lookups - An Alternative To VLOOKUP
Excel's VLOOKUP and HLOOKUP functions are great for simple table lookups. However, they suffer the shortcomings that you can only search the left column, return a value only from a column to the right, and return only a single value. The formulas on this page describe how to use any column as the search column, any column for the returned values, and return multiple value from the table.

Folder And File Tree View
This page desribes code to create a TreeView control to list subfolders and files of a folder. The techniques described on the page are not limited to creating a TreeView for folders and files. The code can be adapted to display any sort of data in a TreeView control.

Folder Tree
This page describes VBA code to create a tree-like listing of subfolders and files within a specified folder.

Fractional Months
Some Excel applications need to calculate the fractional months between two dates. This is somewhat ambiguous because a month may have between 28 and 31 days. This page describes methods and formulas for calculating fractional months.

This page describes VBA code that you can use to create a list of distinct elements from an array of values. A downloadable example workbook is also provided.

Week Numbers In Excel
This page describes how to get a list of distinct elements from a range of cells.

Globally Unique Identifiers (GUIDs) Your application may need to create unique indentifiers to track objects or data values. If you need a unique identifier but you don't need that identifier to contain any meaningful information beyond simply being unique, you can use a Globally Unique Identifier or GUID. This value will be unique across all users, all computers, and all networks. This page describes the code to create a GUID.

Hints And Tips For Connecting To Microsoft Newsgroups
One of the best ways to obtain support and answers to questions if through the NNTP USERNET newsgroups. Often, you will get an answer within minutes. Best of all, it is totally free. This page describes how to connect to the Microsoft Excel newsgroups and provides a list of guidelines to make the newsgroups productive.

Inserting Cells And Filling A Series
This page describes VBA procedures that you can use to insert rows or cells and fill in missing elements of a series of numbers. Suppose you have a partial series of numbers in a range of cells. This code will detect missing entries in the series, insert the appropriate number of rows or cells between the existing entries in the series, and fill those cells with the appropriate values.

First And Last Names
VBA
A frequent task in Excel is to split full names into the first-name, last-name, and middle-name components. This page describes both worksheet formulas and VBA procedures to do this.

Fixed Length Test Files, Importing To Excel
Elsewhere on this site we have examined code for imported delimited text files. This page features code to import data from fixed field list.

FormPosition
Excel typically displays forms in the center of the screen. This is usually fine for data entry and dialog forms. However, in many cases it is desirable to display a form in relation to a specific cell. This is not a simple a task as it seems because the top and left coordinates of a UserForm based on the same coordinate system as the Top and Left coordinates of a cell. To properly calculate the Top and Left coordinates of a UserForm, you have to take in to account the window state (normal or maximized) of the Excel application window, and the Workbook window, and their relative positions, in addition to whether the formula bar is visible, what command bars are displayed, and how they are positioned. Needless to say, these calculation can get rather complicated. Fortunately, I've done the work for you.

Games!
Yes, Excel can be fun. Enjoy some games written me and by others.

GetInfo
The GetInfo function, written in VBA, allows you to retrieve nearly any property of any object in the Excel object model directly from a worksheet cell. Using this one function, you can eliminate dozens of custom UDFs to get application and object properties.

Get Recent File
This page describes a function named GetRecentFile that returns the name of the most recently or least recently modified file in a specified folder. You can specify one or more file extensions to narrow the files to be examined.

Get System Error Message Text
When you work with Windows API calls, you will often need to get the description of an error number, much the same way you do with Err.Description for VBA errors. Windows provides a function named FormatMessage that formats a Windows error into a text description. This page provides a VBA function named GetSystemErrorMessageText that wraps up all the API functions into a nice friendly VBA function.

GMT And Local Times and Windows Time Formats
There is no built-in way in VBA or Excel to convert between local time and Greenwich Mean Time (GMT, also called UTC or Zulu Time). This pages describes methods to convert times between local and GMT, as well as to convert between the various time formats used by VBA and Windows. You can download a bas code module or a complete workbook that contains the conversion procedures.

Headers And Footers
One of the big shortcoming of Excel is that you (still!) can't put cell values in the page headers and footer, nor can you put the full file name of the workbook. This pages has the VBA code to do both, and more.

Hidden Name Space
The "Hidden Name Space" refers to an area of memory that you can use to hold defined names that are available to all workbooks, and which are available to workbooks even after the workbook which created the name is closed. This page appears compliments of Laurent Longre.

Holidays
This page has a number of formulas and procedures for working with holidays that don't fall on the same date each year, such as Memorial Day or Thanksgiving. This is especially useful for scheduling workbooks.

Importing And Exporting Text Files
The VBA code on this pages is used to import data from a text file directly into the active worksheet, and to export a range from a worksheet to a text file. It lets you use any field delimiter you want.

Importing Big Files
Excel 2003 and earlier versions are limited to 65,536 rows of data. You cannot increase this limit. Therefore, if you attempt to import a very large text file, only the first 64K rows will be imported (or fewer if the imported data doesn't start at row 1). Excel's built in import and open functions will only import as much data as will fit on the active worksheet. It will not continue the import operation on subsequent worksheets. This page describes a procedure named ImportBigFile that will import a text file with any number of records. The procedure will create additional worksheets as required. It will optionally split each line of input data into separate columns, delimited by any specified character. The code isn't only for files with more than 64K rows -- it can be used to import a text file of any size. The code requires Excel 2000 or later.

Indirect
The Excel INDIRECT function is much more useful than the Help files may indicate. This page describes some of the interesting things you can do with this under-appreciated function.

Inserting Procedures Name Automatically Into VBA Code
This page describe a procedure named InsertProcedureNameIntoProcedures and supporting function that you can use to automatically insert procedure names in to procedures with a CONST statement.

Installing An XLA Add-In
This page describes how to install and load an XLA add-in file for Excel.

Installing COM And Automation Add Ins
This pages describes techniques and System Registry Keys and Values required to install your COM Add In for users that do not have administrative privileges on their acccount. This describes how to add a COM Add In for an individual user or to configure the add in so that it is available to all users of a machine. This pages assume you are somewhat familiar with the System Registry and the RegEdit Registry Editor program.

IsArrayAllocated
This page describes a VBA function to determine whether a dynamic array is allocated. It will work with an array of any type, of any number of dimensions, including arrays returned by the Split function.

Interfaces And Implementation In Class Modules
The Implements Page provides a detailed and in-depth examination of Inteferaces in VBA and how to Implement an Interface in a class module. Interfaces are an intermediate to advanced level topic. They are not widely used in VBA project because they are not widely known. However, once you understand what an interface is and how to implement in in a class, you have a very powerful programming technique at your disposal. Interfaces should definitely be part of the VBA programmer's arsenal of programming techniques. This page includes a downloadable workbook with all the code described on the page. While the example is in Excel, interfaces are part of the core VBA library and can be used in any VBA application. A solid understanding of classes in general is required to make use of interfaces.

IsFileOpen
This page describes a function named IsFileOpen which determines whether a file is in use by another process.

Julian Dates
This page describes various Excel formulas for working with Julian dates (dates in YYDDD format), including converting them to and from standard Excel dates, and adding days to Julian dates.

Keyboard Shortcut To Formula Bar Depending on your option settings, you may not have a keyboard shortcut to set focus to the Formula Bar. This page describes a method to create a keyboard shortcut to the Formula Bar.

Last Cell In A Range
This page contains VBA code to find the last non-empty value in a range of cells.

Last Cell In A Row Or Column
This page describes formulas you can use to get the last non-blank cell in a row or column.

Last Update Time Of Cell
The formulas on this page allow you to record in one cell the last time that another cell was changed.

Latitude And Longitude
Working with geographic coordinates is fairly simple using the techniques described on this page. There is also a formula for computing the Great Circle Distance between two points.

Lists, First And Last Elements
This page describes formulas to get the first and last elements in a list of data, and to get the positions of those elements.

ListBox Utilities
This page contains about a dozen VBA procedures designed to make the MSForms ListBox easier to use. The module includes procedures for moving sets of list items up or down in the list box and a sorting functions.

Lookup Formulas
This pages describes some formulas to handle looking up data in lists that VLOOKUP can't handle.

Make Multiple Directories
The VBA function MkDir allows you to only create the right-most directory in a path specification, and all the directories on the left must already exist. The MakeDirMulti function described on this page allows you to create a subdirectory nested as deep as you like, and all appropriate subfolders will be created as required.

Matrix And Vector Functions
This page describes formulas to convert an R-by-C matrix to a single column or row vector and to extract one vector out of the matrix. It also uses these formula to create a dynamic chart.

Menus In Excel
This page is an introduction to creating custom menu items in Excel97 and 2000, both manually and with VBA code.

Merging Lists With No Duplicates
A common task in Excel is to merge two lists into a single list, usually preventing duplicates in the final merged list. This page describes exactly how to do this with VBA.

Missing References In VBA
There may be times, especially when copying a workbook from one machine to another, that the references in the VBA Project get screwed up. This page describes why that may happen and the steps you can take to remedy the problem.

Multiple Subdirectories
VBA provides the MkDir function to create a directory, but the parent directory must already exist. This leads to multiple calls to MkDir, which can be cumbersome and prone to error. This page provides a function to create a nested chain of subdirectories with a single line of code.

Name Box
If you use defined names in your workbooks, you have probably been frustrated by the fact that the Name dropdown box just isn't wide enough to fully display the names. This page describes some code you can use to increase the width of the defined name dropdown box.

Named Ranges
You can use a defined name to refer to a range or formula in your workbook, which makes your workbook much more maintainable. Defined names can also be used to circumvent some of the limitations in Excel's Data Validation and Conditional Formatting tools. Learn all about defined names and names range on this page.

Nested IF Functions
It is a well known, and often irritating, limitation in Excel that you cannot nest more than 7 levels of IF statement.  This page describes a technique using defined names that lets you get around this limitation. 

New Posters' Guide
Many people learn about my web site through my participation in the Excel related newsgroups on Usenet.  If you're new to the Excel newsgroups,  please take the time to read these helpful hints and tips for new posters. 

No Blanks
This page describes worksheet formulas and VBA code to extract only values from a range that contains both values and blank cells.

Optimizing VBA Code
As your VBA projects get larger and more complex, it becomes more important that your code is written to be as efficient as possible. Learn about several techniques you can use to improve the speed (and reliability) of your VBA code.

Optional Parameter And ParamArrays To Functions
This page discusses how to implement optional parameters and an unknown number of parameters of a VB/VBA procedure. It illustrates the Optional keywords, the IsMissing function, and the ParamArray type parameter.

Ordinal Numbers In Excel
Ordinal numbers are those which indicate order in a series, such as 1st or 2nd or 3rd. This page describes how to create those suffixes with either a worksheet formula or a VBA function.

Parsing Telephone Numbers
A frequent task in Excel is to split full telephone numbers into the area code, local number, and extension components.  This page describes VBA procedures to do this. 

Pictures On Command Bar Items
This page describes how to add custom images to command bar buttons and menu items. You can use either external image files or images embedded on a worksheet.

This page describes methods to pause execution for a specified amount of time and to pause code to wait for user input on a worksheet.

Playing A Sound From VBA
This page describes how to play wav files from VBA. You can use your own sound files or you can use the sound files provided by Windows in the C:\Windows\Media directory.

Pivot Tables
Pivot tables are an exciting and powerful tool in Excel. This Introduction was written by former MVP Harald Staff.

Preventing Duplicates On Entry
Learn how to use Excel's Data Validation tool to prevent users from entering duplicate values into a list of data. 

Pricing Formulas For Bracket And Progressive Models
In many business, especially manufacturing and wholesaling, the unit price of an item may determined by the number of units purchased. A widget may cost $2.00 when 1 to 9 are purchased, but may cost $1.50 when between 10 and 99 are purchased, and so on, with greater discounts given to larger quantities.

There are two methods to determine the total cost of the order. The simpler is when the the unit price is the same for all units in the order, and depends only on the number of units ordered. For example, this would be the case if 50 widgets were purchased each at $1.50, where the $1.50 unit cost came as a result of the total quantity of 50. Here, the unit cost can be determined by a simple VLOOKUP function (as will be shown later), and the total cost of the order is simply unit-cost times quantity, or $1.50 * 50 = $75.00.

However, it becomes more difficult when the unit price is distributed across the order. For example, suppose that in a order of 50 widgets, the first 9 are priced at $2.00, and the remaining 41 are priced at $1.50. In this example, there is no single unit cost. The total cost of the order is ($2.00 * 9) + ($1.50 * 41) = $79.50. While it is simple enough to do this example by hand, it becomes more complicated when there are a large number of quantity ranges, each with a different unit price.

This page describes formulas for Excel that can be used to determine these costs. We'll call the first method "Bracket Pricing", in which the total order is based on a single unit-cost, and we'll call the second method "Progressive Pricing" in which the price of each unit sold is based on where it falls into various "quantity intervals".

Prime Numbers
This pages describes formulas to test whether an integer is a Prime Number. This is accomplished with a single array formula. The page also provides and expansion on this to determine if a number is a Prime Number, a Prime Twin, or not a Prime Number, all with a single formula.

Printing Multiple Sheets
This page contains VBA code to print multiple worksheets with a single line of code.

Programming The VBA Editor
This page describes some of the objects, properties, and methods of the Visual Basic Editor, and procedures you can use add and delete code modules and procedures, all programmatically with VBA code. In short, these procedures allow you to write code using code.

Protecting Worksheets, Workbooks, And VBA
This page describes how to protect your workbooks, worksheets, and VBA code.

Quick And Easy Directory Tree
This page describes code to create a simple diretory listing. It is simpler and easier to adpat, at the cost of fewer options, than the code at FolderTree.aspx.

Random Numbers In Excel And VBA
This pages describes several formulas and VBA functions for working with random numbers. It includes code to return a number of random values within a range of values and to return an array of unique, non-duplicated random numbers.

Ranking Data In Lists
People often use Excel to store lists of data which need to be ranked. For example, you may use Excel to store information about your bowling team, and you want to determine the ranks for players and scores. Excel provides a worksheet function called RANK to do some fairly simple ranking, but this function has some limitations. This page describes some more advanced formulas for ranking data.

This page is a complete re-write of the original Rank page.

Recursive Programming
Recursive programming is a powerful technique that can simplify otherwise complicated programming tasks. In summary, recursive programming is when a procedure calls itself passing to itself a modified value of the original input parameter(s). This pages uses the mathematical function Factorial to illustrate recursive programming techniques.

Recursive Programming And The FileSystemObject
Recursive programming, in which a procedure calls itself, it a powerful programming technique. This page describes recursive programming in general and then with an example using the FileSystemObject to create a directory tree listing.

Recycling Files
VB and VBA provide the Kill statement to delete a file. This permanently deletes the file; it does not sent the file to the Windows Recycle Bin. This page describes how to send a file or folder to the Recycle Bin.

Registry
If you need to store information from one Excel session to the next, such as user preferences or application configuration data, you can store that data in the System Registry. This page describes about 10 VBA functions the are used to read and write Keys and Values to the System Registry. The procedures presented here should give you full control over the registry. It is assumed that you are familiar, at least at a conceptual level, with the system Registry. Be warned, however, that deleting registry keys can be dangerous. If you delete the wrong key, applicaiton be behave incorrectly or not start at all. In the very worst case, Windows itself may not be able to start.

RegistryWorx DLL Registry Component
The System Registry can be difficult to work with using the standard Windows API functions. The RegistryWorx DLL component wraps the API functions up in to nice VBA-friendly functions. There are functions to create, test, and delete Registry keys and values.

Avoiding Problems When Renaming A Worksheet
VBA problems may occur in your applicaiton may arise when a user renames a worksheet that is explicitly referenced by name in your VBA code. This page describes how to avoid these problems.

Referencing Worksheets From Formulas
This page describes some VBA procedure that you can use to refer to the first, next, previous, or last worksheet in a workbook. These functions are intended to be called directly from worksheet cell, not from other VBA procedures. Because they make use of the Application.Caller property, they will not work unless they are called directly from worksheet cells.

Relative References
This is an explanation of the differences between absolute and relative reference styles in Excel formulas. Learn about the differences, and understand when you should use one or the other.

Returning Arrays From User Defined Functions

This page discusses matters related to returning an array as the result of a User Defined Function in VBA or COM or Automation Add-In.

Returning Errors From VBA Functions
This page describes how to use the CVErr function to return error values from VBA User Defined Functions.

Rounding Errors In Excel97
This page describes the common causes of apparent numeric errors in Excel: actual versus displayed values, and approximation of rational numbers in industry standard double-precision floating point variables. The IEEE format is described at the bit level.

RowLiner
This pages describes the RowLiner add-in, which allows you to display row and/or column leader lines on your worksheet.

SaveCopyAndZip Add-In
This add-in is a front end to both the PKZip and WinZip programs. It allows you to save as a zip file any open workbook. It does not alter the existing workbook in any way. You must have either PKZip or WinZip installed on your machine.

Also available is a SaveCopyAndZip COM Add-In that does its own zipping, without relying on PKZip or WinZip.

Scheduling Procedures With OnTime
You may need to design your Excel workbooks to run a procedure periodically, and automatically. For example, you may want to refresh data from a data base source every few minutes. Using VBA, you can call upon the OnTime method of the Excel Application object to instruct Excel to run a procedure at a given time. By writing your code to call the OnTime method by itself, you can have VBA code automatically execute on a periodic basis. This page describes the VBA procedures for doing this.

Scope Of Variable And Proceduvres
This page describes what is meant by the scope of a variable or procedure and how scope affects the use of variables and procedures within a project and between projects.

Search
The Search page allows you to search for specific words or phrases on the web site. Together with the Topic Index page, this page lets you find what you need to know.

Set Application Icon

This page describes code that you can use to customize your application with your own custom icon on the main Excel application window.

SetParent
Normally, a userform floats above, and independently of, the Excel application window and the worksheet windows. This means that when you move the application window or a sheet window, the form does not move with the window; it stays in its original location on the screen. You can use a series of Windows system calls to set the form as a "child" window to the worksheet window. When you do this, the form will move along with the application window and the sheet window.

SetFocus
When you show a form modelessly, focus is set to the first control on the form. However, there may be times in which you want to worksheet to retain focus, so when the user types, input goes to the worksheet, not the form. This page describes the Windows API function and VBA code that you can use to accomplish this.

Sheet Name Functions
This page contains VBA functions for working with worksheet and workbook name lists and relative sheet addressing.

ShellAndWait
The VBA Shell command starts a process and returns control back to VBA. This page desribes code you can use to wait for a Shell'd command to finish before returning control back to VBA.

Shortcut Keys
This page has three downloadable workbooks, listing the Excel keyboard shortcuts, VBA Editor keyboard shortcuts, and the ALT key menu accelerator keyboard shortcuts.

ShowAnyForm
This pages describes how to show a UserForm that is to be determined at runtime. It utilizes the often forgotten about VBA.UserForms object and CallByName to access a form and its controls using string variables whose values are set a run time.

Shuffling An Array To Random Order
This page descrbes VBA procedures to shuffle an array into random order.

Sorting An Array
Unfortunately, VBA does not have a built-in method for sorting arrays. This page describes two methods for sorting array in VBA.

Sorting By Color
If you have color-code cells in your worksheet, you find that at times it is useful to sort rows by the colors of the cells. That is, sort all the reds at the top, followed by the blues, followed by the yellows, and so on. Unfortunately, Excel provides no such tool. You have to do it manually. This page describes how to do it.

Sorting Worksheets In A Workbook
In some applications, it may be useful to have the worksheets in alphabetical order. For example, if you have a worksheet for each employee on a team and each employee has their own worksheet, you may want these sheets in alphabetical order. You could do this manually, but if you have more than a few sheets, it would be easier to automate the task. Excel does not have a built in tool to do this, but you can use some fairly simple VBA code accomplish this.

NOTE: This is a complete rewwrite of the original Sort Worksheets page with several additional code features.

Special Characters In Excel
Using a typical computer keyboard, you can directly type in approximately 94 different characters, such as letters and number other symbols like punctuation marks. However, there are many other symbols available. In a typical font such as Arial, there are some 200 different symbols available, including the British Pound sign �, the Euro Currency symbol �, and the copyright mark �. These symbols cannot be directly entered with a single key on a typical (US) keyboard. This page describes how to work with these special characters in Excel. You can download an add-in file that makes it easy to view and insert symbols, even if you don't know the code numbers.

Special Folders
This page presents VBA code that you can use to return the name of the current user's Profile Folder and special folders such as My Documents and Desktop.

Split Strings -- A Better Way
This Split function is very useful, but has two shortcomings: it can't properly handle quoted string that should not be split apart, and it has problems with consequtive delimiters between which there is no text. This page describes a VBA function that fixes these problems.

Split Strings With Multiple Delimiters
This page contains VB/VBA functions for splitting strings into sub strings based on multiple delimiters. The delimiters many be single characters or strings of character.

Status Bar
This page describes a few quick hints about working with the Excel status bar, including how to clear your message from it after a set amount of time. 

String Concatenation
Excel's CONCATENATE function is of little or no use. It cannot concatenate test in a range of cells and it cannot be used in an array formula to selectively build a string based on inclusion criteria. This page describes a VBA function named StringConcat that overcomes all the limitations of the CONCATENATE function.

Support When you're new to Excel, and even when you're not, you'll find that you need answers to questions. This page describes how to get answers from various sources on the internet.

Suppressing Events In A UserForm
The Application.EnableEvents property does not apply to events of controls on a UserForm. This pages describes how to write your own EnableEvents property for a UserForm.

Tables And Lookups
This page describes a number of formulas for returning rows and columns from a table, return values based on multiple lookups, arbitrary occurrence lookups, and closest match lookups.

Tables To Row Or Column
This page describes formulas you can use to transform a two-dimensional table of rows and columns into a single row or a single column of data.

Test Excel Shutdown
This page describes a method using a COM Add-In to detect and take action when the Excel application itself shuts down.

Testing The State Of The SHIFT, CTRL, and ALT Keys
This pages contains 3 VBA functions to test whether the SHIFT, CTRL, or ALT keys are pressed. For each key, you can test whether only the Left, only the Right, either the Left or Right, or both the Left and Right keys are pressed.

Time Zones In VBA
Excel and VBA do not provide a direct way to work with either Time Zones or Daylight Savings Time. This page describes how to read this information from Windows. Also, you can download a workbook containing a class module which simplifies all of the Windows API calls, allowing you to access a few simple properties.

TextBox
If you use Userforms in VBA, you almost certainly use the TextBox control. Unfortunately, there is no property that allows you to restrict entry to certain characters, such as numeric only. This page describes some VBA code that restricts data entry to numeric (and supporting) characters only.

Timed Close
You may find that your application should close a file if there has been no user activity on that workbook for a specified period of time. That is, if the user hasn't accessed the open workbook for so many minutes, save and close the workbook. This can be achieved using event procedures and the OnTime method.

Time Bombing A Workbook
The may be times that you want to limit to the time that a user can work with a workbook, such if you are distributing a trial or demonstration version of an application. This page describes a few techniques to limit the amount of time that a user can use a workbook.

Tools For Excel
This page contains descriptions and links to the "must have" tools for Excel users and developers.  These are tools written by others, which I use every day. All are free to download and use.

Topic Index
This page lists, alphabetically, all of the topics covered on the web site, each linked to the page where that topic is discussed. Together with the Search page, this page lets you find what you need to know.

Tracking Workbook Open And Close Operations
This page describes some event procedures you can use to track when and by whom a workbook is opened and closed. While I generally don't like such snooping by the boss, auditing who opens a workbook may have its place.

UnSelecting A Cell Or Area
This page describes VBA procedures to remove a cell or area from a non-contiguous selection of cells or ranges. For example, if you've selected A1, A3, A5, and A7, and you want to remove A5 from the selection, this code can do it for you.

UserForms And The Windows API Functions This pages desribes how to modify (for example, adding Maximize and Minimize buttons) to a UserForm by using the Windows API functions.

Vectors To Matrix
This page describes formulas for transforming a row or column of data to a matrix.

Versions Of Excel
This pages describes some of the changes between the various versions of Microsoft Excel.

Versions Of Excel
This page describes VBA code you can use to read the system registry to determine what versions of Excel are installed on the local machine.

Visible Cells
This page describes how to work with visible and hidden cells.

Wait For File Close
If you are working in a networked environment, you may need to open and modify data in another workbook. If that workbook is open by another user, you will be to open it, but only in read-only mode. You won't be able to update the workbook. This page describes code that you can use to pause your process to wait for an open file to be closed.

Week Numbers In Excel
Many organizations and applications use the week number of a date for identificaiton or organizational purposes. However, this can be problematic because there are a variety of ways of specifying when the first day of the first week of year should fall. This page presents a number of worksheet formulas and VBA procedures for working with various types of week numbers.

Weighted Averages
This page describes weighted averages and formulas to calculate them.

What's New?
Here's where you'll find what's new on the web site -- new topics, new pages, and new downloadable files. Check this page often to be sure you don't miss anything important.

Where To Put The Code


This page describes modules, procedures, and snippets, and explains where to put the code you find on this web site and other venues.

Who The Hell Is Chip?
This page answers your questions about who I am and why I do this.

Word Count
This page describes a macro to count the number of words in worksheet.

Workbook Command Bar
This is a nifty little COM Add-In written in Visual Basic 6 that creates a command bar with a button for each open workbook, along with a 'tree-view' navigation form through which you can directly activate any visible worksheet in any visible workbook. You can download the installation program from the page. The VB6 source code is available upon request.

Working With Lists
This page describes a number of formulas for using SUM, MIN, MAX, and AVERAGE functions to ignore zero values, or negative values, or values that fall outside of an interval. Also included are formulas to reverse the order of a column or row, and to transpose a column or row.

Writing Your Own Functions In VBA
This page describes how to write your own custom functions in VBA that can be called directly from worksheet cells. It includes illustrations of functions that can used as array formuals.

Working With Overtime Hours In Timesheets This page describes a number of formulas for working with regular and overtime hours in worksheets, including excluding time taken for meal breaks. This is a frequent task in Excel, and this page brings together in one example many of the other formulas found in the date and time related pages.

Workbook And Worksheet Name, Returning To Cell
This page describes formulas you can use to get the full name, folder name, file name, sheet name, and file extension of a workbook.

Zooming And Centering On A Range
This page describes VBA procedures that you can use to zoom the screen on a range of cells, and to center a cell or range of cells in the center of the screen.

Other Resources

WebHostingSearch: A good place to check out various hosting types like dedicated servers and other related reviews.

See also the Topic Index Page for a complete list of topics discussed on the web site.

This page last updated: 1-Sept-2011.