1. Technology

Using an Excel 2010 Chart in a VB 2010 Form

By July 1, 2011

Follow me on:

Tom wrote:

"Can you give me a "butt-simple" example of reading two points in from an excel spreadsheet and displaying them on a chart in Visual basic 2010? All the examples I see get bogged down in the dumb chart appearance stuff and you can't see how you just put a couple simple points on a line or bar chart!"

Sure thing Tom! "Simple" is my middle name! (And part of my last name is "butt" too!)

There are still a few points about your "user requirements statement" that are unclear. First, it's not clear whether you want the chart to be displayed in VB.NET or in the Excel spreadsheet. You can do it either way but I'm going to assume that you want the chart displayed in a VB.NET form. Second, it's not clear where your data is coming from. Do you need to add data to the spreadsheet and then chart it? Or is the data already in the spreadsheet? Since your question seems to be mainly about charting, I'll assume that the data is already in an existing spreadsheet ... all two points of it.

So ... Here's a spreadsheet (Excel 2010) containing two points and a chart. (click on the image)

Step 1 is to create a Windows form based project containing a Button and a PictureBox. Add a reference to "Microsoft Excel 14.0 Object Library". (Under the Com tab in the project properties, References tab. See more about this here and here.) In the Click event for the Button ...

  1. Open an instance of Excel
  2. Open the spreadsheet
  3. Drill down to the chart
  4. Export the chart to a file
  5. Assign the file to the PictureBox
  6. Close Excel again

If you assign StretchImage to the Size property of the PictureBox, it looks pretty good! The steps above don't tell you exactly how to write the code, however. Download the project using the link below to see the whole thing.

Click here for the complete code.

Comments
July 2, 2011 at 12:24 pm
(1) Tom Barker says:

Dan, I guess my simple explanation wasn’t so simple……
1. I have an excel spreadsheet file with a single column of integer data in it.
2. I have visual basic 2010 running and I want to use visual basic’s chart object to display the data read in from the excel file. Call the column of data in the excel spreadsheet two points for simplicity.

I think the subject of “data binding” is somehow involved. I am able to read excel spreadsheet data into my vb program but I can’t figure out how to get the data into the VB Chart.

July 2, 2011 at 2:06 pm
(2) visualbasic says:

User requirements are like that.

This is a great opportunity to retell a story from my programmer days.

I developed a technique of responding to user requests for new programs that went like this:

User: I need a program that will report framis divertisiments orthilogitulaterally ordered by gurthuds.

Me: I’ll get right on it!

– one week passes — I work on other projects — at the end of the week, I change the titles in an old report to use the words “framis divertisiments” and “gurthuds” and take it up to the user –

User: That’s Great! Except … Could you just group the divertisments by week and add the current inventory level of gurthuds here and …

– This time I take careful notes so I can start working on the actual program. –

Slight exaggeration, of course. But thanks for the enhanced user requirements.

Now …

Before Framework 4.0 (VB.NET 2010), there wasn’t any such thing as a VB Chart. The way to get a chart into a VB.NET program was to use an Office Excel Chart. The downloadable code shows how to export one to a file and then display that in a PictureBox control.

Do you need to know how to create that chart using Office automation (that is, VB code that controls an Office function like Excel) so you can then export it to a file?

Is creating the chart in advance in Excel (that’s what I did) an acceptable way to do it?

Do you need to know about Framework 4.0 charting. (If that’s the question, then there is no “butt-simple” explanation. But it’s a good idea for a future article!)

July 3, 2011 at 9:56 pm
(3) Chris says:

The Charting Controls are not that complicated to throw a chart on the screen, if you have the data to hand (which Tom says he does).

If you do it entirely in code-behind, you have to make sure that you create and set a ChartArea, then reference the ChartArea in your Series. Pick your favourite data-source and you’re off! Here’s the code:

Imports System.Windows.Forms.DataVisualization.Charting

Public Class Form1

Private Sub Form1_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

Dim myChart As New Chart
Dim myData As New List(Of Point)

myData.Add(New Point(1, 4))
myData.Add(New Point(2, 5))
myData.Add(New Point(3, 7))

myChart.DataSource = myData

Dim myChartArea As New ChartArea
myChartArea.Name = “chartArea”
myChart.ChartAreas.Add(myChartArea)

myChart.Series.Clear()

Dim mySeries As New Series
mySeries.XValueMember = “X”
mySeries.YValueMembers = “Y”
mySeries.ChartArea = “chartArea”

myChart.Series.Add(mySeries)

Me.Controls.Add(myChart)
myChart.Dock = DockStyle.Fill

End Sub

End Class

July 4, 2011 at 10:42 am
(4) visualbasic says:

Thanks for the help!

After posting the message, I remembered that I had actually written an article about this. (Using the Chart Control in ASP.NET but that article is based on ASP.NET, not Windows Forms.

I still think this would be a good article. I’ll be sure to give you credit if I get it done.

July 6, 2011 at 1:34 am
(5) Tom Barker says:

Chris,
Thanks for the code: I’ll give it a try.
While I’ve done some 100-200 line programs in Vb2010, I guess I’m still learning nothing is really simple when it comes to Visual Basic. Learning all the new code words can be daunting!

August 2, 2012 at 5:05 pm
(6) Denise says:

I want to know will this help me find the add-in for panning hand in excel 2010. The hand that I use daily to grab & maneuver left – right – up – down in excel spread sheets is now missing,.
Anyway you may know where I can find it?
I’ve just about tried everything.
This is my last hope. I am have been on every forum, uninstalled reinstalled excel via MS tech level 2, uninstalled re-installed mouse.
Custom ribbon will not reset & panning hand is not there either.
I do not know it I should be checking every add-in that excel has in the auto correct but I am hoping for a miracle.
Please advise. Thanks

August 2, 2012 at 7:55 pm
(7) Dan Mabbutt says:

The “Panning Hand” was as option available in the Tablet PC. It was never available in regular desktop Excel.

From Microsoft …

If you are using a Tablet PC, we have added another way for you to easily read and navigate your email through the Panning Hand feature. The Panning Hand command can be found at the top of the vertical scroll bar as shown below:

When the Panning Hand is turned on, you can use the Tablet PC pen to scroll up and down.

It might have been possible to install something in previous versions of Excel that displayed it, but not in Excel 2010.

August 28, 2012 at 9:47 am
(8) Denise says:

Well I guess I the “user” has asked for feature that isn’t even available in Excel 2010. Just makes me think of how I could of have been using the panning hand in Excel 2010 since Dec 2011? Using it a lot lately with no issues & even showing it to my husband because he was helping me put together some huge spread sheets & when I needed to find something on them he used the panning hand. Quick & efficient unlike the horizontal scroll bar; which is time consuming, cumbersome, & ineffective, especially with many tabs. MS may consider putting 2 bars; 1 for horizontal scroll & other for tabs …it’s be nice if the tabs could wrap (stack) instead of hide. Trying to finding a tab when there are many & definitely have panning hand available in all MS software products, just food for thought. Well it must have been divine intervention or the touch screen All-n-1 desktop has it available if so, I can’t figure out how to get it back. Maybe somehow switching over from my XP Prof. x32 using MS Ofc 2007 Prof. to Win 7 x64 MS Ofc 2010 Prof. Pro brought it from there. We’ll never know all I can do now is move forward & pray that my husband can figure out how to apply action (value code) to the pan hand that he created using VBA & UI Editor. We by no means are techy savvy; in fact since June 2012 we have come a long way considering our schedules. This is so strange & intriguing at the same time. We are optimistic; where there is will, there is a way. I hope. If anyone has a clued on how to add value; please let us know. I am glad to see my lil dilemma is getting some recognition. We have faith that some smart tech will help us solve this mystery We are not giving up.

Leave a Comment

Line and paragraph breaks are automatic. Some HTML allowed: <a href="" title="">, <b>, <i>, <strike>
  1. About.com
  2. Technology
  3. Visual Basic

©2014 About.com. All rights reserved.