We can programmatically control the features of ms-office application using VBA. This customization is done in Visual Basic 6 editor which is built-in feature of all office application like MS Excel. We use Visual Basic language to write programming code. Any piece of code written in VBA is called a MACRO.
Whenever we need to work with Excel objects, we shall write a macro to achieve our goal. What we can we do with a macro? Anything which we can do visually, we can also do that by writing code. For example we can turn font into bold face using CTRL+B key. We can also do this by writing a macro. In addition to this we can do much more with VBA. Excel has a built-in mechanism to record macros. Recording a macro means that excel creates corresponding VBA commands of our visual actions. These commands become procedures in VBA. We can run these procedures (MACORS) by a short key or by pressing button. For section explains how to record a simple macro in excel 2007.
First you need to turn own Developer tab in excel. Click on round Microsoft office button of top left corner of excel workbook. And click on Excel Options button. Excel options window will open. Here click on “Show developer tab on ribbon” checkbox as shown in figure below. Our required option is red circled in figure below.
Turn on Developer tab in excel
Now you can see that developer table is visible in ribbon. Click on developer tab and click on “Record macro” as shown in figure below (Red circled).
Record Excel Macro
A “record macro” dialog box will appear. Name your macro as “MyfirstMacro” and type “r” in short key box and click OK button. After recording this macro you will be able to run this macro using CTRL+r key. As shown in figure below.
Macro recorder Dialog box
Now click on cell B2 and type RED, GREEN in cell C2 and BLUE in D2. And click on stop recording button (Red circled in below figure).
Stop Macro Recorder
We have recorded a macro that can fill B2,C2,D2 cell with basic colors. Now simply click on sheet 2 and press CTRL+r key. You will see that B2 ,C2,D2 cells of second sheet have been filled.
This was a very simple macro. Practically we shall have complex macros doing really tricky and beneficial things dealing with cells, charts and worksheet.
Now on developer tab, press on “Visual Basic” which is left to place where you clicked to record macro. Visual basic editor will open. Here expand modules on left side and double click on module1 icon. This will show command recoded by this macro as shown in figure below.
VBA code for Macro
Now you can save you sheet with .xlsm extension. You cannot save an excel sheet with .xlsx extension which contains a macro. Next time when you open your saved file, you will see a security warning from Excel that macros have been disabled. As a result of this you can no more run macros in this sheet. To resolve this click on option button next to security warning and click on “Enable this content” option button. Or you can add you folder (you excel macro file) as trusted location by going to Excel option.