Office Space: Tips and Tricks for Scripting Microsoft Office Applications

Welcome to Office Space, the column that offers tips and tricks for scripting Microsoft® Office applications. We’ll post new tips every Tuesday and Thursday; to see an archive of previous tips, visit the Office Space Archive. And if you have particular questions about Microsoft Office scripting, feel free to send them to scripter@microsoft.com (in English, if possible). We can’t promise to answer all the questions we receive, but we’ll do our best.

Adding Color and Panache to Your Column Charts

One on-going debate we have here at Microsoft (we mean besides whether to but a new Lamborghini or just stick with the Rolls) is this: do system administrators care about aesthetics, do they care even the slightest about how things look? Many Microsoft types argue no, that system administrators are bottom-line folks who never give aesthetics a second thought. A black-and-white TV instead of a color TV? That’s fine. A striped shirt with polka-dot pants? Hey, what difference does it make? Clothes are clothes, right?

The Scripting Guys – well, at least one of them, anyway – beg to differ. Granted, given the choice between something that worked and something that looked good, system administrators would take the thing that worked every time. But what if you could have something that worked and looked good? What if you could gussy up a report or pretty up a chart without much additional work? What if, using just one extra line of code, you could create a Microsoft Excel column chart that included a cool gradient fill like this one:

We had a feeling you’d say that.

Let’s start off by showing you some code that creates a generic column chart, one pretty much devoid of any formatting whatsoever. After that we’ll show you lines of code that can be tacked on the end of this base script to create breathtakingly-beautiful charts. Note that we won’t talk about the basic script at all; if you’re a newcomer to Microsoft Excel you might want to take a look at Episode 1 and Episode 2 in our series on creating charts and graphs.

Here’s the base code for today’s column:

Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
Set objWorkbook = objExcel.Workbooks.Add()
Set objWorksheet = objWorkbook.Worksheets(1)

objWorksheet.Cells(1,1) = "Operating System"
objWorksheet.Cells(2,1) = "Windows Server 2003"
objWorksheet.Cells(3,1) = "Windows XP"
objWorksheet.Cells(4,1) = "Windows 2000"
objWorksheet.Cells(5,1) = "Windows NT 4.0"
objWorksheet.Cells(6,1) = "Other"

objWorksheet.Cells(1,2) = "Number of Computers"
objWorksheet.Cells(2,2) = 545
objWorksheet.Cells(3,2) = 987
objWorksheet.Cells(4,2) = 611
objWorksheet.Cells(5,2) = 431
objWorksheet.Cells(6,2) = 516

Set objRange = objWorksheet.UsedRange
objRange.Select

Set colCharts = objExcel.Charts
colCharts.Add()

Set objChart = colCharts(1)
objChart.Activate

objChart.HasLegend = FALSE
objChart.ChartTitle.Text = "Operating System Use"

objChart.ChartType = -4100

And here’s the chart this code generates:

Of course it’s boring; the whole idea is to take a plain vanilla chart like this and jazz it up a bit. So let’s see what we can do.

We should mention right from the start that we can’t even begin to cover all the formatting options available to you; there are just too many of them. Therefore, we’re going to provide an overview of some of the more interesting things you can do to pretty up your charts and graphs; in most cases we’ll use some shortcut methods to make this formatting even easier. If you’re interested in additional formatting options (including custom options that take a bit more coding), see the Microsoft Excel VBA Language Reference on MSDN.

Let’s start out by adding a gradient fill to the columns in our column chart. You might not be aware of this, but Excel actually comes with a number of predefined gradients fills. These are found in the Fill Effects dialog box:

Should we care about that? You bet we should; that’s because Excel includes a method named PresetGradient that allows us to apply one of these gradient fills to a chart series, a chart wall, or a chart floor. (For some reason this method doesn’t seem to work on chart areas or plot areas, but we’ll show you a different way to apply a gradient fill to those objects.) To use the PresetGradient method you’ll need to do the following:

Determine the constant value for the gradient style. As you probably know, gradients come in many flavors: the fill can begin at the top and work its way down, the fill can begin at the center of the object and work its way out, etc. (In the Fill Effects dialog box, these different styles are listed under Shading styles.) When using the PresetGradient method, you’ll need to use one the following values in your script:

In our sample code, we’ll use the horizontal gradient style, meaning we’ll specify the value 1.

Determine the constant value for the gradient type. In the Fill Effects dialog box you’ll see a section labeled Variants; inside this section are four gradient fill variations. Each variation has a number assigned to it:

To specify a gradient type simply specify the number of the desired variant. For example, to use the gradient that features orange and red at the top and blue at the bottom, use a 2 when specifying the gradient type. In our sample code, we’re going to use variant 1. (Note that if gradient style is msoGradientFromCenter, the gradient type can only be 1 or 2.)

Determine the constant value for the gradient. Each gradient has a unique constant and value; for example, the Nightfall gradient we’ll be using has a constant name of msoGradientNightfall and a value of 3. How did we determine that? The easiest way is just to view the Microsoft Office VBA Language Reference and click on msoPresetGradientType.

After all that explanation, applying a gradient fill to our chart columns takes just one line of code:

objChart.SeriesCollection(1).Fill.PresetGradient 1,1,3

As you can see, we reference the Fill object of the first (and only) data series in our chart, and then call the PresetGradient method, passing these three parameters:

• 1, representing the horizontal gradient style.

• 1, representing the gradient type.

• 3, representing the Nightfall gradient.

That’s it: add that single line of code to the end of the base script and see what happens.

Like we said, we were able to apply this same method to the walls and floors of our column chart; however, we couldn’t get it to work with the chart area or the plot area. (No error message was generated, and the changes seemed to get applied, but they weren’t visible on screen.) There might be a very simple explanation for that, but rather than spend a lot of time looking into it we decided to use a workaround. Want to apply a gradient fill to a chart area or plot area? Then use code like this:

objChart.PlotArea.Fill.ForeColor.SchemeColor = 27
objChart.PlotArea.Fill.BackColor.SchemeColor = 46
objChart.PlotArea.Fill.TwoColorGradient 1,1

As you can see, we specify the ForeColor.SchemeColor and the BackColor.SchemeColor. (For more information about what colors are available and what their corresponding color numbers are, see this previous Office Space column.) We then call the TwoColorGradient method, passing two parameters:

• 1, representing the gradient style.

• 1, representing the gradient type.

Add that code on to the bottom of our base script, and we end up with a chart that looks like this:

Incidentally, you can use this same approach to fill chart columns or any other object. That enables you to apply a custom gradient rather than being stuck with the predefined fills.

We’d like to mention two other formatting options before we bring today’s column to a close. First, you can easily apply predefined textures to your charts; all you have to do is call the PresetTexured method and specify the desired texture. Want chart columns that look like they were made out of marble? Then add this line of code to your base script:

objChart.SeriesCollection(1).Fill.PresetTextured(10)

And here’s what our elegant new chart looks like:

Yeah, we thought it was cool, too.

Here’s one for the road. Add this line of code to the base script and see what happens (you might have to change the file):

objChart.SetBackgroundPicture _
    ("C:\Documents and Settings\All Users\Documents\My Pictures\Sample Pictures\Sunset.jpg")

If all goes well, you should get a graph that looks something like this:

Now that’s something even a system administrator could love!