Excel 2019 VBA Intermediate Tutorial

Excel 2019 VBA Intermediate Tutorial
Get Ad-Free Training by becoming a member today!
/ @learnittraining
Exercise Files: bit.ly/37mAIZM
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 series, users will be guided through the foundational concepts of VBA in Excel. Users will learn to build macros from the ground up, starting with recording macros that require little to no code, and working towards building their own macros using crucial VBA concepts such as Loop, If Then, and other complex functions.
Join Learnit Anytime for ad-free training, exams, certificates, and exclusive content:
www.learnitanytime.com
For Private Group Trainings please visit:
www.learnit.com/private-group...
Manuals: bit.ly/30xZb59
Username: manuals
Password: password
Start 0:00
Introduction 0:03
Introduction to Expressions, Variables, and Intrinsic Functions 2:06
Variables - Overview, Naming Rules, and Conventions 3:48
Variable Declaration Methods and Scope5:35
VBA Data Types 7:40
Option Explicit Statement and Removing Variables from Memory 9:26
Concise Ways to Declare Variables 13:43
Declaring Procedure Level String Variables and Reviewing Intrinsic Functions and VIN Characters 17:19
Assigning Values to Variables Using Intrinsic Functions and the Line Editor 21:14
Testing the Sub Procedure and Introduction to the With-End With Structure and the Offset Property 28:26
Creating a With-End With Structure that References the Offset Property and Testing the Procedure 33:40
Troubleshooting the Procedure and Re-Testing It 37:44
Saving Files as Macro-Enabled 40:50
Overview of MsgBox and InputBox Functions 41:24
Modifying an Event Procedure to Include a Message Box 46:56
Testing the Message Box Function 51:44
Modifying a Sub Procedure to Include an Input Box 52:56
Testing the Input Box Function 59:45
Overview of Object Variables 1:01:03
Using Object Variables in a Sub Procedure 1:02:38
Testing a Sub Procedure by Stepping Into It 1:10:33
Expressions, Variables, and Intrinsic Functions Recap 1:15:04
Introduction to Controlling Program Execution 1:18:24
Controlling Program Execution and Control of Flow Structures 1:19:02
Boolean Expressions, Logical Operators, and Comparison Operators 1:21:10
Overview of the If-Then Constructs 1:24:40
Using the If-Then-End If Construct 1:27:01
Creating a Function Procedure Using the If-Then-Else-End If Construct 1:30:32
Calling a Function Procedure from a Sub Procedure 1:34:47
Testing the Function Procedure and Modifying the Code 1:37:54
Creating a Function Procedure to Extract the Year from the VIN 1:39:55
Testing the Year Extraction Function 1:43:30
Creating a Function Procedure Using the If-Then-ElseIf-End If Construct 1:44:14
Creating a With-End With Structure for Concise Code - Testing Procedure 1:47:47
Select Case Overview - End Select Construct 1:50:29
Creating Functions Using the Select Case - End Select Construct 1:51:20
Creating Functions by Using a Text File 1:55:13
On Your Own - Creating a Function to Extract the Make 1:58:24
Testing Final Three Function Procedures for Extraction 2:00:11
Overview of Looping Constructs 2:00:56
Using a Do While Loop to Populate the Spreadsheet 2:04:14
Creating Procedures to Append and Clear Info on Sheets 2:08:20
Testing the Append and Clear Sheet Procedures 2:14:19
Avoiding Errors by Using an If-Then-Else Construct 2:15:01
Using the For-Each-Next Loop 2:22:58
Controlling Program Execution Recap 2:27:46
Conclusion 2:28:51
#excel #exceltutorial #exceltips #vba #excelvba
(C) 2022 Learnit, Inc.
Any illegal reproduction of this content will result in immediate legal action.

[@lberry750]

"Tell 'em what you're going to tell 'em, Tell 'em, Tell 'em what your told 'em"...love it!

[@LearnitTraining]

Truth!

[@catboy721]

Very well done and very informative. Two notes/observations: 1) Accepting the default filename is great.... but if the user selects a different file, there are several hard-coded references about returning to the open file that would break. Might be good to address these other references. 2) Be great in a future update to demonstrate VBA used to clean data prior to running these repetitive tasks,( e.g. checking to see that all the source sheets are correctly formatted and that there aren't formulas or other cells, charts and other data that would impact the results. Thanks

[@LearnitTraining]

Thank you for the great tips!

[@jamdownmatket]

This is one of the greatest vba lessons I run into so far. I am definitely stick with you for help with my projects.

[@LearnitTraining]

We are glad it is so helpful! Thank you for watching!

[@PrateekParihar]

I really like the way you first provide the description and then clearing it with examples. Thanks for that it help me to understand it more thoroughly. I have been studying and practicing VBA as a beginner level to intermediate level from youtube and never get a course with both theoritical and practical part. It will also help me to get confidence for the interview.

[@LearnitTraining]

Thank you for the kind words! Good luck on your interview!

[@pallavbhawal]

Loved it very well explained :) Thank you

[@LearnitTraining]

You are very welcome!

[@kebincui]

The Best EXCEL VBA tutorial! 👍👍

[@LearnitTraining]

Thank you so much for watching Kebin! We hoped you learned alot!

[@weronikalassota-paczek2384]

Very useful video to start automating your daily work, thank you!

[@LearnitTraining]

You are welcome!

[@caliview7577]

If you're wondering near the end of the video why your MSRP column is not formatted for currency and the trainer's is, the trainer's MSRP column magically switches from not being formatted to currency to being formatted for currency between 2:14:17 and 2:14:19. I think you just need to manually format the MSRP column for currency and make the column wide enough to fit the dollar amounts. These videos have been great for learning.

[@halhirst2624]

Nah, she has some kind of sub procedure that adds width to the column that got missed in the tutorial..... I'm sure I saw something in the intellisense that sort of lead towards suggesting something about column widths.... but I can'r seem to locate it anymore.... or my eyeballs have gone crossed.🤣

[@afonsoosorio2099]

Awesome 👌, The best vba tutorial by all standards.

[@LearnitTraining]

Glad it was helpful!

[@nbohmwald]

Excelent Videos!! i learned a lot

[@LearnitTraining]

Happy to hear that!

[@sachinv5316]

thank you for this ❤️

[@LearnitTraining]

You're welcome 😊

[@phillpd96]

The linked exercise files are missing the modules that were given in the video. Am I downloading them incorrectly?

[@narongkaikaew081]

Great..!

[@LearnitTraining]

Thanks!

[@mtstans]

lot's of good stuff here. thanks for sharing. I was under the impression that any variable that was declared in one line separated by commas were declared as a variant type unless explicitly declared otherwise? IE Dim cat, dog, bird as strings. only bird would be string type where as cat and dog would be variants?

[@LearnitTraining]

Hello Matthew! This article may help answer your question: learn.microsoft.com/en-us/office/vba/language/concepts/getting-started/declaring-variables

[@haroune711]

good job , keep going and thanks

[@LearnitTraining]

You are welcome! Thank you for the support!

[@eduardocorvello6378]

It seems vehicles file was modified.. no macros there..

[@LearnitTraining]

We are looking into this!

[@eduardocorvello6378]

thanks!@@LearnitTraining

[@michellewong729]

Great videos, very effective. Also same question here, seems the vehicles file's missing some codes, thanks for looking into this.

[@matitom3045]

I could't find the models which you used during the video. Could you send it (Modifying a Sub Procedure to Include an Input Box)

[@LearnitTraining]

Exercise Files: bit.ly/37mAIZM

[@peter_nortje]

Wonderful tutorial but where do you discuss the "Get New Inventory" code? Am I missing something?

[@LearnitTraining]

This video should help: kzbin.info/www/bejne/gIuXoXWblLJ7h5o

[@frederickjackson7877]

In the Sub (AddTotals) TotalFormula = "=sum(I2:" & LastCell.Address(False,False) & ")" can you explain why False, False is included in this statement rather than True, True. how would the end result affect the outcome. I am curious to understand why False versus True. Fantastic Tutorial perfectly detailed explanations.

[@LearnitTraining]

Hello Fredrick! You can get help directly from our instructors at Offsite, our online community - www.offsitebylearnit.com/

[@nbohmwald]

on 1:29:21, how does VBA know to end the first "if" and not the second one??

[@LearnitTraining]

Hi Nicolas! You can get help directly from our instructors at Offsite, our online community - www.offsitebylearnit.com

[@JitendraBhatt28]

Excellent Video!!, Exercise files link is not working, Kindly guide how to download exercise files?

[@LearnitTraining]

Hi Jitendra! We just tested the link and it is currently working

[@KacperitoPL]

1:27:10

[@KacperitoPL]

2:00:00

[@mitchelltsui6710]

Hello, can I have the PowerPoint please?

[@LearnitTraining]

Hello Mitchell! We do not normally give out the PowerPoints for our videos

[@caliview7577]

​@@LearnitTraining Do you think you might consider sharing them? They would be helpful for reference and for the learning process.

[@hussammohamed9205]

hey Guys i run through this error run-time error '1004' sorry we couldn't find the file passible move or delete

[@LearnitTraining]

Hello Hussam! You can get help directly from our instructors at Offsite, our online community - www.offsitebylearnit.com

[@Funlacement]

हिन्दी मे बनाओ भाई हम जैसे लोग भी रहते है जिनको अँग्रेजी नहीं आती है

[@LearnitTraining]

हिंदी जल्द आ रही है मेरे दोस्त!