Excel VBA Tutorial – #1 Getting started in the Excel VBA Editor
Summary: Excel Macros are very useful to automate repetitive tasks and boost your projects.
If you’re just getting started with VBA Macros, read this step-by-step guide to get started with the “Excel VBA Editor”.
This is your tool for writing, editing and testing VBA Macros. Super important stuff!
Click on a link to jump to that section…
#1 Excel Macros – What are they?
#2 How to get the Visual Basic Editor
#3 Code Modules – Inserting a New Code Module
#4 VBA Syntax – What you need to know
#5 New Subroutine – Writing the first VBA Macro
#6 Play – Running the VBA Macro
#7 Final words on Macro security
#8 About the Author – Daniel Lajosbanyai
#1 – Excel Macros – what are they?
Excel Macros are very useful to automate repetitive tasks and boost your projects in Microsoft Office applications.
Macros are programs you can use for a wide range of processes – from copy-paste to setting print area or sending reports via email automatically.
Excel Macros are written and stored in Excel files with a special extension: “.xlsm”. You must save them like this, instead of the standard “.xlsx” file format.
You can choose Excel Macro Enabled Document in the saving type list.
Excel alone cannot be used to edit Macros, it needs a special developer application for it: Visual Basic for Applications (or “VBA” for short).
Fortunately, this tool is built into every modern Excel version. And it’s free!
#2 – How to get the Visual Basic Editor
To find Visual Basic Editor in Excel, first enable the Developer tab in Excel Options.
To do this, click on File > Options > Customize Ribbon. Here you find the Developer option on the right side of the window. Activate it with a click in the box, and then click “OK” to exit the options menu.
Now you can see the Developer tab appears in your ribbon to the right of View.
Click on the Developer tab, and do not be afraid of what you see there 😃
We are going to start with the Visual Basic App on left side.
Next to it you can see a lot of other useful apps and buttons, and we are going to get familiar with them later on (well, not all of them, just the ones that you will use in your daily Macro coding projects).
Click on the Visual Basic button to open the VB Editor.
The Editor opens in a separate new window, and you should see something like this:
#3 – Code Modules – Inserting a New Code Module
On the left side of the window you see two smaller windows. The one on the top contains the main objects of this project (Book1.xlsm), and the bottom one shows the properties of the selected object.
Objects and Properties are going to be important later in the tutorial, but now it is enough for us to know that they can be found here in these windows.
Here comes our next step, to get familiar with Modules.
We are going to insert a new Module to the Objects. This is a must have, because Macros are usually stored in Modules.
To do this, click on the Insert dropdown (circled red in the screenshot) and choose Module from the list. Userform and Class Module are not relevant now, we are going to learn about them later.
After you choose Module, a new object should appear in your tab: a directory called Modules, and inside it a new module – Module1. This is what we just inserted.
You can rename it in the Properties window, if you delete the name “Module1”, and type in your desired name. Let’s name it “Tutorial” now. Note, that the only property our object now has is its name.
#4 – VBA Syntax – What you need to know
In programming there are standard rules to follow – one of these rules is the syntax.
Every programming language has its own syntax.
What does syntax mean?
It’s the way you must write your code to make the computer understand it.
You can picture it like a nation’s language: in Russia you can speak German, but most people are unlikely to understand you.
It’s the same with programming languages: if you code in Visual Basic, you have to use VB commands and punctuation, and not JAVA or C++.
Learning the syntax of a programming language is one of the biggest tasks in mastering a language.
Fortunately, Visual Basic uses easy-to-remember commands, and not any special punctuation, just commas, brackets and double quotes.
As we are going to move on with this tutorial series, you will get to know a lot of useful commands, and the syntax to use the commands and parameters well.
I started to write my program in the right side of the VB Editor.
This white area is a blank page – it is waiting for us to populate it with commands that the computer can process.
I typed “Sub Tutorial” and pressed Enter.
The VBEditor automatically added the brackets at the end, and the “End Sub” command in the next row.
What does this “Sub” mean?
Sub stands for “Subroutine”, and it is the start of the program.
After “Sub” comes the desired name of the macro – you can name it how you want. Actually almost how you want, because it cannot start with numbers, and cannot have spaces in it.
The brackets shouldn’t bother you, later we will add variables there, now just leave them empty.
#5 – New Subroutine – Writing the first VBA Macro
Between the Sub and End Sub we can write our program.
Now, in the tradition of programming tutorials we’re going to make Excel print out “Hello World”.
To do this, we are going to use the command “MsgBox”.
This stands for MessageBox, and it is an easy way to make the program communicate with the user.
Type in MsgBox in the middle of the program (between “Sub Tutorial()” and “End Sub”) and press a space after MsgBox. The following help note should appear:
This note will always appear when you type in a command that has more than one parameter.
This is why I think that VBA is a good language to start coding with – because the Editor helps you with similar notes when you type in a command to not make syntax errors.
Here you can see what kind of parameters you can give to MsgBox:
- Prompt: this is the message you want to show on the monitor. We will type “Hello World!” here, inside double quotes. The quotes are needed for every string-type variable and constant in VBA.
- Buttons: You can specify how many, and what kind of buttons, you want to have in your message box. (Yes/No/Cancel/OK and variations of these). If you leave this empty, the standard vbOKonly parameter will be active – so we are going to have only an “OK” button.
- Title: You can specify the title of the message box. Here let’s type “Tutorial”.
- The other parameters you can just leave empty now. Any parameter with square brackets is optional.
To leave a parameter empty, just write commas in your code.
After each comma is typed in, you will see in the help note that the active parameter’s info will change to the next one.
This shows you exactly, which parameter are you working on. It’s a really helpful function.
When you have typed in Everything, you should see this:
#6 – Play – Running the VBA Macro
To execute a program, just click on the green “Play” button on the toolbar.
This will always start the active macro (active is the one in which the cursor actually is), but since we only have one macro in our module, the VBEditor will automatically execute “Sub Tutorial()”.
If the program ran successfully, you see this on the monitor ➡
If you click OK, the macro will end.
If you don’t click OK, the macro still runs in the background, and waits for the user to click on OK.
You can’t use Excel nor the VBEditor until the button is clicked. There is only one way to make the program pause, and get rid of the message box, if you want to exit the program, or modify something in the code.
If you press Ctrl + Break, it stops the running macro. You can use this to change the prompt, or the title of the MsgBox for example. After pressing Ctrl+Break, the following note appears:
If you choose “Debug”, you get back to the VBEditor and can modify the code.
Clicking on “End” makes the macro stop where it is, and any other commands after the paused command will not be executed.
This is a really helpful feature of VB Editor. Pausing can be used to modify or to debug the code.
Debugging is also self-explanatory in VB, because the Editor marks faulty commands with a red color font.
If you forget to type your prompt in between double quotes or leave the comma out, you get the following error message:
#7 – Final words on Macro security
These are the first steps you need to get familiar with Excel macros in the Visual Basic Editor.
In the next tutorial I’ll introduce the macro recording function, which will make your life much easier if you are new to coding.
Until then, do not be afraid to modify your code, and play around in the VB Editor.
Here is a link to my Excel file with the example macro for this first tutorial.
Download the sample file with VBA code:
Obviously, you do no need to download this since it’s just one line of code you can type easily. I include the file so I can mention Macro security.
To allow Excel to use macros from unknown sources, you do the following:
File > Options > Trust Center > Trust Center Settings > Macro Settings > Enable all macros
Make sure, that you only open trustworthy macro-containing Excel files! Otherwise, you can put your computer in danger of malwares and viruses. Always be aware what you are saving and running on your PC!
Note: if you want to be more cautious, use the setting “Disable all macros with notification”. Excel will then prompt if you want to enable macros when opening a new file. This gives you the chance to open a file without running macros, so you can examine the code first.
#8 – About the Author
Daniel Lajosbanyai – I work as a controller and accountant for a company with a bunch of international subsidiaries. In my daily job I work A LOT with Excel and my tasks are quite repetitive (and sometimes boring!)
To boost my processes and spare some time and energy, I started learning Excel Macros. My first teacher was a university professor, who showed me how to get started. I am really thankful to him, because without this knowledge I might have quit my job years ago.
Now I enjoy writing macros for every task I can automate and feel really happy to have learned this skill! Why should we do repetitive things, when our computers can do them quicker for us? We only need to learn how to give them instructions to follow!
- Excel VBA Tutorial: Understanding the difference between Range and Cells in Excel VBA
- Excel VBA Tutorial: Unlock the Secrets of Worksheet Code Names
- Excel VBA Tutorial – #2 Macro recording (including the difference between absolute and relative)
- Excel VBA Tutorial – #3 Running Recorded Macros Step-by-step