Excel 2021 VBA Intermediate Tutorial

Excel 2021 VBA Intermediate Tutorial

Get Ad-Free Training by becoming a member today!

Who it's for: Business Analysts, Data Scientists, and everyone in between looking to write their own rulebook on what’s possible in Excel.

What it is: Visual Basic for Applications (VBA) is the programming language for Excel and other Microsoft Office programs. VBA is used to automate repetitive processes and frequent actions. Even more, it can be used to build tools that otherwise don’t exist in Excel.

What you'll learn: In this 4-part series, we start with an overview of VBA and its advantages over recording macros in the Excel interface. We'll record a macro, which produces VBA code in the background, and learn the Visual Basic editor environment before editing the code generated by our macro. Throughout this section, you will learn some key terminology that will help you along the way. We will end the first lesson with saving a macro-enabled workbook and modifying trust center settings pertaining to macro security. You'll understand the Excel Object Model, collections, and how to reference objects in VBA code. Next, we'll go over different types of procedures, creating procedures, learning the scope of procedures, and working with methods. You will also learn how to get VBA context-specific help from within the editor. A key component of VBA is language elements. We will learn about this important topic by going over variables, their scope, and how to declare them. You will also gain an understanding of VBA data types, intrinsic functions, and the With-End With structure and how it provides efficiency in your code. You will also learn to create message boxes and input boxes in this lesson, as well as gain an understanding of object variables. The next lesson will teach you how to control program flow through supported control-of-flow structures. This lesson begins with an overview of structures and Boolean expressions. You will then learn how to utilize conditional branching, with the lesson ending with looping constructs. We move into creating forms, including properties, programming, controls, events, and methods. You will ultimately launch a form with code. The next lesson will take you through the steps necessary to create a Pivot Table programmatically in VBA. We end debugging and how to write error-handling code. By the end of this training series, you will be equipped to write your own VBA code, modify macros you’ve recorded, and create efficiency in your daily tasks with VBA.

Join Learnit Anytime for ad-free training, exams, certificates, and exclusive content:

Start 0:00
Introduction 0:03
Variables, Data Types, and Intrinsic Functions 1:51
Introduction to Variables 4:08
Reviewing Project Explorer and Creating New Modules 9:30
Creating Sub-Procedures and Declaring Procedure Level Variables 12:21
Intrinsic Functions 15:32
Assigning Values to Variables Using Intrinsic Functions 18:04
With-End With Structure and Offset Property 25:47
Assigning Variable Values Using With-End With Structure 29:00
Testing ParseVIN Procedures 31:15
Message Boxes 32:05
Input Boxes 36:41
Creating and Testing Message Boxes in Event Procedures 37:28
Creating Input Boxes by Modifying Existing Procedures 44:08
Testing Modified Procedures 51:52
Introduction to Object Variables 52:51
Declaring Object Variables in Sub-Procedures 54:05
Testing Object Variables by Stepping Into Code 1:03:11
Variables, Data Types, and Intrinsic Functions Recap 1:08:32
Controlling Program Execution 1:10:32
Control Structures and Conditional Branching 1:11:31
If-Then-End If Construct 1:19:26
If-Then-Else-End If Construct 1:23:00
Rearranging Code and Creating Function Procedures 1:30:27
Testing Rearranged Code and Function Procedures 1:36:39
Setting With-End With Structure in Calling Procedures 1:37:17
Select Case and End Select Construct 1:44:37
Select Case Statement 1:45:16
Creating Function Procedures with Select Case Construct 1:48:17
Using Text Files to Insert Function Procedures 1:50:34
Looping Constructs - Do Loops 1:53:51
Modifying Procedures to Include Do While Loops 1:56:08
Sub-Procedure to Select, Cut, and Paste to Other Sheets 1:59:30
Creating Procedures to Clear Sheets and Call Procedures 2:03:24
Looping Constructs - For Loops 2:06:59
For To Next Loop and Encountering Errors 2:07:48
Investigating Cause of Error and Fixing It 2:10:34
Creating and Testing For Each Next Loop 2:16:13
Controlling Program Execution Recap 2:19:37
Conclusion 2:20:26

#excelvba #exceltutorial #vba #exceltips #excel #visualbasic

(C) 2023 Learnit, Inc.

Any illegal reproduction of this content will result in immediate legal action.

[МихаилЛюбимцев-пь]

Hi! Great job!
But some common VBA mistake at 15:10. If you declare like this: "Dim Var1, Var2, Var3 As String", only the last variable Var3 will have a String type. The others will be a Variant! You could execute Debug.Print TypeName(Var1) to make sure of it.
You should declare like: "Dim Var1 As String, Var2 As String, Var3 As String".

[analuizanb]

@59:43 some things are unclear. Is variable LastCell supposed to be a range of cells, or one cell only? The instructor contradicted herself.

[wonsilla]

Great pace and presentation of topics!

[anshulagarwal]

Hi, Can you share the ppt used for the VBA trainings?

[RB-edux]

Unfortunately it seems that the video gets lost at 37:38. It refers to a macro-enabled version of the Vehicles file which isn't in the associated file pack (as pointed out by another user). Also the video refers to a column called Status which isn't in the Vehicles spreadsheet and which hasn't yet been created by the tutor. This is a shame as the tutorial was going well till then. Does anyone have a solution to this confusion?

[ryu]

1:41:09 i got an error on here where my Classmarker was read as string but in "If" was read as Integer, so it didn't match the function. Any solution?

[jaredcarbo]

Not sure if I'm too late to the party, but is there a Power Point that I'm not seeing? I'd love to see the actual deck so I can take notes at my own pace!

[rheysarmiento]

This is great but I don't think the exercise files are accurate esp for the "Vehicles". Can you please update it with the correct file? Thanks.

[emmanuelkwekufrimpong]

Please where can I get the slides used for the lessons?

[camcombloux]

The vehicles file isn't xlsm, so I can't see the programs you're referring to during half the video 😕

[cool]

Thanks a lot for your great service. Please teach us about website creation and designing in wordpress.

[hyperhippyhippohopper]

The singular form of 'Parentheses' is 'Parenthesis', not 'Parenthesy'

[dineshrajan]

Can there be a course made to automate SAP using