Course Outline
Part I. Squeeze more from Excel
Overview of tools on the Data tab
- Access to external data - do you really need to visit the bank's website every day to check the current CHF exchange rate?
- Defining connections to external data (Access, Web, Text, XML, ...)
- Multi-level sorting - the rules and proper sorting options
- Efficient Advanced filtering - how to create filter criteria dynamically
- Fast text-to-column conversion
- Delete duplicate data
- Enforcing correct data input - how to ensure data follows a specific format
- Simulation Analysis - how to prepare a professional presentation of possible scenarios
- Simulation Analysis - how to estimate the result of a formula
- Grouping and subtotaling - how to collapse rows and columns and display different levels of detail
PivotTable and PivotChart
- Calculated fields - how to add a field to a PivotTable that does not exist in the source sheet
- Computational elements within the table
- Grouping data and creating professional-looking statements
Part II. Automation, i.e., VBA.
Macros
- Recording and editing macros: Silent recording settings
- Where to store macros - the best locations for writing macros
Introduction to procedural programming - the necessary basis
- Sub and Function - how to invoke them and what they are
- Data Types - what variables are needed and whether it is worth declaring them
- The conditional statement If ... Then .... ElseIf .... Else .... End If
- Case statement and its accompanying trap
- For ... Next loop, For Each ... Next loop
- For ... Loop While, Loop Until
- Loop break instructions (Exit)
Visual Basic in action
- Downloading and uploading data to a spreadsheet (Cells, Range)
- Downloading and uploading data to the user (InputBox, MsgBox)
- The scope and lifetime of variables
- Operators and their priorities
- Useful module options
- Securing code - protecting code from tampering and viewing
- Key Excel objects: Application, Workbook, Workbooks, Worksheet, Worksheets
- ThisWorkbook, ActiveWorkbook, ActiveCell, Selection, Range, Cells, ...
Debugging
- Immediate window
- Locals window
- Step processing - what to do when something stops working
- Watches
- Call Stack
Error handling
- Types of errors and ways to avoid them
- Capturing and handling run-time errors, which is why properly written code can sometimes fail
- Construction: On Error Resume Next, On Error GoTo label, On Error GoTo 0
Requirements
At least average knowledge of MS Excel.
Custom Corporate Training
Training solutions designed exclusively for businesses.
- Customized Content: We adapt the syllabus and practical exercises to the real goals and needs of your project.
- Flexible Schedule: Dates and times adapted to your team's agenda.
- Format: Online (live), In-company (at your offices), or Hybrid.
Price per private group, online live training, starting from 5200 € + VAT*
Contact us for an exact quote and to hear our latest promotions
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
