Sydney, Australia 2008
Excel Thunder Down Under! Event Pictures
Tuesday, March. 11th: Open forum dinner 6:00 pm - Shipley's, Holiday Inn, Darling Harbour
Wednesday, March 12th: Beyond the Basics
|
Conference Sessions Intermediate |
Add-On Classes Advanced |
|
| 8:15am | Sign in - Continental Breakfast | |
| 8:45am | Welcome Address | |
| 9:00am |
Dick Kusleika - Microsoft MVP What's new in 2007 |
Damon Longworth UserForm |
|
10:30am |
Break | |
| 10:45am |
Charles Williams
Calculation |
|
| 12:00pm | Lunch - Included in Registration | |
| 1:00pm |
Dick Kusleika - Microsoft MVP Tips and Tricks |
Charles Williams Developing Efficient User Defined Functions |
| 2:30pm | Break | |
| 2:45pm |
Damon Longworth - Microsoft MVP Periodic Reporting Strategies |
|
| 4:15pm | Q & A - Wrap up | |
| 4:45pm | Close | |
Wednesday, March 12th: Open forum dinner 6:00 pm - Ice Cube Seafood Grill
Thursday, March 13th: Advance to the Next Level
|
Conference Sessions Advanced |
Add-On Classes Intermediate |
|
| 8:15am | Sign in - Continental Breakfast | |
| 8:45am | Breakout to morning class |
Dick Kusleika Introduction to Class Modules |
| 9:00am |
Charles Williams Array Formulas - and Alternatives |
|
|
10:30am |
Break | |
| 10:45am |
Damon Longworth - Microsoft MVP Using Arrays in VBA |
|
| 12:00pm | Lunch - Included in Registration | |
| 1:00pm |
Dick Kusleika - Microsoft MVP VBA Best Practices |
Damon Longworth Intermediate Charting |
| 2:30pm | Break | |
| 2:45pm |
Charles Williams All About Names |
|
| 4:15pm | Q & A - Wrap up | |
| 4:45pm | Close | |
Thursday, March 13th: Open forum dinner 6:00 pm - South Steyne
Friday, March 14th: All User Friday
|
Add-On Classes All Users |
|
| 8:30am |
Charles Williams Optimizing Calculation Speed |
| 9:00am | |
|
10:30am |
|
| 10:45am | |
| 12:00pm | Lunch - Included in Registration |
| 1:00pm |
Dick Kusleika Importing External Data - Queries, etc. |
| 2:30pm | |
| 2:45pm | |
| 4:15pm | |
| 4:45pm | Close |
About the Presenters:
| Dick Kusleika is a CPA (inactive) living in Omaha, Nebraska, USA. He is the Operations Manager for a company that manufactures and sells avionics test equipment. In addition to his work, Excel is his primary hobby. He has received the Microsoft MVP award and has served as the technical editor on several Excel books. He also administers and writes for the Daily Dose of Excel blog.. |
| Damon Longworth is the conference chair. He has a B.S. in Accounting from Southern Illinois University at Carbondale. He developed his spreadsheet skills using Excel in the accounting field for more than 10 years. He was recently recognized by Microsoft with their MVP award in Excel. |
|
Charles Williams
worked for IBM in Europe and the US in: * Decision Support Systems Software Development, Business and Planning Management. * European Software Forecasting Management. * PC Product Management and Opportunity Analysis. * Sales Incentive Planning and Modelling. * Product Marketing and Planning, Decision Support and End-User Computing. * Operations Research Analysis and Programming. Charles' web site - DecisionModels.com |
Conference Sessions on Wednesday:
9:00 - Dick Kusleika - What's New in 2007
10:45 - Charles Williams - Calculation
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. Charles will 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
o Goalseek
o Data Table calculation
o Scenarios
o Pivot Table calculated fields
• Calculation problem areas:
o Formulae not calculating
o Calculate always in Status Bar
o User Defined Functions
• Calculating from VBA
o EnableCalculation
o CalculationState
o RangeDirty
o Range.Calculate
• Introduction to speeding up calculations
1:00 - Dick Kusleika - Tips and Tricks
2:45 - Damon Longworth - Periodic Reporting Strategies
Add-on classes on Wednesday:
9:00 - Damon Longworth - Excel Userforms
Checkboxes and Comboboxes
Adding new data
Automatically displaying a list
Frames, Images, Labels and Listboxes
Getting selected items
Adding/Removing items
Multi-column RowSource
Multipage
Option button
RefEdit
Scrollbar
Spin button
Tab Strip
TextBox and Restricting textbox entries
Password characters
ToggleButtons
Events
Disabling the Close button
Getting information from worksheet to form & vice versa
Creating Wizards
Creating your own controls
Useful URL's
Miscellaneous:
Getting info from clipboard to controls
Invisible objects shown by mouse move
Initialize event
GetCustomListContents
Setting focus
Controltip
Tab order, tag, groupname and tabstop
Enabled default buttons
Startup positions for forms
1:00 - Charles Williams - Developing Efficient User Defined Functions
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 to be covered 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
o Optional Parameters
o Ranges
o Constant arrays
o Strings, numbers, Dates, Logical
• Things you can & cant do from UDFs (& some exceptions)
• 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
Conference Sessions on Thursday:
9:00 - Charles Williams - Array Formulas - And Alternatives
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
o Multiple cell array formulae
o Array Constants
o Array expansion rules
- Too many/few Rows/Columns
- Mixing rows columns and single values
o Debugging Array Formulae
o Single cell array formulae
o Aggregator Functions
o IF, OR, AND, ARRAYOR functions
o Using Wildcards & regular expressions
o Debugging Array Formulae
o Debugging Array Formulae
• 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
o Helper Columns
o Subsetting the data using formulae
o VBA UDFs o DFunctions
o SQL queries/ QBE
o SUMIF & SUMIFS
o Pivot Tables
• Array formulae and constants in Defined Names
• Using array formulae in VBA
• Array formulae tricks
10:45 - Damon Longworth - Using Arrays in VBA
1:00 - Dick Kusleika - VBA Best Practices
2:45 - Charles Williams - All About Names
Charles 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
Add-on classes on Thursday
8:45 - Dick Kusleika - Introduction to Class Modules
Introduction
What is a Class
Why use Classes
Accessing Events in a Class
The WithEvents Keyword
The Application Object’s Events
The QueryTable Object’s Events
The CommandButton Object’s Events
Using Existing Class Modules
The Me Keyword
Interfaces
Example: Custom Message Box
Custom Objects
Property Get
Property Let/Set
Sub and Function Procedures
Public Variables
Example: Abstracting the Quickbooks SDK
Defining the Query Class
Defining the Sales Order Class
Creating a Backlog Report
1:00 - Damon Longworth - Intermediate Charting
1. Intro and Review of Best Practices
2. Combination Charts
3. Dynamic Charts
4. Axis Tricks
5. User Defined Types and Templates
6. VBA to Automate Charting in Excel
7. VBA for Charting Across Applications.
8. Question and Answer
Add-on classes on Friday:
8:30 - Charles Williams - Optimizing Excel Calculation Speed
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.
• Calculation differences in Excel versions 2000-2007
Don't miss this unique chance to attend the master-class on Excel Calculation speed!
1:00 -
Dick Kusleika -
Importing External Data - Queries,
ets.
Importing fixed width text files
Text import wizard
Importing comma delimited files
MS Query
Parameters
Web Queries
Pivot Tables with external data
Importing Access data.