What is a Macro and How to Create a Macro?

• A Macro is a piece of programming code that runs in an Excel environment, which is used to help in automating Daily routine tasks.
• In another word, we can say macro is a recording of our regular steps in MS excel, which can be run by using a single button on a daily basis.
• We can create a Macro by writing a line of codes in VBA Editor.

Create A Macro | Record A Macro | Run The Recorded Macro | Check The Macro Code

Adv: With the help of macros, we can get error-Free Reports / Dashboard or output with a very minimal time and without manual intervention. By using Macro we can also save the Manpower cost and human error.

Now we will create an excel macro in a few steps which have summarized below.

Create an Excel Macro

Step 1: Click on the Developer tab (This is required to create a Macro in Excel VBA). 
(opens in a new tab)

Note: if Developer Tab is not visible in your Menu Bar, please click here to know how to enable this.

Step 2: Now click on insert caption and then from ActiveX Controls we will click on the Command button and draw in Excel Sheet.

Step 3: Right-click on the CommandButton1 and now click on the View Code Option.

Step 4: Right-click on the CommandButton1 and now click on the View Code Option.

Once the VB Editor window is visible write the one line code as MsgBox “Welcome to world of VBA”

Step 5: Press Alt+F11 key and then disable the Design Mode Caption under Developer Tab and then Click on CommandButton1.

Once you clicked on the CommandButton1 one Message Box Popup will Appear as below.

Congratulations!! Now you have learned to create an Excel macro in VBA Editor!

Alternative way for Create a Macro by using Record Macro features in Developer Tab.

Create a Macro By using Record Macro Option under Developer Tab

A Macro Recorder is a very useful tool Which is available in Excel VBA, it will record every task which we will perform in Excel. only we have to do is record a specific task only one time. Next, whenever it’s required we can run the task with a single click of a command button. The Macro Recorder is also a great feature that will help us when we don’t know how to program a specific task in Excel VBA.

Once recording has been completed we can Simply open the Visual Basic Editor to see how it has programmed in the lines of code.

By the way, there are a lot of things that we cannot do with the Macro Recorder. For example, we cannot loop through a range of data with the Macro Recorder. However, the Macro Recorder also uses a lot of code then’s required, which may slow our process down.

Steps for Record a Macro

#1. Under the Developer tab, click on Record Macro.

#2. Below Dialog Box will appear in that we can see Macro Name is “Macro1” is reflecting by default and make sure This Workbook should be selected in the drop-down list. As a result, the recorded macro will be available only in the current workbook.

Note: If we will store our macro in Personal Macro Workbook, then macro will be available in all our Excel workbooks. This is because Excel will store our macro in a hidden workbook that will open automatically whenever the Excel application starts. If we will store our macro in New Workbook, then that macro will be available only in a newly opened workbook.

#3. Click on OK Button.

#4. Do Right click on the active cell, Make sure no another cell is selected ! Next, click on Format Cells.

#5. Click on the Format Cell and select the Percentage under the Number Tab.

#6. Click on OK and then click on Stop Recording which is available under Developer Tab.

Well Done!! We’ve just recorded a macro by using Macro Recorder Features!

#6. Click on OK and then click on Stop Recording which is available under Developer Tab.

Run the Recorded Macro

So as we have recorded our first Macro now we will test the macro to check whether it’s changing the value from number format to Percentage.

#1. Enter few numbers between 0 and 1 then we have to select all the values which we have to convert into decimal form.

#2. Go-to the Developer tab, then click on Macros or Press Alt+F8 key to view the Recorded Macro.

#3. Now Click on Run Button and so we can see now all the decimal values converted into percentage formats.

Check the Macro Code:

So now if we want to check the recorded Macro code press the Alt+F11 shortcut key and in Project Window, we have to click on the ‘+’ icons which are available on the left side of the Modules folder, and then double click on Module1 and then the recorded code is visible.

Note: Recorded Macro got saved into a module which is called Module1. We can use this module code in any sheet of this workbook.

Finally, we have to save this workbook in Macro-Enabled Workbook for future use so Click on File Menu Next click on SaveAs option, then in Save as type: Select Excel Macro-Enabled Workbook and as per our convenience, we can put up the File Name then save this file in the computer.

Congratulations!! We have learned How to Create a Macro, How to Run a Macro & How to Record a Macro in Excel VBA.

Next Chapter: Macro Comments