Dynamic MSGraph.Chart in PowerPoint presentations

Introduction

A few weeks ago, my colleagues told me about their idea to have "an app" to promote one of our new products. While I like fresh ideas, we were lacking time and means to have "an app". So I proposed to make it in PowerPoint, with VBA. Their questions and demands (some charts, some choices,...) didn't seem that hard to create... but I spend a really long time how to properly handle charts on PowerPoint.

In this article I will briefly explain how to add this old piece of software into your PowerPoint application, what the pitfalls are and how it can be handled to produce neat. I'm using PowerPoint 2010.

The purpose to show you how to add a chart to an existing powerslide, manage the data and prepare the slide for further use.

Background

I assume you know how to open the Developer tab in PowerPoint.

One of the best Microsoft resources to check out the object model of MSGraph.Chart. Don't expect too much of it.

Another great Microsoft resource that talks about the implementation of MSChart.

An overview of the shiny colors you can use in such MSGraph's.

Using the code

Adding the MSGraph.Chart on a slide

The explanation of the code

Adding the MSGraph.Chart is already a neat experience. It comes with its own dataset and way of handling data. We won't drag and drop it on the slide, but add it in code. So, open a PowerPoint slide, go to the "Developer" tab, click on "Visual Basic" and check out the first folder in the treeview on the left. It should show you "Microsoft Powerpoint Objects" and at least one slide listed.

Click on the name and slide, now we'll add the code to insert a MSGraph. We'll add it as a Shape, although it will also remain accessible as MSGraph.Chart. A confusing situation.

It's positioned using off sets from the left top corner. The size of this graph is relative to width and height of the page. See the picture below to judge the results.

After adding it, we'll have to clear the cells as MSGraph always comes with a dataset.

We're setting some aspects of the chart, like it's type (ChartType). Feel free to experiment to see the result. Intellisense does work so use it (press ctrl + space).

Next comes the most difficult part... adding actual data. You have to clear the cells in order to get rid of the original dataset. Then, you can built your own. Compare the snippet below with the code and the screenshot and you'll see how it works. It looks like Excel, but not entirely.
"Create a new data series: Data series entries are in the "0" column, and are referenced by .Range("0#") where # is the row in the datasheet. Column titles are in row "0", and are referenced by .Range("$0") where "&" is the row letter."

Confusing? Right click on a MSGraph, select "Chart Object" and "Edit". Check out the screenshot below and see how much easier it becomes to figure out what goes where.

You don't see a MSGraph yet? Indeed! So please read on... I never told you it was going to be straightforward. My advice: draw it on paper, it becomes confusing quite fast.

Finally, we'll be adapting some colors to avoid the lovely nineties look from MSGraph.Chart. See above for a link, it's indeed painfully violent green (4) and plain white (2) using the ColorIndex property. I only need the "Aanvullend pensioen" in a different color, so I've choosen "SeriesCollection(3)". The second color change affects the area behind the chart, not the chartarea itself.

To conclude, I update the MSGraph to show it as I meant it to be.

The actual VBA code

Option Explicit

Dim myChart As Graph.Chart

Sub SetChartData()
          
	Dim lHeight As Single
	Dim lWidth As Single
   
    	' Graph will not cover the entire page   
    	lHeight = ActivePresentation.PageSetup.SlideHeight
    	lWidth = ActivePresentation.PageSetup.SlideWidth
  
	' We'll add the chart here, I'm already on Slide44 but you could as well have slide1
	' the lWidth/5 provides an offset from the left upper corner of 20%, 1/5th), of the page
	Set myChart = Slide44.Shapes.AddOLEObject(Left:=(lWidth / 5), _
                                Top:=(lHeight / 4), _
                                Width:=(lWidth / 1.3), _
                                Height:=(lHeight / 1.4), _
                                ClassName:="MSGraph.Chart", _
                                Link:=0).OLEFormat.Object
     	
	' remove dummy data
    	myChart.Application.DataSheet.Cells.Clear
   
    	myChart.ChartType = xlColumnStacked
    	myChart.WallsAndGridlines2D = False
   
       	' Create a new data series on row 4. Data series
	' entries are in the "0" column, and are referenced
	' by .Range("0#") where # is the row in the datasheet.
	' Comumn titles are in row "O", and are referenced
	' by .Range("$0") where "&" is the row letter.
	'
    	myChart.Application.DataSheet.Range("01").Value = "Loon"
	myChart.Application.DataSheet.Range("02").Value = "Wettelijk pensioen"
	myChart.Application.DataSheet.Range("03").Value = "Aanvullend pensioen"
  
    	myChart.Application.DataSheet.Range("A0").Value = "Pensioen"
    	myChart.Application.DataSheet.Range("B0").Value = "Loon"
   
    	myChart.Application.DataSheet.Range("A1").Value = "0"
    	myChart.Application.DataSheet.Range("A2").Value = "0"
    	myChart.Application.DataSheet.Range("A3").Value = "0"
   
    	myChart.Application.DataSheet.Range("B1").Value = "0"
    	myChart.Application.DataSheet.Range("B2").Value = "0"
    	myChart.Application.DataSheet.Range("B3").Value = "0"
       
    	myChart.SeriesCollection(3).Interior.ColorIndex = 4
    	myChart.ChartArea.Interior.ColorIndex = 2
       
    	myChart.Application.Update
   
End Sub

Two screenshots to illustrate position & data

A view on the position of the MSGraph using the parameters of the above example.

The result of rightclick on the graph, selecting "Chart Object" > "Edit".

You can compare the rows and columns with where the data ends up.

Display the MSGraph on your slide

You don't see the MSGraph.Chart on your slide when you add it by code. Even opening the slide won't help as there's nothing to fire the Sub. There is no autolaunch, onPageOpen, onPresentationStart or whatever in PowerPoint. There is an Autoevents plugin, but that's hassle when the purpose of the slideshow is distrubution as the plugin has to be installed in PowerPoint. So... there has to be another way.

Explanation of the code

Just add a new slide, place it before your previous slide. On this new slide, add a button, click on it and set its Click event to the Sub created in the previous chapter that adds the MSGraph. Clicking this button now sets your MSGraph on the page.

Furthermore, as there is no "SuspendLayout" neither in MSGraph or PowerPoint: if you have a large Graph to update and modify, you can truly impress your friends as it's all performed live on screen as PowerPoint digests your code. In real life circumstances, this means that the Graph is shown and plotted with the default database, then almost disappears as the cells are cleared, then grows as the DataSeries are added and finally receives a fresh new color. That's... awkward to say the least.

So - prepare the button, add the code and, best of all, start the presentation with it (I named the button START anyway). Don't forget, I'm using Slide44, you might be on any other number. In my code, I make the button move the presentation to the next slide to truly position it as the "starter" of the presentation.

The actual VBA code

Option Explicit

Private Sub CommandButtonStart_Click()
	' prepares the next slide	
	Slide44.SetChartData

    	' kicks off the presenation!
	With ActivePresentation.SlideShowWindow
        	.View.GotoSlide (ActivePresentation.SlideShowWindow.View.Slide.SlideIndex + 1)
	End With
End Sub

Remove the MSGraph.Chart from your slide

Oh there's a lot of fun in here... We literally inject the MSGraph.Chart object in the slide. So, even after the presentation, when the deal is closed and champagne is served... it's still there. It actually will not go away until you select the MSGraph and delete it, manually, from the Powerpoint slides.

Worse - forget to delete it and the graphs are positioned on top of each other and you get a free error. I really had a long struggle with this issue as in my final presentation I use three slides with Graphs. I forgot to delete one and *bang* had to start again.

The explanation of the code

Let's just adapt the little chunk of code related to the "start" button we added above. It should be extended to clean the slides, then prepare them and move to the next slide.

So first of all we grab the presenation as it is. Then we'll start looping through pages, shapes, shapes that are embedded objects, MSGraphs. And then we delete them. Be careful - this code only removes MSGraph.Chart.8. Earlier or later versions will remain on your slide. Feel free to use like "MSGraph*" to get rid of all.

The actual VBA code

Option Explicit

Private Sub CommandButtonStart_Click()
	
	Dim sld As Long
	Dim i As Integer
	Dim j As Integer

	' let's loop through all slides
	sld = ActivePresentation.Slides.Count
	For i = 1 To sld
		' loop through all shapes
        	For j = ActivePresentation.Slides(i).Shapes.Count To 1 Step -1
			' find the embedded objects
	        	If ActivePresentation.Slides(i).Shapes(j).Type = msoEmbeddedOLEObject Then
				' check if it's a MSGraph.Chart
				If ActivePresentation.Slides(i).Shapes(j).OLEFormat.ProgID = "MSGraph.Chart.8" Then
					' remove it from the slide
					ActivePresentation.Slides(i).Shapes(j).Delete
				End If
			End If
        	Next j
	Next i

 	' prepares the next slide
	Slide44.SetChartData

    	' kicks off the presenation!
	With ActivePresentation.SlideShowWindow
        	.View.GotoSlide (ActivePresentation.SlideShowWindow.View.Slide.SlideIndex + 1)
	End With
End Sub

Changing the data in the MSGraph.Chart object

Updating data is fairly easy. I related it to a slidebar but you could also use a button or radioboxes or whatever. All you have to do is modify the DataSeries. This only goes if your Chart is freshly added (that's why it's on the fly), not double (that's why it's removed when clicking "start").

The actual VBA code

Option Explicit

Private Sub ChangeChartData(ByVal x As Long)

	myChart.Application.DataSheet.Range("A2").Value = CStr(x * 2)
        myChart.Application.DataSheet.Range("A3").Value = CStr(x * 3)       
    	myChart.Application.DataSheet.Range("B1").Value = CStr(x)
       
	myChart.Application.Update
End Sub

Adding the slider on my slide actually made great use of the "dynamic" updates of MSGraph: the chart moves nicely along with the changing input. All I had to do was make the _Change() event of the slide push its selected value to ChangeChartData()

Points of Interest

Adding the Graph is one thing, removing it another.

Draw the data you want to add and add the row/column names to it. Only way to keep track.

I didn't use Excel because... it actually gave me an error when I added it and I read somewhere it has to be active in order to populate the graphs. This MSGraph.Chart control, old as it is, seemed the faster way forward, keeping in mind a wider distribution of the slides.

Enjoy!