Checking the VBA Environment

Microsoft has made some significant changes in VBA 7.0 that aren’t discussed in VBA for Dummies because the book came out before Office 2010 was released.  All of them are good and necessary changes for the environment that Office 2010 supports. Of course, one of the biggest changes for Office 2010 users is that Office now supports both a 32-bit and a 64-bit environment, so you need a way to check for this condition. It turns out that all sorts of programming functionality changes in a 64-bit environment, including the use of Windows API pointers. Calls to the Windows API that worked just fine in previous versions of VBA may not work in VBA 7.0 when you’re working with a 64-bit version of Office.

Because users often install software that support doesn’t know about, you can’t assume that users will follow the rules and use only the 32-bit version of Office 2010. As a consequence, you must make some changes to your code to ensure it handles any environment thrown at it. Fortunately, Microsoft also makes it possible to check the environment using the following code pattern.

Sub CheckVBA()
    ' Check for VBA 7
    #If VBA7 Then
     
        ' Determine whether this is a 64-bit environment.
        #If Win64 Then
            MsgBox "VBA 7 running in 64-bit Office"
         
        ' This is a 32-bit environment.
        #Else
            MsgBox "VBA 7 running in 32-bit Office"
        #End If
         
    ' This isn't VBA 7.
    #Else
        MsgBox "Older version of VBA"
    #End If
End Sub

Notice the use of the specialized If…Then construction. The #If statement differs from the standard If statement. Because VBA7 isn’t defined in older versions of VBA, the #Else clause is executed. Likewise, only the 64-bit version of Office defines the Win64 constant, so the 32-bit version of Office 2010 will execute the #Else clause.

It’s essential to understand that the Win64 constant refers to the version of Office, not the version of Windows. You can run the 32-bit version of Office 2010 on a 64-bit Windows system without any problem. When you execute this macro on a 64-bit Windows system with 32-bit Office 2010 installed, you’ll still see the “VBA 7 running in 32-bit Office” message. When you think about it, this response makes sense because the version of Office is important, the version of Windows is less so.

Of course, there are times when you also need to know which version of Windows you’re using. The check is easy. Make sure you use an environment variable that only appears on the version of Windows you’re using. Checking for 64-bit Windows is easiest using this code.

Sub CheckWin()
 
    ' Check for a 64-bit-specific environment variable.
    If Not Environ("ProgramFiles(x86)") = "" Then
        MsgBox "Running on 64-bit Windows"
    Else
        MsgBox "Running on 32-bit Windows"
    End If
End Sub

The “ProgramFiles(x86)” only comes with 64-bit Windows. Now that you know how to check the various VBA 7.0 permutations, I’ll look at some VBA 7.0-specific programming in my next post. Let me know if you have any questions or suggestions at John@JohnMuellerBooks.com.

 

A Quick Method for Adding Custom Ribbon Elements

Everyone knows that the days of the CommandBar are gone. Sure, you can still use the CommandBar in your VBA applications, but the result is less than spectacular. All of the entries end up on the Add-Ins tab of the Ribbon. Consequently, even though the code in Listing 12-2 of VBA for Dummies still works just fine, you end up with this addition to the Add-ins tab as a result.

ProgrammingRibbon01

Even the icon is correct from the listing, so the code does indeed work, but it isn’t as nice as it could be. Of course, you could create a true Ribbon addition for your VBA application. Starting on page 266 you discover the incredibly convoluted and horribly difficult technique to add a real addition to the Ribbon in Office 2007. The results really are quite nice and you obtain the full functionality that the Ribbon provides. However, unless you have a lot of time to spend working through these steps, you’ll quickly learn to dread the entire process.

Fortunately, Office 2010 offers a middle ground approach. The results really are Ribbon-specific and you do have some flexibility in how the buttons appear, but there are still a few limitations. Let’s look at what you need to do to obtain this middle ground result. The first phase is to create the buttons you want to use with the application and export them to a file so you can modify their appearance. Uses these steps to accomplish this task.

  1. Create your macros as normal. The example uses these two very simple macros.
    Sub SayHello1()
        MsgBox "Saying Hello From SayHello1"
    End Sub
     
    Sub SayHello2()
        MsgBox "Saying Hello From SayHello2."
    End Sub
  2. Attach the macros to buttons using the technique shown in my “VBA in Office 2010” post. The example uses the setup shown here:
    ProgrammingRibbon02
  3. Check your results. You’ll likely see something like this where the buttons are all large and not very attractively set up:
    ProgrammingRibbon03
  4. Choose File > Options to display the Options dialog box. Select the Customize Ribbon option so that you can see the Ribbon configuration again.
  5. Choose Import/Export to display the import and export options. Select Export All Customizations to display a File Save dialog box where you can save the customizations to disk.
  6. Type a name for the file, such as MyCustomization.exportedUI and click Save. At this point, you have an XML file containing your customizations.

Open the file using an XML editor or even Notepad. Any text editor will do fine. For the example, I’m using Notepad to make things a bit clearer. What you’ll see inside is going to be messy because Office won’t add any carriage returns or white space to make the text easier to read. Adding these features won’t invalidate the file. Here’s the result from the example:

ProgrammingRibbon04

Most of this code isn’t important to the example, but it’s all functional. Let’s say you want to use icons that aren’t supported directly in the Options dialog box and you also want to use medium sized icons. You can make both changes quite easily now. First change the <group> element to allow custom sizing by changing the autoScale property to read autoScale=”false”. Second, change the <mso:button> elements to look like this:

<mso:button idQ="x1:Book1_SayHello1_0_16A6B4C"
            label="Say Hello 1"
            imageMso="AnimationAudio"
            onAction="SayHello1"
            size="medium"
            visible="true"/>
<mso:button idQ="x1:Book1_SayHello2_1_16A6B4C"
            label="Say Hello 2"
            imageMso="WorkflowPending"
            onAction="SayHello2"
            size="medium"
            visible="true"/>


Notice that the buttons are now medium sized and are using icons that aren’t normally supported by the Excel Options dialog box. To see the results of these changes, reopen the Customize Ribbon folder of the Excel Options dialog box. Choose the Import Customization File option of the Import/Export drop down. Excel will ask whether you’re sure that you want to replace the existing Ribbon and Quick Access Toolbar customizations. Click Yes and you’ll see the changes added to your Ribbon. Close the Excel Options dialog box and you’ll see something like this:

ProgrammingRibbon05

Notice that the buttons now use different icons and they’re arranged differently. You can use this approach to create a customized look to this much simpler approach for dealing with the Ribbon. Make sure you get the Office 2010 Icon Gallery or the Office 2007 Icon Gallery to obtain usable icon names to use for your applications.

Of course, there are always limitations. So far I haven’t figured out how to use other kinds of controls. For example, I can display a toggle button just fine by changing the <mso:button> element to an <mso:toggleButton> element, but the resulting control isn’t functional. With any luck, I’ll eventually figure out techniques for working around these issues, but this post is a good start. Let me know if you try this technique out and what additions you can figure out for it at John@JohnMuellerBooks.com. You can download the example files, if desired, to see how things work.

SendKeys in VBA

The SendKeys statement in VBA is one of the more interesting features because you can use it to send keystrokes directly to the user interfacejust as if you were entering the keystrokes yourself. This means you can use SendKeys to perform all sorts of tasks that you might not ordinarily be able to perform using other statements. For example, you could use SendKeys to modify the Ribbon interface programmatically.

Unfortunately, SendKeys can also cause problems for the VBA developer. I’ve received more than a few e-mails regarding SendKeys examples not working properly. For example, Listing 5-8 in VBA for Dummies causes a lot of woe for many readers. The natural inclination is to want to watch the macro run from within the Visual Basic for Applications editor, but you can’t do this with SendKeys. The SendKeys statement always sends its output to the active windowno matter which window that might be. So, if you have the Visual Basic for Applications editor selected, the SendKeys output goes to that window, rather than the Excel window where the output would normally go when a user is running the macro.

So, just how do you run this macro? If you want to see the macro work in the debugger, you begin by setting a breakpoint in the editor. Simply click in the margin area on the left side of the editor and you’ll see a breakpoint added like the one shown here:

SendKeys01

The breakpoint line appears highlighted in red. You also see a red circle in the left margin. When you run the macro, it’ll stop at the ActiveRows = ActiveWindow.RangeSelection.Rows.Count line.

At this point, you can run the macro. Open the Developer tab and you’ll see a Macros button in the Code group as shown here:

SendKeys02

Click the Macros button and you’ll see the Macro dialog box shown here:

SendKeys03

Select the ChangeAllRooms macro from the list. Click Run. The macro will run to the breakpoint you selected.

Now, if you run the macro shown in Listing 5-8, you’ll see that SendKeys is actually broken in VBA in Office 2010 (it worked fine in earlier versions of Office). The key combination is sent, but usually after the rest of the macro has run. The second part of the callthe part that tells VBA to wait until the SendKeys call is processed before proceeding is broken. Consequently, the code to tell Excel to select a column by pressing the Ctrl+Shift+Down Arrow keys doesn’t work. The key combination is sent to the window, but long after the need for the key combination has passed. If there is someone who has a solution to this problem, let me know at John@JohnMuellerBooks.com. In the meantime, you can get this example to work using the following code instead.

Public Sub ChangeAllRooms()
    Dim ActiveRows As Integer   ' Number of active rows.
    Dim Counter As Integer      ' Current row in process.
     
    ' Select the first data cell in the worksheet.
    Range("A5").Select
     
    ' Use SendKeys to select all of the cells in the column.
    ' This call is broken in Office 2010, so I commented it out.
    'SendKeys "^+{DOWN}", True
    ' Select the range manually for the example instead.
    Range("A5", "A10").Select
     
    ' Get the number of rows to process.
    ActiveRows = ActiveWindow.RangeSelection.Rows.Count
     
    ' Reset the cell pointer.
    Range("C5").Select
     
    ' Keep processing the cells until complete.
    For Counter = 5 To ActiveRows + 5
     
        ' Call the Sub created to change a single cell.
        MakeChoice3
         
        ' Move to the next cell.
        Range("C" + CStr(Counter)).Select
    Next
End Sub

OK, now that you have working code, you can run the example. Here are the results you should see from Listing 5-8.

SendKeys04

 

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.