Pages

Add an Excel Macro to a Workbook

This post is for absolute beginners who are new to Excel VBA macros. There are lots of useful vba macros readily available on the internet. Or sometimes you may receive such macros from your friends via emails. If you are an absolute beginner for VBA macros you might don’t know how to add those macros to your existing workbook. So in this lesson I will show you how to add such macro to your existing workbook.

Assume you received the following code from a friend via email.

Sub ListAllCombinations()

Dim counter As Long

counter = 1
For i = 1 To 4
     For j = 1 To 4
          For k = 1 To 4
               Range("E" & counter).Value = _
               Range("A" & i).Value & "" & Range("B" & j).Value & "" & Range("C" & k).Value
               counter = counter + 1
          Next k
     Next j
Next i
End Sub

What this code does is it lists all the possible combinations from columns A, B and C into column E. Above macro is only for the first 4 rows. Let’s assume this is your current workbook.

Existing excel workbook with data

Now let’s look at how to add that above code to the file. First you need to go to the “Developer” tab.

Developer tab in Excel ribbon

If the “Developer” tab is unavailable in your Excel application then read this post to see how to enable it.

How to show the Developer tab

Next click on the “Visual Basic” in the “Code” group.

Visual basic icon in the code group

It will take you to the VBA editor.


VBA editor

Next click on the insert menu.

Insert menu

Select module.

Select module

A new module will be added to the VBA Project.

New module in the VBA project

You can delete “Option Explicit” for now. Otherwise you will get errors if any of the variables are not declared. Then you can paste the macro inside this new module.

New excel macro inside the new module

Now the new macro is added to the existing workbook. If your existing file is a .xlsx file then you will need to save it as a .xlsm or any other macro supporting file format. Here are the different combinations you will get when you run the macro.

Result sheet after running the macro