Course Outline
Macro Management
- Recording and modifying automated tasks
- Determining appropriate storage locations for macros.
- Assigning macros to user forms, interface toolbars, and keyboard shortcuts
VBA Development Environment
- Visual Basic Editor configuration and settings
- Keyboard shortcut definitions
- Optimizing the development workspace
Foundations of Procedural Programming
- Procedures: Functions and Subroutines
- Variable data types
- Conditional logic: If...Then, ElseIf, Else, End If
- Select Case instruction
- Do While and Do Until loops
- For...Next iteration loops
- Loop termination commands (Exit)
String Handling
- String concatenation techniques
- Data type conversion methods - implicit and explicit
- Advanced string processing functions
Visual Basic for Applications Core
- Data import and export between worksheets and VBA (Cells, Ranges)
- User interaction via InputBox and MsgBox prompts
- Variable declaration syntax
- Variable scope and lifecycle management
- Operator precedence rules
- Module-level options
- Creating and utilizing custom functions within worksheets
- Object-oriented concepts: Classes, Methods, and Properties
- Code protection strategies
- Preventing code tampering and review procedures
Debugging Methodologies
- Step-through execution
- Locals window inspection
- Immediate window usage
- Breakpoints and Watch items
- Call Stack analysis
Error Management
- Common error types and prevention strategies
- Capturing and resolving runtime exceptions
- Control structures: On Error Resume Next, On Error GoTo label, On Error GoTo 0
Excel Object Model Architecture
- The Application root object
- Workbook object and the Workbooks collection
- Worksheet object and the Worksheets collection
- Context-specific objects: ThisWorkbook, ActiveWorkbook, ActiveCell
- The Selection object
- Range collections
- Cell object properties
- Status bar messaging
- Performance optimization via ScreenUpdating
- Execution timing using the Timer method
Integration of External Data Sources
- Utilizing the ActiveX Data Objects (ADO) library
- Establishing references to external data repositories
- ADO core components:
- Connection
- Command
- Recordset
- Configuration of connection strings
- Establishing connectivity to databases such as Microsoft Access, Oracle, and MySQL
Reporting and Query Execution
- Introduction to SQL syntax and basic commands (SELECT, UPDATE, INSERT INTO, DELETE) Executing Microsoft Access queries from Excel Forms to facilitate database access
Requirements
- Foundational proficiency with Excel features, including worksheets, formulas, data tables, and sorting or filtering functionalities
- Practical experience in the preparation, modification, and review of reports using Microsoft Excel
- No prior programming experience is required for this module
Target Audience
- Analysts seeking to automate repetitive Excel workflows for government processes
- Business professionals responsible for data management and reporting within Excel
- Team members aiming to develop simple macros and practical VBA solutions for daily operational tasks
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.
