December 4, 2020

How to write Macro in Excel

If there is any repetitive tasks you need to perform in excel, you can create a Macro for the same which is used to automate the task. With macro your keystrokes and mouse clicks are recorded. You can run a macro any number of times.

To create macro in Excel first you need Developer tab.

  • To get the developer tab right click anywhere on the ribbon and then click ‘Customize the Ribbon’.

  • Then in the excel options pane select ‘Developer’ available in the ‘Main Tabs’ or ‘All Tabs’ in ‘Customize Ribbon’. Now click OK.

  • Now as you can see below the Developer tab has been appeared in menu bar.

  • Now as part of creating your first macro in excel, you may create a command button in your spreadsheet by executing the below steps.
    1. In Developer tab click Insert.
    2. Then under ActiveX Controls click the icon for Command Button.

3. Now you place the command button in the desired area in your spreadsheet by dragging your mouse cursor. You can resize and move your command button as and when required by just dragging the same.

  • Now you need to assign a macro to your command button just created. To do that follow the below steps.
    1. Right click on the command Button and then click ‘View Code’.

2. The Visual Basic Editor will appear. Add the below code  between Private Sub CommandButton1_Click() and End Sub.

Range("A1").Value = "Congrats"
Range("A2").Value = "My First Macro is Here"

3. As you can see in the left panel under ‘Project-VBAProject’ the current worksheet(here Sheet 33) has been selected where your command button is currently placed. So your macro code you have just written will be applicable on this worksheet.

4. Now close the Visual Basic window. Then go to your worksheet and make sure the Design mode is NOT selected. Then click the Command Button. As per our macro code, the respective strings have been added automatically in cell A1 and A2 as follows.

  • Now we will learn to write a macro code to swap these 2 values. Like currently below are the data in 2 cells.

A1 = Congrats

A2 = My First Macro is Here

Now we want with each press of command button the data will be swapped between the cell A1 & A2.

  • To do that first open the Visual Basic editor by clicking the Visual Basic icon available in Developer tab

  • Now add the below code  between Private Sub CommandButton1_Click() and End Sub in Visual Basic editor. Then Close the Visual Basic Window.
temp = Range("A1").Value
Range("A1").Value = Range("A2").Value
Range("A2").Value = temp

One more point you must noticed, at the top right side in the Visual basic editor there is a drop down. Currently by default ‘Click’ has been selected there. It means the code you are writing will be triggered with a click on the command button. There are other events available for you to select as per your requirement.

  • Now in your worksheet first ensure the Design mode is NOT selected. Then click the Command Button. You can see with each click the values in cell A1 and A2 are getting swapped.

No let’s take another example to see how the macro can be recorded and saved and run.

We have the below data set containing the count of Flu infected patient for a certain year in couple of US states.

Now we are going to perform some set of actions in my spreadsheet with the below data to find the state with highest No. of Flu infected patients. Before doing that we need to record the actions in a macro. 

  • So we first clicked ‘Record Macro’ available in ‘Developer’ tab.
  • In the record macro pane we have added our macro name as ‘FindHighest’ and clicked OK.

  • Now we have executed the below steps.
    1. Select Column B
    2. Go to Home tab
    3. Click Sort & Filter
    4. Click Sort Largest to Smallest

5. Select ‘Expand the selection’ and click Sort

6. Now the state with the highest count of Flu infected patients will come at the first row of the table. Highlight the row in Home tab as we have highlighted with yellow below.

  • Now again go to Developer tab and click ‘Stop Recording’

  • Now in Developer tab you can see a icon named ‘Macros’

  • The icon named ‘Macros’ is also available in View tab as well. Click on it.
  • You will see the list of macros available. You can control if you would like to see the list of macros available in the current spreadsheet or in the entire excel or in all open workbooks  from the ‘Macros in’ drop down at the bottom on the pane.
  • Now select the Macro name you want to run now. Here we have selected the macro ‘FindHighest’ which we have just created. And then click Run.

  • You will see all the above mentioned steps we performed to find the state with highest count of Flu infected patients till highlighting the first row, will be auto executed.
  • Now if we need to run this macro frequently without going to Macro pane each time to select and run the macro from there, we can create a button in a different alternate way than we have shown at the very first of this lesson.
  • Go to Insert tab. Click the icon for ‘Shapes’ under ‘Illustrations’ group. Now select the desired shape of your button.

  • Drag it anywhere in the current spreadsheet to create the button. Then right click on it.
  • You will see there are various format options appeared to change the color and style as per your choice.  To add/update the text inside button, click ‘Edit Text’

  • We have entered the name as ‘Invoke Macro FindHighest’ inside the button. 
  • Now again right click on the button and click ‘Assign Macro’

  • Now in the Assign Macro pane you can select the desired macro to assign with the button you created and then click OK. Here we have assigned our macro ‘FindHighest’ to the button. There is an Edit button to edit the macro code in Visual basic Editor if needed.

  • Now if you click on the button, the set of operations to find the state with highest count of Flu infected patients till highlighting the first row, will be auto executed.

Leave a Reply

Your email address will not be published. Required fields are marked *