Excel VBA Tutorial – #1 Getting started in the Excel VBA Editor

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!

So, to get started with Macro coding we first need an introduction to the Visual Basic Editor.

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.

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.

We now have our code module, in which we can write and store our first Macro.

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.

“End Sub” at the end represents the last piece of the program code: this is where the macro code finishes.

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:

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:

This is our first self-written program code! Let’s see what it does when executed!

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

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:

The red text will show you exactly where you made a mistake – if you have more lines of program code, this comes in handy so you don’t have to search the whole module.

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.

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.