Dive into an intermediate-level Visual Basic for Applications (VBA) tutorial designed for Excel 2019 users. Learn to automate repetitive processes and build custom tools by mastering foundational VBA concepts. Explore variables, expressions, intrinsic functions, and control structures like If-Then, Select Case, and loops. Practice creating and modifying macros, from simple recorded ones to complex custom-built procedures. Gain hands-on experience with message boxes, input boxes, and object variables. Apply your skills to real-world scenarios, such as extracting information from VIN numbers and manipulating spreadsheet data efficiently. Perfect for business analysts, data scientists, and Excel power users looking to expand their automation capabilities and streamline workflows.
Overview
Syllabus
Start
Introduction
Introduction to Expressions, Variables, and Intrinsic Functions
Variables - Overview, Naming Rules, and Conventions
Variable Declaration Methods and Scope
VBA Data Types
Option Explicit Statement and Removing Variables from Memory
Concise Ways to Declare Variables
Declaring Procedure Level String Variables and Reviewing Intrinsic Functions and VIN Characters
Assigning Values to Variables Using Intrinsic Functions and the Line Editor
Testing the Sub Procedure and Introduction to the With-End With Structure and the Offset Property
Creating a With-End With Structure that References the Offset Property and Testing the Procedure
Troubleshooting the Procedure and Re-Testing It
Saving Files as Macro-Enabled
Overview of MsgBox and InputBox Functions
Modifying an Event Procedure to Include a Message Box
Testing the Message Box Function
Modifying a Sub Procedure to Include an Input Box
Testing the Input Box Function
Overview of Object Variables
Using Object Variables in a Sub Procedure
Testing a Sub Procedure by Stepping Into It
Expressions, Variables, and Intrinsic Functions Recap
Introduction to Controlling Program Execution
Controlling Program Execution and Control of Flow Structures
Boolean Expressions, Logical Operators, and Comparison Operators
Overview of the If-Then Constructs
Using the If-Then-End If Construct
Creating a Function Procedure Using the If-Then-Else-End If Construct
Calling a Function Procedure from a Sub Procedure
Testing the Function Procedure and Modifying the Code
Creating a Function Procedure to Extract the Year from the VIN
Testing the Year Extraction Function
Creating a Function Procedure Using the If-Then-ElseIf-End If Construct
Creating a With-End With Structure for Concise Code - Testing Procedure
Select Case Overview - End Select Construct
Creating Functions Using the Select Case - End Select Construct
Creating Functions by Using a Text File
On Your Own - Creating a Function to Extract the Make
Testing Final Three Function Procedures for Extraction
Overview of Looping Constructs
Using a Do While Loop to Populate the Spreadsheet
Creating Procedures to Append and Clear Info on Sheets
Testing the Append and Clear Sheet Procedures
Avoiding Errors by Using an If-Then-Else Construct
Using the For-Each-Next Loop
Controlling Program Execution Recap
Conclusion
Taught by
Learnit Training
