How to Create a Macro

In this article, we are going to learn how to create a Macro which will work after clicking on a command button. As we all know that Visual Basic Editor is a way to pass our message to Excel in the language which Excel can understand.

First and important thing is whatever we are going to learn we should know that what is the use of it and how we can use it?

Let’s say if we are going to create a command button, we can use it to give the message, to calculate the fields as per the requirement etc. Now, we will learn how we can create a command button using Macro and how we can use it.

To create a command button to calculate the value of the range A1:A2 in cell A3, we need to follow below given steps:-

  • In the Excel Sheet, go to Developer tab
  • Insert Command button (Active X Control) from the group of Control

image 1

  • Then drag the image of Command Button
  • Right click with the mouse on command button and select View Code
  • VBE (Visual Basic Editor) will get open, and in the code window, write the below mentioned code:

Private Sub CommandButton1_Click()
Range(“A3”).Formula = “=Sum(A1:A2)”
Range(“A1”).Select
End Sub

image 2

  • When we will click on command button, formula will calculate the numbers to the range A1:A2 in cell A3 and then will select the cell A1

image 3

We can also use the command button to display the title name as well. Let’s learn with a simple example.

  • Insert Command Button by following the given steps: Go to Developer Tab > Control > Insert > Command Button (ActiveX Control) and drag it.
  • Right click with the mouse on command button and select View Code.
  • VBE will get open, and in the code window, write the below mentioned code:

Private Sub CommandButton2_Click()
Range(“B1”).Value = “Product1”
Range(“B2”).Value = “Product2”
Range(“B3”).Value = “Grand Total”
End Sub

image 4

  • Click on the Command Button 2, and title will start displaying

image 5

This is all about how to create a Macro in Microsoft Excel.

Previous Chapter Next Chapter

Leave a Reply