web analytics

CREATE AND RUN MACROS IN MICROSOFT EXCEL 2007

Microsoft Excel 2007

To automate a repetitive task, you can quickly record a macro in Microsoft Office Excel. After you create a macro, you can assign it to an object (such as a toolbar button, graphic, or control) so that you can run it by clicking the object. It will make you very efficient by giving you the option to done your operations just by clicking one button, which also can be set for changing the cell value, or even for opening a workbooks. This is a very smart way to save your working time while processing some complicated operations. You will significantly decrease your manual work and reduce the time needed for the work completion. You can also create a macro by using the Visual Basic Editor in Microsoft Visual Basic to write your own macro script, or to copy all or part of a macro to a new macro. If you no longer use a macro, you can delete it.

1. Recording a Macro

  • First, open one excel workbook.
  • Second, go to the Developer tab of the workbook.

01

If the Developer Tab is unavailable for using in your workbook, click the Office Button in the upper left corner of the Excel workbook.

02

Then, select the Excel Options button in the bottom of the dialogue box.

03

In the Popular category, under Top options for working with Excel section, select the Show Developer tab in the Ribbon check box, and then click OK.

04

  • Third, Click on “Record Macro” button in Developer Tab.

05

The same procedure can be started by clicking the Macro Button which is located in the bottom of the workbook.

06

 

Now, in the Record Macro Dialogue Box you can enter an adequate name for this macro. You can assign a shortcut key for the recorded macro. This tells you that when the assigned short cut key is pressed, the given macro starts automatically. You can specify the location where the recorded macro should be saved, and you can enter description text for the macro.

07

Naming the macro involves adherence to certain rules. The first character of the macro has to be a letter, and the other characters can be letters, numbers or underscores. Spaces are not allowed to be included in the macro’s name. The underscore character could be used as a word separator.

From the Store macro in drop down list choose the workbook where you will store the macro.

Select Personal Macro Workbook to make the particular macro available every time you start Excel. If Personal Macro Workbook is selected, Excel will create (if it is not already created) and hide the personal macro workbook named “Personal.xlbs” which will be saved in this workbook.

If you want to describe the macro, type the description text in the Description Box and hit OK.

Now, do some formatting, value sorting or other action in the Excel worksheet.

08

To Stop the recording of the macro, click the Stop Button located at the same spot where it was the button for running the Macro.

09

To see the recorded macro, right-click on some of the Sheets, and choose the View Code Tab.

10

In left hand side pane, under VBA Project of that workbook, Expand the Module. Double click on Module1. You can see the recorded code with the given Macro Name.

You can Run the Recorder Macro by Pressing the Run Button, or by Pressing Alt + 8 keys.

2. Running the Macro

Right click on empty (new) sheet from the same workbook and click the View Code Tab. Click on Module1 twice and click the Run Button.

11

In the Macros Dialogue Box click the Run Button.

12

You can do this operation repeatedly in every sheet from the workbook if the macro is recorded.

13

Follow the next steps if you do not want to activate the macro by clicking the Macros Button from Developer Tab.

  • Press Alt + F8,
  • Select your Macro from the Drop Down,
  • Click on Run.

3. Deleting the Macro

To Delete Macros, select the Macros Button in the Developer Tab.

14

Next, select the macro you want to delete in the Macro Dialogue Box. In ‘Macros In box‘ select some of the macros lists to find the macro you want to be deleted. There are several macros lists that you can choose from, such as All Open Workbooks or This Workbook. You can choose a single macro for deletion. Press the Delete Button to delete the selected macro.

15

When the message about deleting the macro appears in the Microsoft Office Excel Dialogue Box, click on Yes to delete the selected macro. Pressing No will return you on the previous step.

4. Assigning the Macro

You can Assign Macro to an object, graphic or control.

16

To assign an existing macro to object, control or graphic, right-click on the desired object and select the Assign Macro Tab. Select the preferred macro you want to assign from the Assign Macro Dialogue Box and press the OK button. Clicking the object that has assigned macro to it will perform the assigned macro’s operations.

Leave a Reply