Thank you for sending your enquiry! One of our team members will contact you shortly.
Thank you for sending your booking! One of our team members will contact you shortly.
Course Outline
Macros
- Recording and editing macros
- Assigning macros to forms, toolbars, keyboard shortcuts
VBA Environment
- Visual Basic Editor and its options
- Management components
- Keyboard Shortcuts
- Optimizing the environment
Introduction to procedural programming
- Procedures: Function Sub
- Data types
- The if statement ... then ... elseif ... else ... end if
- Case statement
- Loops for ... loop while, until
- Loop for ... next
- Instructions loop break (exit)
- Array processing
Quoted string
- Combining quoted strings (concatenation)
- Conversion to other types of (implicit and explicit)
- Features processing quoted 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
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 status bar
- Optimization using ScreenUpdating
- The time measurement by the method Timer
Event handling in VBA
- What is an event?
- Responding to an event and create an event handler
- Triggering Event Handlers
- Service frequently occurring events
VBA Forms
- Create your own forms
- Calling a form from code
- Use the forms in the sheet
Requirements
- Ability to work with a spreadsheet, basic knowledge (references, ranges, sheets, ...)
- VBA background knowledge is not required to create macros or VBA code
Audience
- Data Analysts
21 Hours
Delivery Options
Private Group Training
Our identity is rooted in delivering exactly what our clients need.
- Pre-course call with your trainer
- Customisation of the learning experience to achieve your goals -
- Bespoke outlines
- Practical hands-on exercises containing data / scenarios recognisable to the learners
- Training scheduled on a date of your choice
- Delivered online, onsite/classroom or hybrid by experts sharing real world experience
Private Group Prices RRP from £5700 online delivery, based on a group of 2 delegates, £1800 per additional delegate (excludes any certification / exam costs). We recommend a maximum group size of 12 for most learning events.
Contact us for an exact quote and to hear our latest promotions
Public Training
Please see our public courses
Testimonials (4)
A lot of exercise. Trainer doesn't need to have support for his course. He has really great knowledge for his subject.
Quentin Demeyere
Course - Visual Basic for Applications (VBA) in Excel - Introduction to programming
It was really nice of the trainer that he took a lot of time to answer our questions and helped us improve or gave us hints on how to improve some macros we were already using without fully understanding the code.
David Trieu - CD Projekt SA
Course - Visual Basic for Applications (VBA) in Excel - Introduction to programming
I genuinely enjoyed the short exercises.
Mateusz Podsiadlo
Course - Visual Basic for Applications (VBA) in Excel - Introduction to programming
Training agenda was well thought out.
