Session 2 - Intermediate Excel (4 hours)
In this session, we will cover two important features of Excel, the PivotTable and the VBA macrocode.
PivotTables are a wonderful way to create flexible, manageable reports in Excel.
Rather than create multiple static reports, you can create one flexible report that can be
easily changed to fit individual needs. In this session we will also cover the basics of VBA
macros, with emphasis on recording and modifying the macrocode. These basic macros can be
used to automate repetitive tasks, and perform actions too tedious or too complex to do by hand.
Introduction
PivotTables
Excel Range - absolute ranges, named ranges
External Data - Tie PivotTable directly to a database query
Working with fields in a PivotTable - adding fields, removing fields,
look at definition of different axes in PivotTables, formatting individual fields Refreshing data - manual refresh, refresh on open Calculated fields Formatting your PivotTable report
Introduction to VBA Macros
Record a macro. Play back the macro VBA Editor. User interface, project explorer, properties window, immediate
window, and the object browser Object oriented programming and the Excel object model Revising a Recorded Macro. Good code versus bad code. Rewrite code Adding a command button and code to the sheet
|
