How to Write Macros in Excel 2010

By Jeff Grundy

Macros allow you to perform virtually any task in Microsoft Excel faster and more efficiently. Whether it's a commonly used formula or creating pre-formatted filenames for saving your spreadsheets, macros allow you to perform a function or task with a keyboard shortcut or click of the mouse. You can even create macros that run automatically when you open or close an Excel workbook. You can use the macro recorder to create simple macros, but writing your macros using VBA (Visual Basic for Applications) allows you to automate even the most complex Excel tasks.

Step 1

Open Excel on your computer. Click the "View" tab on the ribbon and then click the "Macros" icon.

Step 2

Enter a descriptive name for the new macro. Do use spaces in the macro name. Create a sample macro to use during the process of learning to write a macro in VBA. Therefore, enter the macro name "Change_Worksheet_Names" in the macro name field and then click "Create." The Microsoft Visual Basic for Application window opens and displays a "Module (Code)" window with the blank "Sub Change_Worksheet_Names()" code document.

Step 3

Continue creating the sample macro, which changes all of the generic "Sheet1," "Sheet2" worksheets in a workbook to a value from a header or title cell in each worksheet. For example, if you enter the title for each sheet in cell "A1" of each worksheet, this macro changes the name displayed at the bottom of each worksheet to the value in the referenced cell -- in this case "A1." To create the macro using VBA, enter the following code between the "Sub Change_Worksheet_Names()" and "End Sub" tags:Dim myWorksheet As WorksheetFor Each myWorksheet In Worksheets 'The next line of code verifies that cell A1 in each worksheet is not empty. This text is a code "remark." The single quote mark at the beginning of the line informs Excel not to include this text in the macro code. Remarks are a good way to leave notes in VBA code that explain commands or syntax used. If myWorksheet.Range("A1").Value <> "" Then 'This command renames the worksheet to the text value in cell "A1" of the first worksheet. myWorksheet.Name = myWorksheet.Range("A1").Value End If'The "Next" command instructs Excel to repeat the above code commands until it has finished renaming all the worksheets in the active workbook.Next

Step 4

Click the "Save" icon beneath the menu bar in the VBA editor window. Enter a name for the Excel template that contains the macro code. Select "Excel Macro-Enabled Workbook" as the file type and then click "Save." Close the VBA editor window.

Step 5

Test the macro for the sample VBA code. Enter a header or sheet title name in cell A1 of each worksheet. For instance, enter "Daily," Weekly" and "Monthly" in cell A1 of three worksheets. The tabs for each worksheet at the bottom of the Excel window should read "Sheet1," "Sheet2" and "Sheet3," respectively.

Step 6

Click "Macro" on the "View" tab. Highlight the "Change_Worksheet_Names" macro and then click "Run." The worksheet tab names at the bottom of the window change to "Daily," "Weekly" and "Monthly."

Tips & Warnings

  • The above sample code is useful for renaming multiple worksheet tabs and is a simple example of the power of VBA macros in Excel. With further study, you can create VBA macros to automate virtually any task in Excel.