Create Macro

“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?

 
 

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”

 
 
Ribbon Customize

 
 
2. In the dialog that appears you will see a list of tabs on the right hand side. Ensure that “Developer” is checked on.

 
 
CheckDeveloper

 
 

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.

 
 
Click Macro Button

 
 
You security settings should match the settings in the screenshot below

 
 
Excel VBA

 
 

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.

 
 
VBA Macro Enabled

 
 

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

 
 
Visual Basic Icon

 
 

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

 
 
VBA Project Window.

 
 
Right click on the workbook where you wish to place the code. Select “Insert” and then “Module”.

 
 
CreateModule

 
 
a new module  will appear:

New Module

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

 
 
MainWindow VBA

 
 

Step 6 Create a Macro

Type “Sub MyFirstMacro” in the main code window and press return. You should have the following

 
 
Create a Macro

 
 

Step 7 Add Some Code to Your Macro

Use MsgBox “Hello World”. This will display a dialog with “Hello World” to the user

 
 
Sample VBA Code

 
 

Step 8 Run Your Macro

To run your macro:

  1. Click anywhere inside the Macro
  2. 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.

 
 
Sample VBA Code Run

 
 

You should get a dialog like this

 
 
Sample VBA Code Run 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 might like to try our other popular posts such as The Complete Guide To Workbooks in Excel VBA or click  here to view all the posts on this site by category.

 
 

Get the Free eBook

How To Ace the 21 Most Common Questions in VBA

Please feel free to subscribe to my newsletter and get exclusive VBA content that you cannot find here on the blog, as well as free access to my eBook, How to Ace the 21 Most Common Questions in VBA which is full of examples you can use in your own code.

Free VBA eBook