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.

Formatting the Basic Column Chart

The first time you try something you invariably think it’s great, and you assume that this is it, this is so cool that you’ll do it for the rest of your life. After that initial rush of excitement, however, many people begin to question this lifelong commitment. (If you need proof we can offer up the history of one of the Scripting Brothers, who in the past 20 or so years has been a hang-glider, a motorcycle rider, a Jet Skier, a boat owner, a downhill skier, a mountain biker, and pretty much anything else that required him to spend a huge amount of money before he inevitably got bored and decided to try something else.) It’s a fact of life: after awhile you begin to find all sorts of things wrong with something that you had previously decided was absolutely perfect.

Uh, Scripting Spouses excluded, of course. (Just in case any of them are reading this.)

What does any of that have to do with scripting Microsoft Office applications? Well, in the Office Space column last Thursday we began our new series on creating charts and graphs using Microsoft Excel, and in just one brief lesson we showed you how to create a graph that looked like this:

And no doubt you were pretty excited about that…at first. After awhile, though, you might have started to get a little jaded. What, has the Internet suddenly gone black-and-white? Can’t you use more than two or three colors in a graph? Can’t you use actual colors in a graph, not just these blueish-greyish things? Is that the only font we can use? And a plain white background? If our focus with this series is to create graphs that not only display information but do so in an aesthetically-pleasing way, shouldn’t we do that?

Hey, take it easy; remember, good things come to those who wait. Charting and graphing in Excel is a huge topic, and it’ll take us awhile to get to everything. At the same time, however, we realize that you crave newer and more exciting things. No problem: today we’ll introduce the basics behind formatting a basic column chart like the one we created last week. When we’re done you’ll be able to create a graph that looks like this one:

Well, OK, but beauty is in the eye of the beholder. And while this might not be exactly what you had in mind, we did at least use a color other than blueish-grey, and we did try to spiff it up a little by fiddling with the fonts and adding a border around the chart area. And if you don’t like the chart we came up with, that’s fine: we’ll show you how you can change the colors, fonts, borders, and other elements to help bring your own artistic vision to life. Anything to bring a little spark and excitement to your Excel graphing.

Before we do that, however, let’s take a quick look at some of the objects Excel uses when it comes to working with charts and graphs:

For today we’re going to divide these objects into three basic categories: chart area, chart title, and chart axes. To begin with, we have the different chart area objects: ChartArea, PlotArea, Walls, Floor, and SeriesCollection. Although there are numerous ways to format these objects we’ll limit ourselves to specifying a color and a border. One nice thing about Excel is the fact that for each of these objects, you set the properties using the exact same approach; if you can set the background color for the ChartArea object then you can set the background color for the Walls object as well.

Next we have the ChartTitle object; we won’t do much with that today other than show you how to change the font size and color. And finally we have the three chart axes. Again, we won’t do much with those, but we’ll at least set the font to boldface and give you a starting point for configuring chart axes.

Let’s start by giving our ChartArea an artsy black background. (Yes, just the way the high-priced chart designers in Paris would do it.) Setting the background color for any of our chart area-type objects requires just one line of code: we merely indicate the object and then set the Interior.ColorIndex property to the desired value. This line of code sets our ChartArea color to black:

objChart.ChartArea.Interior.ColorIndex = 1

Note. For a script that can show you all the colors and their corresponding index number click here.

That’s all you have to do. Want to set the color of the PlotArea to a kind of light orange? Well, then go ahead and do it:

objChart.PlotArea.Interior.ColorIndex = 40

Want to change the color of the Walls, the Floor, or one of SeriesCollection objects? Then just set the Interior.ColorIndex property to the desired color. At the end of this column we’ll include a script that sets all these properties; if you peruse that script you should have no trouble determining when – and how – we do things like set the background color of the Floor object.

Now, what about the border we added to the PlotArea? As it turns out, that’s twice as hard as changing the background color; after all, we need one line of code to specify the line weight (that is, the thickness of the line) and another to set the value of the Border.ColorIndex property:

objChart.PlotArea.Border.Weight = 4
objChart.PlotArea.Border.ColorIndex = 46

No, Microsoft doesn’t know that they’re paying us to “teach” you guys things that require hardly any effort whatsoever. And let’s keep that our little secret, OK?

The only part of this that’s even slightly tricky lies in setting property values for the SeriesCollection. That’s because you can have more than one SeriesCollection on a chart. For example, this chart has two series collections:

This means that, come formatting time, we need to indicate which SeriesCollection object we want to work with. But even that’s a cinch: each object in a SeriesCollection is given a unique index number. (The first item in the collection is SeriesCollection(1), the next item in the collection is SeriesCollection(2), and so on.) We have only a single SeriesCollection object in our graph so to specify the color as, say, red, we simply refer to SeriesCollection(1), the first item in the collection, and assign it the color:

objChart.SeriesCollection(1).Interior.ColorIndex = 3

What if we had a second SeriesCollection object and we wanted to set the background color of that object to red? Hey, why not:

objChart.SeriesCollection(2).Interior.ColorIndex = 3

Note. When you refer to our “master” script at the end of the column note that, in addition to setting the background color, we also gave our SeriesCollection a name: 2004. Just one of the many bonus features the Scripting Guys are famous for!

With the ChartTitle all we’re doing is changing the size of the font to 24 point and the color of the font to white. That requires nothing more than accessing ChartTitle.Font and setting the values:

objChart.ChartTitle.Font.Size = 24
objChart.ChartTitle.Font.ColorIndex = 2

Last, but surely not least, we have the chart axes. On a standard column chart we have three different axes, and any time we format the chart we need to specify which axis we want to work with:

• The Value axis. The Value axis (the y-axis to you mathematical types) is the vertical axis for the graph. To work with the Value axis we need to define a constant named xlValue and assign that constant the value 2.

• The Category axis. The Category axis (the x-axis) is the horizontal axis for the graph. To work with the Category axis we need to define a constant named xlCategory and assign that constant the value 1.

• The Series axis. Represents the “depth” of a three-dimensional chart; the Series axis is most useful when you have more than one SeriesCollection in your chart. To work with the Series axis we need to define a constant named xlSeriesAxis and assign that constant the value 3.

In today’s script we change the font of the “tick labels” for all three axes to boldface. We did this mainly to show you how to access the different axes; for example, this line of code modifies the tick labels for the Value axis:

objChart.Axes(xlValue).TickLabels.Font.Bold = TRUE

Again, there are a lot more things we can do to the axes, but most of those will have to wait for a future column. In the meantime, this ought to at least point you in the right direction if you decide to check out the Excel VBA Language Reference and venture out on your own. What you might want to do is take the basic script we provide you and just start changing things. Change the font color or font size of the ChartTitle; change the background color of the PlotArea. And try to do something we didn’t talk about. Anything to keep things interesting, right?

By the way, here’s the script that generates our artistic-looking chart:

Const xlCategory = 1
Const xlValue = 2
Const xlSeriesAxis = 3

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) = 145
objWorksheet.Cells(3,2) = 987
objWorksheet.Cells(4,2) = 611
objWorksheet.Cells(5,2) = 41
objWorksheet.Cells(6,2) = 56

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

objChart.ChartArea.Interior.ColorIndex = 1

objChart.ChartTitle.Font.Size = 24
objChart.ChartTitle.Font.ColorIndex = 2

objChart.Walls.Interior.ColorIndex = 37
objChart.Floor.Interior.ColorIndex = 37

objChart.SeriesCollection(1).Interior.ColorIndex = 3
objChart.SeriesCollection(1).Name = "2004"

objChart.PlotArea.Interior.ColorIndex = 40
objChart.PlotArea.Border.Weight = 4
objChart.PlotArea.Border.ColorIndex = 46

objChart.Axes(xlValue).TickLabels.Font.Bold = TRUE
objChart.Axes(xlCategory).TickLabels.Font.Bold = TRUE
objChart.Axes(xlSeriesAxis).TickLabels.Font.Bold = TRUE

Yes, it is pretty exciting, isn’t it? We feel just like a kid in a charting store.