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 Spreadsheet Page.
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 project.
- 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 sheets).
- 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 chart.
- 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.