After you have the skill in the VBA language, you can write new macros from scratch in the Visual Basic Editor instead of just editing ones that you’ve previously recorded in your spreadsheet by using Excel’s macro recorder. When creating a macro from scratch in the Visual Basic Editor, you need to follow these general steps:
Click the name of the VBA project in the Project Explorer window where you want to add the new macro.
If you want to write a macro just for the current workbook, click the VBAProject function that contains its filename in parentheses, as in VBAProject (My Spreadsheet). If you want to write a global macro in the Personal Macro Workbook, click VBAProject(PERSONAL.XLSB) in the Project Explorer window.
Choose Insert→Module on the Visual Basic Editor menu bar.
Excel responds by opening a new, blank Code window in the Visual Basic Editor window and by adding another Module icon (named with the next available number) in the outline in the Project Explorer window under the appropriate VBA Project.
Next, you begin your macro by creating a subroutine (all macros, even the ones you record in the spreadsheet, are really Visual Basic subroutines). To do this, you just type sub (for subroutine).
Type sub and then press the spacebar.
Now, you need to name your new macro, which you do by naming your subroutine. Remember that in naming your new macro (or a subroutine), you follow the same rules as when naming a range name (begin with a letter and no spaces).
Type the name of your macro and then press the Enter key.
As soon as you press the Enter key, the Visual Basic Editor inserts a closed pair of parentheses after the macro’s name, a blank line, and an End Sub statement on its own line below that. It then positions the insertion point at the beginning of the blank line between the lines with the Sub and End Sub statements. It’s here that you enter the lines of code for the macro that you’re writing.
Enter the lines of VBA code for the macro in between the Sub and End Sub statements.
Before you begin writing the VBA statements that your macro is to execute, you should first document the purpose and functioning of this macro. To do this, type an apostrophe (') at the beginning of each line of this text to enter it as a comment. (Excel knows not to try to execute any line of code that’s prefaced with an apostrophe.)
When you press the Enter key to start a new line that begins with an apostrophe, the line of text turns green, indicating that the Visual Basic Editor considers it to be a comment that’s not to be executed when the macro runs.
After you document the purpose of the macro with your comments, you begin entering the statements that you want the macro to execute (which must not be prefaced by apostrophes). To indent lines of code to make them easier to read, press Tab. If you need to outdent the line, press Shift+Tab. When you finish writing the code for your macro, you need to save it before you test it.
Choose File→Save on the Visual Basic Editor menu bar or press Ctrl+S.
After you save your new macro, you can click the View Microsoft Excel button on the Standard toolbar to return to your worksheet where you can try it. To run the new macro that you’ve written, choose View→Macros on the Ribbon or press Alt+F8 to open the Macro dialog box and then click the name of the macro that you just wrote before you click OK.
If Excel encounters an error when running the macro, it returns you to the Visual Basic Editor, and an Alert Microsoft Visual Basic dialog box appears, indicating (in very cryptic form) the nature of the error. Click the Debug button in this dialog box to have the Visual Basic Editor highlight the line of code that it can’t execute.
You can then attempt to find the mistake and edit it in the line of code. If you do eliminate the cause of the error, the Visual Basic Editor removes the highlighting from that line of code, and you can then click the Continue button (which automatically replaces the Run button when the Editor goes into debug mode) with the blue triangle pointing to the right on the Standard toolbar to continue running the macro.