SendKeys for Office 2010 Revisited

Some time ago, I wrote a post entitled, “SendKeys in VBA.” In that post, I described a problem where the SendKeys example in Listing 5-8 of VBA for Dummies isn’t working as expected. It turns out that a lot of people are having this problem and not just with Excel—the problem seems to happen with other Office products as well. I’ve played with the macro in Listing 5-8 quite a lot and while it does work in Office 2007 SP2, it doesn’t work in Office 2010 as written.

Apparently, this problem isn’t unique to Office 2010 either. I’ve found threads online that indicate that the problem also existed in early versions of Office 2007, but was fixed in SP2 after a lot of people complained. Wandering around the Internet has proven interesting because I’ve found other blog posts that try to fix the problem, but didn’t work on my system for whatever reason. The bottom line is that SendKeys is broken and quite a few people know it.

Since my original post, I’ve tried several experiments, none of which resolve the problem, but some of which serve to highlight the true essence of the problem. For example, I tried to break the code in Listing 5-8 apart into room selection and row processing pieces. Here is the room selection piece:

Sub SelectRooms()
    ' Select the first data cell in the worksheet.
    Range("A5").Select
     
    ' Use SendKeys to select all of the cells in the column.
    VBA.SendKeys "+^{DOWN}", True
End Sub

and here’s the row processing piece:

Sub ProcessRows()
    Dim ActiveRows As Integer   ' Number of active rows.
    Dim Counter As Integer      ' Current row in process.
     
    ' 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

If you run these pieces one at a time, the example will work just as it did with earlier versions of Office. However, that means running two separate macros instead of one, which does cut down on the usability of the technique. The SendKeys function appears to perform its task at the end of the macro, whenever the end of the macro occurs. Initially, I thought that calling these two macros in succession might do the trick, so I created this piece of code:

Sub ChangeAllRooms2()
    ' Select the rooms.
    SelectRooms
     
    ' Process the selections.
    ProcessRows
End Sub

Unfortunately, the result is the same as before. The SendKeys function really does appear not to output anything until the macro is finished. However, because I’m a glutton for punishment, I tried a few other things. For example, I thought perhaps that the wait part of the SendKeys call was to blame, so I created a manual wait like this:

Sub WaitForSendKeys(Timeout As Integer)
    ' Create a variable to hold the wait interval.
    Dim WaitTime
     
    ' Set the interval.
    WaitTime = TimeSerial(Hour(Now()), _
                          Minute(Now()), _
                          Second(Now()) + Timeout)
     
    ' Wait for the event to happen.
    Application.Wait WaitTime
End Sub

I then modified the original macro to look like this:

Public Sub ChangeAllRooms3()
    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.
    SendKeys "+^{DOWN}"
     
    ' Add a wait interval.
    WaitForSendKeys 5
     
    ' 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

The results are that the macro runs a lot slower, but still doesn’t do the job. The only way that SendKeys works is to have it at the end of the macro. In short, you need to execute the code in Listing 5-8 in two parts in order to make it work as originally described in the book. As an alternative, you can still use the code in my previous post. The fix that seems to work best is not to use SendKeys at all.

Microsoft has been quiet about this whole issue. Nowhere have I found any word from Microsoft on the problem with SendKeys and I doubt there is a fix in the offing. If someone has a suggestion for fixing this problem, I’m all ears. Please contact me at John@JohnMuellerBooks.com with full details of your fix. Make sure you’ve tested your code using Office 2010 on a Vista or Windows 7 system. I’ll be sure to give full credit to anyone who does come up with a fix for this problem.

 

Examining the Calculator in Windows 7

Almost every book I write on programming has some element of application design in it. If you don’t create applications with a great design, users are less likely to use them. I examine general design issues in C# Design and Development. However, books like C++ All-In-One Desk Reference For Dummies, LINQ for Dummies, Professional IronPython, Start Here! Learn Microsoft Visual C# 2010 Programming, and Web Matrix Developer’s Guide all have design elements in them because I feel application design is extremely important. Even RibbonX for Dummies and VBA for Dummies have design discussions in them because they both talk about user interfaces. The most specialized book I’ve created about application design is Accessibility for Everybody: Understanding the Section 508 Accessibility Requirements, which discusses user interface requirements for those with special needs. All of these books have one thing in common, they all try to quantify what makes for good user design. They all ask the question, “How can you as a developer write an application that users will enjoy using and use efficiently?” Unfortunately, examples of poor design abound and seem to be more common as time goes on, which is the point of this post.

The Calculator application in Windows 7 is a perfect example of an application that has gone the wrong direction when it comes to the user interface. Sure, the Standard view is the same in both cases as shown here.

Calculator02 Calculator01

The Windows 95 version of Calculator is on the left and the Windows 7 version is on the right. As you can see, the buttons are the same, but someone at Microsoft felt obliged to rearrange them, just to make things difficult for anyone used to using the old version of Calculator. There isn’t a good reason for the change except to change for change’s sake. The Windows 7 version does have larger numbers, but at the expense of providing room for larger calculations. Personally, I’d prefer the Windows 95 version because sometimes I do need to perform really large calculations. Both versions of Calculator offer features such as digit grouping, so there isn’t any change here either. In short, Windows 7 offers less capability in a rearranged format that makes it hard for people who are used to the old version of Calculator to use it—breaking the rules of good design.

The problems get worse, unfortunately. I never used the Standard view of Calculator because I need to perform programmer math and scientific calculations relatively often. Switching to Scientific view in the Windows 95 version of Calculator, you see the perfect interface as shown here.

Calculator03

During the time I worked with Windows 95, I never switched out of this view. All I did was start Calculator whenever I needed it and did the math that I needed to do. Never once did the view I was in enter my mind. That’s great design! Users shouldn’t have to think about how your application works—the user’s only thought should be how to get work done.

The Windows 7 version of Calculator now has four modes: Standard, Scientific, Programmer, and Statistics. Every time I use Calculator now, I must first figure out what I want to do and change the view appropriately—wasting time and effort in the process. The views unnecessarily limit my options. For example, look at the Programmer view.

Calculator04

I do gain access to the RoL (rotate left) and RoR (rotate right) buttons, but I can’t think of when I’ll need them. The modern languages that most developers use don’t actually require these buttons. If I were using assembler, they could see some use, but I don’t remember ever using rotate left or rotate right with C#, Visual Basic (any version), Python, or even C++. So, these buttons are extraneous and only serve to complicate the interface. In addition, I do gain a bit display, which could prove helpful at some point, but I found that the Bin view on the old version of Calculator worked just fine and served my needs well. However, notice that the decimal point button is disabled. The loss of this button means that every time I have to perform any sort of math with a decimal point, I have to switch to another view. Programmers do use floating point numbers! So, a capable programmer calculator has been replaced with something less useful—something that makes me irritable and work harder.

Now let’s switch to Scientific view. Remember, I used to be able to do everything in one view. If I want to raise a value to a power now as part of checking application output, I have to switch to the Scientific view shown here.

Calculator05

As usual, someone felt that it was absolutely required to move the buttons around so that it takes a while to relearn how to use this view. For the most part, I don’t use any of the new features provided by this view. The few times I needed to use this feature with the old Calculator required a switch to Standard view, but the switches were seldom indeed. The Scientific view does include a number of additional features, all of which are likely helpful to someone, but the cost is one of having to switch views when you need to do something else.

Before someone writes to tell me that the new calculator has square root, cubed root, and root to any power buttons, the Windows 95 Calculator has these features as well. You just had to know how to use a calculator to understand them. Let’s say you want to find the cubed root of 27 using the Windows 95 Calculator. Simply type 27, check Inv, and click the x^3 button. You’ll see the cubed root answer of 3. Not convinced? OK, let’s try a quad root. Type 16, check Inv, and click the x^y button. Now, type 4 (the quad root) and press the = button. You’ll see the quad root of 16 is 2, which is the correct answer. The Windows 7 calculator adds nothing new in this regard—just more buttons to hunt and peck!

In short, the new Calculator is an example of a failed interface update. Most people will find that they have to constantly switch between views to get anything done. When you force people to think too much about the interface, they tend to get grumpy, less productive, and end up tossing out the application at some point. Personally, every since  I found the Windows 95 version of Calculator runs just fine on my Windows 7 system, I’ve been using it instead of the broken product Microsoft has offered. The Windows 95 version truly is the superior application. Let me know your thoughts on Calculator and on application design in general at John@JohnMuellerBooks.com.

 

Creating a Global Toggle Button

I’ve worked through a number of odd issues with the Ribbon and VBA in this blog and in two of my books VBA for Dummies and RibbonX for Dummies. For example, the Creating a Custom Styles Group post shows how to make a gallery look correct when you move it to a custom tab. Likewise, the A Quick Method for Adding Custom Ribbon Elements post demonstrates a new technique for customizing the Ribbon using built-in Office 2010 features. Unfortunately, those posts won’t show how to add some specialty controls to your custom Ribbon, such as a toggle button, that also automatically loads when Excel opens. In fact, most people assume that any custom Ribbon element must be added to the individual worksheet or as part of an Excel add-in. This post will dispel that notion.

Before you begin this article, make sure you’ve read the Creating Global Macros in Excel 2007 and 2010 post. You need to understand things such as the location of the PERSONAL.XLSB file on your system and its purpose. Of course, the main purpose is to provide a means of creating global applications that load immediately when Excel loads.

If you try to add a toggle button directly to the PERSONAL.XLSB file, you’re going to quickly find that the process doesn’t work. I tried for hours to get the toggle button to even appear. Regular buttons work fine. You can also easily add galleries using techniques I’ve discussed before, but toggle buttons proved elusive at best.

The trick is to think outside the box. You can create an individual file that contains a toggle button without any problem. Yes, it does require that you create a customUI entry in the application either directly (by changing the file extension of the file to .ZIP and adding the correct elements) or by using an editor, such as the CustomUI Editor. This post won’t cover the basics of creating a custom tab. If you don’t have a copy of RibbonX for Dummies or VBA for Dummies, you can find this information on a number of sites, including MSDN.

Begin by creating a standard Excel workbook and saving it an Excel Macro-Enabled Workbook (.XLSM file). You’ll need to add a custom tab, group, and toggle button to your workbook. Here’s the code I’ve used for this example. It’s nothing elaborate. In fact, I purposely made it simple to ensure that the post would focus on technique.

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<customUI onLoad="RibbonLoaded"
  <ribbon>
    <tabs>
    <tab id="myTab" label="My Tab">
      <group id="myGroup" label="My Group">
             <toggleButton id="SayHello"
                           label="Toggle SayHello"
                           onAction="SayHello_Click"
                           getPressed="SayHello_Pressed"
                           size="large"
                           imageMso="HappyFace"/>
      </group>
      </tab>
    </tabs>
  </ribbon>
</customUI>

This toggle button has two events it must handle: onAction and getPressed. In addition, you must load the Ribbon before you can make anything work. To make the button functional, I needed to add the following macros and global variables to the workbook.

' Define a global variable to hold the Ribbon reference.
Dim Rib As IRibbonUI
 
' Determines the behavior button state.
Dim lBehavior As Boolean
 
' Callback for customUI.onLoad
Sub RibbonLoaded(ribbon As IRibbonUI)
 
    ' Save the ribbon reference.
    Set Rib = ribbon
     
    ' Initialize the behavior state.
    lBehavior = False
     
    ' Show a status message.
    MsgBox "Ribbon is Loaded."
End Sub
 
' Callback for SayHello onAction
Sub SayHello_Click(control As IRibbonControl, pressed As Boolean)
 
    ' Change the behavior state.
    lBehavior = pressed
     
    ' Update the control.
    Rib.InvalidateControl (control.ID)
     
    ' Display the status.
    If pressed Then
        MsgBox "The toggle button is pressed."
    Else
        MsgBox "The toggle button isn't pressed."
    End If
End Sub
 
' Callback for SayHello getPressed
Sub SayHello_Pressed(control As IRibbonControl, ByRef returnedVal)
 
    ' Return the current behavior state.
    returnedVal = lBehavior
End Sub

Don’t forget to make sure you define the relationships if you’re editing the file by hand. OK, at this point you have a toggle button that’s functional. If you save this file, close it, and then reopen it, you’ll find that you can click the button and it’ll tell you its state—either pressed or not pressed. That’s not really the big deal here.

Now, save your file as an Excel Binary Workbook (.XLSB file). Rename this file PERSONAL.XLSB and copy it into your XLSTART folder (such as, C:\Users\John\AppData\Roaming\Microsoft\Excel\XLSTART on my system). You’ll suddenly find that you have a global toggle button. It loads every time Excel loads and the macros work just as you’d expect. Please let me know if you have any questions about this technique at John@JohnMuellerBooks.com.

Creating Global Macros in Excel 2007 and 2010

Ever since I wrote the A Quick Method for Adding Custom Ribbon Elements post and then expanded on it with the Creating a Custom Styles Group post for my VBA for Dummies readers, I’ve received a number of e-mails about global macros. I should explain that you can add Ribbon elements to a particular file or to Excel as a whole. When you add them to Excel as a whole, you need to create a global macro, rather than one that sits in a particular workbook or the button will be useless most of the time.

Before you can do anything, you need to create a global macro. The following steps describe how to perform this task.

  1. Choose Record Macro on the Developer tab or click the Record Macro button in the lower left corner of the Excel window. You’ll see the Record Macro dialog box shown here.
    GlobalMacro01
  2. Type a name for the macro in the Macro Name field (the example uses GlobalSayHello).
  3. (Optional)Type a value in the Shortcut Key field when you plan to use this macro regularly.
  4. Choose Personal Macro Workbook in the Store Macro In field. This is an essential requirement for storing the macro in the right place.
  5. Type a description for your macro. The example uses, “This is test macro; delete it later.”
  6. Click OK. Excel creates the macro for you.
  7. Choose Stop Recording on the Developer tab or click Stop in the lower left corner of the Excel window.
  8. Choose Visual Basic on the Developer tab or press Alt+F11 to open the Visual Basic editor. Notice that there is a new PERSONAL.XLSB entry in the Project window. When you drill down into this project, you see that there is a Module1 entry as shown here.
    GlobalMacro02
  9. Double click Module1. You’ll see an editor window appear with code that looks like this:
    GlobalMacro03
  10. Type your code as normal. The example uses the simple message box statement: MsgBox “Hello from GlobalSayHello”.

When you save this macro, Vista and Windows 7 users will find a new file in the C:\Users\UserName\AppData\Roaming\Microsoft\Excel\XLSTART folder of your system, where UserName is the name of the user. (Windows XP users will find a similar \Documents and Settings folder entry.) Whenever Excel loads, it also loads this macro, so the macro is always accessible. You create an entry on the Ribbon for it as you would any other macro. The difference is that this macro is named PERSONAL.XLSB!GlobalSayHello, where the part before the exclamation mark (!) is the name of the file and the part after is the name of the macro. It’s possible to customize the entry as you would any other Ribbon entry as shown here for this example.

GlobalMacro04

Global macros are essential when you need to have a macro available at all times. This technique is also essential for certain types of control additions that have required callbacks, such as toggle buttons (a puzzle I’m still working to solve, but more on that later). Let me know if you have any questions about this technique at John@JohnMuellerBooks.com.

Creating Freeform Shapes for Excel 2003

I had previously written a post for my VBA for Dummies readers entitled, “Creating Freeform Shapes“. Unfortunately, it appears that the example doesn’t work for Excel 2003 users. Microsoft sometimes changes things in VBA without telling anyone. I’m not sure whether that’s the case here or not, but none of my graphics examples would work with that older copy of Excel, which left me scratching my head for a while.

After a lot of research, trial and error, and input from faithful readers, I determined that the only solution is to use the BuildFreeform() function with a lot of complex equations. My code still didn’t work after quite a few trials and it should have. That’s when experimentation kicked in. When creating a freeform shape, you must define the initial shape, and then add nodes to it to add additional lines and curves. The VBA documentation stated that I could use the AddNodes() function with either msoEditingCorner or msoEditingAuto. It turns out that the documentation is in error—only msoEditingAuto works in Excel 2003 (this problem has been fixed in newer versions of Excel, so the documentation is now correct). So, without further ado, here is an example of creating a freeform shape for Excel 2003 readers (it also works in newer versions of Excel).

Sub ModifyChart4()
    ' Obtain access to the chart.
    Dim TestChart As Chart
    Set TestChart = Sheet1.ChartObjects(1).Chart
 
    ' Obtain the dimensions of the charting area.
    Dim XLeft As Double
    XLeft = TestChart.PlotArea.InsideLeft
    Dim XWidth As Double
    XWidth = TestChart.PlotArea.InsideWidth
    Dim YTop As Double
    YTop = TestChart.PlotArea.InsideTop
    Dim YHeight As Double
    YHeight = TestChart.PlotArea.InsideHeight
     
    ' Obtain access to each of the series.
    Dim Series1 As Series
    Set Series1 = TestChart.SeriesCollection(1)
    Dim Series2 As Series
    Set Series2 = TestChart.SeriesCollection(2)
     
    ' Determine the number of entries for each series.
    Dim S1Count As Long
    S1Count = Series1.Points.Count
    Dim S2Count As Long
    S2Count = Series2.Points.Count
     
    ' Obtain the series entry X-axis spacing in pixels.
    Dim S1XSpacing As Double
    S1XSpacing = (XWidth - XLeft) / S1Count
    Dim S2XSpacing As Double
    S2XSpacing = (XWidth - XLeft) / S2Count
     
    ' This offset isn't calculated right now, but it needs to
    ' be obtained from some source on the chart.
    XOffset = 14
     
    ' Define the X position of the second and third points for
    ' the first and second series in pixels.
    Dim S1X2 As Double
    S1X2 = S1XSpacing * 2 + XOffset
    Dim S1X3 As Double
    S1X3 = S1XSpacing * 3 + XOffset
    Dim S2X2 As Double
    S2X2 = S2XSpacing * 2 + XOffset
    Dim S2X3 As Double
    S2X3 = S2XSpacing * 3 + XOffset
     
    ' Determine the minimum and maximum values for each series and
    ' then create a range value.
    Dim YMin As Double
    YMin = TestChart.Axes(2).MinimumScale
    Dim YMax As Double
    YMax = TestChart.Axes(2).MaximumScale
    Dim YRange As Double
    YRange = YMax - YMin + 1
     
    ' Define the Y axis pixel length.
    Dim YPixels As Double
    YPixels = YHeight + YTop
     
    ' Define the position of the second and third nodes for each
    ' series within the Y axis.
    Dim S1Y2 As Double
    S1Y2 = YPixels - (YPixels * (Series1.Values(2) / YRange))
    Dim S1Y3 As Double
    S1Y3 = YPixels - (YPixels * (Series1.Values(3) / YRange))
    Dim S2Y2 As Double
    S2Y2 = YPixels - (YPixels * (Series2.Values(2) / YRange))
    Dim S2Y3 As Double
    S2Y3 = YPixels - (YPixels * (Series2.Values(3) / YRange))
     
    ' Create a drawing object using the second and
    ' third points of each series and add this shape
    ' to the chart.
    With TestChart.Shapes.BuildFreeform(msoEditingCorner, S1X2, S1Y2)
        .AddNodes msoSegmentLine, msoEditingAuto, S1X3, S1Y3
        .AddNodes msoSegmentLine, msoEditingAuto, S2X3, S2Y3
        .AddNodes msoSegmentLine, msoEditingAuto, S2X2, S2Y2
        .AddNodes msoSegmentLine, msoEditingAuto, S1X2, S1Y2
        .ConvertToShape
    End With
     
    ' Obtain access to the new shape.
    Dim MyDrawing As Shape
    Set MyDrawing = TestChart.Shapes(TestChart.Shapes.Count)
     
    ' Modify the shape color.
    MyDrawing.Fill.ForeColor.RGB = RGB(128, 128, 255)
    MyDrawing.Line.ForeColor.RGB = RGB(64, 64, 128)
End Sub

This version doesn’t use any of the newer techniques for gaining access to the chart or creating the shape. It begins by figuring out the dimensions of the plotting area. Everything is based on knowing the location of the plotting area within the chart object.

The problem is figuring out where each of the nodes on that plot are. You don’t have access to them. So, you’re faced with a situation where you have to calculate the position of the individual nodes, which can be quite an undertaking. Computing the X-axis comes first. The nodes are evenly spaced across the X-axis, so you divide the number nodes by the plotting area length in pixels. That gives you the spacing between nodes. All you do to find the X location of a particular node is multiply that node’s number by the spacing. When I tried this is in the real world though, I found that there is an offset for the Y-axis legend, but that there wasn’t any way to obtain this value. Consequently, you see my trial and error value of 14 as the offset (if someone knows how to obtain this value, please let me know).

The Y-axis values are a little harder to find out. First, you need to obtain the full range of the Y-axis in pixels. Then, you need to work from the top down. The numbers you get for values on the chart are from the bottom up, though, so you need to convert between the two orientations. In addition, the node value doesn’t equate directly to a pixel count, so you must create a ratio between the largest value that the Y-axis supports and the value of the current node, and then multiply by the full pixel range to obtain the position of the current node in the Y-axis.

At this point, you know the X and Y values for each of the four nodes. You use the BuildFreeform() function call to create a FreeFormBuilder object. This object contains only the first node. To add more nodes, you rely on the AddNodes() function. As with any shape, you must close the shape before you call ConvertToShape() to convert it into a closed shape. Otherwise, you simply have a curved line.

The remainder of this example works much like the previous examples in this series. The code gains access to the new shape, colors the interior, and changes the line color. What you’ll see as output varies with the version of Excel you’re using. Here’s the Excel 2010 version of the output.

ChartDrawing0201

Please let me know if there are any additional problems with this example atJohn@JohnMuellerBooks.com. I also want to express my thanks to the readers who wrote in about this example.

Creating Freeform Shapes

It’s important to realize that VBA usually provides more than one way to accomplish any given task. Each technique offers benefits and comes with costs. In the Creating A Connection Between Series post, you saw one way to use shapes to work with a series in a chart. This post builds on the information starting on page 314 of VBA for Dummies. Interestingly enough, there is another way to obtain the same output, as shown here:

Sub ModifyChart2()
    ' Obtain access to the chart.
    Dim TestChart As Chart
    Set TestChart = Sheet1.ChartObjects(1).Chart
     
    ' Obtain access to each of the series.
    Dim Series1 As Series
    Set Series1 = TestChart.SeriesCollection(1)
    Dim Series2 As Series
    Set Series2 = TestChart.SeriesCollection(2)
     
    ' Obtain the position of the second and third points
    ' for each series.
    Dim Point1 As Point
    Set Point1 = Series1.Points(2)
    Dim Point2 As Point
    Set Point2 = Series1.Points(3)
    Dim Point3 As Point
    Set Point3 = Series2.Points(2)
    Dim Point4 As Point
    Set Point4 = Series2.Points(3)
     
    ' Create a drawing object using the second and
    ' third points of each series and add this shape
    ' to the chart.
    With TestChart.Shapes.BuildFreeform(msoEditingCorner, Point1.Left, Point1.Top)
        .AddNodes msoSegmentLine, msoEditingCorner, Point2.Left, Point2.Top
        .AddNodes msoSegmentLine, msoEditingCorner, Point4.Left, Point4.Top
        .AddNodes msoSegmentLine, msoEditingCorner, Point3.Left, Point3.Top
        .AddNodes msoSegmentLine, msoEditingCorner, Point1.Left, Point1.Top
        .ConvertToShape
    End With
     
    ' Obtain access to the new shape.
    Dim MyDrawing As Shape
    Set MyDrawing = TestChart.Shapes(1)
     
    ' Modify the shape color.
    MyDrawing.Fill.ForeColor.RGB = RGB(255, 0, 255)
    MyDrawing.Line.ForeColor.RGB = RGB(128, 0, 128)
End Sub

The disadvantages of this method are that you must spend more time creating the initial shape and the code is less readable. Using a predefined shape means that you simply move the points to meet your needs and you’re finished. However, the advantages of this method are:

  • Create a shape with any number of segments
  • Use curved segments instead of straight lines
  • Easy control how the corners look


In short, even though this method requires a lot more effort initially, it offers significantly more flexibility in exchange for that effort.

The code begins much like the previous exampleobtaining data points from two series and using those points to create a connection. The difference in this example is the call to BuildFreeform(), which allows you to create a shape of any complexity by relying on the AddNodes() method. Simply add any number of nodes to create the shape desired by tracing its outline.

The one thing that many developers forget is to include a call to ConvertToShape(). If you don’t make this call, you end up with an outline, not a shape. The inside won’t be filled in because Excel doesn’t see it as a shapeit’s simply a series of lines.

After you create the shape, you must gain access to it. Because this is the first and only shape on the chart, the index is easyuse a value of 1. If you create a number of shapes, then you must determine which index to use, which will always be the last shape entered if this is the newest shape in the list. Use the Count property to obtain this value. For this example, you’d use TestChart.Shapes.Count to obtain the value. When you run this example, you get precisely the same output as with the earlier example.

The point of this exercise is that you should always consider the possibility of an alternative solution to any given problem. Once you’ve come up with an alternative, consider the pros and cons of this new approach to determine which approach is best for your specific needs. It isn’t always easy to determine which approach will work best, so be willing to create some test cases to check the outcome of a specific approach for your requirement. Let me know if you have any questions at John@JohnMuellerBooks.com.

Creating A Connection Between Series

A reader recently wrote in asking me how to draw a shape between two series on a line chart. The shape should connect two consecutive points on the chart so that what you see is a trapezoidal shape between the two lines. Of course, the big thing is to gain an understand of how charts work. The discussion starts on page 314 of VBA for Dummies. After you know the basics of chart creation, you can start with a worksheet and chart that looks like this:

ChartDrawing01

These values are contrived, but the technique will work with any chart you might want to create. The trapezoid will appear between the second and third points of the two lines. Here is the code you’ll need to perform the task.

Sub ModifyChart()
    ' Obtain access to the chart.
    Dim TestChart As Chart
    Set TestChart = Sheet1.ChartObjects(1).Chart
     
    ' Obtain access to each of the series.
    Dim Series1 As Series
    Set Series1 = TestChart.SeriesCollection(1)
    Dim Series2 As Series
    Set Series2 = TestChart.SeriesCollection(2)
     
    ' Obtain the position of the second and third points
    ' for each series.
    Dim Point1 As Point
    Set Point1 = Series1.Points(2)
    Dim Point2 As Point
    Set Point2 = Series1.Points(3)
    Dim Point3 As Point
    Set Point3 = Series2.Points(2)
    Dim Point4 As Point
    Set Point4 = Series2.Points(3)
     
    ' Create a drawing object using the second and
    ' third points of each series.
    Dim MyDrawing As Shape
     
    ' Add this shape to the chart.
    Set MyDrawing = TestChart.Shapes.AddShape(msoShapeTrapezoid, 15, 20, 25, 30)
     
    ' Modify the position of the shape.
    MyDrawing.Nodes.SetPosition 1, Point1.Left, Point1.Top
    MyDrawing.Nodes.SetPosition 2, Point2.Left, Point2.Top
    MyDrawing.Nodes.SetPosition 3, Point4.Left, Point4.Top
    MyDrawing.Nodes.SetPosition 4, Point3.Left, Point3.Top
    MyDrawing.Nodes.SetPosition 5, Point1.Left, Point1.Top
     
    ' Modify the shape color.
    MyDrawing.Fill.ForeColor.RGB = RGB(0, 255, 0)
    MyDrawing.Line.ForeColor.RGB = RGB(0, 128, 0)
End Sub

The code begins by gaining access to the chart, which already appears on the worksheet. Perhaps someone drew it there and is relying on your macro to show the correlation between the two lines. TestChart provides access to the only chart on the worksheet. The Sheet1.ChartObjects index always begins at 1 and counts up.

This chart has two seriesone for each of the lines. The next step is to gain access to those series as Series1 and Series2. In short, we’re drilling down into the chart components to obtain specifics about those lines so we can draw something between them.

The chart draws the two lines according to a list of points stored in the series that are based on the individual data values you provide and the order of that point in the list. We want to use the second and third points to draw the trapezoid, so you need to access the Points property for each series and supply and index of 2 and 3. You end up with four Point variables that describe the location of the points on the line.

It’s important to create the shape from the chart. Yes, you could create it completely manually, but using the chart saves you time. So, this example creates a Shape, MyDrawing, using the TestChart.Shapes.AddShape() function. The points you supply when creating the shape are unimportant because you’ll change them later anyway.

Every closed shape consists of one more node than the drawing requires. If you’re drawing a four-sided shape, as we are here, then you need five nodes to define it. That’s what the next step does. It redefines the position of the shape based on five nodes, using the first position to define the fifth nodesa really important concept to keep in mind if you want the shape to be solid.

Finally, the example changes the inside color of the resulting shape to a bright green and the line that defines it to a darker green. You’ll see these results from the example:

ChartDrawing02

As you can see, the shape precisely follows the two lines as it should. You can perform other drawing tricks like this one using the technique I’ve provided here. Let me know if you have any questions about it at John@JohnMuellerBooks.com.

 

Creating a Custom Styles Group

A problem with some of the automation provided by Word 2010 is that you don’t always get the results you want. Even if you create a custom Ribbon configuration as described in the A Quick Method for Adding Custom Ribbon Elements post, you may not get the results you want without some experimentation. For example, you might want to add a full version of the Styles group as shown here to a custom Ribbon.

StylesGroup01

As described in my previous post, Office 2010 makes it easy to add tabs, groups, and existing or custom commands to the Ribbon. The standard practice would be to create a custom tab, Styles group within that tab, and then add the Quick Styles command to it as shown here.

StylesGroup02

The result isn’t going to be what you expected. The Custom tab will look something like this:

StylesGroup03

You don’t see any of the styles. To see them, you must click the down arrow, rather than simply view them as you would on the Home tab. No amount of configuration in Word is going to change this display. In order to get the right appearance, you must export your customizations as described in my previous post and modify the XML directly. After you export the customizations, you see XML for the Custom tab that looks like this:

<mso:tab id="mso_c1.F646E0" label="Custom" insertBeforeQ="mso:TabInsert">
   <mso:group id="mso_c3.F7999E" label="Styles" autoScale="true">
      <mso:gallery idQ="mso:QuickStylesGallery" showInRibbon="false" visible="true"/>
   </mso:group>
</mso:tab>

It’s important to note that the autoScale=”true” setting is correct and it’s something you’d need to add if you were creating this code by hand. However, the <mso:gallery> tag isn’t set up correctly right now. Unfortunately, VBA for Dummies currently suggests the answer, but doesn’t discuss it outright. My book, RibbonX for Dummies, provides better information in this case on page 151 when it describes the attributes for a gallery. In order to get the appearance you want, you need to change the code so it looks like this:

<mso:tab id="mso_c1.F646E0" label="Custom" insertBeforeQ="mso:TabInsert">
   <mso:group id="mso_c3.F7999E" label="Styles" autoScale="true">
      <mso:gallery idQ="mso:QuickStylesGallery" size="large" visible="true"/>
   </mso:group>
</mso:tab>

which means removing the showInRibbon=”false” attribute and adding a size=”large” attribute. When you make these two changes and import the updated XML into Word, you’ll see a new version of the Custom tab as shown here:

StylesGroup04

As you can see, the Custom tab now presents the Quick Styles Gallery as you’d expect. There are many situations where you need to modify the Ribbon customizations using similar techniques to get the appearance you want. If you have any questions, please contact me at John@JohnMuellerBooks.com.

 

Using References in VBA

There are many situations where you need to create a reference to an external module in VBA in order to use the capabilities of that module. A reference provides access to the module. If you want to create forms with special features, then you need a reference to the module that contains those special features. Many examples in VBA for Dummies rely on references to perform their work.

I’ve had a number of readers write that they can’t find the modules I reference in the book. The most common request is about the example that begins on page 156. You can see the problem screenshot on page 157 in Figure 7-1.

To begin, you choose Tools | References to display the References – VBAProject dialog box. That dialog box will contain the references specific to your version of Office and the versions of other products installed on your machine. Because there is so much potential for differences between systems, your dialog box will never precisely match mine. In fact, my dialog box no longer matches those in the book because I’ve upgraded my version of Office since then (partly so I can discuss Office 2010 changes in my posts). Here are the default references for Excel 2010 (remember, your dialog box may vary from the one shown hereI’m using Office 2010 Professional Plus).

References01

As you can see, this version of Excel uses the Microsoft Excel 14.0 Object Library, not the 12.0 version shown in Figure 7-1. The example still works fine, despite the differences. When I opened the ExcelForms.xls file, Excel automatically updated the references used by the example applications for me. Even if it hadn’t, all I’d need to do is update them manually using the References – VBAProject dialog box.

A good rule of thumb is that if the book references a particular version of a module, you can use that version or higher safely. Generally, Microsoft makes modules used for VBA backward compatible. It isn’t always the case though. For example, the Ribbon interface negates the use of older menu-style interface changes in many respects. Yes, the changes appear on the Add-ins tab, but they don’t quite work the same as they did in the past, so clever changes might not work at all. From a module perspective, the updates do generally work.

Some readers have asked about using older modules. For example, if I have the 12.0 version of a module in the book, they wonder about using the 10.0 version. In this case, there aren’t any guarantees. Most of the examples should work just fine, but there isn’t any guarantee that they will. If someone has tried using an older version and found that something doesn’t work, let me know about it. When I have enough of these situations, I’ll create a post so that you know what won’t work with an older version. A safer bet is to obtain a copy of the 4th edition of my book which does use some older modules (unfortunately, I wasn’t able to find a link for this postAmazon shows it as unavailable).

What happens if you can’t find a module at all? It could mean that you’re using an educational version of Office. These educational versions have caused a good deal of problems in getting the book examples to work. In general, I don’t recommend using the educational versions for working with VBA unless you want to write simple examples. Microsoft doesn’t make the educational versions feature complete for a good reasonthey want you to buy the full version of their product so they can make large sums of money. If you do run across other problems, please let me at John@JohnMuellerBooks.com.

 

Accessing Windows Functions

There is currently an example of how to access the registry on page 290 (Chapter 13) of VBA for Dummies. It will still work when working with the 32-bit version of Office 2010 and VBA 7.0albeit unreliably. This example won’t work with the 64-bit version of Office 2010 and could, in fact, cause problems in some cases (although I haven’t personally been able to produce something to show you, the fact remains that pointers are inherently dangerous and should be used with caution).

The registry access code shown in Listing 13-5 is used by the AccessAnObject() macro in Listing 13-6 on page 292. This macro will work fine after you make changes to the RegistryFuncs module. Here’s the current RegistryFuncs code.

' This Windows API function opens a registry key.
Public Declare Function RegOpenKey _
    Lib "advapi32.dll" _
    Alias "RegOpenKeyA" (ByVal HKey As Long, _
                         ByVal lpSubKey As String, _
                         phkResult As Long) As Boolean
 
' Use this enumeration for the top level keys.
Public Enum ROOT_KEYS
    HKEY_CLASSES_ROOT = &H80000000
    HKEY_CURRENT_USER = &H80000001
    HKEY_LOCAL_MACHINE = &H80000002
    HKEY_USERS = &H80000003
    HKEY_PERFORMANCE_DATA = &H80000004
    HKEY_CURRENT_CONFIG = &H80000005
    HKEY_DYN_DATA = &H80000006
End Enum
 
' This Windows API function reads a value from a key.
Declare Function RegQueryValue _
    Lib "advapi32.dll" _
    Alias "RegQueryValueA" (ByVal HKey As Long, _
                            ByVal lpSubKey As String, _
                            ByVal lpValue As String, _
                            lpcbValue As Long) As Boolean
 
' This Windows API function closes a registry key.
Public Declare Function RegCloseKey _
    Lib "advapi32.dll" (ByVal HKey As Long) As Boolean

Notice that these functions all use a Long data type to hold the pointer returned from the function calls. A Long isn’t the same as a pointer, but because there weren’t any pointer types before VBA 7.0 a Long was the best you could hope to achieve. VBA 7.0 does support pointers and you can see the list of them in the Application Programming Interface Compatibility topic on MSDN.

The correct replacement for the pointers in this example is a LongPtr. The LongPtr type works equally well with both 32-bit and 64-bit versions of Office. Of course, earlier versions of VBA don’t support the LongPtr type, so you need some way to work in both environments, which was the purpose of the Checking the VBA Environment post. Consequently, the new code looks like this:

' Use this enumeration for the top level keys.
Public Enum ROOT_KEYS
    HKEY_CLASSES_ROOT = &H80000000
    HKEY_CURRENT_USER = &H80000001
    HKEY_LOCAL_MACHINE = &H80000002
    HKEY_USERS = &H80000003
    HKEY_PERFORMANCE_DATA = &H80000004
    HKEY_CURRENT_CONFIG = &H80000005
    HKEY_DYN_DATA = &H80000006
End Enum
     
#If VBA7 Then
 
    ' This code replaces the Long values used in
    ' previous versions of VBA with LongPtr values.
    ' This Windows API function opens a registry key.
    Public Declare Function RegOpenKey _
        Lib "advapi32.dll" _
        Alias "RegOpenKeyA" (ByVal HKey As LongPtr, _
                             ByVal lpSubKey As String, _
                             phkResult As Long) As Boolean
     
    ' This Windows API function reads a value from a key.
    Declare Function RegQueryValue _
        Lib "advapi32.dll" _
        Alias "RegQueryValueA" (ByVal HKey As LongPtr, _
                                ByVal lpSubKey As String, _
                                ByVal lpValue As String, _
                                lpcbValue As LongPtr) As Boolean
     
    ' This Windows API function closes a registry key.
    Public Declare Function RegCloseKey _
        Lib "advapi32.dll" (ByVal HKey As LongPtr) As Boolean
 
#Else
 
    ' This Windows API function opens a registry key.
    Public Declare Function RegOpenKey _
        Lib "advapi32.dll" _
        Alias "RegOpenKeyA" (ByVal HKey As Long, _
                             ByVal lpSubKey As String, _
                             phkResult As Long) As Boolean
     
    ' This Windows API function reads a value from a key.
    Declare Function RegQueryValue _
        Lib "advapi32.dll" _
        Alias "RegQueryValueA" (ByVal HKey As Long, _
                                ByVal lpSubKey As String, _
                                ByVal lpValue As String, _
                                lpcbValue As Long) As Boolean
     
    ' This Windows API function closes a registry key.
    Public Declare Function RegCloseKey _
        Lib "advapi32.dll" (ByVal HKey As Long) As Boolean
 
#End If

Please contact me if you have any trouble with this code at John@JohnMuellerBooks.com. Also, be sure to keep letting me know about your Office 2010 needs!