4. Absolute versus Relative Macros An will always affect the same – “Absolute” cells each time whereas a macro will affect those cells relative to where “Relative” your cursor is positioned when you invoke the macro. It is crucial that you understand the difference. 5. Editing Macros Once created, you can view and/or edit your macro using the View – Macros option. This will open the macro subroutine in a Visual basic programming window and provide you with a plethora of VB tools. 6. Advanced Visual Basic Programming For the truly ambitious CPA, in the Visual – Basic Programming window, you have the necessary tools you need to build very sophisticated macros with dialog boxes, drop down menu options, check boxes, radio buttons the whole works. I invite you to knock yourself out. To see all of this – power, turn on the Tab” in Options” (see below). “Developer “Excel Presented below are more detailed comments and step-by-step instructions for creating and invoking macros, followed by some example macros. 1. Creating a - To create a macro, click Macro” in the Group” on the “Record “Code Developer Tab. Assign a Name - In the Name” box, enter a name for the macro. “Macro i. The character of the macro name must be a letter. ii. Subsequent characters can be letters, numbers, or underscore characters. iii. Spaces cannot be used in a macro name (an underscore character is often used as a word separator. iv. If you use a macro name that is also a cell reference, you may get an error message that the macro name is not valid. Assign a CTRL Combination (optional) You can assign a CTRL combination – to run the by typing any lowercase letter or uppercase letter that you want to use in the Shortcut key box. i. The shortcut key will override any equivalent default Excel shortcut key while the workbook that contains the macro is open. Macro Location - In the Macro In” list, select the workbook where “Store you want to store the macro.
4. Absolute versus Relative Macros – An “Absolute” will always affect the same cells each time whereas a “Relative” macro will affect those cells relative to where your cursor is positioned when you invoke the macro. It is crucial that you understand the difference. 5. Editing Macros – Once created, you can view and/or edit your macro using the View Macros option. This will open the macro subroutine in a Visual basic programming window and provide you with a plethora of VB tools. 6. Advanced Visual Basic Programming – For the truly ambitious CPA, in the Visual Basic Programming window, you have the necessary tools you need to build very sophisticated macros with dialog boxes, drop down menu options, check boxes, radio buttons – the whole works. I invite you to knock yourself out. To see all of this power, turn on the “Developer Tab” in “Excel Options” (see below). Presented below are more detailed comments and step-by-step instructions for creating and invoking macros, followed by some example macros. 1. Creating a - To create a macro, click “Record Macro” in the “Code Group” on the Developer Tab. Assign a Name - In the “Macro Name” box, enter a name for the macro. i. The character of the macro name must be a letter. ii. Subsequent characters can be letters, numbers, or underscore characters. iii. Spaces cannot be used in a macro name (an underscore character is often used as a word separator. iv. If you use a macro name that is also a cell reference, you may get an error message that the macro name is not valid. Assign a CTRL Combination (optional) – You can assign a CTRL combination to run the by typing any lowercase letter or uppercase letter that you want to use in the Shortcut key box. i. The shortcut key will override any equivalent default Excel shortcut key while the workbook that contains the macro is open. Macro Location - In the “Store Macro In” list, select the workbook where you want to store the macro.
2009%20Excel%20Functions%20Macros%20and%20Data%20Commands%20Manual.docx