Learn VBA in Excel: These 11+ Tutorials Teach You VBA in 20 Hours

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.

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 for Applications editor is where you create, manage, and run VBA code on your Excel spreadsheet.

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!

The lines of code in VBA is called a “sub”. Subs are stored in modules.

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! ✍

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:

1. Declare or create a variable
2. Store a value in a variable
3. 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:

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:

• Not
• And
• Or
• Xor

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.

There are 3 typical conditional expressions in VBA:

• IF-THEN
• IF-THEN-ELSE
• ELSEIF

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.

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.

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:

Awesome, right?

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:

• Syntax
• Compilation
• Runtime

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.

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.