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.

Creating Pie Charts in Microsoft Excel

As the old saying goes, “Man does not live by bread alone.”

The idea behind the saying, of course, is that variety is the spice of life, that you can’t just do the same thing over and over and over again. Nothing wrong with bread, but mind you, you need other things as well. Things like, say, pie.

This is as true in scripting as it is anywhere else. In the first few installments of our series on drawing charts and graphs in Microsoft Excel we focused on creating column charts. But as they say, human beings, without regard to gender, do not live by column charts alone. They need other things. Things like, say, pie.

Or at least pie charts.

Today we’re going to introduce you to the fine art of making pie charts, starting with this simple script that creates a standard 3D pie chart:

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) = 487
objWorksheet.Cells(4,2) = 211
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.ChartType = -4102

We won’t discuss this code in detail today; if you don’t know what the UsedRange is or don’t know why we called the Activate method then you should refer to the previous columns in this series. For now we’re only concerned with two things; the data being graphed, and the single line of code that turns our data into a 3D pie chart.

Our data – which represents the distribution of computer use in an organization – looks like this:

This, by the way, is the kind of data that pie charts live for. You’re supposed to use a pie chart whenever you want to show relative percentages. In this case, the fact that 487 computers are running Windows XP is really irrelevant; what’s truly important is that over half the computers in the organization are running Windows XP. That’s the kind of information that can be seen – at a glance – with a pie chart.

As for turning the data into a pie chart, our script starts off exactly the same as our previous scripts, the ones that created column charts. We create an instance of Excel, then create a new workbook and a new worksheet. We type our data into the worksheet, select the data, and then add a new chart. We then select the chart and run this line of code:

objChart.ChartType = -4102

That’s the line that gives us a 3D pie chart. Microsoft Excel can create numerous types of charts and graphs, and each type has been assigned a unique ChartType number (you can find these numbers by looking at the xlChartType enumeration in the Microsoft Excel VBA Language Reference on MSDN). For example, would you rather have an exploded doughnut chart? Then set the chart type like so:

objChart.ChartType = 80

Of course, we don’t want an exploded doughnut, we want a 3D pie chart. (It makes at least one Scripting Guy cry if he even thinks about the possibility of a doughnut exploding.) Therefore, we set the value of the ChartType property to -4102, run our script, and get back something that looks like this:

Cool. If you want an exploded pie chart then set the value of the ChartType property to 70. That’ll give you a chart that looks like this:

Nice try. But the big piece is ours.

All in all, the perfect chart. Well, except that the pie slices are a bit hard to see; it might be nice if we could tilt the chart a bit. And because we’re interested in percentages it might be nice if the chart told us what percent of all our computers were running the different operating systems. And we don’t really like that gray background behind the chart. And …

OK, we get the idea. We can’t run through your entire wish list today, but we’ll see if we can do something to spiff up the old pie chart.

Let’s start by seeing if we can tilt the chart a bit. In the charting world, this is known as changing the chart elevation. By default, the elevation of a new chart is 0 degrees. To change that, all we have to do is change the value of the Elevation property. For example, adding this line of code to the bottom of our script gives us a chart with an elevation of 30 degrees:

objChart.Elevation = 30

If we run the script now, we’ll get a chart that looks like this:

Hey, it’s already looking better. In addition to tilting the chart up and down we can also turn it around and around. If we don’t like the location of the Windows XP slice (the big slice at the bottom) then all we have to do is change the value of the Rotation property:

objChart.Rotation = 80

Of course, we’re still missing those percentages. But we can take care of that as well. To show the percentages for each slice we need to define a constant named xlDataLabelsShowPercent (assigning this constant the value 3), and then add this line of code to the end of the script:

objChart.ApplyDataLabels xlDataLabelsShowPercent

Now we have a chart that looks like this:

As you can see, we simply call the ApplyDataLabels method and pass it the constant xlDataLabelsShowPercent, which means we want to show the labels as percents of the whole. (As you might expect, there are other ways to apply data labels; for more information, see the Microsoft Excel VBA Language Reference.)

You say the font’s too small? Then make it bigger; it’s easy to modify the size, color, font style, and other properties of the data labels. All you have to do is bind to the first SeriesCollection (there’s only one in a pie chart like this) and then modify the DataLabels.Font property. For example, this line of code changes the font size to 14-point:

objChart.SeriesCollection(1).DataLabels.Font.Size = 14

And this line of code changes the ColorIndex to white:

objChart.SeriesCollection(1).DataLabels.Font.ColorIndex = 2

We want to show you one more thing, a neat little trick that applies to any kind of chart, not just 3D pie charts. Up till now our pie chart has been bounded by a little gray box known as the plot area. We believe an argument can be made that, in this case at least, that gray box doesn’t really add anything to our chart; besides, we don’t like it. So if we don’t like it, then why not get rid of it?

objChart.PlotArea.Fill.Visible = False
objChart.PlotArea.Border.LineStyle = -4142

To get rid of a plot area or chart area you simply need to reference the appropriate object (PlotArea or ChartArea) and do two things:

• Set the Fill.Visible property to False.

• Set the Border.LineStyle property to -4142, which is the constant for “Don’t show me a border at all.” Note that setting the Visible property alone won’t do the trick: if you do just that you’ll still have a gray border surrounding the chart. To get rid of that gray border you need to configure the LineStyle property as well.

Now we have a chart that looks like this:

Maybe you can’t live by bread alone, but pie charts like these might be enough in and of themselves to ensure a happy and fulfilling life.

And now for the grand finale. Although we won’t show you the code, by combining what you learned today with what you learned previously, you should now be able to create a chart that looks like this:

OK, fine: we’ll show you the code. But this is the last time:

Const xlDataLabelsShowPercent = 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) = 487
objWorksheet.Cells(4,2) = 211
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.ChartType = 70
objChart.Elevation = 30
objChart.Rotation = 80

objChart.ApplyDataLabels xlDataLabelsShowPercent

objChart.PlotArea.Fill.Visible = False
objChart.PlotArea.Border.LineStyle = -4142

objChart.SeriesCollection(1).DataLabels.Font.Size = 14
objChart.SeriesCollection(1).DataLabels.Font.ColorIndex = 2

objChart.ChartArea.Fill.ForeColor.SchemeColor = 49
objChart.ChartArea.Fill.BackColor.SchemeColor = 23
objChart.ChartArea.Fill.TwoColorGradient 1,1

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

objChart.Legend.Shadow = True