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 Graphs with Microsoft Excel: The Basic Column Chart

Way back in 1886, when Coca Cola was first invented, the soft drink was peddled as a so-called “patent medicine,” with advertisements extolling “the valuable tonic and nerve stimulant properties of the coca plant and cola nuts." As time went on, the company began downplaying the alleged medicinal properties of Coca Cola, and today few, if any people, would tout the soft drink as anything other than “Delicious and Refreshing.”

In a way, the Office Space column has followed a somewhat similar path (well, minus the huge success and enormous amount of money accorded the Coca-Cola company). When we originally hatched the idea for the column (in a secret meeting held in the Scripting Cave, located somewhere on the outskirts of Gotham City) one of the driving forces was the fact that “People want to know how to script the creation of graphs and charts using Microsoft Excel.” That was definitely true, and still is. So how many columns have we had that talked about creating charts and graphs in Excel? You got it: zero, zip, zilch.

In other words: none.

Of course, the primary mission of the Scripting Guys is to correct injustices and to right wrongs (the fact that we’re usually responsible for those wrongs in the first place is beside the point). Therefore we’ve decided that – beginning today – every Thursday for the next few weeks will be Graph Day: we’ll devote the Thursday column entirely to creating graphs and charts in Excel.

Thank you. And a happy Graph Day to you, too.

Today we’re going to see if we can create a basic graph using Excel; assuming we succeed, we’ll then spend subsequent columns trying to spruce that basic graph up a bit, as well as introduce additional chart types and nifty little charting tips and tricks. So maybe we should get right to it then.

Let’s say you have some data regarding the types of computers used in your organization; to be a bit more specific, let’s say you have this data:

What we’d like to do is write a script that can generate a basic column chart similar to this:

Can we use a script to create a graph similar to this one? Did you even have to ask?

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()

If you were expecting something complicated, you’re going to be disappointed: creating a basic chart in Microsoft Excel is unbelievably easy. In fact, it’s even easier than it might first appear; after all, most of the code shown above is there simply to create a blank worksheet and then enter the data to be graphed. Drawing the graph itself, as we’ll explain, requires only two lines of code.

We start off – as we almost always do – by creating an instance of the Excel.Application object and setting the Visible property to True; that gives us a running instance of Excel that we can actually see on screen. We use the Add method to create a new workbook, then use this line of code to bind to the first worksheet (Sheet 1) in that workbook:

Set objWorksheet = objWorkbook.Worksheets(1)

Of course, now we need to populate our worksheet with some data; the next 12 lines of code are there simply to give us a worksheet that looks like this:

If you’re a devoted follower of the Office Space column (and who isn’t?) everything up to this point should be very familiar.

Now that we have our dataset (and note that we don’t have any blank rows or columns) we’re ready to create our graph. Before we can do that, we need to tell Excel which part of the dataset should be graphed. Because we want to graph the entire worksheet we use this code to create a range object encompassing all the cells in the spreadsheet that contain data:

Set objRange = objWorksheet.UsedRange

Once we’ve defined the range we then use this line of code to select all the cells in that range; by default, Excel will create a graph using all the selected cells:

objRange.Select

And now for the moment of truth. To draw our chart all we need to do is create an instance of the Charts collection and then call the Add method. That takes just two lines of code:

Set colCharts = objExcel.Charts
colCharts.Add()

That’s it: by default Excel creates a basic column chart that looks just like this:

Had we known creating Excel graphs was this easy we would have written this column a long time ago.

Yes, we know: now you have tons of questions about creating charts. (How do I change the colors? How do I create a pie chart? How do I graph multiple years on the same chart?) We’ll cover all those questions – and more – in future columns. For now we’ll just do a couple things to pretty this chart up:

• We’ll get rid of the legend (the box labeled Number of Computers). In this case, the legend just takes up space without adding any real value. (Which is exactly how the Scripting Guys performance reviews read this year.)

• We’ll change the chart title from Number of Computers to Operating System Use.

And then, because it is Graph Day, we’ll give you a special little bonus treat as well.

To modify the default settings for a graph we simply need to activate that graph and then change the appropriate properties. To activate our graph (which happens to be the only graph in the current workbook) we use these two lines of code:

Set objChart = colCharts(1)
objChart.Activate

All we’re doing here is binding to the first chart in the Charts collection and then calling the Activate method. From there all we need to do is add this code in order to get rid of the legend and to change the chart title:

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

And here’s what our new chart looks like:

Scripting: It Revives and Sustains.

Here’s the complete script for creating that 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(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"

And what about our promised Graph Day present? Well, we aren’t going to talk about different chart types today, but just tack this line of code on to the end of the preceding script:

objChart.ChartType = -4100

If all goes according to plan, your revised script should generate a chart that looks like this:

If that isn’t “delicious, exhilarating, refreshing and invigorating” we don’t know what is. See you next week.