Dealing with Acronyms and Abbreviations

My books are packed with acronyms and abbreviations, and readers complain about them all the time. An acronym is a series of letters that shorten a term and you can say. For example, Language INtegrated Query (LINQ) is pronounced “link” so it counts as an acronym. An abbreviation is a shortened version of a term or phrase. For example, MicroSoft Developer Network (MSDN) is an abbreviation because you can’t say the term and must instead say each letter individually. Whether the term is an acronym or an abbreviation, I usually try to define it once every chapter. However, some truly common terms are only defined once in a book and if a term is considered universally known outside computer circles, such as CPU (for Central Processing Unit), I don’t define it at all.

Unfortunately, making an assumption can be a dangerous thing. I try to err on the side of defining terms too often so that readers can gain maximum benefit from my books with the least amount of effort. However, even making my best efforts, there are times when you might find an acronym or abbreviation that you simply don’t understand in one of my books. When this happens, you can always contact me at John@JohnMuellerBooks.com and I’ll be happy to define it for you. My goal is to ensure you have a great reading experience and that you discover everything possible about the topic at hand.

Some people prefer to do things for themselves. Hands on learning produces the best results for them and I do understand the need to address the learning methods each person uses with greatest ease. In this case, you have other options for finding the term you need defined. These sites will provide you with common terms used in my books (depending on the book, you may need to use more than one site):

Of course, there are many other fine online references, but these references should provide what you need in most cases. The worst case scenario would be to use the acronym or abbreviation without really knowing what it means. I encounter this problem all too often. Readers will contact me with a question that I truly can’t understand because of a misused term. Knowing what terms mean is an essential part of clear communication. Given that most of my communication is through e-mail, clear communication saves time and effort for everyone involved.

The question I get asked relatively often about acronyms and abbreviations is why the computer community uses them at all. After all, they’re confusing. Typing the full term every time you wanted to use it would be cumbersome at the least and error prone as well. Using a shorter term means concise communication. Using the terms correctly means precise communication. Every trade has its jargon and those jargon terms were created in order to ensure that two people communicating about a topic could do so in the most precise manner possible. I’ve discussed the need for jargon in the past in posts such as Power Words.

 

VBA Book Needs and Wants

A lot of people still buy VBA for Dummies even though this book has gotten a little long in the tooth. The reason is simple, the book still works fine because Microsoft has put little effort into making VBA a better language. Even though the idea was that people would move to Visual Studio to create Microsoft Office add-ons, the fact is that most of the people using VBA have no desire whatsoever to make the move. In addition, there is a huge base of VBA resources available and no business in its right mind would ever stop using them as long as they get the job done. So, the fact of the matter is that VBA for Dummies will continue to be a great book for your shelf.

However, I also realize the book has shortcomings. It goes too far in some directions and not nearly far enough in others. The blog posts for this book point out a number of these flaws and provide fixes for them. In addition, people have been asking for a “next book” for quite some time now. With this in mind, I’m currently working on ideas for that next book, the one that you want to put right beside VBA for Dummies on your shelf to get a fuller picture of what VBA can do for you and to act as a resource when you need it.

So far I have collected well over 1,200 reader e-mails for this book. Certainly, I have enough ideas to start another book. However, I do want to hear from you. What sorts of things do you consider essential for that next book? Some readers have suggested that I open my book up to products other than Microsoft Office. There are a number of them out there that do use VBA (or something close enough that knowing VBA helps quite a lot). Other readers have requested better information about forms and still others want to see a full-fledged application of some sort. Many of the e-mails I receive talk about interoperability between Office products. In short, there are a lot of potential areas that you could tell me you need, but perhaps you have some unique need that I should consider.

VBA for Dummies is a great book that continues to sell well and I want to support your coding efforts in every way that I can. What I need are ideas that would generally appeal to everyone. Don’t send me a request to provide what amounts to free consulting for your specific application. A request to provide better support for forms that help format content is a valid request; a request to demonstrate access connection techniques for your company’s local intranet won’t work at all. Contact me with your ideas for that next book at John@JohnMuellerBooks.com.

As always, thank you for the incredible level of support you have provided me over the years. I continue to be amazed at some of the e-mails I receive. These ideas would be just another example of your desire to have high quality technical books on your shelves.

 

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.

 

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.