This course teaches users how to automate repetitive processes and build tools in Excel using Visual Basic for Applications (VBA). By learning foundational VBA concepts such as Loop, If Then, and Object Variables, users will be able to create macros from scratch. The course covers topics like variable declaration, controlling program execution, and looping constructs. The intended audience includes Business Analysts, Data Scientists, and anyone interested in enhancing their Excel skills through automation and customization.
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
