Course Outline
Macros
- Recording and editing macros
- Determining macro storage locations.
- Assigning macros to forms, toolbars, and keyboard shortcuts
VBA Environment
- The Visual Basic Editor and its configuration options
- Keyboard shortcuts
- Optimizing the development environment
Introduction to Procedural Programming
- Procedures: Function and Sub
- Data types
- Conditional statements: If...Then....Elseif....Else....End If
- The Case instruction
- Loops: While and Until
- For...Next loops
- Breaking out of loops (Exit)
Strings
- String concatenation
- Conversion to other data types: implicit and explicit
- String processing features
Visual Basic
- Reading and writing data to spreadsheets (Cells, Range)
- Exchanging data with users (InputBox, MsgBox)
- Variable declaration
- Variable scope and lifetime
- Operators and their precedence
- Module options
- Creating custom functions and applying them within sheets
- Objects, classes, methods, and properties
- Code security
- Preventing code tampering and security preview
Debugging
- Step-through processing
- Locals window
- Immediate window
- Watchpoints (Traps - Watches)
- Call Stack
Error Handling
- Common error types and prevention strategies
- Capturing and managing run-time errors
- Error handling structures: On Error Resume Next, On Error GoTo label, On Error GoTo 0
Excel Object Model
- The Application object
- Workbook object and the Workbooks collection
- Worksheet object and the Worksheets collection
- Objects: ThisWorkbook, ActiveWorkbook, ActiveCell, etc.
- The Selection object
- The Range collection
- The Cells object
- Displaying data on the status bar
- Performance optimization using ScreenUpdating
- Time measurement via the Timer method
Using External Data Sources
- Leveraging the ADO library
- References to external data sources
- ADO objects:
- Connection
- Command
- Recordset
- Connection strings
- Establishing connections to various databases: Microsoft Access, Oracle, MySQL
Reporting
- Introduction to SQL: Basic structure (SELECT, UPDATE, INSERT INTO, DELETE), invoking Microsoft Access queries from Excel, and using forms to support database interactions
Requirements
- Familiarity with fundamental Excel features, including worksheets, formulas, tables, and data sorting or filtering
- Experience in preparing, updating, or reviewing reports within Microsoft Excel
- No previous programming experience is necessary
Target Audience
- Analysts seeking to automate repetitive Excel tasks
- Business professionals who regularly manage data and reports in Excel
- Team members looking to create simple macros and practical VBA solutions for daily workflows
Testimonials (7)
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
The training was perfect in my opinion, opened my eyes to a lot of things that I was not aware of. Straight to the point with a lot of exercises, for some people it was too fast maybe but due to my background experience I did not feel that way.
Maen Hatoum - Red Bull GmbH
Course - Visual Basic for Applications (VBA) for Analysts
The specialist knowledge was amazing! The way that you took that and broke it up, so we could understand was awesome. I think i just have to start with the simple stuff. the Last Subject was a bit high level and I struggled to keep up but will get there :)
Zaskia Stanz - BMW
Course - Visual Basic for Applications (VBA) for Analysts
Detailed examples & training material.
KAREN LOUW - BMW
Course - Visual Basic for Applications (VBA) for Analysts
He was prepared and also give good pointers
Annemarie Van Aardt - BMW
Course - Visual Basic for Applications (VBA) for Analysts
I liked the fact that we were a small group and therefore the trainer was able to offer individual attention to each trainee.
Claire Pace
Course - Visual Basic for Applications (VBA) for Analysts
I appreciate that the training was customized to our company's needs.
