Excel Downloads

Excel Downloads

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.
I hope you find them useful: email comments to Charles Williams are welcome.

Variant Read/Write Benchmark timing

This file shows you the time taken to read and write cells to or from a variant variable.

Note that the Google Desktop COM addin slows down write time significantly (factor of 3 for a single cell).
I recommend disabling this addin.

VariantBenchMark.zip

FastExcel Sample problems and Output: FXLSampleProblemV2.zip .

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:

• FXLSamplProblemV2.pdf: The FastExcel Version 2 Sample problem Guide.
• FXLSampV2.xls: contains the output from a FastExcel analysis of these workbooks, and an explanation of the analysis and the optimisations done on the After.xls and NoNamesAfter.xls workbook.
• Before.xls: the unoptimised workbook, calculates in 22 seconds on a 1200 MHZ AMD Athlon
• After.xls: the optimised workbook with dynamic range names, calculates in 0.17 seconds at 1200 MHZ
• NoNamesAfter.xls: the optimised workbook with the range names removed, calculates in 0.03 seconds at 1200 MHZ

FastExcel User Manual: FxlV2User.zip

This downloadable zip file contains the PDF version of the FastExcel Version 2.3 User manual (1.40MB). (Build 462 Version 2.3)

Note: The PDF version of the Optimise Calculations Manual is also available to purchasers of FastExcel.

NameManager V4.2 (23 January 2012, build 632, 2.26MB)

This is Version 4.2 of Name Manager. Please send feedback and problem reports to me

Name Manager V4.2 2007 build 632

This version of Name Manager has been developed for Excel 2007 and 2010.

Name Manager was created in collaboration with Excel MVP Jan Karel Pieterse.

NameManager.zip v2.3 for Mac (31 July 2003, 288KB Build 410)

Contains: Setup Name Manager.xls, Name Manager.xla and Name Manager Manual 23.doc,

Thanks to Matthew Henson who ensured this version works on Mac Excel.

An enhanced version of Name Manager, including a Dynamic Range Wizard, Find and Replace and online help, is integrated into Version 2 of FastExcel.

Contains: Setup Name Manager.xls, Name Manager.xla and Name Manager Manual 42.doc,

If you are in need of a utility to manage defined names in your workbooks, this one is a must-have.

• List all names in your active workbook.
• Easily work with long names and long refers-to formulae.
• Filter names using 13 filters, e.g. "With external references", "With errors", hidden, visible.
• Combine filters using And/Or.
• Show just names that contain a substring.
• Show just names unused in worksheet cells.
• Work with single or multiple selected names.
• Edit Local, Global, Relative and Absolute names.
• Edit them in a simple dialog or make a list, edit the list and update all names in one go.
• Delete, hide, unhide, localise, globalise selected names with a single mouse click.
• Evaluate, Analyse and GoTo names.
• A1/R1C1 toggle.
• Works with localised Excel versions (English and European).
• Screens and error messages in 8 languages.
• Modeless multi-workbook working with Excel 2000, 2002, 2003, 2007 and 2010.
• Supports Excel 97

Changes in version 4.2 build 625

• Automatic or manual selection of Language based on Excel User Interface Language
• Miscellaneous bug fixes

Changes in version 4.2 build 620

• Supports 64-bit Excel 2010
• Icon & button for Rename
• Icon and Button for GoTo
• Revised Icons
• Double-click a name invokes GoTo rather than Rename
• GoTo has been removed from Options
• Workbook Name shows in NM caption, dynamically refreshes when a different workbook is activated
• Add Name allows you to add a name that refers to a different (open) workbook
• scroll bar in options removed
• bug fixed for Highlight a single Nam
• GoTo now works for 3D names, Relative Names, Names referring to open external workbooks
• Evaluate works for names referring to open external workbooks
• Minor EUI changes
• Spanish language available
  

Changes in version 4.1 build 615

• Fixed bug creating new local name on the active sheet.
• Fixed bugs enabling Name Manager to run with Excel 2010 Technical Preview
• Fixed bug in Refersto Active Sheet filter

Changes in version 4.1 build 612

• fixed Compile error with Excel97
• Minor bug fixes
• If filtered on hidden names, new names are hidden too
• Hidden names now stay hidden after editing
• Added creating local names for all sheets.
• Fixed bug editing refersto
  

Changes in version 4.1 build 606

• Supports Finnish language

Changes in version 4.1 build 603

• Hidden names now remain hidden when editing
• When filtering hidden names new names are hidden by default
• Fix in names where used for names resembling column references

Changes in version 4.1 Build 601

• Fixes for Excel 2007 and multi-area Names

Changes in version 4.1 Build 594

Removed the use of the Treeview control which was causing problems on some systems.

Changes in Version 4.1 Build 592

We've added a tiny but extremely handy toolbar to the VBE:

Selecting a name will paste that name at the current insertion point in your code!

Changes in Version 4.0 Build 580

• more fixes for renaming local names!
• fix for LoadPicture error on some systems

Changes in Version 4.0 Build 579

• Fixes for Rename Names, including renaming local names

Changes in Version 4.0 Build 573

Changes in Version 4.0 Build 565

• Revised End-User Interface
• Rename a name. Name Manager now includes a rename option, which will enable you to change the name of a name and have all objects that use that name update too.
• View where name is in use. A treeview shows all locations where a name is used, including VBA code.
• Choice between icons on commandbuttons or text labels
• GoTo option shows the current region for the name with the named range selected

Fixes in build 532: Editing local names.

Changes in Version 3.2

• Resizable Form (including minimise/maximise buttons)
• Movable splits between Names and Refersto.
• Sorting of names and RefersTo formulas,
• New add name dialog.
• New filter: active sheet.
• Shortcut key (default control-shift-n) to start Name Manager.

Changes in Version 3.1

• The functionality to edit names that refer to
  large multiple areas.
• Miscellaneous bugs in Pickup
• Fix for Evaluate with duplicate Global/Local names
• Fix for deleted Global Name when Localise fails (Build 478)

Changes in Version 3.0 of Name Manager.

• Improved user interface with icon buttons for most commands.
• International version, screens and error messages in national languages:
  • English
  • Danish
  • German
  • French
  • Dutch
  • Brazilian Portugese
  • Swedish
• Analyse Command, allows partial evaluation of names etc.
• Evaluate now resolves names containing XLM functions and external references.
• Defaults to Multi mode, with option to show Refersto
• Choice of And/Or for type filter
• Highlight ranges containing names
• Clear Highlights
• Improved editing of relative names
• Language changer
• Options listbox shows options for:
  • Confirm Changes
  • R1C1 Notation
  • GoTo Selected Names
  • Show RefersTo
  • Show Excel System Names
  • Sort Names (sort local names together by sheet)

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:

• COUNTROWS and COUNTCOLS count the number of rows or columns from a referenced cell to the last visible non-empty cell in the row or column.
• COUNTCONTIGROWS and COUNTCONTIGCOLS count the number of rows or columns from a referenced cell to before the next visible empty cell.
• COUNTUSEDROWS and COUNTUSEDCOLS count the number of rows or columns from a referenced cell to 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.
Similarly COUNTCOLS and COUNTCONTIGCOLS can count columns in either a single row or multiple adjacent rows.

All these functions are volatile functions.

Changing cell visibility using Automatic or Advanced filter will trigger a recalculation of COUNTROWS, COUNTCOLS, COUNTCONTIGROWS and COUNTCONTIGCOLS.
Changing cell visibility using Hide, Unhide or by setting width or height to zero will NOT trigger a recalculation of COUNTROWS, COUNTCOLS, COUNTCONTIGROWS and COUNTCONTIGCOLS.

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.

Add-In Loader Version 2: AddloaderV2.ZIP

Revised 11 April 2006:

• Runs Auto-Open and Auto-Close macros in addins when they are opened or closed.
• Bug fix for opening addins with auto option followed by addins without auto option.

The Add-In Loader is designed to solve some of the problems of maintaining and loading Add-Ins on a network:

Dynamic reversioning of add-ins with automatic link updating.

Control from a central point which add-ins will be automatically loaded when Excel starts.

Control from a central point which add-ins will be available for on-demand loading.

Enable updated versions of an add-in to be placed on a server at the same time as previous versions are in use.

Optionally specify and/or change the network path for each add-in.

No Registry changes for load and unload.

The Add-In Loader helps the User:

Improve Excel start-up time by bypassing the loading of add-ins that are only needed intermittently.

Minimize memory use by loading add-ins when needed and easily unloading them when not needed.

Dynamically load the latest version of an add-in, whilst unloading any version which is currently loaded.

Dynamically update any links that point to old versions of loaded add-ins when a workbook is opened or an add-in is loaded.

The Zip file contains:

AddLoaderV2.XLA - the Add-InLoader Add-In.

AddinLoad.txt - example file containing test Add-In Root names.

Add-in Loader V2.Doc - Word document explaining how to install and use the Add-In Loader V2.

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:

Option to specify the network path for add-ins.

Option to automatically load add-ins.

Improved View/Unload command.

Dynamic link updates when a workbook is opened or an add-in is loaded.

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 handling.
Note that interpreting the results and designing conclusive experiments can be complex.

The functions work by incrementing and returning a counter each time they are calculated:

ZeroCalcSeq  - a Macro which sets the counter to zero.

CalcSeqCountSet(theInput)  - Function - Counter is set to theInput.

CalcSeqCountRef(theRange)  - Function - Counter is incremented as a dependency on theRange.

CalcSeqCountVol()  - The function is volatile: - Counter is incremented at each Calculation: used to check the effect of volatile on calculation sequence.

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:

AreasBug2.xls demonstrates the bug.

AreasBugBypass2.xls demonstrates a method of bypassing the bug.

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

High Resolution Timers: Timers.ZIP

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.
Portions of the code in these functions are from Ken Getz and John Green.

Mostly I use the code in these functions as timers within VBA, but you can also use them as UDF's.
To use as a timer, call the function, execute the thing you want to time, call the function again and subtratct the second time from the first.

The XLA password is dm

The functions measure elapsed time rather than processor time.
The two functions are:

Microtimer: returns a Double containing seconds. Resolution about 1 microsecond and takes about 5 microseconds to execute in Win ME at 1200MHZ

Millitimer:   returns a Long containing milliseconds. Resolution about 1 millisecond and takes about 5 microseconds to execute in Win ME at 1200MHZ

Conditions of Use

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.