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 from Excel
Overview of tools on the Data tab
- Accessing external data - do you really need to visit your bank's website daily to check the current CHF exchange rate?
- Defining connections to external data sources (Access, Web, Text, XML, etc.)
- Multi-level sorting - understanding the rules and selecting the correct sorting options
- Efficient advanced filtering - how to create filters with access to specific criteria
- Quick text-to-columns conversion
- Removing duplicate data
- Data validation - ensuring input conforms to a specific format
- Simulation Analysis - creating professional presentations of possible scenarios
- Simulation Analysis - estimating the outcome of formulas
- Grouping and automatic outlines - how to roll up rows and columns to display different levels of detail
PivotTables and PivotCharts
- Calculated fields - adding fields to a PivotTable that don't exist in the source data
- Computational elements within tables
- Data grouping and creating professionally styled reports
Part II. Automation with VBA
Macros
- Recording and editing macros: Silence on the set - understanding macro recording
- Where to store macros - best practices for storing and managing macros
Introduction to procedural programming - the essential foundation
- Subs and Functions - how to call them and what they do
- Data types - which variables are needed and whether it's worth declaring them
- The conditional statement If ... Then ... ElseIf ... Else ... End If
- The Case statement and its common pitfalls
- Loops: For ... Next, Loop ... Each
- Loops: For ... Loop While, Loop Until
- Loop break instructions (Exit)
Visual Basic in action
- Reading from and writing to spreadsheet cells and ranges
- Interacting with users: InputBox and MsgBox
- Variable scope and lifetime
- Operators and their order of precedence
- Useful module options
- Securing your code - protecting against unauthorised changes and previewing
- Key Excel objects: Application, Workbook, Workbooks, Worksheet, Worksheets
- ThisWorkbook, ActiveWorkbook, ActiveCell, Selection, Range, Cells, and more
Debugging
- The Immediate window
- The Locals window
- Step-by-step execution - what to do when something stops working
- Watch expressions
- Call Stack
Error handling
- Types of errors and how to avoid them
- Capturing and handling run-time errors - why even well-written code can sometimes fail
- Structures: On Error Resume Next, On Error GoTo label, On Error GoTo 0
Requirements
A working knowledge of Microsoft Excel is required.
28 Hours
Testimonials (5)
well-prepared training, interesting topics
Maciek Boiski - Instytut Energetyki - Panstwowy Instytut Badawczy
Course - Microsoft Office Excel - poziom podstawowy
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
