2008 Australian Excel User Conference

The 2008 Australian Excel Users Conference took place in Sydney on March 12-14.

The presenters were:

• Dick Kusleika
• Damon Longworth
• Charles Williams

See http://www.exceluserconference.com/AUEUC.html for details of the sessions and how to register.

I presented three sessions and two add-on classes.

Wednesday March 12th:

Excel Calculation: session for Intermediate Users.

The heart of any spreadsheet is its calculations. This session is designed to help you make the most of the many different Excel calculation options, and to shed light on some of the more frequently encountered calculation stumbling blocks. I hope to cover the following topics:

• Understanding Recalculation and Full Calculation, dependencies, volatile functions.
• The different Excel calculation methods (Automatic, Manual, Worksheet, Range) and how to control them.
• Saving and Restoring Calculation properties, what happens when you open a workbook.
• Calculation differences in Excel versions: Excel97 - Excel2007
• More advanced and what-if calculation:
  • Iteration and circular references
  • Goalseek
  • Data Table calculation
  • Scenarios
  • Pivot Table calculated fields
• Calculation problem areas:
  • Formulae not calculating
  • Calculate always in Status Bar
  • User Defined Functions
• Introduction to speeding up calculations

Developing efficient VBA UDFs: class for more advanced Users

VBA User Defined Functions (UDFs) are commonly thought of as being extremely slow, but in fact following some simple rules can speed up your UDFs by a factor of 100 or more.

The class will demonstrate development of a wide variety of UDFs showing how to speed them up. Examples will include:

• Calculation overhead and how to avoid it: TestUDF
• Fast data transfer: AverageTol - average a range excluding numbers below tolerance
• Fast calculation: FinCalc - a financial calculation based on a probability distribution
• Fast string handling: CheckDigit - calculating a check digit
• Using VBA efficiently: CountU - counting uniques
• FLookup - a lookup function that is faster than VLOOKUP
• ArrayIFS and FSUMIFS - Calculate multiple condition sums, counts etc faster than SUMPRODUCT

Topics I plan to cover include:

• Parameters, dependencies, volatility
• Minimizing calculation overhead: (example 19 secs reduced to 0.09 secs)
• Minimize data transfer time: (example 13 secs reduced to 0.23 secs)
• Range Objects & .VALUE, .VALUE2 and .TEXT
• Handling Input Parameters
  • Optional Parameters
• Ranges
• Constant arrays
• Strings, numbers, Dates, Logical
• Things you can & cant do from UDFs
• Persisting information from inside UDFs
• Application.Caller
• Embedding Excel functions efficiently
• Using the EVALUATE method inside functions
• Using ISEMPTY to handle uncalculated values
• Array functions
• Function Wizard & Help
• Error Handling, returning error values
• Is VB6 faster than VBA?
• Function libraries: XLA vs XLL vs Automation addins

If you need to use UDFs you really should attend this class.

Thursday March 13

Array Formulas - and the alternatives: a session for more advanced users

Array formulae and SUMPRODUCT are one of Excel's most powerful features, but they can slow your PC to a crawl.

This session will show you

• How array formulae work
  • Multiple cell array formulae
  • Array Constants
  • Array expansion rules
    • Too many/few Rows/Columns
    • Mixing rows columns and single values
  • Debugging Array Formulae
  • Single cell array formulae
  • Aggregator Functions
  • IF, OR, AND, ARRAYOR functions
  • Using Wildcards & regular expressions
• Implicit Intersection
• Using SUMPRODUCT instead of Array Formulae: how to/ when to
• Why array formulae can be slow & how to speed them up
• What the alternatives are to array formulae & when to use them
  • Helper Columns
  • Subsetting the data using formulae
  • VBA UDFs
  • DFunctions
  • SQL queries/ QBE
  • SUMIF & SUMIFS
  • Pivot Tables
• Array formulae and constants in Defined Names
• Using array formulae in VBA
• Array formulae tricks

All about Names: a session for more advanced users

I will use the Name Manager addin, developed by myself and Jan Karel Pieterse, to discuss all the different flavours of Excel's defined names and to show some details from the design and development of this very popular advanced addin.

Defining and using Names:

• Global/Local
• Hidden/Unhidden
• Relative/Fixed
• References/Formulae/Constants/Arrays/XLM
• 3-D/External/Multi-Area/Dynamic
• Implicit Referencing
• Naming Conventions
• Hidden Namespace
• Names & Excel Calculation
• Using and evaluating Names from VBA

What all the buttons in Name Manager do.

Design and Development of Name Manager:

• Supporting Excel 97 through Excel 2007 and Mac Excel
• Modeless dynamic refresh using Application-level events
• Resizable forms & API calls
• Dynamic Range Wizard - Multipage and Refedit controls
• Parsing all the formulae in a workbook efficiently to generate the Name Map
• Trouble with Treeview

Friday March 14

The Optimising Excel Calculation Speed class

Attendees will get a FREE license for FastExcel 2.3

Bring your problem workbook with you and we will start speeding it up!

The workshop session will cover:

• Finding, measuring and prioritising Calculation bottlenecks
• Four golden rules for speeding up calculations, with examples
• Tips and examples for optimising Calculation bottlenecks
• Controlling and extending Excel's calculation methods using FastExcel.
• Optimising Recalculation speed.
• Using FastExcel functions to speedup calculation.
• Calculation differences in Excel versions 2000-2007

Don't miss this unique chance to attend the master-class on Excel Calculation speed!