Creating A Simple Macro and Add-In

What are Macros and Add-Ins?

Macro is a set of commands which can be executed anytime to perform a task. Recording or writing macro is very useful if you perform a task again and again on similar type of data. For instance if you extract data in excel from your data source daily and apply some manipulation on it, then you can once record the macro and next time whenever you extract data and run the macro, it will manipulate data as defined. Macro code resided in Microsoft Visual Basic module and the Excel file is saved as “Excel Macro-Enabled Worksheet“. You can record or write macro in MS Excel 2013, 2010, 2007 and other versions.

Macros option needs to be enabled every time, when you want run a macro. To avoid enabling macro and giving it permission to run in all workbooks, you need to save the Macro as “Excel Add-In“. Once saved, you can add it to Microsoft Excel. Now it will be available for every Excel Workbook in your system without enabling Macro.

You can get many useful Macros and Add-Ins for our free Macros and Add-Ins Tools section.

Recording or Writing a Macro:

A non-programmer can also record a macro and programer can make best use of it. Here we will learn how we can record or write macro step by step. You can perform these steps in Excel 2010, 2013, 2007 and 2003. Snapshots are taken from Excel 2007.

Recording Macro:

Step#1: Open a new Excel file and click on “View Menu“. You will find the “Macros” tab at the end in toolbar. Click on Macros button (Drop-down) and then on “Record Macro“.

Step#2: Here “Record Macro” dialog box will open, where you can give your macro a name and assign a shortcut key. Now press ok and a recording has started in the background. Whatever activity you do now, same will be recorded and later you can repeat it again and again. For a short test you can type a list of few names in column A.

Step#3: Stop recording now by clicking on stop recording button in macro drop-down option.

Step#4: Now we will check if the recording is working. Clear all the names you have typed in column A.

Step#5: Now go to Macros Drop-down button and click on “View Macros“. You can see your macro here. Click on Run button and see the all names are here again. Macro has repeated the task you performed while recording.

Every macro file needs to be saved as Macro Enable Workbook to keep you macro in it. Next time if you will open this file and run the macro, you would required to enable macro first.

Writing Macro:

If you are a comfortable with VBA or other programming language, you can easily write macro code. Press ALT + F11 to open Microsoft Visual Basic Editor. To start writing a Macro, you need to define the start of module and end of module first:

Sub Macro_Name()
'Your Code Here
End Sub

Here is a very simple code of macro that will open message box when run.

Sub opendialog()
MsgBox "This is a message."
End Sub

You can simply add a button to run macro or assign a shortcut key to run. Always use Macro and Add-Ins from trusted resources because these can harm your system / data.