This intermediate Excel VBA tutorial aims to equip Business Analysts, Data Scientists, and Excel users with the skills to automate processes, build tools, and enhance efficiency in Excel. By the end of the course, learners will be able to write VBA code, modify recorded macros, create forms, control program flow, create Pivot Tables programmatically, and implement error-handling code. The course covers topics such as variables, data types, intrinsic functions, object variables, control structures, conditional branching, looping constructs, and creating function procedures. The teaching method includes hands-on exercises, demonstrations, and practical examples.
Overview
Syllabus
Start
Introduction
Variables, Data Types, and Intrinsic Functions
Introduction to Variables
Reviewing Project Explorer and Creating New Modules
Creating Sub-Procedures and Declaring Procedure Level Variables
Intrinsic Functions
Assigning Values to Variables Using Intrinsic Functions
With-End With Structure and Offset Property
Assigning Variable Values Using With-End With Structure
Testing ParseVIN Procedures
Message Boxes
Input Boxes
Creating and Testing Message Boxes in Event Procedures
Creating Input Boxes by Modifying Existing Procedures
Testing Modified Procedures
Introduction to Object Variables
Declaring Object Variables in Sub-Procedures
Testing Object Variables by Stepping Into Code
Variables, Data Types, and Intrinsic Functions Recap
Controlling Program Execution
Control Structures and Conditional Branching
If-Then-End If Construct
If-Then-Else-End If Construct
Rearranging Code and Creating Function Procedures
Testing Rearranged Code and Function Procedures
Setting With-End With Structure in Calling Procedures
Select Case and End Select Construct
Select Case Statement
Creating Function Procedures with Select Case Construct
Using Text Files to Insert Function Procedures
Looping Constructs - Do Loops
Modifying Procedures to Include Do While Loops
Sub-Procedure to Select, Cut, and Paste to Other Sheets
Creating Procedures to Clear Sheets and Call Procedures
Looping Constructs - For Loops
For To Next Loop and Encountering Errors
Investigating Cause of Error and Fixing It
Creating and Testing For Each Next Loop
Controlling Program Execution Recap
Conclusion
Taught by
Learnit Training
