BInterest

As a Person of the Statics Background, while working in the office just thought of sharing some of the Excel Tips to the Readers of My Blog and would continue to do as and when i think it is worth learning while working in the Excel. Today would help you learn How to Record and then Implement the Macro in the Excel.

Record a macro is the simplest way to enhance the power of excel. And you do not have to know programming to make use of macros to enhance the efficiency of excel. We are going to record a macro that will format the cell with a number format that is not provided by any of the Excel icons. To make it complete, we need to create an icon and assign the macro to the icon, First let us show you how to record the macro and save it such that you can use it in all the worksheets.

1. Enter a number in the active cell.
2. Go to the menu, click on Tools –> Macro –> Record New Macro.
3. Enter a name for the macro e.g. Custom_Num_Format (note: spaces are not allowed), select to store the macro in “Personal Macro Workbook”.
4. Click on the right mouse button, select format cell.
5. Go to the Number Tab. Continued below :

6. Select the number category, check the box “Use 1000 Separator (,)”, select the negative number format with bracket and coloured red.
7. Click OK to exit Format Cell Dialog box.
8. Stop the Macro Recorder by click on the pop up box. Alternatively, goto to menu, click on Tools –> Macro –> Stop Recording.
9. Close the workbook without saving.
10. Close the Excel Application. Excel will prompt you whether you want to save the changes made to the personal macro workbook. Click Yes.
11. Re-launch Excel.

Now that you have created the Macro in the Excel, Next Step would be Run excel macro using an icon

1. Go to menu, click on tools, –> macro –> macros. When the Macro dialog box appears, select the macro and click on the run button.
2. Use a form button and assign the macro to this button.
3. Use a short cut key (e.g. Press the CTRL key together with a character) to run excel macro. You can set up the short cut key when you start recording a macro. If you have created the macro, you can set the short cut key by going to menu, click on tools, –> macro –> macros. When the Macro dialog box appears, select the macro and click on the option button.
5. In the following section, we will show you how to assign the macro to an icon so that the macro can be run by clicking on the icon. We will use the macro which we have created in the tutorial “record a macro” for this tutorial. Click here if you need to find out how to create the macro.

a. Click on the right mouse button.
b. Select Customize.
c. Go to the Command Tab, select the Category called Macros.
d. Go to list on the right side of the dialog box called Commands. Select Custom Button / Icon.
e. Drag it to the formatting toolbar.
f. To modify the button image, click on the modify selection on the Customize Dialog Box, go to change button image. Select your preferred image.
g. Go to modify selection again and select assign Macro.
h. Select the Macro named “Custom_Num_Format” and click OK.
i. Close the Customize Dialog Box.
6. Enter some numbers into the worksheet and test out your new macro.
7. Once you have done the above steps, you will be able to run excel macro using the icon.

Hope, Next time you work with the Excel, you would get your work done faster with the help of Macro in the Excel.

You can follow me on Twitter at http://twitter.com/binterest.Do stay tuned to Binterest.com for more, Best Way 2 do it, Subscribe to Binterest's Feed Updates