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! 😊
Table of Content
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:
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.
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:
After that, a new tab will be added to your tab list.
To open the VBA editor:
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:
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:
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:
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:
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! ✍
Recommended Readings:
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:
Also, there are lots of different types of variables. The most common are:
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:
