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 Line Charts in Microsoft Excel

With today’s column we’re going to bring our series on charting and graphing in Excel to a close, at least for awhile. (There, there, don’t cry; it’ll be OK.) We realize that we haven’t covered everything you can do with charts and graphs, but with any luck the topics that we have covered will be enough to get you started. We figure that once you’ve mastered the basics you’ll be able to figure out the rest without too much trouble. So far that’s worked with everything from WMI to ADSI; why not with charting and graphing?

For our grand finale we thought we’d do two things: introduce the line chart, and then use some of the code we’ve already talked about to jazz that chart up a bit. That way we not only show you how to create and modify a line chart, but we also kind of do a quick refresher on formatting. It’s like two columns for the price of one.

See: we told you everything would be OK.

Let’s start off by talking about the basic line chart. Line charts are typically used to track changes over time, and make it easy for you to pinpoint trends or anomalies. For example, suppose we have the dataset shown below, which shows the number of people who’ve tried logging on to our three domain controllers in the past week:

If you just glance at the table, you might not see anything amiss. However, that’s not the case when you actually graph this data:

As you can see, atl-dc-01 had an unusually high number of attempted logons on Thursday. Why? Well, we don’t know: there could have been network problems that were kicking people off and forcing them to log back on. Maybe atl-dc-01 went down and everyone had to log on again. Or maybe, just maybe, someone was trying to hack their way into the system. A graph like this won’t necessarily tell you why something unusual happened, but it clearly shows that something out-of-the-ordinary took place on Thursday. And it clearly indicates that you should look into this, just in case. That’s the kind of thing line charts can help you with.

What’s equally nice about the line charts in Excel is that they are no harder to create than the column charts and pie charts we’ve already looked at. As long as you’ve set the data table up correctly – in this case, with the individual computers in rows and the logon attempts per day in columns – then all you have to do is set the ChartType to 65 and you’re on your way. In fact, here’s a script that creates the preceding chart for us:

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

Set objWorksheet = objWorkbook.Worksheets(1)

objWorksheet.Cells(1,2) = "Monday"
objWorksheet.Cells(1,3) = "Tuesday"
objWorksheet.Cells(1,4) = "Wednesday"
objWorksheet.Cells(1,5) = "Thursday"
objWorksheet.Cells(1,6) = "Friday"

objWorksheet.Cells(2,1) = "atl-dc-01"
objWorksheet.Cells(2,2) = 82
objWorksheet.Cells(2,3) = 85
objWorksheet.Cells(2,4) = 91
objWorksheet.Cells(2,5) = 178
objWorksheet.Cells(2,6) = 91

objWorksheet.Cells(3,1) = "atl-dc-02"
objWorksheet.Cells(3,2) = 104
objWorksheet.Cells(3,3) = 87
objWorksheet.Cells(3,4) = 93
objWorksheet.Cells(3,5) = 100
objWorksheet.Cells(3,6) = 96

objWorksheet.Cells(4,1) = "atl-dc-03"
objWorksheet.Cells(4,2) = 78
objWorksheet.Cells(4,3) = 76
objWorksheet.Cells(4,4) = 95
objWorksheet.Cells(4,5) = 90
objWorksheet.Cells(4,6) = 91

Set objRange = objWorksheet.UsedRange
objRange.Select

Set colCharts = objExcel.Charts
colCharts.Add()

Set objChart = colCharts(1)
objChart.Activate

objChart.ChartType = 65

If you don’t understand any of this code, then you might want to take a look at the first article in our series on charting and graphing. Briefly, we create an instance of the Excel.Application object and then set the Visible property to True. We create a new workbook and a new worksheet, and then type in our data.

After the data has been entered we select that data and create and activate a new chart. We then use this line of code to turn our column chart (the standard chart style) into a line chart:

objChart.ChartType = 65

It’s that easy.

Of course, that doesn’t mean we can’t still pretty our chart up a little bit. Let’s start off by filling the plot area with the preset gradient Ocean. That requires just one line of code, and gives us a chart that looks like this:

objChart.PlotArea.Fill.PresetGradient 1,1,7

Not bad, except it is kind of hard to see the individual lines, especially the blue one. (And, yes, we started with a gradient fill specifically to make the lines a little harder to see.) But that’s easy enough to fix. In an Excel line chart, each line is a member of the SeriesCollection collection. (And each member of the SeriesCollection is a Series object.) Each line also has a unique index number: the first line has an index number of 1, the second line has an index number of 2, and so on. If we want to modify the properties of the lines in our line chart all we need to do is reference the appropriate SeriesCollection member and then make the changes. For example, these three lines of code change the weight of all three SeriesCollection lines (one-by-one) to medium:

objChart.SeriesCollection(1).Border.Weight = -4138
objChart.SeriesCollection(2).Border.Weight = -4138
objChart.SeriesCollection(3).Border.Weight = -4138

We can also change other properties of an individual SeriesCollection line. For example, this line of code changes the blue line to a more-readily distinguishable white line:

objChart.SeriesCollection(1).Border.ColorIndex = 2

And seeing as how we changed the color of the line itself, we should also change the color of the data markers:

objChart.SeriesCollection(1).MarkerBackgroundColorIndex = 2

No, this is not too hard. Just go to the VBA Language Reference and look at the properties for the Series object. If you take a look, you’ll see properties such as Border and MarkerBackgroundColorIndex, the same properties we’ve been modifying in our script. Start with the examples we’ve used, and go from there.

Suppose we tack all the “extra” code we’ve just shown you onto the end of our script. What will the chart look like now? Something like this:

See? Already it looks nicer, and is easier to read.

As you gaze in wonder and astonishment, you might notice that the data markers for line one (our white line) have a nice dark outline around them; that makes them stand out a little better. Why do they have a nice outline and the other markers don’t? Well, by default Excel makes the foreground (outline) and background (fill) colors of the data markers the same; for example, our yellow line has both a yellow foreground and a yellow background. Because we changed only the background color of the white line markers, those markers still have a dark blue outline around them. If you like those outlines then just change the foreground color of the markers for lines 2 and 3 to a darker color (like black):

objChart.SeriesCollection(2).MarkerForegroundColorIndex = 1
objChart.SeriesCollection(3).MarkerForegroundColorIndex = 1

Again, you’ll find all these properties – and an explanation of what they are and how to set them – in the VBA Language Reference.

But what if you want to add a chart title, or change the background of the chart area, or maybe put the legend inside a shadow box? You know, something like this:

The truth is, if you’ve followed the previous articles in this series then you already know how to do this. We won’t bother explaining all the code (you’ll find explanations in the previous articles), but here’s a script that creates a fancy chart just like the one shown above:

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

Set objWorksheet = objWorkbook.Worksheets(1)

objWorksheet.Cells(1,2) = "Monday"
objWorksheet.Cells(1,3) = "Tuesday"
objWorksheet.Cells(1,4) = "Wednesday"
objWorksheet.Cells(1,5) = "Thursday"
objWorksheet.Cells(1,6) = "Friday"

objWorksheet.Cells(2,1) = "atl-dc-01"
objWorksheet.Cells(2,2) = 82
objWorksheet.Cells(2,3) = 85
objWorksheet.Cells(2,4) = 91
objWorksheet.Cells(2,5) = 178
objWorksheet.Cells(2,6) = 91

objWorksheet.Cells(3,1) = "atl-dc-02"
objWorksheet.Cells(3,2) = 104
objWorksheet.Cells(3,3) = 87
objWorksheet.Cells(3,4) = 93
objWorksheet.Cells(3,5) = 100
objWorksheet.Cells(3,6) = 96

objWorksheet.Cells(4,1) = "atl-dc-03"
objWorksheet.Cells(4,2) = 78
objWorksheet.Cells(4,3) = 76
objWorksheet.Cells(4,4) = 95
objWorksheet.Cells(4,5) = 90
objWorksheet.Cells(4,6) = 91

Set objRange = objWorksheet.UsedRange
objRange.Select

Set colCharts = objExcel.Charts
colCharts.Add()

Set objChart = colCharts(1)
objChart.Activate

objChart.ChartType = 65

objChart.PlotArea.Fill.PresetGradient 1,1,7

objChart.SeriesCollection(1).Border.Weight = -4138
objChart.SeriesCollection(2).Border.Weight = -4138
objChart.SeriesCollection(3).Border.Weight = -4138

objChart.SeriesCollection(1).Border.ColorIndex = 2
objChart.SeriesCollection(1).MarkerBackgroundColorIndex = 2

objChart.SeriesCollection(2).MarkerForegroundColorIndex = 1
objChart.SeriesCollection(3).MarkerForegroundColorIndex = 1

objChart.HasTitle = True
objChart.ChartTitle.Text = "Attempted Logons"
objChart.ChartTitle.Font.Size = 18

objChart.ChartArea.Fill.Visible = True
objChart.ChartArea.Fill.PresetTextured 15

objChart.ChartArea.Border.LineStyle = 1

objChart.HasLegend = True
objChart.Legend.Shadow = True

Yes, we know: no sooner do we show you how to create cool-looking charts then we tell you that we aren’t going to do any more columns on charting and graphing, at least not for awhile. But, hey, that just means we have complete faith in your ability to take these basic lessons and create cool-looking charts and graphs on your own.

Well, sure, it could also mean that we’re just too lazy to write any more columns on charting and graphing. But the thing about “complete faith in your ability” just sounds so much better! See what you can come up with, and then send your coolest and most-breathtakingly beautiful charts to scripter@microsoft.com (in English, if possible); we’d love to see them.