Learn VBA in Excel: These 11+ Tutorials Teach You VBA in 20 Hours
Written by co-founder Kasper Langmann, Microsoft Office Specialist.
Learning VBA sounds daunting, isn’t it?
It’s only natural. VBA is a huge topic.
Naturally, questions like “Where do I start? Where does it end? What should I focus on?” will arise.
Without a plan, learning VBA will be hard.
That’s why we made this ultimate tutorial, to teach you how you can start writing code from scratch! We’ve collected more than 11 of the best tutorials around the web you can bounce if you would like to know more.
Excited? Let’s get started! 😊
Table of Content
Getting started with VBA and Macro
The terms “VBA” and “Macro” are often used interchangeably.
And that’s okay! Why? Because more or less, when someone mentions either word, they actually mean the same thing.
But looking more inwardly, what’s VBA and Macro?
VBA is short for “Visual Basic for Applications”. It’s a programming language created by Microsoft that comes pre-installed with Microsoft Office applications like Excel, Word, Access, and Outlook.
Macro, on the other hand, is sort of a series of instructions that perform a series of tasks. A macro is also referred to as a subroutine (sub) or a procedure.
At the end of this tutorial, our objective is for you to be able to create a sub, which is a collection of lines of VBA code that performs a task.
To aid with that, we included at the end of every section what we call “recommended readings” where you can read more about certain topics.
This tutorial serves only as a crash course. Treat it like a blueprint of topics you should learn so you would be able to learn Excel fast.
If you would like to invest a bit more and become a pro within 20 hours, we have a “VBA Masterclass” program.
The beauty and advantage of this program are summarized in its 3 pillars:
- Actionable real-life training
- High-level support and feedback
- Interactive skill-assignments
Otherwise, this tutorial is good enough for you. 😊
The Visual Basic Editor
The Visual Basic for Applications editor is where you create, manage, and run VBA code on your Excel spreadsheet.
If you’re familiar with programming, the VBA editor actually looks like an IDE (integrated development environment).
Here’s how the VBA editor looks like:
If you’re new to Excel, you might’ve noticed that you can’t see anything related to Visual Basic in the Ribbon.
The reason for this is, there are certain steps you have to take to be able to see and use the VBA editor.
First of all, you need to be able to see the ‘Developer’ tab.
Here’s a summary of how you can add the ‘Developer’ tab to your tab list:
- Access the backstage view of Excel by clicking ‘File’ from the tab list.
- Then, click ‘Options’.
- Click ‘Customize Ribbon’ on the left-hand sidebar.
- On the right side of the screen, you’ll see the “Main Tabs” section. Check the ‘Developer’ box.
- Hit ‘OK’.
After that, a new tab will be added to your tab list.
To open the VBA editor:
- Click the ‘Developer’ tab from your tab list.
- Click the ‘Visual Basic’ icon under the ‘Code’ group.
After that, you’ll immediately see the VBA editor pop up!
Now that you have the VBA editor up and running, it’s time for some action!
- How to Use the VBA Editor in Excel (3 mins. reading time)
- Visual Basic Editor – How to Open and Use it in Excel (16 mins. reading time)
Warming up with Modules and Subs
The lines of code in VBA is called a “sub”. Subs are stored in modules.
You need to know how to create modules so you have a place to store the subs or your lines of code.
Here’s a summary of how to create a module:
- On the upper-left side of your VBA editor, you’ll see a tab titled “Project – VBAProject”. Below it, right-click on either ‘Sheet1 (Sheet1)’ or ‘ThisWorkbook’.
- Click ‘Insert’ from the options.
- Click ‘Module’ from the options.
What you just did is already 50% of the work!
With a module on, all that’s left is for you is to write your first VBA code!
Let’s have you write your first 2 codes:
- Sub WriteValue
- Sheet1.Range(“A1”) = “Hello World!
Write down the first one, “Sub WriteValue” on your module and press “Enter”. Immediately, you’ll see another line of code pop up at the bottom, “End Sub”.
Now, write the second code, the popular “Hello World!” between the first code and the code that just popped up.
Just a little explanation about the code your writing:
What the code indicates is to write the value (right side) on the specified location (left side) which is cell A1 of Sheet1.
Here’s how the module will look like:
To see how your code will affect the worksheet, you’ll have to run it first:
- On the VBA editor, click the ‘Run’ tab.
- Then, click ‘Run Sub/UserForm’.
Actually, you can press ‘F5’ to run the sub right away. 😊
Try and see your worksheet.
You’ll now be seeing this:
Congratulations! You just run your first code!
How long has it been since you started this tutorial? A few minutes? An hour? And now, you’ve already written your first code.
As you might have guessed, you can tweak the codes to display whatever text you like in any cell inside the worksheet. Feel free to try it! ✍
- How to insert and run VBA code in Excel – tutorial for beginners (2 mins. reading time)
- How to Use Ranges in VBA (5 mins. reading time)
- The Complete Guide to the VBA Sub (14 mins. reading time)
Playing with Variables
The same with other programming languages, variables are vital in VBA.
If you’re not familiar with variables, here’s a good definition:
A variable stores a value. It stands in the place of a value.
There are 3 things you can do with a variable:
- Declare or create a variable
- Store a value in a variable
- Read the value in the variable
Also, there are lots of different types of variables. The most common are:
- String – for text
- Long – for integers
- Double – for decimals
- Date – for date and time
- Currency – similar to decimal but only up to 4 decimal places
- Boolean – for true or false
When creating a variable, you’ll have to use “Dim” which is short for dimension. It tells Excel that the next word is the variable name.
Then, you’ll have to use “As” as the declaration statement. It tells Excel that you’re going to use that data type for that variable.
As an example, let’s say you would create a variable called “company” which is a string of text.
Then, to set the variable, you’ll need to create a line that assigns a value.
In VBA, all you have to do is append “.Value” to the variable and put an equals (=) sign pointing to that value.
Here’s how it would look like in your VBA editor:
Easy, right? Don’t worry too much about variables and data types. As long as you know the basic ones, you’ll be fine!
- VBA DIM: How to Declare Variables in VBA (4 mins. reading time)
- Excel VBA Variables, Data Types & Constant (4 mins. reading time)
Dealing with VBA Logic and Boolean
This section includes both the logical operators you can use in Excel VBA as well as the IF statements.
First off, there are 4 logical operators in VBA:
These operators are usually used in tandem with boolean statements or expressions.
Knowing how to use the IF statements in VBA will make your life easier.
Why? Because IF statements has always been an important concept in programming. These conditional clauses will help you accomplish almost anything.
There are 3 typical conditional expressions in VBA:
Here’s an example of how it works:
Suppose you’re using VBA to check a cell and see if the number is even. With that, we’re using this syntax:
If you put 24 on cell A1, you’ll see a message box like this:
You would need more practice in this part. Knowing the operators and the boolean clauses are only half the battle.
Knowing how to use them and when is what you’ll have to master.
- How to Use IF Statements in VBA (6 mins. reading time)
- Logical Operators in Excel VBA (5 mins. reading time)
- Excel VBA IF THEN Statement (and a useful tip) (6 mins. reading time)
The Magic of Do Loops
Loops are easy to understand. But they get complicated real fast.
From the name itself, do loops allow you to run a command multiple times or repeat the command until specific criteria are met.
There are three types of “loop”:
- For Loop
- Do Until Loop
- Do While Loop
Here’s an example of how a for loop works:
This code will start a loop and write numbers 1 to 5 and stops at that last number:
Mind you, this is just a simple for loop code.
Once you learn how to make more complicated loops, you’ll start to see how much more you can do with a loop!
- How to Use For Loops in VBA (5 mins. reading time)
- VBA Do Loop (5 mins. reading time)
Allow User Interaction by Input Boxes
Pretty sure that by now, you have realized how Excel VBA can greatly affect your productivity using Excel.
With only a few lines of codes, you could save yourself an hour of work!
But here’s a question:
What if you want to get information directly from the user?
Yes! Input boxes are the way to do it.
Input boxes are like message boxes where users can enter information. You can then use that information directly on your macro.
For example, we would like to know how many hours you have practiced VBA since you started.
Here’s its code:
On the worksheet, a message box will appear:
With input boxes, you can store input from user entries that can be used in a macro.
- Using an Input Box to Gather Data in VBA (4 mins. reading time)
- VBA – InputBox (2 mins. reading time)
Don’t Be Afraid of Errors
No one is expected to write perfect code all the time. Sooner or later, you will experience errors.
That’s why it’s vital that you should learn about error handling.
Error handling is a practice of anticipating and writing codes to handle errors that happen in running applications.
Here are the 3 types of errors in VBA:
How do you know when there’s an error?
The good thing is, the VBA editor will prompt you when there’s an error in your code.
Here’s an example of how a compilation error looks like:
Knowing the type of error that occurred is already half of the puzzle.
Of course, you don’t have to learn every possible error there is at once! Just learn on the go. Errors will pop up here and there.
Little by little, you would experience a lot of errors in VBA. And every instance will be a learning experience for you. 😊
- Error Handling In VBA (7 mins. reading time)
- VBA Error Handling – A Complete Guide (22 mins. reading time)
Where to go from here…
You might be wondering why we said you’ll learn VBA in 20 hours when in fact, adding all the reading time of every tutorial we recommended, only sums up to an hour and 45 minutes.
As with any computer programming language, learning the theory and principles is the easiest part. Correct practice and repetition are the ways to master VBA.
Our advice to you is to spend the remaining 18 hours to practice. If you do so, becoming a VBA pro will be at your arm’s reach! 🥉