Excel is a very powerful tool, and with VBA programming you can automate both simple and complex tasks. VBA is a programming language that is both accessible and powerful. With VBA you can perform multiple actions in a single macro, modify an existing Excel formula to suit your specific needs, build a program with logic and loops, and much more.
This practical course enables participants to carry out the majority of the examples themselves on their own workstations.
Duration
1 day
Schedule
See training dates for details
Regular fee
$375
Preferential fee
A preferential rate is offered to public institutions, to members of certain professional organizations as well as to companies that do a certain amount of business with Technologia. To know more, please read the "Registration and rates" section on our FAQ page. Please note that preferential rates are not available for online training courses. Discounts cannot be combined with other offers.
$340
Objectives of the training
In this training course, you'll learn how to use VBA (Visual Basic for Applications) programming in Microsoft Excel to automate repetitive tasks.Targeted audience
General publicPrerequisite
Excel basicsTrainers
Upcoming information
Course architecture
Introduction
- Why use VBA programming in Excel?
- What's the difference between a macro and VBA code?
- Understanding workbooks with macros (.xlsm) and security
- Overview of the VBA editor and developer menu
- How to use online help and auto-completion when reacting to code
Recording macros
- How to record a macro
- The difference between relative and absolute references
- Browsing, understanding and modifying recorded code
Excel's special objects
- Understanding data references in code: workbook, sheet, range and cell, named cells and ranges, named tables.
- Understand the benefits of using named ranges in code.
- Use data validation to create a drop-down list.
- Understanding Excel events: workbook_open(), worksheet_change(), worksheet_activate(), etc.
VBA programming and debugging tools
- Understand the difference between a procedure and a function
- Create a simple function and use it in a cell
- Creating a simple procedure and using it in a cell
- Understanding arguments and returned value for a function, ByVal, ByRef
- How to use variables, constants and arrays
- What are the different types of data (string, integer, long...)?
- Interacting with the user: Msgbox and Inputbox
Simple error handling
- Understand the different types of error: syntax, compilation and runtime errors
- Writing error-handling code
Using loops
- Understanding the different loops (if then else, loop until, do while, select case)
- How to use with to lighten code
Custom forms (UserForm)
- See a simple example of a custom form
Useful functions
- Learn about string manipulation functions (left, mid, right...)
- Understanding the iif() and vraifaux() functions
- Overview of useful calculation functions
- Understanding sheet and VBA code protection
Debugging
- How to compile to see compile errors
- How to use breakpoints and step-by-step execution
- How to track variable values (spyware)
- How to use the execution window
Documentation
- Using F1 online help
- Efficient Web searches
- Using ChatGPT to write and debug code
Pedagogical details
Training architecture
-Theoretical content -Interactive presentations -Demonstration of proposed methods -Guided and independent practical exercises on a computer workstation
Type of training
Skill development
Skill development and knowledge integration
Decision support
Basic knowledge
Private or personalized training
If you have more than 8 people to sign up for a particular course, it can be delivered as a private session right at your offices. Contact us for more details.
Request a quoteDuration
1 day
Schedule
See training dates for details
Regular fee
$375
Preferential fee
A preferential rate is offered to public institutions, to members of certain professional organizations as well as to companies that do a certain amount of business with Technologia. To know more, please read the "Registration and rates" section on our FAQ page. Please note that preferential rates are not available for online training courses. Discounts cannot be combined with other offers.
$340
Private or personalized training
If you have more than 8 people to sign up for a particular course, it can be delivered as a private session right at your offices. Contact us for more details.
Request a quote