What's New On The Site

This page is a chronological listing of updates to the web site, from 1998 through the present.

This page lists, in reverse chronological order, updates to the web site. It does not include minor changes or formatting changes.

Click here to subscribe to the What's New RSS Feed.

Set up RSS in Outlook.

 

4-Jan-2014
I completely rewrote the site MasterPage, which is the container in which the content of all the other pages is displayed within. The result is a cleaner, more streamlined look and feel. I hope you like it.

17-June-2014 Interfaces And Implentation In Class Modules
The Implements Page provides a detailed and in-depth examination of Interfaces 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 and can be confusion. 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.

23-Dec-2013 Downloads Page
The Downloads page has been completely rewritten to make it easier to find the download you're looking for. It lists all the zip files available for download and it list the files contained within a zip file, and it list all the web pages that reference that zip file. You can download an entire zip file, or just one file contained within a zip file. You can also view the content of a file contained within a zip file.

19-Dec-2013 Site Feedback Page
The Site Feedback page has been completely rewritten. It is easier to use and allows you to attach a file to your message if you think a file will help illustrate your comments or questions.

3-Nov-2013 Problems with the MOD function and Mod operator.
The MOD worksheet function and the Mod operator in VBA can cause problems when used with very large numbers. This page describes work around formulas and code to prevent error with very large numbers.

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

5-Jan-2013 Debugging VBA
This page describes how to use VBA's built in debugging tools.

24-Nov-2012 Last Non-blank cell in row or column.
This page describes formulas you can use to determine the last non-blank cell in a row or column.

27-Oct-2012 BreakInClassModule.aspx
This page describes how and why you should always use the Break In Class Module error trapping setting.

26-Oct-2012 Setting Folder Depth
The DirTree add-in and code now has a Depth option that allows you to specify the maximum depth in a folder hierarchy to list.

20-Oct-2012 Averaging A Range Excluding Values
This page describes formulas and VBA to get the average of a range of numbers, excluding values.

29-Sept-2012 Getting A Filtered List Of Worksheet Names
This page describes VBA code to get a filtered list of worksheet names, either to the worksheet or to other VBA code.

22-Sept-2012 Getting A List Of Printers
This page describes VBA code that you can use to get an array of printers, each element of the array being the name of a single printer. You can use this to change the ActivePrinter to a value selected by the user in a list box or input box.

16-Sept-2012 Ordinal Suffixes
This page illustrates how to add an ordinal suffix to a number, changing, for example, 23 to 23rd. Both code and formula methods are described.

13-Sept-2012 Passing Arrays Between The Worksheet And VBA
This page describes how to pass arrays between the worksheet and VBA arrays. Properly used, these techniques can show a significant increase in performance of your VBA application.

17-August-2012 The CELL Function
This page describes how to parse the output of the CELL function in order to retrieve file, folder, and sheet information.

29-June-2012 Email From VBA
This page describes how to send email from VBA without using Outlook.

6-March-2012 File Descriptions
This page shows how to read the registry to get the ProgID and file description of a file based on its extension.

4-March-2012 Existing Excel Versions
This page describes the VBA code to read the system registry and determine what versions of Excel are installed on the local machine.

08-Feb-2012 Setting The Excel Window Icon
This page describes code that you can use to change the icon of the main Excel application window to your own custom icon.

02-Jan-2012 Exporting to fixed field length files.
This page describes how to export data from a worksheet to a fixed field length text file.

02-Sept-2011 Importing Fixed Field Length files to Excel
This page describes VBA code to import data from a fixed field length file into Excel.

09-July-2011 Pausing Code And Wait For User Input
This page describes several methods for pausing code execution and pausing code to wait for user input to a worksheet cell.

06-June-2011 Returning Workbook And Worksheet Names
This page describes formulas and VBA code for returning the sheet name, file name, full name, directory name and file extension of a workbook.

28-March-2011 Returning Errors From VBA Functions
This page describes how to return errors from User Defined Functions in VBA.

25-March-2011 Delimited text in worksheet cells.
This page describes worksheet functions for working with delimited text in a worksheet cell.

14-February-2011 Common Functions For Dates.
This page describes worksheet functions for working with dates.

12-February-2011 Functions For Worksheet and Workbook name lists and relative sheet addressing.
This page describes VBA functions to create lists of worksheet or workbook names and relative sheet addressing.

5-February-2011 Removing blank values from a range.
This page describes worksheet functions and VBA code to return only non-blank cells from a range.

31-January-2011 Converting A Row Or Column To A Matrix
This page describes formulas for converting a column or row of data to a matrix.

29-January-2011 Printing Multiple Worksheets
This page provides VBA code for printing multiple worksheets with a single function call.

20-January-2011 Utilities For The ListBox Control
This page provides about 10 functions for working with listboxes.

12-January-2011 An Improvement To The 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.

8-January-2011 Testing For Various Characters In Strings
This page describes a number of array formulas that you can use to test the character content of cells.

10-September-2010 Creating A List Of Enum Value Names
This page describes code to get the list of value names of an enum and put that list into the Windows clipboard, suitable for inclusion in a Select Case statement to validate a value that should contain an enum value.

20-August-2010 GetLastCell
This page describes VBA code to find the last used (non-empty) cell in a worksheet range.

8-August-2010 GetDistinct
This page describes a VBA function that you can use to create a list of distinct values in a list of values.

2-August-2010 A Simple Directory Listing
This page describes simple code to create a directory tree listing. While it has far fewer options than the code at FolderTree.aspx, it is much easier to adapt to use in your own code.

30-July-2010 Problems When Renaming A Worksheet
This page describes how to prevent problems in VBA code that may arise if a user renames a worksheet.

21-June-2009 Linked List Boxes
This page describes how to link multiple listboxes in a fashion that changing one listbox value will update "downstream" list boxes.

09-June-2009 Weighted Averages
This page describes weighted averages and formulas to calculate them.

20-April-2010 A Better Alternative To The Split Function
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.

10-April-2010 Tracking Workkbook Open And Close Operations
This page describes VBA event procedures you can use to track when and by whom a workbook is opened and closed.

8-April-2010 Full File Names In Window Captions
This page describes some event code that you can use to display the full file name, including drive and path information, in the Excel application window caption and the individual worksheet window captions.

6-April-2010 Where To Put The Code
This page describes modules, procedures, and snippets and describes where to put and how to use the code that is found on this web site and in other venues.

18-March-2010 Keeping Focus On The Worksheet
When you display a user form, input focus goes to the form. However, you may want focus to remain on the worksheet. This page describes a few simple Windows API functions you can use to accomplish this.

10-Feb-2010 Creating Multiple Subdirectories
This page describes VBA code to create nested subfolders in a single line of code.

30-Sept-2009 Calculating Easter
This page describes how to calculate the date of Easter for a given year. Both a worksheet formula and a VBA function are shown.

3-Sept-2009 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.

18-August-2009 A Better WORKDAY Function
Excel's WORKDAY function suffers a significant shortcoming: Saturday and Sunday are hard-coded into the function. You cannot specify other or additional days of the week. This page provides a function that allows you to exclude any days of the week.

14-August-2009 Functions For Working With Days Of The Week
This page provides about ten functions, both as worksheet formulas and VBA functions, that works with calculations involving days of the week.

6-June-2009 Defined Names In Worksheet And Workbooks
This page introduces Defined Names and illustrates how to use them in worksheets and workbooks.

23-April-2009 Downloading A File From The Internet
This page describes code you can use to download a file from the internet.

3-April-2009 Flexible Lookups - 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.

20-March-2009 Globally Unique Identifier Values (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.

19-March-2009 Fractional Months
Some Excel applications need to calculate the difference between two dates as a fractional month. This can be ambiguous because a month can have between 28 and 31 days. This page describes formulas for working with fractional months.

17-March-2009 Merging Lists With VBA
A common task in Excel is to merges two lists into one, usually preventing duplicate entries in the final list. This page describes the code to do just this.

1-March-2009 Playing A Sound In VBA
This page describes how to play a sound file in VBA. You can use your own sound files or you can use one of the files supplied by Windows in the C:\Windows\Media directory.

23-February-2009 Custom Pictures On Command Bar Buttons
This page describes how to put custom images on command bar buttons and menu items. You can use either external image files or images embedded on a worksheet.

13-February-2009 Returning Every Nth Row From A Column
This page describes how to return every Nth row from a column of data and how to use SUM and AVERAGE on those data values.

20-January-2009 Creating Optional And Arbitrary XML Elements in an XSD Schema
This page describes how to write an XSD XML Schema to allow optional XML of any structure to be included in the XML instance document.

18-December-2008 Creating A Table From A Column With Variable Block Sizes
This pages describes VBA code to create a table from a column of blocked data where the data blocks are of variable length.

16-December-2008 Procedure And Module Attributes For The Object Browser
You can add Attribute codes for modules and procedures that will be displayed in the Object Browser. This page describes how to do that.

16-December-2008 Windows Clipboard
This page describes VBA code that you can use to put data in the Windows clipboard and to retrieve data out of the Windows clipboard.

13-December-2008 FindAll XLA Add-In
This page describes the free XLA add-in named FindAll.xla that allows you to search any number of worksheets in a workbook and display the results on a single user interface. The page links to the downloadable file.

20-November-2008 Table To Column Or Row
This page describes formulas you can use to transform a two-dimensional table of rows and columns into a single column or a single row of data, in either row-by-row or column-by-column order.

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

9-November-2008 New Page: IsArraySorted
Sorting is an expensive operation in terms of processor overhead and memory usage, and this problem only gets worse as the size of the array increases. This page illustrates functions to test whether an array is already sorted and therefore does not need to be sorted again.

6-November-2008 Modified CountColor and SumColor functions.
The functions CountColor and SumColor have been modified so that you can specify a ColorIndex of 0 to indicate either xlColorIndexNone or xlColorIndexAutomatic when counting or summing cells that have no color applied to the background Interior or to the Font. This makes the functions easier to user because a ColorIndex value of 0 will work regardless of the value of the OfText parameter and a value of 0 is more intuitive and easier to remember than the numeric values of xlColorIndexNone or xlColorIndexAutomatic.

20-Oct-2008 Get What's New Information Via RSS
You can learn what's new on the web site via an RSS feed. Point your RSS aggregator (or simply point your browser) to http://www.cpearson.com/RSS.xml

19-Sept-2008 Connection To Microsoft Newsgroups
One of the best ways to get support for Excel and answers to your questions is via the Microsoft NNTP USENET newsgroups. Often, you'll get an answer in a matter of minutes. Best of all, it is totally free. This page describes how to connect to the Microsoft newsgroup server and provides guidelines about how to post in a manner that maximizes the probability of getting a satisfactory answer.

15-Sept-2008 Prime Numbers And Prime Twins
This page presents a formula for testing whether a number is a Prime Twin, a Prime Number, or not a Prime, all with a single formula.

9-Sept-2008 ShellAndWait
The VBA Shell command starts an external process and immediately returns control back to VBA. This page describes how to wait for the Shell'd process to terminate before return control back to the caller.

4-Sept-2008 Visible And Hidden Cells
This page describes code to work with visible and hidden cells.

31-July-2008 Fractional Arithmetic
The page on Fractional Arithmetic using the DOLLAR functions and array formulas has been re-written.

31-July-2008 Shuffling An Array To Random Order
This page describes to procedures to shuffle an array into random order.

17-May-2008 Modifying UserForms Using Windows API Functions
This page describes how to use Windows API functions to modify a UserForm. For example, you can add a Maximize and Minimize buttons to a UserForm.

5-May-2008 A Better NETWORKDAYS
The NETWORKDAYS function works fine if your weekends or days off are Saturday and Sunday. However, NETWORKDAYS will not work with other days of the week. This pages describes two formulas that overcome the limitations of NETWORKDAYS.

5-May-2008 Time Zones And Daylight Time
This pages describes VBA procedures for working with time zones and daylight savings time.

15-March-2008 File Extensions And VBA Code
By default, Windows does not display file extensions in Explorer windows. That is, the file Book1.xls is display only as Book1, without the xls extension. The value of this Windows setting has implications in Excel VBA. This page describes how to test this setting and code appropriately.

3-May-2008 IsFileOpen
This page describes a function named IsFileOpen which determines whether a file is currently open by another process.

6-March-2008 Functions For Working With Colors
The old Colors page has been completely rewritten. Most functions have been improved and many new functions have been added.

5-January-2008 Scope Of A Variable Or Procedure
This page describes what is called the scope of variables and procedures. The scope of a variable or procedure defines from where that variable or procedure can be accessed. Scope is discussed in the context of procedures, modules, projects, and groups of related projects.

5-January-2008 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.

4-January-2008 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.

24-December-2007 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.

5-December-2007 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.

6-November-2007 Returning Array From User Defined Functions
This page describes techniques to return arrays from user defined functions in VBA, COM Add-Ins, or Automation Add-Ins.

5-November-2007 DistinctValues Function
This page describes a VBA function named DistinctValues that returns an array containing the distinct values of a range or array of values. This function may be array entered into a range of cells on a worksheet, incorporated into an array formula, or called from other VB code independent of a worksheet.

2-November-2007 String Formulas
This page describes some worksheet formulas for working with text strings in worksheet cells.

27-October-2007 Missing References In A VBA Project
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.

26-October-2007 Finding All Occurrences 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.

20-October-2007 String Concatenation With Ranges And Arrays
This built in CONCATENATE function is so limited that it is not a useful function. You cannot concatenate text in arrays or ranges and you cannot use it in an array formula. This page presents a VBA functions named StringConcat that concatenates strings in a range of cells and arrays and can be used in array formulas to selectively create strings. You can download the code from the page.

8-October-2007 Creating An XLA Add-In
If you know how to code in VBA, you know almost everything you need to create your own XLA add-ins. This page fills in some of the blanks.

4-October-2007 New Function For Random Numbers
A new function named RandsFromRange allows you to return a specified numbers from a range on a worksheet, in random order and with no duplicates.

29-Sept-2007 Optional Parameters To Functions In VB With ParamArray
This page discusses how to implement optional parameters and an unknown number of parameters to a VB/VBA procedure. It illustrates the Optional keyword, the IsMissing function, and the ParamArray type parameter.

25-Sept-2007 Creating A Function Library Automation Add-In With NET
This page describes how to create an Automation Add-In Function Library In VB NET.

23-Sept-2007 Sorting And Moving Worksheets Within A Workbook
Excel does not directly support sorting worksheets. This page contains VBA code to do this and much more, with a number of functions for orderng worksheets within a workbook. This is a complete rewrite of the original SortWS page with much more code and features.

21-Sept-2007 Splitting Text Into Substrings With Multiple Delimiters
This page describes two VBA procedures that extend on VBA's Split function to allow splitting strings based on multiple delimiters. Each delimiter can be one or more characters.

20-September-2007 Ranking Data In Lists
This page is a complete re-write of the original Rank page and provides worksheet functions for working with ranking data in lists.

16-September-2007 Additional Registry Functions Added To The RegistryWorx DLL Component
Several new functions have been added to the RegistryWorx.dll ActiveX component. RegistryWorx is an ActiveX DLL that wraps up all the Windows API calls for working with the System Registry into nice VB/VBA-friendly procedures.

15-September-2007 TreeView Control To Display Folders And Files
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.

14-September-2007 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.

13-September-2007 Matrix And Vector Formulas
This page describes worksheet formulas that you can use to convert a two-dimensional range on a worksheet into a single row of column of data, and to extract a single row or column out of a two-dimensional range. It also uses these formulas to create a dynamic chart.

4-Sept-2007 Time-Bombing A Workbook
There may be times that you want to limit a users access to a workbook after a period time, such as when you are distributing a trial or demonstration verison of an application. This page describes a few techniques to do this.

3-September-2007 Creating Menus For The VBA Editor
This pages describes the VBA code required to create new menu items or command bar controls for the VBA Editor.

1-September-2007 Writing Your Own Worksheet Functions In VBA
In addition to Excel's built in functions, you can write your own custom functions in VBA and call those functions directly from worksheet cells. This pages describes how to write User Defined Functions, including functions that can be used to return arrays to array enter functions.

23-August-2007 COM Add-Ins In Excel 2007
This page desribes some considerations regarding COM Add-Ins in Excel 2007 and the differences between using a COM Add-In in Excel 2003 and Excel 2007.

22-August-2007 CountType VBA 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.

21-August-2007 Application Events In Excel.
This page is a total and complete rewrite of the original Applicaton Events page. It desribes how to trap Application events in an existing object module such as ThisWorkbook or to trap events in a dedicated class module.

20-August-2007 Event Programming In VBA.
This page is a total rewrite of the old Events pages. The new page is better organized and provides more information. The page consists of a complete overview of events in general, the event object hierarchy, and complete information and examples regarding creating your own events in your classes.

16-August-2007 Bug Fix To Ensuring Macros Are Enabled, Technique 1 (Sheet Visibility)
This page uses sheet visibility properties to render a workbook unusable if macros are not enabled.

16-August-2007 Ensuring Macros Are Enabled, Technique 2 (Errors In Formulas)
This page describes a technique to cause #VALUE errors in critical formulas if workbook is opened with macros disabled.

12-August-2007 Creating COM Add Ins In Visual Basic
This page describes how to write a COM Add In using Visual Basic 6, including Automation libraries that can contain functions callable directly from worksheet cells. This page is a complete rewrite of the old COM Add Ins page.

11-August-2007 Random Numbers In Excel And VBA
This page describes how to use random numbers in Excel and VBA. It includes functions to return a number of random values from a range of numbers and to return a list of unique, non-duplicated random numbers.

10-August-2007 Automation Add Ins As Function Libraries
This page describes how to write Automation Add Ins to provide function libraries available to all users on a machine. It also describes how to write Automation Add Ins whose functions can be called by VBA code.

10-August-2007 Installing COM And Automation Add Ins
This pages describes the steps you need to take to install a COM or Automation Add In for users that do not have administrative privileges on their account. It describes installing the Add In for a particular user and installing for all users of a machine. This page assumes you are somewhat familiar with the System Registry and the RegEdit Registry Editor program.

2-August-2007 Is Array Allocated VBA Function
This page describes a VBA function that can be used to test whether an array has been allocated. It works on any sort of array with any number of dimensions, including the type of arrays returned by the Split function.

31-July-2007 RegistryWorx Registry Component
The Windows Registry can be difficult to work with using the standard Windows API functions. This DLL component wraps up the API functions in to nice VBA-friendly functions. It has functions to create, test, and delete registry keys and values. The component is an ActiveX DLL written in VB6 and can be used in either VBA or VB6. Download the RegistryWorx setup program here.

21-July-2007 Ensuring Macros Are Enabled
In complex workbooks and Excel-based applications, you may need to require that the user has macros enabled in order to use your workbook. There is no way to force a user to enabled macros. Instead, you can make your workbook unusable unless macros are enabled. This page describes how to do that.

19-July-2007 Persistent Minimums And Maximums
You can use Circular References to create formulas that will return the minimum or maximum value that has ever occurred in a range of cells, even if that value no longer remains in the range.

19-July-2007 Weekly Newsletter
I am pleased to announce that beginning today, I will be publishing a weekly newsletter that will describe various formulas, VBA code snippets, and general information about Excel, Formulas, and VBA For Excel. Subscribe here and get the newsletter in your email.

9-July-2007
NEW PAGE: Series Information
This page describes VBA code to get information about series of numbers, such as present and missing blocks of numbers and the minimum and maximum gaps between blocks of sequential numbers.

2-July-2007
NEW PAGE: Window Class Names Of Office 2003 and 2007 Applications
This page lists the window class names of most Office 2003 and 2007 applications. These are useful if you are using the FindWindow or FindWindowEx API functions.

1-July-2007
NEW PAGE:  Multiple Monitors
This pages describes how to use multiple monitors in Excel.

24-June-2007
NEW SITE:  The transformation from HTML to XHTML ASP.NET 2.0 has begun in earnest.

28-May-2007
NEW SITE:  I am starting the process of converting the entire site from HTML to ASP.NET 2.0. This will be a months-long process to get all 200 pages migrated  Please be patient during the transition period.

11-March-2007
NEW PAGE:  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.

10-March-2007
NEW PAGE: Recursive Procedures And The FileSystemObject
Recursive programming, in which a procedure calls itself, it a powerful programming technique. This page describes recursive programming using the FileSystemObject to create a directory tree listing.

28-Feb-2007
IMPROVED CODE: Recycle A File Or Folder
The code for recycling a file or folder has been completely rewritten.

27-Feb-2007
NEW PAGE: Suppressing Events In A UserForm
The Application.EnableEvents property does not apply to events of controls on a UserForm. This pages describes simple code you can use to implement your EnableEvents property of a form.

16-Feb-2007
NEW FORMULA: Testing Whether A Series Of Numbers Is In The Correct Order
The formula described in this section of the page determines whether a series of numbers in a column increase or decrease by a specified interval; that is, whether the elements in the series differ by a prescribed value.

15-Feb-2007
NEW PAGE: 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.

13-Feb-2007
NEW PAGE: 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.

8-Feb-2007
NEW INFO:  New Functions on the Programming To The VBA Editor page.
This page takes you step-by-step to create an Automation Add-In in Visual Basic 6 for Excel 2002 (XP) and later.

3-Feb-2007
NEW PAGE: Creating Automation Add-Ins With Visual Basic 6
This page takes you step-by-step to create an Automation Add-In in Visual Basic 6 for Excel 2002 (XP) and later.

30-Jan-2007
NEW PAGE: Deleting Duplicates With Advanced Filter
If you have a list of data and want to delete duplicates, you can do this with VBA code using Advanced Filter.  The function below, DeleteDuplicatesViaFilter uses Advanced Filter to make visible only unique rows, and then goes through and deletes the hidden rows.

28-Jan-2007
NEW PAGE: Creating COM Add-Ins In VB6
This page takes you step-by-step to create a COM Add-in that supports multiple Office applications in VB6. It also describes Automation Add-Ins, which allow you to write functions in VB6, compiled to machine code, that can be called directly from worksheet cells.

27-Jan-2007
NEW PAGE: Creating Nested Subfolders
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.

24-Jan-2007
NEW PAGE: Random Numbers
This page describes procedures for getting arrays of random Long integers between a specified minimum and maximum. The page includes a function that will return an array of integers with no duplicate entries.

23-Jan-2007
NEW PAGE: Find Method Of Range Object 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.

21-Jan-2007
NEW PAGE: Functions For Working With The 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.

20-Jan-2007
NEW PAGE: Creating A Tree-like List Of Subfolders And Files
This page describes VBA code to create a tree-like listing of all subfolders and files within a specified folder.

18-Jan-2007
NEW PAGE: Detecting And Acting Upon Excel Shutdown
This page describes a method using a COM Add-In to detect and take action when the Excel application is shutting down.

18-Jan-2007
NEW PAGE: Daylight Savings Time
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.

17-Jan-2007
NEW PAGE: Inserting Cells And Filling A Series
New This page describes a VBA procedure 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.

14-Jan-2007
NEW INFO: New functions on Collections And Dictionaries
Four new functions, CollectionToRange, DictionaryToRange, RangeToCollection, and RangeToDictionary, have been added to the Collections And Dictionaries page.

12-Jan-2007
NEW PAGE: Creating An Add-In
This page describes how to create an Excel add-in.

11-Jan-2007
NEW PAGE: Empty Folder
This page contains VBA code used to delete the contents of a folder, including all files and subfolder. The specified folder itself is not deleted. By default, the contents of the folder are sent to the Windows Recycle Bin, but you can specify that you want to permanently delete the contents.

7-Jan-2007
NEW PAGE:  Collections And Dictionaries
This page contains procedures for working with Collection and Dictionary object,  including sorting procedures.

4-Jan-2007
NEW PAGE:  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.

31-Dec-2006
NEW FUNCTION:  IsArraySorted
A new function, IsArraySorted, has been added to the ArraySupport library. It returns True or False indicating whether an array is sorted, in either ascending (the default) or descending order. Since sorting is an expensive operation, especially so with large arrays of Variants or Strings, you can call IsArraySorted to determine whether a Sort operation is really required.

31-Dec-2006
NEW PAGE:  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.

30-Dec-2006
NEW PAGE:  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.

27-Dec-2006
NEW ADD-IN:  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.

24-Dec-2006
NEW INFO:  ChangeBoundsOfArray, TransposeArray and VectorsToArray
Three new functions, ChangeBoundsOfArray, TransposeArray and VectorsToArray, have been added to the VBA Array Procedures page.

23-Dec-2006
NEW PAGE:  Converting Between Local Time And GMT And Date 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.

21-Dec-2006
NEW PAGE: 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.

21-Dec-2006
NEW PAGE: Getting and Setting File Times
This page describes two functions, GetFileDateTime and SetFileDateTime that can be used to retrieve or set the Create Date, Last Access Date, or Last Modified Date of a file. These functions are VBA-friendly wrapper functions for the GetFileTime and SetFileTime Windows API functions. GetFileDateTime and SetFileDateTime handle the date format conversions and the translations to and from local time to GMT Time.

2-Dec-2006
NEW PAGE: Calling Worksheet Functions In VBA
This page describes using Excel worksheet functions in VBA.

30-Nov-2006
NEW PAGE: Truly Global Variables In Excel
This page describes a method to create truly global variables in Excel, variables that will retain their values and remain accessible from any workbook, regardless of any workbooks that may be opened or closed, including the workbook that created the variable.  These numeric variables (Long type values) exist as long as the Excel application remains running.

19-Nov-2006
NEW PAGE: Sorting Arrays Of Objects
This pages describes the QSortObjectsInPlace function which can sort an array of object variables (any type of object) in either ascending or descending order. You must provide the function that determines whether one object is "less than" or "greater than" another object. An example of this function can be found on the QSortObjectCompare page.  

14-Nov-2006
NEW PAGE:  Detecting Scrolling In A Worksheet Window
Excel provides no method or event you can use to detect when the user scrolls in a worksheet window. This page describes a technique for detecting scrolling in a worksheet window.

7-Nov-2006
NEW PAGE:  Passing Arrays To And From Procedures
This pages describes how to pass arrays between procedures and how to use arrays as the result of Function procedures.

4-Nov-2006
NEW PAGE:  Working With Temporary Files
If your application needs to create temporary files, you need to ensure that the file name is unique.  This page contains procedures for working with the folder designated for temporary files for the current user and for creating temporary file names. The code on this page contains two procedures. The first procedure, GetTempFolderName , returns the name of the folder that is designated for temporary files for the current user. It does not name or create a new folder. Instead, it returns the name of the folder that the system has specified for current user to store temporary files. The second procedure on this page, GetTempFile , is used to get a filename that is guaranteed to be unique within the folder in which it is created.

3-Nov-2006
NEW PAGE: Getting The Names Of User-Specific Special Folders
On an operating system that supports multiple user accounts (such as Windows 2000 or Windows XP), each user of the system has his own data folder, called the UserProfileDirectory, that contains a user's special folders like My Document, Recent Files, and the Desktop. The exact folder name for these folders is different for each user.   If your application is opening or saving files in locations like My Documents or Application Data, you need to retrieve the correct folder for the current user.  This page describes two functions,  F_7_AB_1_UserProfileFolder and F_7_AB_1_GetSpecialFolder, that can be used to retrieve the specific folders for the current user.

2-Nov-2006
NEW PAGE: Importing Large Files Into Excel
An Excel (2003 and earlier) worksheet can contain 65,536 rows of data. If you attempt to import a text or csv file that has more records that Excel has rows, you will only be able to import the first 64K records from your file. The page contains a procedure that will import text or CSV files of any size. It will create new worksheets as needed to successfully import the data file, regardless of the number of rows.

31-Oct-2006
NEW PAGE: Connected And Disconnected Object Variables
An object type variable may become disconnected from its target reference if that target reference is destroyed. In this case, the variable is not set to Nothing. This page contains a procedure you can use to determine whether an object variable is connected to a valid object. Using this test will prevent automation errors in your code due to deleted objects like worksheets or shapes.

31-Oct-2006
NEW INFO: PathCompactPathEx
A new function, ShortenTextToChars, has been added to the SizeString page. This function is a VBA-friendly wrapper function to the Windows API function PathCompactPathEx. This function will take a fully-qualified file name and return a truncated version, replacing folder name componenets with "..." to size the file name to a specified number of characters.

30-Oct-2006
NEW PAGE: Sorting An Array
Unfortunately, VBA does not have a built in method for sorting an array. This pages contains a procedure called QSortInPlace that will sort an array of either string or numeric data, in either ascending or descending order.

30-Oct-2006
NEW PAGE: Getting the DLL Name Of A COM AddIn
Neither VBA nor Excel (or any Office application) provide a way to get the DLL file name of a COM Add-In. This page contains a procedure DLLOfComAddin that will return the fully-qualified file name of the DLL file of a specified COM Add-In.

26-Oct-2006
NEW PAGE: ShellAndWait
This page contains two procedures, ShellAndWaitSimple and ShellAndWaitEnhanced, that allow you to pause your VBA code until a Shell'd program ends. ShellAndWaitEnhanced allows you to display status messages to the user as the shell'd program runs.  An example workbook contains the fully documented code.

26-Oct-2006
NEW PAGE: VBA Arrays
This page contains a few VBA procedures for working with VBA Arrays.

24-Oct-2006
NEW PAGE: Working With Fixed Length Strings
This page contains a few VBA procedures for working with fixed length strings.

24-July-2006
NEW PAGE AND DOWNLOAD: PowerPoint Slide And Shape Renamer
In PowerPoint, there is no way in the User Interface to assign names to Slides and Shapes. Both Slides and Shapes support a Name property, but PowerPoint provides no mechanism to assign these names. You must use code. The PowerPoint Renamer COM Add-In provides a method to assign names to Slides and Shapes.

24-July-2006
NEW INFO: 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 pages describes a method to create a keyboard shortcut to the Formula Bar.

24-July-2006
NEW INFO: Programming To The VBA Editor
When you use VBA code to write VBA code, the VBA Editor will display itself.  Generally, this is undesirable. This section describes how to use Windows API calls to prevent any screen flickering.

24-July-2006
NEW PAGE: Moving A Form With A Window
Normally, a modeless userform (Excel 2000 and later) 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.

23-July-2006
NEW PAGE: 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.

18-March-2006
NEW PAGE: Class Modules
This page is an introduction to class modules.

17-March-2006
NEW PAGE: Enums
This page introduces the use of Enum data types in VBA.

6-March-2006
NEW PAGE: Error Handling
This page describes the basics of error handling in VBA code.

31-October-2005
NEW PAGE: 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 the OnTime method.

4-September-2005
NEW PAGE: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.

4-March-2005
NEW PAGE: 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.

1-January-2005
NEW PAGE: Feet And Inches
This pages includes a formula and a VBA function to work with feet and inches in Excel.

22-November-2004
NEW PAGE: WordCount
This pages includes a macro to count the number of words in worksheet.

3-March-2004
NEW PAGE: Browse For Folder
This page describes two methods to display a "browse for folder" dialog to the user.

23-April-2003
NEW PAGE: 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.

11-Mar-2003
NEW PAGE: Declaring Variables In VBA
This page describes the reasons that you should always use Option Explicit in your VBA code modules.

20-Feb-2003
NEW PAGE: 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.

4-Feb-2003
NEW PAGE: Debugging VBA Code
This page provides an introduction to the debugging and diagnostic tools available in VBA.

23-Jan-2003
NEW PAGE:  Progress Reporter
While a long procedure is executing, it is often desirable to indicate the progress to the user. The simplest way is to use the Excel's Status Bar area to display text messages, but if you want a graphical representation, you can use the ProgressReporter library described here.  ProgressReporter will display a simple window in Excel, and update a simple bar graph to display your procedures progress.  Of course, you'll have to tell ProgressAlerter how far along you are in your procedure. Download the DLL here.

20-Jan-2003
NEW PAGE: 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.

17-Jan-2003
NEW PAGE: Cell View
This add-in allows you to see the characters and their codes in a cell. This makes it easy to find unprintable characters such as tabs that don't display but may affect formulas.

23-Nov-2002
NEW PAGE: 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.

10-Nov-2002
NEW PAGE: Alert
The best way to display diagnostic messages to users and developers. Overcomes the limitations of MsgBox, Debug.Print, and Application.Statusbar.  Recommended for intermediate and advanced developers.

7-Nov-2002
NEW INFO: Using Windows Timers
The most common way of scheduling procedures or creating timers is to use Excel's OnTime method.  You can get more precise timers using Windows API procedures.

6-Nov-2002
NEW INFO: First And Last Weekdays Of A Month
New formulas for returning the first and last weekdays of a month, given a date.

2-Nov-2002
NEW PAGE: End User License Agreement Questions
The End User License Agreement (EULA) is the contract between you and Microsoft that governs how you may use Office. This page answer a number of frequent questions about your EULA.

2-Nov-2002
NEW INFO: More On Sorting Worksheets
New sections on this page illustrate how to sort worksheets in custom order, and how to group sheets by tab color (Excel 2002 and later only).

27-Oct-2002
NEW PAGE: SortByColor
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.
 
15-October-2002
NEW PAGE: RowLiner
This page describes the RowLiner add-in, which allows you to display row and column lines on a worksheet.

12-October-2002
NEW PAGE: Recycle Bin
This page describes the VBA code to send a file to the recycle bin.

30-August-2002
NEW PAGE: 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.

28-July-2002
NEW PAGE: Default Property Of A Class
This page describes how you can specify a property of a class to be the default property -- that is, the property that the compiler uses if a property is not specified in code. When you have specified a property as the default, you can write code like MyObject = 123 rather than MyObject.Value = 123.

24-June-2002
NEW DOWNLOAD: Merging Maps In MapPoint 2002 (zip file)
This isn't related to Excel, but might be useful to users of MapPoint 2002. These modules can be executed in any application that executes VBA. It will merge the routes from any number of MapPoint maps in to a single "master" map.  The VBA is fully documented, and self explanatory.  This project started when I took an 8 day driving trip through New Mexico and Colorado.  I had 8 individual maps, one for each day, and wanted to merge them in to a single "master map". MapPoint doesn't support this directly, so I wrote VBA code to do it myself.  MapPoint doesn't support VBA, so this code must be run in any "host" application that supports VBA (such as Excel or Word).
I would like to thank Eric Frost and Gilles Kohl (MapPoint MVPs) for support code and guidance.  If you are interested in MapPoint, take a visit to MP2KMag - The Magazine For MapPoint.

6-March-2002
NEW PAGE: 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.

31-Dec-2001
NEW INFO: Document Properties From Closed Workbooks
This new section on the Document Properties page describes how to read properties such as Author, Company, and so on from files that are not open.  This technique works for any OLE Structured Storage file, such as Word documents, PowerPoint presentations, and so on, not just Excel workbooks.

6-Dec-2001
NEW INFO: Closest Match Lookups
This new section on the Lookups page describes a technique using array to find closest matches in a list, even when the list is not in sorted order.

3-Dec-2001
NEW INFO: Arbitrary Lookups
This new section on the Lookups page describes a technique using array formulas to look up data corresponding an arbitrary occurrence of a value in a list. Using this technique, you are not subject to the limitation that VLOOKUP imposes by always looking up the first matching value.

6-October-2001
NEW DOWNLOAD:  Clock.Zip
This download illustrates how to create and manipulate Shape objects on a worksheet to create an analog clock that ticks every second.

13-Sept-2001
NEW PAGE: Lookup Techniques
This page describes some formulas for looking up data when VLOOKUP won't work.

2-July-2001
NEW DOWNLOAD:Shortcut Keys For The VBA Editor
This zip file (6 KB) contains a workbook which lists all of the keyboard shortcuts available in the VBA Editor. I think it is complete.  If you find that I've missed something, send me an email. For a list of keyboard shortcuts within Excel, download Excel Keyboard Shortcuts.

10-June-2001
NEW PAGE: Excel Versions
This page describes some of the differences between the various versions of Microsoft Excel.

9-May-2001
NEW INFO: Copying Modules Between VBA Projects 
A new section in the Programming To The VBE page describes how to programmatically copy modules (classes, userforms, and classes) between VBA Projects.

27-April-2001
NEW PAGE: 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.

15-April-2001
NEW DOWNLOAD: Sample COM Add-In For Office 2000 Developer 
This download has a sample COM Add-In written for Excel in Office 2000 Developer Edition.  It illustrates the basic programming for a COM Add-In, including how to write menu controls, how to handle modeless forms and how to make functions in the COM Add-In available to worksheet cells (via VBA code).  The actual download doesn't really do anything, so it will be of interested only to those who want to write COM Add-Ins.  You must have the Developer Edition of Office 2000 to view the code.

7-April-2001
NEW PAGE: 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.

23-March-2001
NEW DOWNLOAD: Symbolizer.DLL
This COM Add-In  will display a form that allows you to view and insert special symbols such as currency symbols into your workbook.  It works much like the "Insert Symbol" tool in Microsoft Word. (16KB).  This will work only in Excel 2000 or XP.  It will not work in any other version of Excel. 

2-February-2001
NEW INFO: Exporting And Deleting All VBA In A Project
Two new procedures have been added to the Programming To The VBE page.  These procedures will export of existing VBA code to text files, and will delete all VBA code in a project. 

21-January-2001
NEW PAGE:  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.

20-January-2001
NEW PAGE:  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.

1-December-2000
NEW PAGE:   Using Variables (Properly) In VBA   
This page describes the "do's and don'ts" of declaring variables and objects in VBA code, in order to make your code as efficient and solid as possible.

30-June-2000
NEW GAME:    X-Ray! For Excel
I wrote this game several months ago.  It is similar to a game called "Black Box" that I used to play in Junior High.  You try to find targets by shooting paths into a grid, and watching whether those paths hit the targets, exit out of the grid, or are reflected back to their original cell.  Complete details are in the workbook file. 

25-June-2000
NEW PAGE:    Additional Resources For Excel Users
This page describes additional resources where Excel users and VBA programmers can find more detailed technical information. 

14-June-2000
NEW DOWNLOAD: Compare.XLA
This add-in, written by Myrna Larson and Bill Manville, allows you to compare the contents of two workbooks. 

13-May-2000
NEW PAGE: 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.

7-May-2000
NEW PAGE: Progressive Pricing Formulas In Excel
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". 
 

15-Mar-2000 NEW PAGE: 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. 

25-Feb-2000
NEW PAGE:  Color Banding With Conditional Formatting  
You can use the Conditional Formatting tool in Excel97 and 2000 to make your worksheets look like accounting ledgers or computer "green bar" paper, with alternating bands of colors.   By using Conditional Formatting rather than manually formatting the cells, the color bars will remain intact after you sort a worksheet range.

14-Feb-2000
NEW PAGE:  Week Numbers  Some business applications use week numbers to categorize dates.  For example, a business may report sales amounts by week, and identify each period as "9912", representing the 12th week of 1999.  While this may be convenient in some applications, you need to be careful when using week numbers.   When does week 1 start? On the first day of the year? As the first full 7 day week of the year? The first week having more than 3 days?  Moreover, what day does a week begin on?  Sunday or Monday?   This page describes both worksheet formulas and VBA functions for working with week numbers.

07-Feb-2000
NEW PAGE:  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. 

04-Feb-2000
REORGANIZED PAGES:  The Dates And Times pages are the most popular pages on the web site.  Accordingly, I've reorganized the Date And Time information.  The Dates And Times page is still the central source, but it has many more links and a mini table of contents, specifically for date and time information. 

29-Jan-2000
GAMES!:  Microsoft MVP David Hager has made two Excel-based games available to the masses.  See the Games page for more info! 

23-Jan-2000
NEW FORMAT:  Another formatting change for the site.  This one will take weeks (months)  to take place, as each page is changed one by one.  Until it is complete, you'll see some color and format mismatches from one page to the next.  Please be patient.

15-Jan-2000
NEW PAGE:  A new page called  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.   

15-Jan-2000
NEW DOWNLOAD:  A new downloadable file (10 KB zipped) that has all the formulas and data used on the  Distributed Dates page.

15-Jan-2000
NEW DOWNLOAD:  A new downloadable file (8 KB zipped) that has all the formulas and data used on the Working With Overtime Hours page.  

15-Jan-2000
NEW PAGE:  A new Interactive page illustrating the formulas and data on the Working With Overtime Hours page.  Web-based interactive pages use the Spreadsheet Office Web Component, and can only be used by people with Excel2000.  If you don't have Excel2000 and IE4.01 (or above), you won't be able to use this page. 

01-Jan-2000
NEW DOWNLOAD:  A new downloadable file (16KB zipped) that has all the formulas and data used on the Ranking Data In Lists page. 

01-Jan-2000
NEW PAGE:  A new page called 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. 

01-Jan-2000
UPDATE:  Nothing new, but no problems!  The Y2K event was a non-event.  Have you had problems with Y2K issues in Excel?  Let me know!

29-Dec-1999
UPDATE:  The Search facility is working again.  I'm using a third party indexing service, so you'll have to put up with a banner ad at the top and bottom of the results page, but you can live with that, can't you?  

24-Dec-1999
NEW PAGE:  Who are the readers?  The Site Stats page tells you about who you guys and gals are, and where you come from.

10-Dec-1999
NEW PAGE:  A new Working With Overtime Hours In Timesheets   page describing 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.

29-Nov-1999
NEW PAGE:  A new page called Zooming And Centering On Cells. You can use the Zoom and Center procedures to make selected data more visible to users.  The ZoomToRange procedure will zoom the screen so that a specified range of cells fills the entire screen.  The CenterOnCell procedure will scroll the screen to that a specified range or cell is at the center of the screen. 

21-Nov-1999
NEW PAGE:  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.  

21-Nov-1999
NEW INFO:  A new formula on the Dates And Times page to return the date of the Nth Day-Of-Week of a given year.  For example, the formula will return 11-April-2001 for the 15th Wednesday of the year 2001.
17-Nov-1999

NEW PAGE:  More Pictures of your favorite MVPs from the 1999 MVP Summit.

31-Oct-1999
NEW PAGE:  Pictures of your favorite MVPs from the 1999 MVP Summit in Seattle, September, 1999.

30-Oct-1999
NEW TOPIC:  A new section on the Working With Lists page to return the most or least frequently occurring value in a range.

30-Oct-1999
NEW TOPIC:  A new section on the Dates And Times page describes a formula to determine the number of working days and hours between two dates and times, counting only hours during the work day.  For example, suppose a project starts on Monday, 25-October-99 at 13:00 and ends on Thursday, 28-Oct-99 at 15:00, and that your normal working hours are 9:00 to 17:00.  In this case, there are 3 days and 2 hours between the start and end times (2 full days, Tuesday and Wednesday, plus 4 hours on Monday and 6 hours on Thursday, for 2 days and 10 hours, or, since there are 8 hours in a workday, 3 days and 2 hours).  See Working Days And Hours Between Two Date-Times  for details.

20-Oct-1999
NEW PAGE:  The Referencing Worksheets From Formulas  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.   

16-Oct-1999
NEW PAGE:  The Menus In Excel page is an introduction to creating menu items in Excel, both manually and with VBA code. 

10-Oct-1999
NEW PAGE:  Adding Menu Items To VBE (Visual Basic Editor) 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.  You can also download a workbook containing these procedures.

26-Sept-1999
NEW PAGE:  A new Parsing Telephone Numbers  page.  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. 

18-Sept-1999
NEW PAGE:  A new page called UnSelecting Cells Or Areas describes the two VBA procedures that you can use to remove a single cell or an area from a non-contiguous selection.  Suppose you have selected A1, A3, A5, and A7, and you want to remove A5 from the selection.  These procedure can do this for you.

18-Sept-1999
NEW INFO:  Several new VBA procedures were added to the Named Ranges page.  

5-Sept-1999
NEW PAGE:  A new page called Working With Lists 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.   

1-Sept-1999
NEW PAGE:  I've added a new page on Conditional Formatting that describes how to use this powerful tool, and how to use formulas with it. 

30-Aug-1999
NEW PAGE:  The Direct Connection To MSNEWS page describes why it is advantageous to connect directly to the MSNEWS server when reading and posting to the MS Excel newsgroups.  This page was written by Len Meads and appears on my site with his kind permission. 

29-Aug-1999
NEW INFO:  I added support for the NETWORKDAYS function in the formulas on the Date Intervals page. 

27-Aug-1999
NEW PAGE:  First And Last Names 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. 

24-Aug-1999
NEW PAGE:  The first of the Interactive pages has been added.  If you are using Internet Explorer version 4 or later, and you have an Office 2000 program installed on you machine, you can see the Date Interval formulas in action.  Enter your dates into the spreadsheet, and see the result right in your browser.  From this page, to restore the default data and formulas, hold down your CTRL and SHIFT keys, and click the REFRESH button on your browser. 

24-Aug-1999
NEW PAGE:  More date related stuff.  This time, Julian Dates, which has formulas for working with Julian Dates, including converting to and from standard Excel dates, and adding days to Julian dates.  Also includes VBA functions for working with Julian dates. 

23-Aug-1999
NEW PAGE:  A new page called Status Bar, which has a few quick tips about working with the Excel status bar from VBA, including how to clear your message from it after a given amount of time (a nice way to get rid of "We're Done" message after a few seconds).

22-Aug-1999
NEW PAGE:  A new page called Date Intervals, which has several formulas regarding dates and date intervals.  Specifically, it address the questions of whether a date falls within an interval, the number of days that two intervals overlap, and the how many days are in one interval, excluding those days in another interval.   One frequent responder to the newsgroups once wrote that I was "obsessed" with Excel dates and times.  I guess that I can't disagree.  

25-July-1999
NEW PAGE:  A new page called Programming To The VBE describes some of the objects and their methods and properties for working directly with modules and VBA code via VBA code.  This pages describes some methods to add or delete modules or procedures in modules.  

17-July-1999
NEW PAGE:  A new page called Changing Case describes the VBA procedures to change a range of cells to upper or lower case, and how to change the case automatically as the data is entered.
NEW TOPIC:  A new topic called "Replacing Duplicate Values" has been added to the Duplicates page. This VBA procedure allows you to replace duplicate entries in a grouped list with a blank value, or any other value.  It does not delete the actual cells, only replaces their contents. 
NEW PAGE:  The INDIRECT function is more useful than the Help files lets on. This page describes how to use this under-appreciated function.

16-July-1999
NEW PAGE:  I've added a new page called Code Modules And Code Names, which describes the different types of code modules in Excel VBA, and where you should and should not put VBA code.  The CodeName property and its use (and misuse) is also discussed. 

13-July-1999
NEW SECTION:  I'm adding a whole new Interactive section that will allow you see some of the formulas on the web site in action. You can enter in your own data, and watch the formulas do their magic.  All inside your browser.  These pages require Excel2000 and Internet Explorer Version 4 or later (because they use the Spreadsheet control, which must exist on your computer).  I don't know if they'll work with Netscape or AOL browsers.  Additional pages will be added to the Interactive section over time.  

13-July-1999
NEW PAGE:   The =EXCEL web site now has a Search capability.  From this page, you can search the entire site for a word or phrase.  Remember, though, that this is a text search, so searching for a word like "date" will bring up lots of matches.  You may want to consult the Topic Index page before going for a search. 

9-July-1999
NEW FORMAT:   A few new formulas and procedures in the Date And Time page and the Deleting Rows page.

9-July-1999
NEW FORMAT:   I've dumped the "frames" layout, and gone back to a simple collection of pages with shared borders.  This makes it easier to go directly to specific page.  

25-Jun-1999
NEW SITE:   The =EXCEL site now has its own home on the internet!  www.cpearson.com or go directly to the Excel stuff at www.cpearson.com/excel 

9-Jun-1999
NEW PAGE:   I've added a new page about Fractional Arithmetic, which is what you want to use when working with values, like feet and inches, that aren't in standard decimal fractions.

8-Jun-1999
NEW INFO:   More example code added to the Document Properties page.  Also, new macro for Importing Text Files to prompt the user for the file name and delimiter character.  Changed some script stuff to fix problems with Netscrape browsers.   Lots of minor things updated.

7-Jun-1999
NEW DOWNLOAD:   I've added a new Excel97 workbook, StockWeb, that illustrates using Excel to download stock price information from the Web.

28-May-1999
NEW PAGE:   I've added a new page with some information about getting support for Excel.  Experienced Excel users already know all this (newsgroups, the KB, etc), but new users may find it useful.  Also, some minor housekeeping things (new default font).

30-Mar-1999
NEW PAGE:   Several have asked, and now it is available to the masses.  Just who the hell is Chip?

28-Mar-1999
NEW PAGE:   I've added a new page that describes the VBA procedures for working with the Built-In and Custom document property collections.  Also, some minor housekeeping things.

21-Jan-1999
NEW INFO:   I've greatly simplified the formulas on the Duplicates page.

11-Jan-1999
NEW LINKS:   I've added several new links to the Links page. Nothing relating to Excel, just to the absolutely best sites in the universe.

8-Jan-1999
FORMAT CHANGES:   I've gotten rid of the last of the graphics and Java classes.  Sure, they looked cute, but they made the pages load slowly.   Almost everything is now strictly text-only. You want cute? Go to the Disney site.

30-Dec-1998
NEW PAGE:   A new page on Date And Time Entry, which describes the VBA procedures to allow you to enter dates or times without the separators.  For example, you can enter 122598 to get the valid date 25-Dec-1998, or 1234 to get the valid time 12:34:00.  Works only  in Excel97 and above.

30-Dec-1998
NEW PAGE:   I've added a page that describes how to use Data Validation (Excel97 only) to prevent the user from entering duplicate entries in a range of cells.

30-Dec-1998
NEW INFO:   I've added a VBA procedure to export the selected cells to a text file. The cells values may be delimited by any character.  This procedure is in the Export section of the Importing Text Files page.

15-Nov-1998
NEW PAGE:  An Introduction To Pivot Tables.   This page was written by Harald Staff, our Norwegian correspondent.

15-Nov-1998
NEW PAGE:  I've added a page about Headers And Footers, which describes the VBA code to create customized headers and footers for your worksheets.

2-Nov-1998
NEW PAGE:  I've added a page called Importing Text Files, that describes the VBA code necessary to import a text file directly into the active worksheet, beginning at the current cursor position.   The values in the imported file may be delimited by any character.

2-Nov-1998
NEW PAGE:  A rather extensive page about Rounding Errors In Excel97, which describes the two most common types of perceived errors in Excel:  displayed versus actual values, and approximation of rational numbers in IEEE double precision floating point variables.  The IEEE standard is described at the bit level.

13-Oct-1998
NEW FILES: Two new files added to Downloads page. 1) EventSeq.xls, which demonstrates the sequence all of the events in Excel97.   2) Reader.xls, which will read your cell data (numeric and dates only) back to you through your sound card.  Includes all sound files.  All sound files must be stored in the same directory folder as the workbook.   Opening this workbook will create a "Reader" entry on your "Tools" menu.  Yes, that's my voice reading the data.

13-Oct-1998
NEW FORMAT: Navigation Hover-buttons at the bottom of each page have been removed.  (They caused some problems for some Netscape users.)  Replaced with clickable image map.  The pages load alot quicker, too!

11-Oct-1998
NEW FORMAT: I've change the internal organization of the site.  

11-Oct-1998
NEW PAGE:  I've written a rather substantial page on Event Procedures In Excel97. 

11-Oct-1998
NEW PAGE: I've added a new page called "Articles" from which you can download Word97 and RTF formats documents of some of my larger writings.

25-July-1998
NEW INFO:  Additional methods for extracting words from strings, with Formulas.

20-July-1998
NEW INFO:  1) Logical operations (OR, XOR, NAND) with array formulas.  2) Using dates in Range.Find method.

30-Jun-1998
NEW FORMAT:  Changed site layout for Frames. Still working out a few bugs.  Be patient.

20-Jun-1998
NEW PAGE: Added Nested IFs page, about how to overcome the  limit on nested function in formulas.

This page last modified: 3-September-2011