Isn’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.
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.
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.
Back on your document, you should now see the “Developer” menu item available. Within this menu, you will find the “Design Mode” button.
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.
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.
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 AxesoChart.Axes(chAxisPositionLeft).NumberFormat = "$#,##0" oChart.Axes(chAxisPositionLeft).MajorUnit = 1000 'Show the legend at the bottom of the chart oChart.HasLegend = True oChart.Legend.Position = chLegendPositionBottomEnd WithEnd 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.
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
More articles about: