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:
Do the following steps to turn the developer tab on:
- First right click anywhere on the ribbon, and then click on Customize the Ribbon.
- Within the window of “Customize the Ribbon”, on the right side of the dialog box select the Main Tabs (if that should be necessary).
- Then check the developer check box:
- Then click on OK
- One can also find the “Developer tab” right next to the “view tab”.
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.
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”.
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:
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).
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:
And there you have it, congratulations! You have just created a macro in Excel.
This post originally appeared on – www.excel-easy.com/vba/create-a-macro.html
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.