Last updated on October 22, 2018 By Victor Chan
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!
Difficulty: Beginner
Download the Sample Workbook
Download the sample file with VBA code
Macro_Recording.xlsm (80 Kb)
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.
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:
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.
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()”.
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:
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.
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.
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!
Hey, I'm Victor Chan
Are you struggling with complex Excel tasks? Feeling overwhelmed by spreadsheets that are hard to use?
Many people believe mastering Excel is about learning shortcuts, functions, and formulas. But this overlooks the importance of building practical, real-world applications. It's not just about knowing the tools. It's about using them effectively.
That's where I come in. You'll get a unique perspective to Excel training from me. I have over 20 years of experience at Deloitte and two global tech companies. And I know what can make a difference in your career.
Let me help you integrate Excel into your professional life. Starting today. Read one of my articles, watch one of my videos. Then apply the new technique to your work. You'll see the difference immediately!
Recommended Posts
Discover the PROVEN Blueprint for transforming your Excel skills, supercharging your productivity, and standing out in your career! My course helps you to learn Excel VBA and save hours of time even if you have zero prior experience with programming.
Solve tricky Excel problems and take your work to the next level! Get customized solutions for your unique needs. Save time and gain insights with truly expert Excel solutions from only $97 per task.
Get a clear overview of your project progress using the Excel project timeline. Use it to communicate the big picture, track task progress, and stay on top of your project goals. Stay organized with our project timeline!
Our cheat sheets provide quick and easy reference to commonly used Excel VBA concepts and code snippets.
Unlock new levels of productivity and efficiency with our cheat sheets, and write VBA code like a pro in no time.
RECOMMENDED READING
Are you looking to upskill and stay ahead of the curve? Excel is a powerful tool that keeps growing in demand. We round up the best online courses for learning Excel.
Are you looking to up your spreadsheet game? Excel is an invaluable tool that can help you stay organized and save time. From data analysis to budgets, Excel can do it all!
Today, having Excel skills is more critical than ever. Those who know how to use Excel are more likely to find higher-paying jobs. And get promoted faster.