VBA in Office 2010

Office 2010 has a lot to offer to the Visual Basic for Applications (VBA) developer. Of course, all of the things that I discuss in VBA for Dummies still applies. You can still create great VBA applications using Office 2010. However, there are many new twists with Office 2010 as well, such as a new version of VBA, VBA 7.0. Many of the differences in VBA 7.0 are due to the 64-bit release of Office 2010, but there are many other goodies that I plan to introduce as the weeks pass.

However, before I get into anything very complicated, let’s address a simple need that several readers have written aboutnamely gaining access to VBA in the first place. It appears that Microsoft has simplified the Office interface to the point of making some things hard to find. In order to start using VBA, you need to expose the Developer tab on the Ribbon. The following steps will get you started:

 

  1. Choose File > Options. You’ll see the Options dialog box (such as Word Options in Word).
  2. Select the Customize Ribbon folder. You’ll see a list of tabs in the right pane as shown here:
    CustomizeRibbon01
  3. Check the Developer tab and click OK. You now have access to VBA. Here’s how the Developer tab looks in Wordthe other Office applications provide a similar view:
    CustomizeRibbon02

Now that you have access to the Developer tab, you should try creating a macro. One of the things I’ve been encouraging readers to do is to spend more time recording macros. Recording a macro helps you understand the actual code used to perform the tasks that you normally perform better. So, let’s try something simple in Word 2010.

 

  1. Click Record Macro. You’ll see the Record Macro dialog box shown below. Begin by filling out the information shown in the screenshot. This dialog box looks similar to the version used in previous versions of Office, but there are some things to consider. First, notice that you can now assign macros to buttons on the Ribbon, so your macros no longer have to hide somewhere. When you assign your macro to a button in a template, everyone using the template will see that button, so VBA is definitely becoming Ribbon friendlier.
    CustomizeRibbon03
  2. Click Button. You’ll see the Word Options dialog box. Microsoft assumes you want to place the new macro on the Quick Access Toolbar (QAT), but this is a bad idea. Most developers reserve the QAT for users, so they can easily customize their Office interface.
  3. Select the Customize Ribbon folder. Choose Macros from the Choose Command From list. You’ll see your macro in the list. Let’s put the macro on its own custom tab in its own custom group.
  4. Click New Tab. You’ll see a New Tab (Custom) and a New Group (Custom) added to the Main Tabs list.
  5. Highlight New Tab (Custom) and click Rename. You’ll see the Rename dialog box shown here:
    CustomizeRibbon04
  6. Type My Custom Tab and click OK. You’ll see the name change appear in the Main Tabs list.
  7. Highlight New Group (Custom) and click Rename. You’ll see a different Rename dialog box this time as shown here:
    CustomizeRibbon05
  8. Type Test Macros. Select the green right arrow as a symbol. Click OK. You’ll see the name change appear in the Main Tabs list. It’s time to add our macro to the Ribbon.
  9. Highlight the macro entry in the macro list. Highlight the Test Macros (Custom) entry in the Main Tabs list. Click Add. Word adds the macro to the custom Ribbon entry. Your Word Options dialog box should look like the one here now:
    CustomizeRibbon06
  10. Click OK to accept the changes. At this point, you can start recording the macro. Let’s do something very simple this time.
  11. Type Hello World in the Word document. Click Stop Recording. Word stores the macro you’ve just created.
  12. Select the My Custom Tab tab on the Ribbon. You’ll see something like this:
    CustomizeRibbon07
  13. Click the Test Me macro. You’ll see Word Type Hello World.
  14. Click Visual Basic on the Developer tab. You’ll see the Visual Basic editor opened.
  15. Double click the Project (Document1)\Modules\NewMacros entry. You’ll see the source code for the macro you just created, which will look something like this.
Sub TestMe()
'
' TestMe Macro
' This macro shows the results of using the Macro Recorder in Word 2010.
'
    Selection.TypeText Text:="Hello World"
End Sub


That’s all there is to it! You can create macros now that will appear anywhere on the Ribbon. Give it a try and use this technique to start all of your macros in the future. I’ll visit the new VBA features in greater detail in future posts. In the meantime, contact me with your questions at John@JohnMuellerBooks.com.