Thank you for sending your enquiry! One of our team members will contact you shortly.
Thank you for sending your booking! One of our team members will contact you shortly.
Course Outline
Part I. Getting More Out of Excel
Overview of Tools on the Data Tab
- Accessing external data - do you really need to visit the bank’s website every day just to check the current exchange rate for CHF?
- Establishing connections to external data sources (Access, Web, Text, XML, etc.)
- Multi-level sorting - understanding the rules and proper sorting options
- Efficient advanced filtering - creating filters that allow access to specific filter criteria
- Quick text-to-column conversion
- Removing duplicate data
- Enforcing correct data entry - ensuring data adheres to a specific format
- Scenario Analysis - how to prepare professional presentations of potential scenarios
- Scenario Analysis - estimating formula results
- Grouping and outlining - rolling up rows and columns and displaying different levels of detail
PivotTables and PivotCharts
- Calculated fields - adding fields to a PivotTable that do not exist directly in the worksheet
- Calculated items within the table
- Grouping data and creating professional-looking statements
Part II. Automation Using VBA
Macros
- Recording and editing macros: Silent recording settings and techniques
- Where to store macros - determining the best location for writing macros
Introduction to Procedural Programming - The Essential Foundation
- Sub and Function procedures - how to invoke them and their purpose
- Data Types - understanding variable needs and the importance of declaring them
- The If...Then...ElseIf...Else...End If conditional statement
- The Case statement and associated handling
- For...Next loops and For Each loops
- Do...Loop While and Do...Loop Until loops
- Loop exit instructions
Visual Basic in Action
- Reading and writing data to spreadsheets (Cells, Range)
- Interacting with the user via InputBox and MsgBox
- Variable scope and lifetime
- Operators and their precedence
- Useful module features
- Protecting code - securing it from tampering and unauthorized viewing
- Key Excel objects: Application, Workbook, Workbooks, Worksheet, Worksheets
- ThisWorkbook, ActiveWorkbook, ActiveCell, Selection, Range, Cells, and more
Debugging
- Using the Immediate window
- Using the Locals window
- Stepping through code - troubleshooting when something stops working
- Using Watch windows
- Understanding the Call Stack
Error Handling
- Types of errors and strategies to avoid them
- Capturing and handling run-time errors, explaining why well-written code might sometimes fail
- Constructs: On Error Resume Next, On Error GoTo label, On Error GoTo 0
Requirements
At least an intermediate level of knowledge of MS Excel.
28 Hours
Testimonials (4)
scope of material
Marcin - Instytut Energetyki- Panstwowy Instytut Badawczy
Course - Visual Basic for Applications (VBA) w Excel - wstęp do programowania
Well structured. Good teaching techniques. Course well documented!
Sorin
Course - VBA For Access & Excel
What I liked most about the training was the trainer’s knowledge of Excel. I appreciated learning useful things like shortcuts and formulas that I can use every day.
Martin
Course - Visual Basic for Applications (VBA) for Analysts
Very practical
