Write excel macros using VBA – A simple Guide

In this tutorial you will learn how to write a excel macro using vba codes (Visual Basic for Applications). This will be the foundation for a tutorial series that gives your essential understanding of how to use VBA applications and macros to make you a pro excel user. I have used Microsoft office 365 version of the excel to create this tutorial. Anyway there will not be any major differences compared to other versions.

Displaying the developer tab

In the last tutorial we stepped into the codes for the recorded macros to see them and modify in the way we want. That is one way of accessing the back end of the Excel Macros. But excel has a dedicated menu section for all the tasks and commands related to macros and add-ins management. It is called as the “Developer” tab.

By default, the Developer tab is hidden unless you or someone else has already activated it in your Excel application. You can easily display the Developer tab in the Excel Window following below steps.

1. Right Click anywhere on the Ribbon Area
2. Click “Customize the Ribbon…” command from the appeared contextual menu.
3. Excel Options dialog box appears with Ribbon Customization option.
4. Put a check mark for the “Developer” tab in the Right-side column.
5. Click “OK” to close to close the “Excel Options” dialog box.

Open the Visual Basic Editor (VBE) Window

We use Visual Basic Editor (VBE) window to write codes for excel macros. You may be already familiar with the VBE as we used it in this tutorial where we stepped into the macro code to see it and modify.

You can easily open the VBE window as given below,

1. Go to “Developer” tab
2. Click “Visual Basic” command button in the “Code” commands group.

Now the Visual Basic Editor (VBE) window appears.

Where to write vba codes for excel macro

You might wonder where to write VBA codes for the macro within after launching the VBE window.

Usually we write our vba codes for the macros within components called as “Modules”. You can have as many modules as you need. Let us see how to add new module to your vba project.

1. Select the required workbook from the “Project” window
2. Goto → Insert , Then choose “Module”

A new module will be added to the project.

1. Now double click on the new module within the “Project” window.

Vba “Code” window opens for the selected module. All vba scripts for your macro will be written inside this window.

Structure of the vba code for excel macros

1. Sub procedures

Commands or actions written in a sub procedure can be accessed by the user or by another macro. It will be listed within the macros list. You can use it as same as a recorded macro.

Structure of Sub procedure

Sub macro_name ()
<macro script>
End Sub

2. Function Procedures

Function procedure can be used to create custom worksheet functions. It accepts function arguments and returns a single value. The function procedures could be used in other vba scripts or as other usual worksheet functions. We created a custom worksheet function in this tutorial.

Structure of Function procedure

Function function_name ()
<function script>
End Function

Writing your first excel macro in vba codes

Let us now create our first sub procedure by writing vba coded or scripts. It always better to start learning a new programming language by saying Hello to world!

So, we are going to create simple macro which triggers a message box with “Hello World!” message.

Write the below code withing the code window for your module.

Sub HelloWorld()
MsgBox “Hello World!”
End Sub

Now save your file by clicking “Save” button and close VBE window.

Running the Excel macro

You can easily run sub procedure macros or macros written using vba editor the same way you run a recorded macro.

There are three ways run macro.

Method 01:

Goto “View” tab → Macros → select the required macro → click “Run”

Method 02:

Go to “Developer” tab → Macros → select the required macro → click “Run”

Method 03:

• Open the “Macro” dialog box following the steps in above methods.

• Select the required macro from macro list on the left.
• Click options button.
• Make sure that the cursor is located within the input box for entering the short cut key.

• Press a key from the keyboard that you would like to assign for your macro as a short cut key.

You must combine the shortcut key with “Ctrl” key to use the macro.

It is possible to assign a short cut key combined with “Shift” key also. In that case you must press the shortcut key combined with “Ctrl” and “Shift” keys to run the macros.

• Click “Ok”
• Now you can close the macro dialog box also.
• Press the short cut keys you just assigned.

You can run the macros using any of the above methods.It will run the macro which triggers appearing a message box with “Hello World!” message.