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.

Kasper Langmann, Co-founder of Spreadsheeto

Excited? Let’s get started! 😊

*This tutorial is for Excel 2019 for Windows. Got a different version? No problem, you can still follow the exact same steps.

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.

Kasper Langmann, Co-founder of Spreadsheeto

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).

Kasper Langmann, Co-founder of Spreadsheeto

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’.
file from the tab list
select options from the file tab

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.
the visual basic icon under the code group in the ribbon

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!

Recommended Readings:

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.

Kasper Langmann, Co-founder of Spreadsheeto

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:

module with the code for hello world

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. 😊

running the hello world sub

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

Recommended Readings:

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:

  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:

Easy, right? Don’t worry too much about variables and data types. As long as you know the basic ones, you’ll be fine!

Kasper Langmann, Co-founder of Spreadsheeto

Recommended Readings:

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:

  • 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.

Why? Because IF statements has always been an important concept in programming. These conditional clauses will help you accomplish almost anything.

Kasper Langmann, Co-founder of Spreadsheeto

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:

how to create a simple if then code using VBA

If you put 24 on cell A1, you’ll see a message box like this:

vba-even-msgbox

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.

Recommended Readings:

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:

how to create a simple for loop code using vba

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!

Kasper Langmann, Co-founder of Spreadsheeto

Recommended Readings:

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:

how to code a simple input box using vba

On the worksheet, a message box will appear:

an input box asking how many hours the user has spent practicing VBA

Awesome, right?

With input boxes, you can store input from user entries that can be used in a macro.

Kasper Langmann, Co-founder of Spreadsheeto

Recommended Readings:

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:

  • 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:

compile error: for without next

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. 😊

Kasper Langmann, Co-founder of Spreadsheeto

Recommended Readings:

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

Kasper Langmann, Co-founder of Spreadsheeto
2019-10-01T16:17:49+00:00