05 April 2012
Application Macros are one of the important feature in Office application and especially for Excel. In Macros, Visual Basic for Applications (VBA) is used very widely to simplify tasks in Excel and other highly used Office applications like Word and Powerpoint.
In Excel 2003, one can access the Macro features and settings from Tools -> Macro. But in Office 2007, where are the Application Macro features and settings as there is no tab as tool in the Office Ribbon?
These settings are hidden in the tab ‘Developer‘ and this Developer tab is not shown in the Office Ribbon by default and one has to check the change the settings in Office 2007 to view the Developer tab.
for more explaining link are:
Start by turning on the macro recorder and running through the tasks. When you turn off the recorder, Office Excel 2007 converts every click and key press into a series of commands and functions that are stored as a module in a workbook or in Excel 2007. The next time you need to perform the tasks, run the macro, and the commands and functions are performed automatically in the order in which they were recorded.
Because Excel 2007 is running the commands, tasks are performed at the speed of a computer and without interruption. Manually, a task could take 10 or more seconds, but by using a macro, a range can be formatted in a fraction of a second. The macro not only saves you time, it saves you from having to remember a lot of tedious details. With a macro, the formatting is exactly the same every time you run it.
Think about the tasks that you need to automate when you work with certain types of worksheets. For example, you could create a macro that quickly hides a group of monthly columns so that only the summary data shows. You could create another macro to show all of the details again. You could even create a macro to format cells so that long headings wrap around the cells. Create the macro
1. After deciding that you would like to create a macro to handle a repetitive task, start by running through the steps. For example, select a range, add a border, add a fill color, and change the font size. Also, think about how you will use the macro. In this example, you know that, in practice, you will run the macro after you make a range selection. So, when you create the macro, start recording after you have made a selection. Try to perform your task in as few steps as possible to save time. 2. When you are ready to record your macro, on the Developer tab in the Code group, click Record Macro, and enter the following information: * Macro name. Type a name that begins with a letter. * Shortcut key. Type a letter. You can run your macro by pressing Ctrl and this shortcut key. * Store macro in. Choose where the macro is saved: the current workbook, a new workbook, or your Personal Macro Workbook. If you choose the latter, the macro will be available any time you use Excel 2007. * Description. Type an optional description, such as whether you plan to make your macro-enabled workbook available to your students.
# Click OK, and the Macro Recorder starts. # Run through your steps, and then click Stop Recording. # To see what your macro looks like in Visual Basic code (optional), click Visual Basic. 1. Select one or more cells on a worksheet. 2. Press CTRL and the macro shortcut key, or on the Developer tab in the Code group, click Macros. Click the macro name, and then click Run. Office Excel 2007 performs the commands contained in the macro.
If you make a mistake while recording, you can delete the macro and start over, or if you understand the basics of programming with Visual Basic, you can modify the macro code. The following figure shows part of the macro code that was used to format the table above, open in the Microsoft Visual Basic Editor. The section of code on the right (that starts with Sub FormatSelection) adds a color and border around a range of cells. Keep in mind that you are not required to work with macro code to record and use macros, because Office Excel 2007 creates all of the code for you.