Sparklines OM in Excel & Confessions of a Microsoft “Insider” (shhhh)

You might be tempted to think that, given that I’m so-called “insider” I’d know all about everything in Office 2010. But, the fact is that there is so much to the products in Office that few people can know it all and know it early.

So, this brings me to when I was recently demo’ing Office 2010 and someone asked me to show off new Excel features. (BTW: there is an Excellent series of posts on the Excel Team blog about Sparklines, how to use and customize them. Read it and the posts that follow it there).

Well, I had seen a few presentations on Sparklines months ago, but the sheer velocity and volume of my workload prevented me from doing much experimentation. So, there I stood with a perfectly delightful customer asking me to demo anything new in Excel, and, while I could speak to the feature and knew the general API for it, my hands-on experience was….lacking. Fortunately, I learned in a matter of moments and was able to demo the feature. The customer was delighted. Whew!

I then showed off Sparklines and Slicers (more on in next post) to every person who came to our kiosk to see Office 2010 unveiled. Each one, without exception, was dazzled by the features and could immediately see how they would save users time.

Excel developers are known for doing some of the most amazing things, and the Excel OM is very rich—including the newly added OM members for Sparklines.

Sparklines are organized into SparklineGroups, and each SparklineGroup contains a variable number of Sparkline items. In the following image you can see my rows and columns of data with the Sparkline items in the last column:


Now, how does what is shown here map to the three objects I just mentioned? In this case, there is one SparklineGroup representing all the financial data, and there are four Sparkline items:



You can easily write some VBA to get access to these various object instances and work with their properties and methods. At the simplest, the following code shows you the basics:

Private Sub EnumSparklines()
    Dim slgs As SparklineGroups
    Dim slg As SparklineGroup
    Dim sl As Sparkline
    Dim r As Range
    Dim slCount As Integer
    Dim i As Integer
   'Using a named range to quickly find the Groups
    Set r = ThisWorkbook.Names("spark1").RefersToRange
    Set slgs = r.SparklineGroups
    Set slg = slgs.Item(1)
    slCount = slg.Count
    For i = 1 To slCount
        Set sl = slg.Item(i)
        MsgBox sl.SourceData

End Sub

Obviously, there is a lot more to the OM here than what I am showing. For example, the Sparkline object has a ModifyLocation method that lets you quickly modify the Range to which the Sparkline instance refers.

Rock Thought of the Day:

I can understand why some people do not like Country music. How much can crooners sing about tractors, anyway? Look, I love a John Deere or McCormick tractor like any other corn-fed boy from the mid-west or the south, but there is a limit to the number of meaningful odes one can write in their honor.

But, what I have found is that most people really like genuine Country music when they hear it. One of the best producers of the best Nashville can offer is Brad Paisley. His new album, American Saturday Night is instantly appealing. And, it proves once again that he is simply one of the best guitar players the music industry (of any genre) has ever seen. If you want smart music that wears its roots on its sleeve—check this record out. I know Johnny Cash, Hank Williams, Buck Owens, and Merle Haggard are proud.

Rock On

Comments (0)

Skip to main content