Whats New in FastExcel V2.4

Whats new in FastExcel Version 2.4

Changes in build 495 include:

• Fix for clean workbook delete shapes with zero height OR zero width
• Fix for cleaning used range by rows, columns or both

Changes in build 494 include:

• Fix for Range.calculate with multi-cell array formulae in some Excel versions
• Fix for Setting Excel Initial Calculation Mode
• Fix for MixMode Sheets with sheetnames containing blanks
• CHM Help file

Changes in build 492 include:

Fix for subscript out of bounds error in 'Are selected names used' button.

Changes in build 491 include:

• Name Manager 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 build 490 include:

• Name Manager Build 612
• Fix for compile error with Excel97
• Option to add Local names to all sheets
• Hidden names stay hidden after editing

Changes in build 489 include:

• Ctrl/Alt/Shift/F8 toggles FastExcel debug mode. For AVLOOKUP, AVLOOKUPS, AMATCH, AMATCHES debug mode gives an error message when a column name cannot be found (the function returns #Ref).
• Fix in AVLOOKUP, AVLOOKUPS, AMATCH, AMATCHES to improve handling of uncalculated cells.
• Fix to Name Manager to show all options in button mode as well as Icon mode.

Changes in build 488 include:

• Fix to expose FastExcel Calculation methods and timer functions (MicroTimer, MilliTimer)to VBA (use Reference to FastExcel)
• Fix to prevent GoBack opening Name Manager if not already open
• Fix for Object Required message if both Name Manager and FastExcel Name manager addins are installed
• Correction of Build Numbers in several places.

Changes in build 487 include:

• Additional fixes in Clean workbook

Changes in V2.4 build 486 include:

• Fix for conditional format UDFs without error handling in Profile Workbook
• Warning message when choosing Active Workbook calculation mode
• Profile workbook memory is optional (FastExcel Options)
• Clean Workbook: option to clean the active sheet or all worksheets
• Fix for cleaning unused range with merged cells

Name manager build 603 changes include:

• Hidden names remain hidden when edited
• When filtering hidden names new names are hidden by default
• Fix for where-used names that resemble column references

Changes in V2.3 include:

Build 484 Fixes:

• Name Manager build 602

Build 483 Fixes:

• Error with Excel 2007 compatibility mode.
• Name Manager build 601

Build 480 Fixes:

• Name Manager 594 - removed the Treeview control that caused compile errors on some systems.
• Installer no longer installs MSCOMCTL.OCX

Build 479 Fixes:

• Error in Optimise Worksheet Calculation Sequence when the default printer is not a valid printer.
• 1004 error in ProfBookMemory with unusual columnwidths

Build 478 Fixes:

• Name Manager 592 - Additional toolbar in the VBE with dropdown box allows you to insert Names into VBA code.

Build 476 Fixes:

• Name manager build 590 - fix for external names filter
• GoBack button recentres Name Manager Form
• Fix for reference to the RefEdit control (compile error in hidden module fxlNameManager)

Build 475 fixes:

• Fix for Profile Workbook with Iteration switched on
• Fix for slow performance of Clean Workbook with systems using Google Desktop Search
• Includes MSCOMCTL.OCX to fix issues on some systems with use of the TreeView control in Name Manager

Build 474 fixes to Name Manager 586:

• Multi-area range names
• Pickup
• Find and Replace Refersto
• Add Names

Build 472 fixes:

• Fixes for Clean Workbook and Dynamic Range Wizard in Excel 2007 (>64K rows)

Build 471 fixes:

• Fix for opening a file by double-clicking it
• Fix for Cleaning Temp Directory in Excel 2007
• Excel 2007 support.

Name Manager Build 582

Build 469 fixes:

• Name Manager Build 580
• Several fixes for renaming names, including renaming Local names

Build 468 fixes:

• Beta-level support for Excel 2007 B2TR
• Name Manager Build 574

Build 467 fixes:

• AVLOOKUP fix for mixed numeric and alphabetic lookup column when looking up alphabetic.
• Name Manager build 569: fixes for external names filter in Excel2002 and later versions.

Build 466

Profile Workbook fixes:

• 1004 when no Default Printer
• Notes in Profile workbook

Name Manager fixes (build 568)

• Resizing form fixes.
• More than 5468 Names
• List names with Active worksheet filter

Name Manager 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

• Calculation of Worksheet Volatility
• Changes to calculation of worksheet overhead
• VBA Events are inhibited except when timing calculations

Profile Headers:

• Correction of RAM and Page File size when >2 Gigabytes
• Display of number of processors/cores

The Function Wizard now shows help for all FastExcel functions and their arguments.

Revised Help Files and PDF Manuals for both FastExcel and Optimising Calculations.

Minor bug fixes.

Whats new in FastExcel Version 2.1

FastExcel Version 2.1 provides many new tools to help you work with large and complex spreadsheet models, as well as significant enhancements to the Version 1 Profiling and Timing commands.

Build 457 includes three fixes to Profile Workbook

• Formatting of Worksheet overhead
• Error message: Copy method of Range class failed
• Increased accuracy in Microtimer overhead to eliminate occasional small negative timings

Build 456 includes a number of minor bug fixes.

Build 452 includes a number of minor bug fixes and upgrades Name Manager to version 3.2. The documentation is still at the build 444 level.
Changes in Name Manager 3.2 include:

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

Build 446 includes fixes for Run and Time Macros command with protected workbooks and minor bug fixes for Profile Workbook and Name manager.
Build 444 includes some minor bug fixes to Name Manager, the count of loaded addins and the user documentation and help files.

Changes in Version 2.1 of FastExcel

• Detection of Linked Shapes in Profile Workbook
• Excel 2003 available memory 1 Gigabyte
• Updated Help and PDF Manuals for FastExcel and Optimising Calculations

Version 3.1 of Name Manager with Dynamic Range Wizard and Find/Replace

Changes in Version 3.1 of FastExcel name manager

• Find and Replace
• 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 FastExcel name manager

• Improved user interface with icon buttons for most commands.
• 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
• 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)

Changes in Version 2.0 of FastExcel name manager

• 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).
• Modeless multi-workbook working with Excel 2000, 2002 & 2003.

Clean Workbook

Dynamic Range Wizard

Name Manager

New Excel Calculation Methods and Options

18 new Advanced Functions

Profiling Improvements

Additional VBA FastExcel facilities

Advanced Lookup Function

The AVLOOKUP function provides additional advanced function compared to the standard Excel VLOOKUP function, whilst offering superior or comparable performance.

• Lookup either a single matching row, or all matching rows.
• Two-Dimensional Lookup: lookup both rows and columns
• Lookup multiple rows and multiple columns with a single AVLOOKUP.
• Multi-column Lookup: lookup rows based on more than one column
• Use AVLOOKUP in array formulae or in formulae like SUM, AVERAGE, MEDIAN, COUNT
• Use either Column header labels or column numbers for the Lookup column(s) and Answer column(s)
• Specify the error returned when Exact Match cannot find a matching row
• Lookup_Range can be sorted ascending, descending or not sorted
• Separate Sort and Exact Match options allow faster Exact Match with sorted data
• Built-in memory with Exact Match (Excel 2000 and later) makes AVLOOKUP faster.
• The lookup column(s) can be any column(s) in the Lookup_Range
• Use wildcard characters in text exact match lookup.

AVLOOKUP has a small additional overhead (.2 millisecs at 1200MHZ) compared to VLOOKUP but will usually outperform VLOOKUP except for sorted approximate match.

Additional Functions

• AMATCH and AMATCHES: advanced MATCH functions similar to AVLOOKUP
• COUNTROWS: counts rows from a reference to the last non-empty row
• COUNTCOLS: counts columns from a reference to the last non-empty column
• COUNTCONTIGROWS: counts rows from a reference to the row before the first non-empty row.
• COUNTCONTIGCOLS: counts columns from a reference to the column before the first non-empty column.
• COUNTUSEDROWS: counts rows from a reference to the last used-range row.
• COUNTUSEDCOLS: counts columns from a reference to the last used-range column.
• STRCOLID: returns a column name (A-IV) given a column number (1-256
• EVAL: evaluates a string as an Excel expression
• ISFORMULA: tests whether a cell contains a formula
• ISLIKE: an array-capable implementation of wildcard pattern matching
• VLINTERP: efficient Linear Interpolation in a vertical table
• Calculation sequence and counting functions:
  • CalcSeqCountRef
  • CalcSeqCountVol
  • CalcSeqCountSet
• Microtimer: High resolution timing function

Additional Calculation Options:

• Work with multiple workbooks with different calculation modes.
• Calculate only the active workbook.
• Mixed Mode Calculation: calculate some worksheets automatically and others on request.
• Set Excel’s initial calculation mode, or leave it to the first workbook opened.
• Restore the workbook calculation mode after opening a workbook.
• Calculate Mixed Mode sheets on open.
• FullCalc sheet and Recalculate sheet commands
• Time calculation for buttons and/or keystrokes

Timing and Calculating Commands

• Alt-F9: Calculate Range
• Shift-F9: Recalculate Sheet(s)
• Alt-Shift-F9: Fullcalculate Sheet(s)
• F9: Recalculate
• Ctrl-Shift-F9: Recalculate including MixedModesheets
• Ctrl-Alt-F9: Full calculate
• Ctrl-Alt-shift-F9: Full calculate with dependency rebuild
• Dirty Range
• Switch on or off calculation timing for calculation keys or buttons
• Show both the individual and total calculation time in Automatic mode.

Clean Workbook

17 new tools to help you trim your workbook size, manage your workbook formatting and clean up your Excel environment:

• Reset used range
• Clean excess used range. Handles even "difficult" used ranges.
• Delete excess used range
• Delete temporary files
• Close VBE windows
• Remove invalid names
• Delete empty workbooks
• Remove zero-sized shapes
• Clean pivot tables
• Remove unused styles
• Remove unused number formats
• Where-used map for styles
• Where-used map for number formats
• Find all cells on a worksheet with the same style or number format as the selected cell.
• Clear undo memory
• Clear clipboard memory
• Link to Rob Bovey’s Code Cleaner and Bill Manville’s FindLink

Name Manager

If you use defined names you should be using Name Manager, which was developed in collaboration with Jan Karel Pieterse:

• List all names in your active workbook.
• Dynamic Range Wizard
• Filter them using 13 filters, e.g. "With external references", "With errors", hidden, visible.
• Show just names that contain a substring.
• Find unused Names
• Edit them in a simple dialog or make a list, edit the list and update all names in one go.
• Delete, hide, unhide selected names with a single mouse click.
• Evaluate and GoTo names.
• A1/R1C1 toggle.
• Works with localised Excel versions (English and European).
• Modeless multi-workbook working with Excel 2000, 2002 and 2003.
• Fix corrupt names.
• Selective transfer of names between workbooks using List and Pickup.
• Supports relative names
• Localise and Globalise names.
• Name Manager supports US International keyboard

Dynamic Range Wizard

The dynamic range wizard gives you an easy way of creating named ranges, which dynamically expand and contract as you add or delete data.

• Expansion by rows or by columns or by both.
• Create dynamic ranges that include intermediate empty/blank cells.
• Choose from five different expansion methods:
  • Expand to last non-empty cell in the anchor range or all the fixed columns/rows.
  • Expand to cell before the first empty cell in the anchor range or all the fixed columns/rows.
  • Expand to the used range.
  • Expand using the count of non-empty cells.
  • Use the number of rows/columns stored in a separate cell.

Profiling Commands Improvements

The Profile information block has been redesigned and extended to include an additional 21 items, including CPU speed, swap file and RAM useage, temporary files count and so on.
Additional intelligence and user control of sensitivity in highlighting (orange) problem areas.

Profile Workbook Sheets:

• Option to find maximum and minimum sheet calculation time helps analyse sheet overhead.
• Sheet memory split by used range memory and other.
• Bytes per cell
• % Waste and % Sparse

Profile Workbook Book

• Range memory and total memory
• % Waste and % Sparse
• Count of linked workbooks.

Profile Worksheet

• Option to profile rows as well as columns and areas

Additional VBA facilities:

• Time worksheet function
• Time Macro execution
• Callable FastExcel calculation routines
• Documentation on using Microtimer to time portions of VBA code

GoBack command: reverses the last GoTo

Improved access to both Contextual and Fastexcel help

FastExcel Version 2.3 requires Excel97 SR2, 2000, 2002 or 2003 on a Windows PC, and a FastExcel License Code.

Please report all problems and feed back to Charles@DecisionModels.com

The FastExcel V2.3 installation package is approximately 4.50 MB.
The package contains

• The FastExcel V2.3 program.
• Help files and manuals for both FastExcel Version 2.3 and Optimising Calculations
• The uninstall package.

Download time is dependent on the speed of your network connection and internet traffic. .

To install FastExcel V2 you need BOTH a FastExcel License Code,
  AND the downloaded installation package.

Please Uninstall Version 1, Version 2 and any beta versions using the FastExcel Uninstall package before installing Version 2.1

It is possible but not advisable to have both version 1 and version 2 installed at the same time.

Download the V2.4 Installation Package, Version 2.4 Build 495