Excel Expert E-Letter
Excel Help Resources
Power Utility Pak for Excel
About the Excel Experts E-Letter
The Excel Experts E-Letter (or EEE) is a newsletter produced by David
Hager. EEE is filled with useful tips and tricks for Excel. Most of the material in geared
towards the more advanced user.
Note: The EEE newsletter is no longer distributed via email. Any
future issues will appear here. I'm pleased that David has agreed to make the past issues available from The
EEE Back Issues
Following are links to past issues of EEE. The descriptions provided below are
highlights. Each issue contains additional information.
- Issue No. 20 (July 8, 2001): Formula to
count unique items in filtered column. Formula to mimic NETWORKDAYS. VBA
code to disable copy/cut/paste. VBA code to change the Excel icon. VBA
code to substitute range address for range names in selected formulas.
- Issue No. 19 (June 1, 2000): Getting
unique entries in an n*m array. Disable user changes in Print Preview.
Using an array as a loop counter index. VBA code to insert code into
modules. Overview of class modules.
- Issue No. 18 (April 1, 2000): Pointer
to Excel 2000 SR-1 fixes. An array formula to sum diagonally. VBA code
to support >2 autofilter criteria. VBA code to compare the contents
of two worksheets.
- Issue No. 17 (March 1, 2000): Links to
information on ExecuteExcel4Macro method. An array formula (and VBA
function) to combine the functionality of XIRR and MIRR. Code to change
the font size of al cell comments. Code to open Excel Data Form in
"new record" mode. Use class modules from another
- Issue No. 16 (January 31, 2000): Formula to determine if the items contained in Range1 are
contained in Range2. Formula to identify Fibonacci numbers. VBA code to
read a range from close file and insert it into a worksheet. VBA code to
count the number of Excel instances. VBA code to create a list of unique
items from a range. VBA code to place a shortcut on the desktop. How to
duplicate your VBE setup.
- Issue No. 15 (December 22, 1999): Formula
to reverse sequence of cells. Formula to identify prime numbers. VBA
code to read sheet names in a closed workbook. VBA code to print a
multisheet range on a single sheet of paper. Add an event procedure to a
control inserted at runtime. VBA code to determine all custom number
formats in a workbook.
- Issue No. 14 (November 1, 1999): VBA
function to determine if a cell uses conditional formatting. Code to
de-link chart data. Shell and wait procedure. Remove all VBA code from a
workbook. Data normalization. Use a cell comment to store the
previous value of a cell.
- Issue No. 13 (October 15, 1999):
Special edition: Excel Magic Consolidator. A technique that allows the user to write formulas on the consolidation worksheet (called
"Summary" by default) that act on the same cell from every worksheet in the
workbook. The results of the formulas change dynamically as sheets are
added/deleted from the workbook. There is no VBA or XLM macro code used in this solution. All of the work is done by named formulas.
- Issue No. 12 (October 1, 1999): VBA
code to fill formulas across selected worksheets. VBA code to an error
checking to formulas. Using names defined in a different workbook.
- Issue No. 11 (September 15, 1999):
Links to password-cracking software. Formulas: case-sensitive MATCH,
rounding to significant digits. VBA: select last used cell, get the
dimension order of an array, get data using ADO, display the files shown
by Edit-Links, display the Chart Wizard dialog, adjust row height of
merged cell, get range names that include the active cell, search all
- Issue No. 10 (August 1, 1999): Using
INDIRECT to sum a 3D range. Solve simultaneous equations. VBA code to
disable Excel's Close button. VBA code to delete values (but not
formulas) from a range. Using the Worksheet Calculate event to allow
some types of changes, but disallow others. Creating a COM add-in in
Excel 2000 and VB6.
- Issue No. 9 (July 15, 1999): Passing arguments to named
formulas. VBA code to find ###### values in cells. VBA code to look up the Windows 95
serial number. VBA code to remove tabs and carriage returns from cells. VBA code to turn
off the 'Break on Unhandled Errors in Class Module'. Laurent Longre's method to create new
user-defined function categories. VBA code to read a cell from a closed workbook.
Issue No. 8 (July 1, 1999): Special edition on conditional
formatting. This self-extracting EXE file file (117K) contains a Word document and an
Excel workbook that provides lots of useful information about conditional formatting for
Excel 97 and later.
- Issue No.7 (June 15, 1999): Pointers to Microsoft articles
regarding Excel 2000 problems. A VBA function to sort characters in a string. VBA code for
data encryption. VBA code to remove unused custom number formats. Information about
Microsoft's ActiveX Spreadsheet component (Excel 2000).
- Issue No. 6 (June 1, 1999): Access DejaNews posts from Excel.
VBA code to create a list of all number formats in use. VBA code to remove tabs and
carriage returns from cells. VBA code to create custom tips for each data point in a
- Issue No. 5 (May 15, 1999): Includes: Code to prevent Save As,
high performance timer, find string across sheets, prevent update links message, array
formula to reverse digits in a number, invert the values in a range.
- Issue No. 4 (April 30, 1999): This issue is devoted to
techniques for working with unique items from a data set. Examples include array formulas
and VBA code.
- Issue No. 3 (April 15, 1999): This issue is devoted to
"3D" functionality in Excel. Functions that work across multiple worksheets.
Conditional formatting based on a value in a different sheet. Formulas that adjust
automatically when filled across worksheets. VBA functions that work across worksheets.
- Issue No. 2 (April 1, 1999): Overcoming Excel's limit of seven
nested functions. Changing a link in a formula without changing the formula. VBA code for
blinking text in cells. Interpolating using lookup tables. VBA code to read Excel's
command line. using the ID property of a Range (Excel 2000).
- Issue No. 1 (March 16, 1999) Creating a global range name. A
formula to create an array of filtered items in a column. A VBA function to determine if a
directory is empty or doesn't exit. VBA code to remove a directory and all of its files
and subdirectories. VBA code to determine the module that contains a specific procedure.