Step 1: You need to open the Visual Basic Editor. You can do that by pressing Alt + F11.

Step 2: You need to add a new module in the Visual Basic Editor by selecting Module on that editor's Insert menu (not the spreadsheet's menu). A blank module window would then appear inside the main window of the Visual Basic Editor.

P1.png

Step 3: Give your macro a name. Type the word Sub in the module window followed by a space and the name of the macro. You can choose to have any name of your choice.

Why do you need to write Sub before the macro name? It is just that way.

If you typed Sub MyMacro, for instance, you'd create a new macro called MyMacro. Because the Visual Basic Editor is so smart, it would automatically insert a line below your Sub line that said End Sub — how convenient! You've got the beginning and the end, now all you need is some code between them, in the handy new space.

Step 4: Add code to your macro. Suppose you'd followed these steps and created a macro. Because there's nothing between your Sub line and your End Sub line, your new macro wouldn't actually do anything. You'd need to add code to bring your macro alive. Here's an example. Say you wanted to show a simple message. Between the Sub line and the End Sub lines, you would type:

MsgBox “My first macro.”

While MsgBox may look like a typo, this is the VBA word for a message box. Because VBA is very literal, you must type exactly what appears above, and it's important to include the quotation marks around the rest of the text in this line of code. You can run the macro by clicking the ‘Run’ button in VBA or by clicking Macros in Excel in the Developer tab & selecting the macro or by clicking the Sub MyMacro() line followed by pressing F5 on your keyboard). On running the macro, Excel would show a message containing My first macro and an OK button, which would close the message.

P2.png

Tip:
A great way to create your own code example is to record a macro for the action first. On the Tools menu, point to Macro and select Record New Macro. In Excel 2007, select the Developer tab and click on Record Macro. Once you've recorded the macro, take a look at it in the Visual Basic Editor to see how its code works. On the Tools menu, point to Macro and then select Macros to find the one you recorded. Select it and click Edit to see the code.