top of page

Macros in Excel

  • In Microsoft Word and other programs, a macro is a saved sequence of commands or keyboard strokes that can be stored

  • And then recalled with a single command or keyboard.

  • The file extension of a macro is commonly .MAC.

  • A macro is used to replace a repetitive series of keyboard and mouse actions.

  • We can create a simple macro which will be executed after clicking on a command button.

  • First, turn on the Developer tab.

Steps for Macros

Step 1

  • First, enable 'Developer' menu in Excel 20XX. 

  • To do the same, click File > Option.

1
2

Step 2

  • Click Customize the Ribbon tab and check Developer check box.

  • Click OK.

1
2
3

Step 3

  • You can find the Developer ribbon appears in the menu bar.

Step 4

  • To place a command button on your worksheet, execute the following steps.

  • Click Insert on the Developer tab.

  • Click Command Button in the ActiveX Control group.

  • Drag a command button on your worksheet.

2
1
3

Step 5

  • To assign a macro (one or more lines) to the command button, execute the following steps.

  • Right click on CommandButton1 (Make sure design mode is selected).

  • Click View Code.

Step 6

  • The Visual Basic Editor appears.

Enter your code here

Step 7

  • Place your cursor between private Sub CommandButton1_Click() and End Sub.

  • Add the code line shown below.

Step 8

  • Close your visual editor.

  • Click on the CommandButton1 in the sheet. (Make sure Design Mode is deselected).

Click the button to execute the code.

Step 8

  • To open the Visual Basic Editor, on the Developer tab, click Visual Basic.

Step 9

  • You can coding by simply adding a message.

MsgBox "Hi"

Step 9

  • Close the editor and click the CommandButton1 sheet .

  • The pop-up window will show a message.

Record A Macro

  • You can record every task you perform with excel.

  • Next, you can execute the task over and over with the click of a button.

  • It is an advantage when you don't know how to program a specific task in Excel VBA.

  • Here are some steps for Macro Recording.

  • Macro recording is slow because it records more code than it is required.

  • You can not loop through a range of data with the Macro Recorder.

  • Record Macro:

  • On the Developer tab, click Record Macro.

1
2

  • Set a name to macro:

1
2
3
4

Enter a name

  • Stop recording macro:

  • After some task, you can stop the macro recording your activities.

  • Macro records all the mouse and keyboard movement and activities.

  • You can click Stop Recording in the ribbon.

  • Run a Recorded Macro:

  • Now you can test the macro to see if it can do the same task.

  • You can press the shortcut assigned to Macro.

  • It will run after you clicked the Macro > View Macros > MacroName.

bottom of page