14 hours (usually 2 days including breaks)
- Good Excel user knowledge.
- 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 Modifying Macros
- Assigning Macros to Toolbar, Buttons, Shortcut key
Visual Basic for Applications Environment
- Visual Basic Editor and its options
- Component Management
- Environment Optimization
Introduction to Procedural Programming
- Procedures: Functions and Sub-routines
- Data Types
- If ...Then....Elseif....Else....Endif
- Do...Loop , While
- For... Next
- Arrays and Loops
- VBA and Sheet Data Exchange (Cells, Range Methods)
- Getting and Printing Data from Users (InputBox, MsgBox)
- Variable Declaration
- Scope and LifeTime of Variables and Objects
- Operators and Their Priorities
- Modules Options
- Creating Worksheet Functions
- Objects, Classes, Methods and Properties
- Locking Project for Viewing
- Run Time, Design Time, and Break Mode
- Brakes and Watches
- Local Window
- Immediate Window
- Call Stack
- Errors Types
- On Error GoTo
- On Error Resume Next
Excel Model Object
- Application Object
- Workbook Object and Workbooks Collection
- Worksheet Object and Worksheets Collection
- ThisWorkbook, ActiveWorkbook, ActiveCell, ....
- Selection Object
- Range Collection
- Cells Collection
- Using Status Bar
- Screen Updating and Optimizing
- Timer and Time Measuring
Events Handling in VBA
- Creating and Using Forms
- Invoking Forms in VBA Code
- Using VBA Forms in Worksheet
The instructor's method of writing the code and finding solutions provided a good example of how to approach things in VB. Recording a macro to access its code, intentionally leaving some items incorrect to show how that impacted the result, etc. Finding errors and solutions in code can be difficult but his training approach showed his VB problem solving methodology
Manson Construction Co.
practical application using my data and how I can use VBA to process and produce my reports
Environment, Marine and Fisheries
Working on and using our own data/spreadsheets, where we could see how it would benefit us most.
Julie Longridge - Environment, Marine and Fisheries
appropriate adjustment of the level of exercises to the knowledge of the participants, help of the teacher
consolidating my basic knowledge and broadening it.
Organizing the acquired theoretical knowledge and the multitude of practical exercises.
practical examples of VBA application
VBA practical exercises
Mali liu - 深圳汉莎技术有限公司
A lot of practical examples
Tasks and practical examples
a good explanation of all the issues, so that they were understandable