Course Outline
Macros
- Recording and editing macros
- Where to store macros.
- Assigning macros to forms, toolbars, keyboard shortcuts
VBA Environment
- Visual Basic Editor and its options
- Keyboard Shortcuts
- Optimizing the environment
Introduction to procedural programming
- Procedures: Function, Sub
- The data types
- The conditional statement If...Then....Elseif....Else....End If
- Instruction Case
- Loop while, until
- Loop for ... next
- Instructions break the loop(exit)
Strings
- Combining strings (concatenation)
- Conversion to other types - implicit and explicit
- Features processing strings
Visual Basic
- Download and upload data to a spreadsheet (Cells, Range)
- Download and upload data to the user (InputBox, MsgBox)
- The declaration of variables
- The extent and lifetime of variables
- Operators and their priorities
- Options modules
- Create your own functions and use them in a sheet
- Objects, classes, methods and properties
- Securing code
- Security code tampering and preview
Debugging
- Processing step
- Locals window
- Immediate window
- Traps - Watches
- Call Stack
Error handling
- Types of errors and ways to avoid
- Capturing and handling run-time errors
- Structures: On Error Resume Next, On Error GoTo label, On Error GoTo 0
Excel Object Model
- The Application object
- Workbook object and a collection of Workbooks
- Worksheet Object and Collection Worksheets
- Objects ThisWorkbook, ActiveWorkbook, ActiveCell ....
- Object Selection
- Collection Range
- Object Cells
- Display data on the statusbar
- Optimization using ScreenUpdating
- The time measurement by the method Timer
The use of external data sources
- Using ADO library
- References to external data sources
- ADO objects:
- Connection
- Command
- Recordset
- Connection string
- Create connections to different databases: Microsoft Access, Oracle, MySQL
Reporting
- Introduction to the SQL language The basic structure of SQL (SELECT, UPDATE, INSERT INTO, DELETE) Calling a Microsoft Access query from Excel Forms to support the use of databases
Requirements
- Fundamental proficiency with Excel functionalities, including worksheets, formulas, tables, and data sorting or filtering techniques.
- Proven ability to prepare, update, and review reports using Microsoft Excel.
- No previous programming background is necessary.
Intended Audience
- Analysts seeking to automate routine Excel processes.
- Business professionals who routinely utilize Excel for data analysis and reporting.
- Team members aiming to develop straightforward macros and practical VBA solutions to support daily operations for government agencies.
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.
