How to Develop Macros for Microsoft Excel

Recording macros has not changed much through the multiple Microsoft Excel versions, just how to initiate them. An Excel macro allows the user to perform a sequence of events and capture them identically on a macro recorder. Advanced users can code the macros line by line, and for very difficult functions, this is a necessity. However, for most users the macro recorder will do the job.

Difficulty: Easy

Instructions

Create the First Macro

1. 
    
   Blank Workbook

   Open Microsoft Excel 2007.

2. 
    
   Developer Tab

   Click the "Developer" tab.

3. 
    
   Recoed Macro

   Click the "Record Macros" selection in the "Code" section. A "Record Macro" window will open. Under "Macro Name" type "TestMacro1." Macro names cannot contain spaces. Leave the rest of the information blank for this exercise. Click the "OK" button. Every keystroke will now be recorded into your macro. We are going to create a very simple macro as an example.

4. 
    
   Numbers Added

   Place the following numbers (1, 2, 3, and 4) into cells A1, A2, A3 and A4, respectively. Click the "Stop Recording" selection. You have created your first macro. We have now decided that we want these particular numbers to be shown as currency values.

Create a second macro

5. 
    
   Cells Highlighted

   Click the "Record Macros" selection and name this macro "TestMacro2." Click the "OK" button to start the recording. Highlight cells A1 through A4.

6. 
    
   Format Cells...

   Right-click the selected cells and select "Format cells..."

7. 
    
   Format Cells to Currency

   Change the Category to "Currency." Click the "OK" button.

8. 
    
   Stop Recording

   Click the "Stop Recording" selection. Now we want to add the values together.

Create a third macro

9. 
    
   Select cell A6

   Click the "Record Macros" selection and name this macro "TestMacro3." Click the "OK" button to start the recording. Select cell A6.

10. 
     
    Add mathematical function

    Type the following into the A6 cell: =A1+A2+A3+A4

11. 
     
    Stop Recording

    Press the "Enter" key. Click the "Stop Recording" selection.

Running the macros

12. 
     
    Click to Sheet2

    Change the sheet selection to "Sheet2." Click the "Macros" icon. The "Macro" window will open.

13. 
     
    Run Macro window

    Select "TestMacro1" and press the "Run" button. You will see that your numbers have appeared on the sheet. Do the same for the other two macros you have created.

14. Notice that the final version is an exact duplicate of what you created on "Sheet1." These three macros have showed you how to copy text, format cells and how to perform mathematical functions.

Resources

• Developing Macro Commands in MS Excel
• VBA in Macros