Excel Downloads
This page contains the FastExcel example problems and Excel Add-In
files I have written for Excel 2013 nd later versions. 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.
Lambda Explorer for Windows and Mac
Lambda Explorer is a FREE Excel XLAM Addin tool for exploring and debugging Excel Lambda Functions.
It is designed as a companion product for the Advanced Formula Environment addin.
Watch this short video for an introduction to Lambda Explorer
Lambda Explorer Demo
Download FastExcel Lambda Explorer for Windows and Mac.
Variant Read/Write Benchmark timing
This file shows you the time taken to read and write cells to or
from a variant variable.
VariantBenchMark.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
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.
- 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 .
- Supports Excel 2010 through Excel 365
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.
Revised 18 November 2014:
- Handle both XLA and XLAM files
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
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.
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.
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
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.
|
