Course Outline
Macros
- Recording and editing macros.
- Where to store macros.
- Assigning macros to forms, toolbars, and keyboard shortcuts.
VBA Environment
- Visual Basic Editor and its options.
- Keyboard Shortcuts.
- Optimising the environment.
Introduction to Procedural Programming
- Procedures: Function, Sub.
- Data types.
- The conditional statement If...Then...Elseif....Else....End If.
- Case instruction.
- Loop While, Until.
- Loop For...Next.
- Instructions to break the loop (Exit).
Strings
- Combining strings (concatenation).
- Conversion to other types – implicit and explicit.
- String processing features.
Visual Basic
- Downloading and uploading data to a spreadsheet (Cells, Range).
- Downloading and uploading data to the user (InputBox, MsgBox).
- Variable declaration.
- Scope and lifetime of variables.
- Operators and their priorities.
- Module options.
- Creating your own functions and using them in a sheet.
- Objects, classes, methods, and properties.
- Securing code.
- Security code tampering and preview.
Debugging
- Step processing.
- Locals window.
- Immediate window.
- Traps – Watches.
- Call Stack.
Error Handling
- Types of errors and ways to avoid them.
- 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 the Workbooks collection.
- Worksheet Object and the Worksheets Collection.
- Objects: ThisWorkbook, ActiveWorkbook, ActiveCell, etc.
- Object Selection.
- Range Collection.
- Cells Object.
- Displaying data on the status bar.
- Optimisation using ScreenUpdating.
- Time measurement via the Timer method.
Using External Data Sources
- Using the ADO library.
- References to external data sources.
- ADO objects:
- Connection.
- Command.
- Recordset.
- Connection string.
- Creating 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
- Basic working knowledge of Excel features such as worksheets, formulas, tables, and sorting or filtering data.
- Experience preparing, updating, or reviewing reports in Microsoft Excel.
- No prior programming experience required.
Audience
- Analysts seeking to automate repetitive Excel tasks.
- Business professionals who work with data and reports in Excel.
- Team members wishing to build simple macros and practical VBA solutions for daily work.
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 3900 € + VAT*
Contact us for an exact quote and to hear our latest promotions
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.
