“The secret of getting ahead is getting started” – Mark Twain.
This post covers how to Create a Macro in Excel. Even if you have never created one before you will find this post simple to follow.
Let’s start by having a quick look at, what are Macros?
Contents
- 1 What are Excel Macros?
- 2 What is VBA?
- 3 Step 1 Make the Developer Tab Visible
- 4 Step 2 Check Your Security Setting
- 5 Step 3 Save as a Correct File Type
- 6 Step 4 Enter the Visual Basic Editor
- 7 Step 5 Create a Module
- 8 Step 6 Create a Macro
- 9 Step 7 Add Some Code to Your Macro
- 10 Step 8 Run Your Macro
- 11 Conclusion
- 12 What’s Next?
What are Excel Macros?
The term “Excel Macros” causes a lot of confusion. In a nutshell, Excel Macros refers to the programming language VBA that comes built-in to Excel.
When the terms Excel Macros or VBA are used they are referring to the same thing. Generally speaking, somebody new to VBA will use the term Macros and once they become familiar they use the term VBA.
What is VBA?
VBA is the programming language, Visual Basic for Applications. This language comes pre-installed with Excel. It is very powerful and allows you to extend the functionality of Excel.
VBA is also packaged with all other Microsoft Office application such as Microsoft Word, Access and Outlook.
In the following sections, we are going to create a Macro from scratch. By Macro we mean a Sub.
A Sub(short for Subroutine) is simply a collection of lines of VBA code that perform certain tasks. Any VBA application you create is a essentially collection of Subs.
So now we understand the background, let’s go ahead and create a macro.
Step 1 Make the Developer Tab Visible
The Developer Tab is normally visible by default on your ribbon. If not, you can make it visible using the following steps:
1. Right click on the ribbon. In the list that appears, select “Customize the Ribbon”
2. In the dialog that appears you will see a list of tabs on the right hand side. Ensure that “Developer” is checked on.
Step 2 Check Your Security Setting
The next step is to ensure your Macro settings are correct. On the Developer Ribbon, Click on the “Macro Security” button under the “Code” section.
You security settings should match the settings in the screenshot below
Step 3 Save as a Correct File Type
If you workbook has Macros then you must save it as a Macro Enabled Workbook. It is a good idea to do this in the beginning so as to avoid the potential lost of code.
Select File->Save As and choose “Macro Enabled Workbook” from the File type. Then click on Save.
Step 4 Enter the Visual Basic Editor
To enter the Visual Basic editor click on the Visual Basic icon on the Developer tab of the Ribbon. Alternatively you can press Alt + F11 on the keyboard
Step 5 Create a Module
Macros are stored in Modules so create a module.
Look in the VBA Project Window. If the window is not visible the select “View” ->”Project Explorer” from the menu (shortcut Ctrl key and R).
Select the workbook where you want to place the code. The workbooks will be in parenthesis beside “VBAProject”. Each open workbook will have an entry like this in the VBA Project window
Right click on the workbook where you wish to place the code. Select “Insert” and then “Module”.
a new module will appear:
When you double click on a Module in the VBA Project window the code of that module is displayed in the main code window. When a new module is created it normally doesn’t contain any code(note it may contain “Option Explicit” if you have this option turned on).
Step 6 Create a Macro
Type “Sub MyFirstMacro” in the main code window and press return. You should have the following
Step 7 Add Some Code to Your Macro
Use MsgBox “Hello World”. This will display a dialog with “Hello World” to the user
Step 8 Run Your Macro
To run your macro:
- Click anywhere inside the Macro
- Select Run->Run Sub/UserForm
Note: If the cursor is not inside a Macro then VBA will display a list of available Macros and ask you to select one to run.
You should see this dialog
Conclusion
This post showed you how to get up and running using VBA. It went through the steps for setting up correctly and ended with you running a macro.
You are now ready to get started with VBA/Macros. You may want to check out The Ultimate VBA Tutorial which is aimed at the complete beginner.
What’s Next?
Free VBA Tutorial If you are new to VBA or you want to sharpen your existing VBA skills then why not try out the The Ultimate VBA Tutorial.
Related Training: Get full access to the Excel VBA training webinars and all the tutorials.
(NOTE: Planning to build or manage a VBA Application? Learn how to build 10 Excel VBA applications from scratch.)
Hi Paul:
I am enjoying (and learning a lot; I’m a bit of a newbie when it comes to VBA) your posts on Workbooks, Worksheets and Cells. Being a mainframe programmer for more years than I will admit, I like to ‘write once and save’ code, so that i can reuse it where appropriate. So… I’ve been saving my Subs and Functions in “Personal.xlsb”. Now, I realise that references like “thisworkbook” refer to the startup .xlsb, regardless of which workbook I have open (or not) at the time. I think I’ll be needing to specifically identify any workbook I plan to use, throughout the VBA code, so that I can accurately switch focus, yes?
Do you have any comments on the use of “Personal.xlsb”, positive or negative? Any hints on moving forward when designing a project?
Thanks in anticipation of your response!
Best,
Norman
Hi Norman,
Thanks for you comment. Glad you’re getting benefit from the site.
Regarding the personal.xlsb file. They are quite useful for common macros that you would use a lot. For example, if you had a macro to check if a workbook contained a worksheet with a given name. These type of macros would take the workbook as a parameter so you don’t need to specify a particular workbook.
If you write code for a specific function e.g. create a report from data in a workbook, then that code should be in its that workbook.
Regarding design. This is a large area. VBA is an object oriented language and so getting familiar with the basics of OO Design is useful.
In VBA you use class modules for classes and when these are used at runtime they are called objects.
Another hint is to break down a project into tasks and get these parts working before moving to the next. I normally start with the output e.g. a report and write dummy values. Then I work backward.
I hope this helps. If you have more queries feel free to email me at PaulKellyKK@gmail.com
Regards
Paul
Hi Paul,
Thanks for a great site. I’m starting my Excel VBA journey. Could to talk about how you decide what variables you need to declare at the beginning of a project? How many you need, what type etc? Basically the steps you go through in initial planning. ie Do you declare all the cells? Declare the worksheet? I’m trying to get my head around the methodology. Using a simple example A1 + B1 =C1, in excel I just sum the A nd B cells. In VBA would I declare all 3 cells?, the worksheet? Interested to hear you thoughts.
Thanks again.
Hi Dale,
Normally you declare variables as you need them. When starting a project you break it down into smaller tasks to make your life easier. The main types you use are Long(integers), Double(decicmal), String(text) and Date.
If you want to add cells values you can do it like this:
You can write between cells without using variables. You normally use variables for storing and manipulating temporary pieces of data when you application is running.
The following post explains using ranges and cell in detail: The Complete Guide to Ranges and Cells in VBA
Paul
Dear sir,
This is parthu, I am glad to said to u .your site is very good ,could you please provide the entire material of the
vba macros
good afternoon;
will you help me place futures data into excel? hoping to build trading chart.
Paul,
I have a complex VBA / macro written by someone else and I’m trying to correct it. its been 20+ years since I took a VBA course. Are there any help forums that I could post the Macro in and get some advice? I don’t know what codes to use to make it do what I need.
I’m on a Mac, and trying to get started with this. Took me about 30 minutes just to work out how to open the Visual Basic editor. I believe the above instructions are hard to follow to get the developer tab up… ended up Googling this, and found that for Mac need to just go to preferences under “Excel” in the top left. Right clicking the ribbon didn’t work for me.
Are these courses and instructions Mac friendly or do we need to get another computer?
Hi Jacqueline, This website is based on Excel for Windows.