Add A Chart To A Microsoft Document With Office Web Components

Ads by Google

barcharts   Add A Chart To A Microsoft Document With Office Web ComponentsIsn’t it amazing how something as simple as creating a chart can feel so complicated sometimes? Whether you’re using Excel, Word, Powerpoint or Access – sometimes it would be nice to just insert a quick chart with a bunch of data without having to do a whole lot of configuring and messing about.

In this article I’m going to show you how to use a really convenient and simple VBA object from Microsoft Office Web Components that lets you insert a Chart directly into a document, presentation or anything else where you can create VBA code to run it. This can also include non-Microsoft products that have a VBA back-end, which many do.

In this example, I’m going to show you how to enable design mode in your program (in this case, we’ll use Microsoft Word), embed the chart object into your project, and then how to write the code that fills it in with data.

In some of my past VBA articles, a few of you mentioned that the task at hand was a little too complicated for anyone that has never written code before. I hope that after reading this article, you’ll be motivated to try your hand with VBA. The code included here is very straightforward and simple to write. Embedding this chart is a great way to get your hands dirty with VBA if you’ve never tried it before.

Ads by Google

If you do know VBA, then this is a great tool to start embedding quick charts into your projects with much less effort than ever before.

Enabling Design Mode

In this example, I wanted to put together a quick document with an embedded chart that displays data from the current month of my budget as well as the monthly average of spending year to date.

In this example, I’m going to place those values hard-coded in an “array” right inside the VBA code, but ultimately you can use a variety of techniques to load those array variables (an array is simply a variable that contains a list of values) from other sources, like an external spreadsheet, or even a form where you enter the values in manually every month.

To learn how to import data into Word from Excel, check out my article on the topic. For the purpose of this article, we’re going to focus on creating the chart to display data.

wordchart1   Add A Chart To A Microsoft Document With Office Web Components

Now that I’ve started my document, I want to get into the code. In Word or Excel, you do this by clicking on “File”, “Options” and then click on the option to Customize the Ribbon. On the right side of options, you’ll see the “Customize the Ribbon” column, and in the “Main Tabs” field you should see “Developer” unchecked. Check that box.

wordchart2   Add A Chart To A Microsoft Document With Office Web Components

Back on your document, you should now see the “Developer” menu item available. Within this menu, you will find the “Design Mode” button.

wordchart3   Add A Chart To A Microsoft Document With Office Web Components

Click on Design Mode, and then click on that folder/tools icon for “Legacy Tools”, and then at the lower right corner of the popup box, under ActiveX Controls, click on the “More Controls” button.

Scroll down the list of available controls until you get to “Microsoft Office Chart xx.x”. If you have Microsoft Office installed on your computer, then this control will most likely be available.

wordchart4   Add A Chart To A Microsoft Document With Office Web Components

Once you insert the control into your document, you’ll see it show up as a box that says “Microsoft Office Web Components” inside. This is essentially your empty chart, ready to display your data.

wordchart5   Add A Chart To A Microsoft Document With Office Web Components

The next step is to write up the code that will set up your chart type, and display the data. If you want the process to be manual, you could place a button on the document that would load the data into the chart, but in my case I wanted the whole thing to be complete automated. The moment I open the document, I wanted it to run the script that loads the chart, so you can do this in the code (click on the Visual Basic button to get into the code editor), and selecting the Document Object, and the “Open” event.

This will automatically place an empty function called “Document_Open()” into your code. Inside that function, you want to paste the following code.

Private Sub Document_Open()
 Dim i As Integer
 Dim oChart
 Dim oSeries1
 Dim oSeries2

 'Create arrays for the x-values and the y-values
 Dim xValues As Variant, yValues1 As Variant, yValues2 As Variant

 xValues = Array("Electric Bill", "Mortgage", "Phone Bill", _
 "Heating Bill", "Groceries", _
 "Gasoline", "Clothes", "Shopping")
 yValues1 = Array(124.53, 1250.24, 45.43, 253.54, 143.32, 259.85, 102.5, _
 569.94)
 yValues2 = Array(110, 1250, 50, 200, 130, 274, 95, _
 300)

 

This section of code creates three arrays. The first (xValues) is basically your x-axis list of descriptions for each data element. In my case I’m creating a column bar graph with yValues1, but you can also create a line graph. I’ll show you how to do that with yValues2. Now paste the following segment of code as well.

 With ThisDocument.ChartSpace1
 .Clear
 .Refresh
 Set oChart = .Charts.Add

 oChart.HasTitle = True
 oChart.Title.Caption = "Monthly Budget Numbers vs Average"

 

This section of code creates actually creates the chart itself within your “chartspace” container. Your chart doesn’t have any data at this point, but with a couple of commands you can set the title for the chart as well as the caption. Now it’s time to add the data. Paste the following code below the code you’ve already pasted to do so.

Set oSeries1 = oChart.SeriesCollection.Add
 With oSeries1
 .Caption = "This Month"
 .SetData chDimCategories, chDataLiteral, xValues
 .SetData chDimValues, chDataLiteral, yValues1
 .Type = chChartTypeColumnClustered
 End With

 'Add another series to the chart with the x-values and y-values
 'from the arrays and set the series type to a line chart
 Set oSeries = oChart.SeriesCollection.Add
 With oSeries
 .Caption = "Average Spending"
 .SetData chDimCategories, chDataLiteral, xValues
 .SetData chDimValues, chDataLiteral, yValues2
 .Type = chChartTypeLineMarkers
 End With

The code above creates two series to display in your chart. The first series configures the data to display inside the chart object as a “ColumnClustered” format, and the second series is set up to display as a “TypeLineMarkers” format. This will display both sets of values on the same chart, but it’ll use different chart types – which can actually be a very cool way to display and compare multiple sets of data.

Now that the data is added, all that’s left is to tidy up the axis and finish off the chart details.

 'Format the Value Axes
 oChart.Axes(chAxisPositionLeft).NumberFormat = "$#,##0"

 oChart.Axes(chAxisPositionLeft).MajorUnit = 1000

 'Show the legend at the bottom of the chart
 oChart.HasLegend = True
 oChart.Legend.Position = chLegendPositionBottom
 End With
End Sub

The code above formats the numbers on the left axis to display in numerical dollar format. The next line configures the maximum limit of the vertical axis. Since I know individual items won’t go over $1000, that’s what I set the max y-axis limit to.

Save the document, close it out, re-open, and voila – up comes your chart, automatically loaded with the data from your two arrays.

wordchart6   Add A Chart To A Microsoft Document With Office Web Components

Now that you know how to insert the charts and automatically load data into them, just think about the possibilities. All you have to do is get any external data into those arrays – maybe an Excel spreadsheet, maybe an Access database, or anything else – and suddenly this document becomes a very dynamic and valuable window into the stored data.

This is just the tip of the iceburg with the MS Office Web Components objects. I started off with the charts because I find that feature to be the most exciting. To add charts to any VBA application you want with a very short script is very useful and very powerful.

If you’re all about charts, why not give this chart object a try? Do you know of any other useful charting objects for VBA? How do you embed data into your apps? Share your thoughts and experiences in the comments section below.

Image Credit: Tablet Finance Bar Chart via Shutterstock

Ads by Google

5 Comments - Write a Comment

Reply

Lambvolution

is it works on office 2007?

Ryan Dube

Yes, it works in Office 2007. You’ll see it under References as “Microsoft Office 12.0 Object Library”, and when you go to insert more controls, it’ll be listed as Microsoft Office Chart 10.0 or 11.0.

Reply

Rigoberto Garcia

Excelent article Ryan. I have Office 2010 and can not find the control “Microsoft Office Chart xx.x”. As you mention in the article, may already be installed. How do I insert?

Ryan Dube

Hi Rigoberto. Thank you. There are two steps to add the control. First you need to add the reference when you’re in the VBA code window. It’s under Tools – References. And you just scroll down and find “Microsoft OFfice 12.0 Object Library” and select it. Then when you go to insert a control and click on the “More Controls” button inside of Excel, you’ll see it listed at Microsoft Office Chart 10.0 or 11.0. I hope that helps!

Rigoberto Garcia

Thank you Ryan. The reference was already added in the VBA code window (Microsoft Office 14.0 Object Library) and does not get control “Microsoft Office Chart 10.0 or 11.0″ to insert it. I also added the reference “Microsoft Graph 12.0 Object Library” and I managed not to be presented on control. Any other ideas?

Your comment