Monday, March 2, 2009

Recording Excel Macro / Writing Excel Macro (VBA)

A macro records your mouse clicks and keystrokes while you work and lets you play them back later. You can use a macro to record the sequence of commands you use to perform a certain task. When you run the macro, it plays those exact commands back in the same order, causing Excel to behave just as if you had entered the commands yourself.

If you are recording macros for the first time make you "Visual Basic" toolbar visible as it comes handy for recording.

You need to go to "View -> Toolbars -> Visual basic" Once you have made it visible it will look like as seen in picture below:-

Once you have this visible you are ready to record a excel macro.
Click on to start recording.
Once you have clicked , system will prompt with Record Macro dialog box as seen below.

Here the available fields can be changed by user. Macro Name can be any combination of key strokes of your choice. Keep the name you can relate it for job you want your macro to do. Once you click OK button, system is ready to start recording you actions and following toolbar appears on your screen to stop macro recording

Now you perform the task you want you macro to repeat,
Before you record or write a macro, plan the steps and commands you want the macro to perform. If you make a mistake when you record the macro, corrections you make are also recorded. When you record macros, Visual Basic stores each macro in a new module attached to a workbook.

Once you are through with all the steps , you need to stop recording by clicking on . Now your macro has been created by Excel using a programming language called Visual Basic® for Applications (VBA) to record your instructions. You don't have to know anything about programming or VBA to create and use macros.


  1. Hi Yogesh, good post, but how do i record the "start makro recording" command in VBA? thanks in advance, martin

  2. Hi Martin

    If you are using Excel 2003 then you Select "View -> Toolbars -> Visual basic"

    If you are using Excel 2007 then
    Selct Office button > Excel Options > Customise.

    You will need to choose all commnads and then add record macro button quick access toolbar.

    For more on customising quick access toolbar pls visit

    I hope this helps

  3. Hi Yogesh, thanks for your reply (I only noticed it today .. sorry). I know about customising the excel toolbar, to make it include the macro button. However what I am ultimately after is a keyboard shortcut to _record_ a macro (not to play it), similar to the behaviour of Ctrl+Shift+R in Visual Studio, which invokes the temporary macro recorder. The recording is stopped by typing Ctrl+Shift+R once more, and the macro is executed by Ctrl+Shift+P. This is a feature I am really missing in Excel and I can't believe that there is nothing like it. Maybe you know something? Thank you very much for your time. martin PS: Currently I am using excel2000.

  4. Hi Martin

    Thanks for writing back. I am also not aware of any such shortcut.

    Yogesh Gupta

  5. i have make a sheet in which all cells are linked with differents formulas and locked the sheet and only data entry sheet are active if users entry data and insert row formulas are not linked automatically and again i must do all copy and paste so what to do to link data automatically even rows are inserted by users

  6. Hi sir,
    i am sarvjeet pandey student of "ica" and i very like
    your every trick , I have make a sheet for number to word convreted
    for cheque printing it's make your very support
    thank for you (pleas increas 999 to 999999999)

    1. @sarvjeet , you can now download Excel Addin and install it on your machine. Checkout new post

  7. Dear Sir,

    Want to learn VBA language. Could you please advice on which institute gives good training.

    waiting for you reply soon..



Learn how to create Excel dashboards.
Yogesh Gupta's Excel Tips