Course Outline
Part I. Squeeze More from Excel for Government
Overview of Tools on the Data Tab
- Access to External Data - Eliminate the need to visit external websites daily to retrieve current exchange rates.
- Defining Connections to External Data (Access, Web, Text, XML, etc.)
- Multi-Level Sorting - Understand the rules and proper sorting options for complex datasets.
- Efficient Advanced Filtering - Create sophisticated filters with access to specific criteria.
- Fast Text-to-Column Conversion
- Delete Duplicate Data
- Data Validation - Ensure that data entries adhere to a specific format.
- Simulation Analysis - Prepare professional presentations of potential scenarios.
- Estimating Formula Results - Use simulation analysis to estimate the outcomes of formulas.
- Grouping and Outlining - Roll up rows and columns to display different levels of detail.
PivotTable and PivotChart for Government
- Calculated Fields - Add fields to a PivotTable that are not present on the original sheet.
- Computational Elements in Tables
- Grouping Data and Creating Professional Statements
Part II. Automation with VBA for Government
Macros
- Recording and Editing Macros - Best practices for recording macros.
- Macro Storage - Determine the optimal location to write and store macros.
Introduction to Procedural Programming - The Essential Foundation for Government
- Sub and Function Procedures - Understand how to invoke them and their purposes.
- Data Types - Explore variable types and the importance of declaration.
- The Conditional Statement If ... Then .... ElseIf .... Else .... End If
- Case Statement and Common Pitfalls
- Loop for ... next, loop ... each
- Loops for ... while, until
- Break Instructions (exit)
Visual Basic in Action for Government
- Data Input and Output to Spreadsheets (Cells, Range)
- User Interaction - Data Input and Output (InputBox, MsgBox)
- Variable Scope and Lifetime
- Operator Precedence
- Useful Module Options
- Code Security - Protect code from tampering and unauthorized access.
- Key Excel Objects: Application, Workbook, Workbooks, Worksheet, Worksheets,
- ThisWorkbook, ActiveWorkbook, ActiveCell, Selection, Range, Cells, ...
Debugging for Government
- Immediate Window
- Locals Window
- Step-by-Step Processing - Troubleshooting when something stops working.
- Watches
- Call Stack
Error Handling for Government
- Types of Errors and Prevention Strategies
- Capturing and Managing Run-Time Errors - Ensure that properly written code can still function effectively.
- Constructs: On Error Resume Next, On Error GoTo label, On Error GoTo 0
Requirements
Proficiency with Microsoft Excel is required for government tasks and operations.
Testimonials (5)
There was a good amount of information for the time of training. Also the trainer was very engaged with the group, especially when people were having troubles or asking questions. It was very nice of him to offer help in case of future ideas.
Aleksander Jarecki - Amazon Development Center Poland Sp. z o.o.
Course - Visual Basic for Applications (VBA) in Excel (intermediate level)
Working on and using our own data/spreadsheets, where we could see how it would benefit us most.
Julie - Environment, Marine and Fisheries
Course - Excel VBA Introduction
Training Room and quite location and all the stuff.
Abdullah Adelyar - USAID - Kabul Afghanistan
Course - Access VBA
I enjoyed the Excel sheets provided having the exercises with examples. This meant that if Tamil was held up helping other people, I could crack on with the next parts.
Luke Pontin
Course - Data and Analytics - from the ground up
I generally enjoyed the practical examples.
