vb123.com

Garry Robinson's Popular MS Access, Office and VB Resource Site

 

Home  Contact Us

Order Software

Search vb123

 Smart Access  
The Magazine that Access Developers loved to read and write for is back
Article Index Here or
Purchase Here

RSS & Newsletter  
Join our XML/RSS Newsfeed or sign up for our informative newsletter on Office Automation, Access and VB topics
Sign up here

Get Good Help
If you need help with a database, our Australian Professionals could be the answer
Read More

  The Workbench  Find out who has your database open, start the correct version of Access, easy compacting and zip backups, change startup options,  compile, shutdown database
Read and Download


Access > SqlServer 
Upsize to SQL Server 2005 or 2008, easily repeated conversions, highly accurate SQL query
translation and web form conversion.
Read More

Like FMS Products?
Purchase them from us and get a free Workbench or Smart Access  More

The Toolbox
Libraries of software that we regularly import into our projects.  More..


Garry's Blog
Find out a few other things that Garry has been writing about Microsoft Access. Read more

About The Editor Garry Robinson writes for a number of popular computer magazines, is now a book author and has worked on 100+ Access databases. He is based in Sydney, Australia
Contact Us ...


 

 

Next Tip    Microsoft Chart - A How To Guide For Handling The VB 6 Charting Beast  

by Garry Robinson.   
(NO Support is provided for this page but feel free to read the content)

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.

 Introduction

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.

Syntax object.Legend

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

 

Getting Started

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

MSChart1.ToDefaults

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

With MyChart
  .chartType = VtChChartType2dBar

'  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

  Next iCount

' 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

End With

------------

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

ChartData Transfers an array of suitable data to the control
DataGrid Transfer data and column labels to MSCharts datagrid using methods similar to those that you would use in arrays.
ADO You can using ADO data binding version 6 of visual basic to add data. Search for "Data Binding the MSChart Control" in MSDN

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
Dim X() As Variant
Dim iRow As Integer
ReDim X(1 To 8, 1 To imax + 1)

dataScale = 1
iRow = 1
X(1, iRow) = "Period"
X(2, iRow) = "June 1998"

……

X(8, iRow) = "June 2004"

For iRow = 2 To imax + 1
  X(1, iRow) = "Ann Depr%" & iRow
  X(2, iRow) = (12.2 + (iRow * Rnd) * 20) * dataScale
  X(3, iRow) = (45 + (iRow * Rnd) * 20) * dataScale

……

  X(8, iRow) = (16 + (iRow * Rnd) * 20) * dataScale

Next iRow

addChartData:

' Reset the chart back to default to avoid surprises

MSChart1.ToDefaults

' 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.

With MSCHART1

  .Title.Text = TitleVar
  .Title.VtFont.Name = "Arial"
  .Title.VtFont.Size = 12
  .Plot.AutoLayout = True
  .Title.Location.LocationType = VtChLocationTypeTop

end with

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

With MSCHART1.Legend

  .Location.Visible = True
  .VtFont.Name = "Arial"
  .VtFont.Size = 8
  .Location.LocationType = VtChLocationTypeTop
  .VtFont.Effect = VtFontStyleBold

end with

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

With MSChart1

  chartStr = graphType1(Index).Caption

  Select Case chartStr

    Case Is = "2D Area"
      .chartType = VtChChartType2dArea
      .Stacking = True

    Case Is = "2D Bar"
      .chartType = VtChChartType2dBar
      .Stacking = False

    Case Is = "3D Bar"
      .chartType = VtChSeriesType3dBar
      .Plot.Projection = VtProjectionTypeOblique
      .Stacking = True

  end select

end with

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  ole.gif (1090 bytes)

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

Read All The Help On-Line About Using MS Office Chart From Visual Basic

or Try Our Microsoft Access Graph Loop

That the end of the VB Chart Lessons !!!

  Click Here for the download file Click here to find out how to donate to get the download file or to purchase The Toolshed.   

Next Tip  Next Article

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

 

Links >>>  Home | Search | Workbench | Orders | Newsletter | Access Security | Access professionals