Dive into an intermediate-level tutorial on Excel 2021 Visual Basic for Applications (VBA), designed for Business Analysts, Data Scientists, and Excel enthusiasts seeking to enhance their automation skills. Learn to write custom VBA code, modify recorded macros, and create efficient tools beyond Excel's standard capabilities. Explore the Visual Basic editor environment, understand the Excel Object Model, and master key programming concepts such as variables, data types, and control structures. Gain hands-on experience with creating procedures, implementing message boxes and input boxes, and developing forms. Discover how to programmatically create Pivot Tables and implement error-handling techniques. By the end of this comprehensive 2-hour and 21-minute tutorial, acquire the skills to streamline daily tasks and unlock Excel's full potential through VBA programming.
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
