Excel VBA Programming For Dummies, 6th Edition
Book image
Explore Book Buy On Amazon
Ready to do some Excel 2016 VBA programming? Hopefully, you are because here comes the hands-on part. To start recording your first macro, you need to first find the Macro Recorder, which is on the Developer tab. Unfortunately, Excel comes out of the box with the Developer tab hidden — you may not see it on your version of Excel at first. If you plan to work with VBA macros, you’ll want to make sure that the Developer tab is visible. To display this tab:
  1. Choose File  → Options  → Customize Ribbon. The Customize the Ribbon dialog appears.
  2. In the Customize the ribbon section on the right side of the dialog, click the Developer checkbox.
  3. Click OK. The Developer tab is now on the ribbon.
Now that you've got the Developer tab on the ribbon, just follow these instructions carefully and you’ll be well on your way:
  1. Create a new workbook.
  2. Select a cell. Any cell will do.

  3. Choose Developer  →  Code  →  Record Macro or click the macro recording button on the status bar.

    The Record Macro dialog box appears.

    The Record Macro dialog box appears when you’re about to record a macro.

    The Record Macro dialog box appears when you’re about to record a macro.
  4. Enter a name for the macro.

    Excel provides a default name (something like Macro1), but it’s better to use a more descriptive name. NameAndTime (with no spaces) is a good name for this macro.

  5. Click the Shortcut Key box, and enter Shift+N (for an uppercase N) as the shortcut key.

    Specifying a shortcut key is optional. If you do specify one, you can execute the macro by pressing a key combination — in this case, Ctrl+Shift+N.

  6. Make sure the Store Macro In setting is This Workbook.

  7. You can enter some text in the Description box, if you like.

    This step is optional. Some people like to describe what the macro does (or is supposed to do).

  8. Click OK.

    The Record Macro dialog box closes, and Excel’s macro recorder is turned on. From this point, Excel monitors everything you do and converts it to VBA code.

  9. Type your name in the active cell.

  10. Move the cell pointer to the cell below and enter this formula:

    =NOW()

    The formula displays the current date and time.

  11. Select the formula cell, and press Ctrl+C to copy that cell to the Clipboard.

  12. Choose Home  →  Clipboard  →  Paste  →  Values (V).

    This command converts the formula to its value.

  13. With the date cell selected, press Shift+up arrow to select that cell and the one above it (which contains your name).

  14. Use the controls in the Home  →  Font group to change the formatting to Bold and make the font size 16 point.

  15. Choose Developer  →  Code  →  Stop Recording.

    The macro recorder is turned off.

Congratulations! You just created your first Excel VBA macro. You may want to phone your mother and tell her the good news.

About This Article

This article is from the book:

About the book authors:

Michael Alexander is a Microsoft certified application developer and author of several books on advanced business analysis with Microsoft Access and Microsoft Excel. He has been named a Microsoft MVP for his ongoing contributions to the Excel community. Dick Kusleika has been helping users get the most out of Microsoft Office products for more than 25 years through online forums, blogging, books, and conferences.

This article can be found in the category: