21 hours (usually 3 days including breaks)
- Ability to work with a spreadsheet, basic knowledge (references, ranges, sheets, ...)
- VBA background knowledge is not required to create macros or VBA code
- Data Analysts
It is an introduction to procedural programming in VBA. Training allows you to gain a strong foundation for further learning and VBA environment.
This instructor-led, live training (online or onsite) is aimed at data analysts who wish to use VBA in Excel for automating tasks.
By the end of this training, participants will be able to:
- Record and edit macros as required.
- Write procedures using data from the sheet.
- Create your own functions.
- Handle an event (opening worksheet cell update etc.) by means of the handler.
- Create a form.
Format of the Course
- Interactive lecture and discussion.
- Lots of exercises and practice.
- Hands-on implementation in a live-lab environment.
Course Customization Options
- To request a customized training for this course, please contact us to arrange.
- Recording and editing macros
- Assigning macros to forms, toolbars, keyboard shortcuts
- 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
- Combining quoted strings (concatenation)
- Conversion to other types of (implicit and explicit)
- Features processing quoted strings
- 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
- Processing step
- Locals window
- Immediate window
- Traps - Watches
- Call Stack
- 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
- Create your own forms
- Calling a form from code
- Use the forms in the sheet
I mostly enjoyed the exercises.
Training agenda was well thought out.
I genuinely enjoyed the short exercises.
a large range of information
problem solving on a regular basis. searching for the most useful elements for trainees.
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
A lot of exercise. Trainer doesn't need to have support for his course. He has really great knowledge for his subject.
A synthetic and efficient course of training. A small group, a lot of exercises
Practical use of content provided during training in the exercises. Translation on examples.
SECO/WARWICK S.A. ; ; TRW Steering Systems Poland Sp. z o.o.