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.

 

Author: John

John Mueller is a freelance author and technical editor. He has writing in his blood, having produced 99 books and over 600 articles to date. The topics range from networking to artificial intelligence and from database management to heads-down programming. Some of his current books include a Web security book, discussions of how to manage big data using data science, a Windows command -line reference, and a book that shows how to build your own custom PC. His technical editing skills have helped over more than 67 authors refine the content of their manuscripts. John has provided technical editing services to both Data Based Advisor and Coast Compute magazines. He has also contributed articles to magazines such as Software Quality Connection, DevSource, InformIT, SQL Server Professional, Visual C++ Developer, Hard Core Visual Basic, asp.netPRO, Software Test and Performance, and Visual Basic Developer. Be sure to read John’s blog at http://blog.johnmuellerbooks.com/. When John isn’t working at the computer, you can find him outside in the garden, cutting wood, or generally enjoying nature. John also likes making wine and knitting. When not occupied with anything else, he makes glycerin soap and candles, which comes in handy for gift baskets. You can reach John on the Internet at John@JohnMuellerBooks.com. John is also setting up a website at http://www.johnmuellerbooks.com/. Feel free to take a look and make suggestions on how he can improve it.