|FastExcel Sample Problem & Manual||Name Manager||RangeCalc||Reversioning Add-In Loader|
|Calculation Tracer||Interlinked Sheets||UDF Areas Bug||High Resolution Timers|
This page contains the FastExcel example problems and Excel Add-In
files I have written for Excel 97, Excel 2000 and Excel 2002 . The
Add-Ins are contained in ZIP files to simplify the download.
Variant Read/Write Benchmark timing
This file shows you the time taken to read and write cells to or from a variant variable.
Revised 12 Sep 2004. These workbooks do NOT require FastExcel.
The FastExcel samples download is approximately 1.5MB (approximately 8 minutes at 28K). The package is a ZIP file containing four Excel workbooks and a PDF manual:
FastExcel V4 PDF User Manuals:
Name Manager V4.5 for Excel 2007- 2019 build 688
This version of Name Manager for Windows has been developed for Excel 2007 and later versions.
Name Manager was created in collaboration with Excel MVP Jan Karel Pieterse.
An enhanced version of Name Manager for Windows, including a Dynamic Range Wizard, Find and Replace and online help, is integrated into Version 4 of FastExcel.
If you are in need of a utility to manage defined names in your workbooks, this one is a must-have.
Counting Functions: fxlCountFuncs.zip :run-time functions for the FastExcel Dynamic Range Wizard
This addin contains a set of functions designed to be used in Dynamic Range Names. The addin can be used as a runtime library for workbooks whose dynamic range names have been geberated by the FastExcel Dynamic Range Wizard. The addin works with Excel 97, Excel2000, Excel 2002 and Excel 2003.
Although it is possible to duplicate some of these functions using array formulae, these functions calculate significantly faster than the equivalent array formulae.
The password for the XLA file is dm.
The Functions in the addin are registered into category 5 of the excel Function wizard (Lookup and Reference).
These functions give you the ability to find the number of rows or columns to the last empty cell, the next empty cell, or the last cell in the used range:
You can use these functions in Dynamic Range names and for the number of rows and columns arguments in OFFSET.
COUNTROWS and COUNTCONTIGROWS can count rows in either a single column
or multiple adjacent columns.
All these functions are volatile functions.
Changing cell visibility using Automatic or Advanced filter will
trigger a recalculation of COUNTROWS, COUNTCOLS, COUNTCONTIGROWS and
Full descriptions of these functions are givrn in the downloadable FastExcel Manual (PDF).
RangeCalc Add-In: RangeCalc.zip: one of the FastExcel command buttons
This addin adds a Calculate Range button to Excel. This button calculates the currently selected cells, and times the calculation using the MicroTimer high-resolution timer. RangeCalc automatically expands the current selection to include all the cells of any multi-cell array formulae that partly intersect the selected range.
The addin works with Excel 97 through Excel 2010 (both 32 and 64 bit versions), and bypasses many of the quirks of Range.Calculate such as the Iteration and Array Formulae problems in Excel2002/3/7.
The password for this addin is dm.
Revised 18 November 2014:
Revised 11 April 2006:
The Add-In Loader is designed to solve some of the problems of maintaining and loading Add-Ins on a network:
The Add-In Loader helps the User:
The Zip file contains:
You can customise the Add-In Loader code as required. The XLA is password protected to prevent accidental alteration.
To view the VBA code in the Addin unprotect the XLA using a password of dm
Version 2 Changes:
Version 1 Revisions:
25/May/2001: fix for chDrive and UNC Path
Calculation Tracer: CalcTrace.ZIP
This Add-In file contains User Functions and a macro designed
to enable you to explore Excel's calculation sequence and dependency
The functions work by incrementing and returning a counter each time they are calculated:
The functions may be entered from the User section of the Function Wizard, or entered directly into a worksheet formula.
The XLA password is dm
Memory Limit example of interlinked sheets: MakeInterlinkedSheets.zip (11KB)
Updated 9 Jan 2004 to allow generating more than 255 sheets and for comments on excel 2003.
This zip file contains a workbook with a VBA macro that generates a variable number of interlinked worksheets. It demonstrates an extreme case of the memory used by interlinked sheets, some of Excel's memory limits, and that it is possible to create a workbook that you cannot re-open!
Use thuis example file to test Excels's memory limits by version.
UDF Areas Bug Example and Bypass: AreasBugBypass2.zip
Howard Kaikow http://www.standards.com, discovered a bug that can occur in Excel's processing of multi-area input arguments for a UDF, and can result in the UDF calculating incorrectly. The bug exists in Excel 97, Excel 2000, Excel 2002 and partially in Excel 2003. It has been fixed in Excel 2007. Some of Excel's own functions also have this problem (but not SUM).
The bug occurs when a UDF has a multi-area input argument that refers to the sheet containing the function formula, and the function is recalculated by Excel when a different sheet is the activeSheet. In this case the function will incorrectly use data from the activesheet.
It is possible to bypass the bug at the expense of making the UDF Volatile, modifying it and adding a dummy worksheet containing a worksheet calculate event.
The downloadable zip file contains two workbooks:
If you write a UDF which processes multi-area arguments you should implement a similar bypass.
With Excel 2003 the bug has been fixed for multi-area references that explicitly reference other sheets ((Sheet2!$A$1:$A$3),(Sheet3!$B$1:$B$3)) but not for implicit references (($A$1:$A$3),($B$1:$B$3)).
This Add-In contains VBA function wrappers for Windows API high resolution
timers. These functions are useful when you need greater accuracy
than is available from the VBA Timer (which turns out to be most of
the time). VBA Timer is also quite slow to execute.
Mostly I use the code in these functions as timers within VBA, but
you can also use them as UDF's.
The XLA password is dm
The functions measure elapsed time rather than processor time.
All of the files available for download are provided as-is, without any warranty or support. All use of these files is at your own risk. The files have been tested with Excel versions 97, 2000, 2002, 2003, 2007 and 2010, but they may not work on your machine. You may use and adapt these utilities for your own projects. Please let me know if you want to use the code in commercial applications. You may not include these files in any shareware or freeware catalogues, books, articles or periodicals without the written permission of the author.