Microsoft Chart - A How To Guide For Handling The
VB 6 Charting Beast |
by Garry Robinson.
If you want to read articles on Access graphs and pivot charting, head to this Smart Access page
Summary Lesson 1.
Microsoft Chart is one of the many controls that is bundled in the Professional Edition of Visual Basic and with 190 methods and properties, you would imagine it to be one of the best. If you really want the current state of the art in graphs, you had better think twice as you may be heading up a dead end. This article explains why it is a tool that can bring good results as long as you keep your enthusiasm under control.
After spending a number of years manipulating MS Graph in Microsoft Access and Excel, I was called in to make the Microsoft Chart control work for a VB program with over 3000 end users. They had converted their product over to VB 5 and had made a decision to replace their aging 3rd Party graph control with MS Chart. I came in with little knowledge of MS Chart and assumed that MS Graph would be similar quality products. Well the only real similarities are that they are both complicated and they both come with really poor help for programmers. The MS Office Graph Active X object is far more visually appealing and feature rich. See Sidebar.
My task though was to make MSChart look professional and work with visual basic. This article outlines a few changes that make MSChart look a lot better than it is when you first add it to your forms.
Know MSChart's Limitations
Before committing yourself to MS Chart, check that it will provide the graphical look that you and your users will find acceptable. If you end up discovering this 3 weeks after you start, you will simply have to throw away the code away as it will unlikely be reusable with any other 3rd part graphing tool.
The best way to quickly find out about the appearance of MSChart is to add a new chart to a form and open up the property pages. Now experiment with the different features in using the Chart Tab.
You Need Help (and you will be lucky to get it)
Microsoft Chart comes with volumes of help in MSDN and 95% of it is useless. Here is an example of the help that you get for the chart legend property
"Returns a reference to a Legend object that contains information about the appearance and behavior of the graphical key and accompanying text that describes the chart series.
The object placeholder represents an object expression that evaluates to an object in the Applies To list."
There are no links to any useful examples and very few full examples in the help system. So the only assistance that you can really get is VB IntelliSense. This gives you all the methods and a lot of constants and it really is a hit and miss exercise. But even IntelliSense comes up with some beautiful long winded statements like that which is required to turn off the grids on the chart.
Mschart1.Plot.Axis(VtChAxisIdX).AxisGrid.MajorPen.Style = VtPenStyleNull
Click on your components bar and select Microsoft Chart Control (Called mschart.ocx). You will need to have Visual Basic for Professionals as MSChart does not come in the Learners Edition. At this stage you might try setting up a demonstration install kit run to see that the object installs on representative end users systems.
Making The Graph Look Better (Manually)
Before you start programming the charts, you can make the charts look better by changing the following items. These are my preferences and naturally are open to opinion. It would be a good idea to write down any changes that you make to your graphs manually as you probably should do the same in your startup code.
Tip 1 : Put a border on the bars. You do this by right clicking on the MSChart object and choosing properties.
Select series color and for C1 change edge color to black. Do this for the first four colors. The default setting is the same color as the bar which gives it no definition.
Figure 1 - Use the property pages to change the edge color to black
Tip 2 : Select Axis Grid in the property pages. Set the Style to NULL for the X Axis, Y axis and Secondary Y Axis.
Tip 3 : Using Excel as a benchmark, select the fonts properties and set the following
Axis Labels - Arial 9 or 10 Regular
Title - Arial 10 or 12 Bold
Tip 4 : At this stage you may want to manually change the chart type using the Chart Tab.
Tip 5 : Finally change the background color to white by selecting Backdrop and defining the background Pattern to be full shade rather than null.
VB 5 & 6 Microsoft Chart - Initializing The Chart (Lesson 2)
The Demonstration Software
The accompanying resource file contains a VB 5 project called GRChart. This project will show you how to program a number of the key elements that you need to make MS Chart a smarter object. The code is separated into local form specific code and some general code that you can use for any MSChart object.
Figure 2 - GRChart Project up and running
Initializing The Settings
We should establish an initialization subroutine to preset these graph startup sequences every time we open the chart object. You need to do this to ensure that you have the same look and feel in your graphs every time. Before calling the startup settings, reset the chart to avoid any surprises using
And add the data to the graph so that it has all the series internally stored. This is important as the settings subroutine (called FX_ResetMSChart) needs to establish the extent of new data.
Dim numSeries As Integer
' Establish the number of items in the group
numSeries = .Plot.SeriesCollection.Count
' Add a black line border of each of the shapes
For iCount = 1 To numSeries
.Plot.SeriesCollection(iCount).DataPoints(-1).EdgePen.VtColor.Set 0, 0, 0
' Turn off the background grids
.Plot.Axis(VtChAxisIdX).AxisGrid.MajorPen.Style = VtPenStyleNull
.Plot.Axis(VtChAxisIdY).AxisGrid.MajorPen.Style = VtPenStyleNull
.Plot.Axis(VtChAxisIdY2).AxisGrid.MajorPen.Style = VtPenStyleNull
.Plot.Wall.Pen.Style = VtPenStyleNull
' Define the background color to white
.Backdrop.Fill.Brush.FillColor.Set 255, 255, 255
.Backdrop.Fill.Style = VtFillStyleBrush
VB 5 & 6 Chart - Getting Data Into The Chart (Lesson 3)
There are a number of ways that you can get data into the Chart control and they basically involve passing a column of labels and a number of columns of numbers to the chart control. The chart control then places these in a spreadsheet like container called a datagrid that you can only access using visual basic. The specific methods that you can use are
For this article, I will illustrate how you can add an array to MSChart. In this example, I am going to pass 6 different variables to the chart for 8 monthly periods. When testing your control, use more than the 4 variables to make sure that your initial settings routine is working well.
imax = 6
dataScale = 1
X(8, iRow) = "June 2004"
For iRow = 2 To imax + 1
X(8, iRow) = (16 + (iRow * Rnd) * 20) * dataScale
' Reset the chart back to default to avoid surprises
' Pass the chart object reference and array to sub
Call addDataArray(MSChart1, X(), True)
The shared subroutine then adds the data to chart using the following method
MyChart.ChartData = X
VB 5 & 6 Chart - Titles and Legends (Lesson 4)
Most users have had a lot of experience with high quality graphs through spreadsheets like Excel and Lotus so the pressure is on to add some more information to your graph. The first thing you might do is add a title. Some of the relevant methods for handling titles are as follows.
.Title.Text = TitleVar
and if you wish to turn the Title off
MSCHART1.Title.Text = ""
For displaying the legend, the following methods will display the legend at the top of the screen
.Location.Visible = True
The simultaneous display of title and legend demonstrates a problem with Chart. The layout is very difficult to control. In this case the title starts taking up a lot of valuable acreage on the chart. The other big problem that occurs is where one programmable event effects an other. This occurs if you want to show the Title and Legend at the one time in a place of your own choice. The resultant graph positions can at sometimes be uncontrollable and you will end up chasing methods and events all over the place.
So my recommendation is to avoid the title and show the legend at the top. That way the legend acts as a definitive title and you do not have to provide additional code to generate the title. On large charts, it is quite feasible to show both the title and and legend at the same time.
VB Chart - Lines, Bars, 3D, Stacked Bars and Area Graphs (Lesson 5)
Microsoft graph gives you the ability to come up with a number of different graph types and I have included an option bar in my demo to demonstrate them. The way that you change graph types using visual basic is easier than most chart methods and I illustrate three examples
chartStr = graphType1(Index).Caption
Select Case chartStr
Case Is = "2D
Case Is = "2D
Case Is = "3D
Now I have to stick my neck out here and tell you what I think about the different chart types that are available in MSCHART. The two dimensional Vertical Bar, Line, Stacked Bar and Area graphs are all quite acceptable. The other 2D graphs such as Pie, Radar and Scatter are quite quirky and would only suit a small number of applications.
The 3 Dimensional Graphs are very poor and only work if your chart control is Large and Square in shape. Figure 3 shows the very best that you can achieve with 3D graphs. Note the terrible display of the Series Titles at the bottom of the graph. Now if you put this into a rectangular shape, you still get a Square display which can look really bad. So my tip if you want perfect 3D graphs, look for a third part control.
Figure 3 - Illustration Of The 3 Dimensional Features Of MS Chart
VB 5 & 6 Chart - A Summary and An Alternative (Lesson 6)
MSChart is not the best looking graphing product that is available on the market. If you limit the amount of vb that your write and you do not want 3D graphs, it is probably a reasonable place to start graphing. Visual basic 6 edition now supports ADO record sources so that you can get you valuable data into the control and start visualizing your group by queries quite quickly. The source code that I have provided in the demonstration database is probably approaching the maximum amount of code that you should invest in manipulating the control. If you are going to spend a lot of time programming the control, try MS Graph that comes with Office or have a look at the alternatives 3rd Party Controls on the market.
About The Author
Garry Robinson runs a software development company called GR-FX based in Sydney, Australia. Recently he has written a number of articles for Smart Access on topics such as Consolidating Data Using Queries, Manipulating MS Graph with Access and using Access to Automated your E-mail. He developed a popular shareware data mining tool that will allow you to drilldown on data in any linked backend database and then to visualize that data in 2 or 3D using MS Graph. He also runs a VB resources page at http://www.gr-fx.com/
Alternatively add MS Graph That Comes With Office"
Visually the Office Graph object is far more professional looking than Microsoft Chart. To add MS Graph to your project, you need only have a registered copy of any of the Office Products. To get started, on the VB components Toolbar, select the OLE
button and draw a square on your form with your mouse where you want to place the graph object. Release the mouse and choose either the Microsoft Graph 97 Chart, or the Excel Chart. This will add the OLE/Active X object to your project. If you are developing for a single organisation that runs Office on all the target users, this is probably the best visual solution that you can provide your end users. To support this article, I have written a series of lessons on how to deploy MSGraph with visual basic available from
That the end of the VB Chart Lessons !!!
Published 1999-09: A lot of the information in these Visual Basic graph lessons first appeared as the lead article in the July edition of Visual Basic Developer by Pinnacle Publishing. www.pinpub.com