How to Create a Macro With MS Excel

How to Create a Macro With MS Excel

Did you know that with Excel you can now automate tasks by writing so called programs macros. In this tutorial, we will learn how do so, by learning to create a simple macro, which will executable after clicking a command button. To begin you must first turn on the developer tab:

 
How to Create a Macro With MS Excel
 

Developer tab:

Do the following steps to turn the developer tab on:

 

  1. First right click anywhere on the ribbon, and then click on Customize the Ribbon.

 

Photo Coutesy of excel-easy.com

Photo Coutesy of excel-easy.com

 

    1. Within the window of “Customize the Ribbon”, on the right side of the dialog box select the Main Tabs (if that should be necessary).
    2. Then check the developer check box:
       
      Photo Coutesy of excel-easy.com

      Photo Coutesy of excel-easy.com

 

  1. Then click on OK
  2. One can also find the “Developer tab” right next to the “view tab”.

 

Photo Coutesy of excel-easy.com

Photo Coutesy of excel-easy.com

Using the command button:

For placing a command button on your worksheet you must follow these below mentioned steps:

 

Step 1: when on the Developer tab, click Insert.

Step 2: then go to the ActiveX Controls group and click on Command Button.

 

Photo Coutesy of excel-easy.com

Photo Coutesy of excel-easy.com

 

Step 3: Then drag the chosen command button on your worksheet.

How to assign a macro:

In order to assign a macro to a command button, follow the steps mentioned here.

With the design mode selected, right click on the CommandButton1. And then click on “view code”.

 

Photo Coutesy of excel-easy.com

Photo Coutesy of excel-easy.com

 

This will turn on the visual basic editor:

The place the cursor between the Private Sub CommandButton1_Click() and End Sub.

Then you have to add the code mentioned in the line shown below:

 

Photo Coutesy of excel-easy.com

Photo Coutesy of excel-easy.com

 

The window on the left with the names Sheet1, Sheet2 and Sheet3 is called the project explorer window, and if not visible can be opened by clicking on the View, Project Explorer. For adding the Code window for the first sheet, click Sheet1 (Sheet1).

 

 Read Also : DexLab Analytics – Training the Future to be Big Data Analytics Fluent

Then you may close the Visual Basic Editor.

Finally, click on the command button on the sheet (make sure Design Mode is deselected).

The output is shown below:

Photo Coutesy of excel-easy.com

Photo Coutesy of excel-easy.com

 

And there you have it, congratulations! You have just created a macro in Excel. 

 

 

Interested in a career in Data Analyst?

To learn more about Machine Learning Using Python and Spark – click here.
To learn more about Data Analyst with Advanced excel course – click here.
To learn more about Data Analyst with SAS Course – click here.
To learn more about Data Analyst with R Course – click here.
To learn more about Big Data Course – click here.

Dexlab