Making The Most Of The OLE Chart Object

The Microsoft OLE Chart Object

There will be no support provided for this page (But the download is free)

Click here to read about exploring data in Access 2007

The Other MS Graphing Tool For VB6 (Lesson 1)

The quality of the Microsoft Chart object that comes with Visual Basic 5 & 6 is generally not very good when compared with the Excel Chart or other 3rd Part graphing tools. This especially applies to 3D charts where the quality is only just acceptable when viewing large square charts and hopeless when using smaller more rectangular views. The example on the shows the vb chart object exactly as it is shown in the VB 6 help manual. Basically the axes are illegible, the definition on the shapes are poor etc etc.

Another problem with the Visual Basic Chart tool is that you need to purchase Visual Basic Professional to be allowed to use this tool. If you have the Visual Basic Learners Edition, you are going to have to find an alternative.

One Alternative

On The Toolbar, select the button, 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. For these examples, I selected Microsoft Graph.

This now adds an OLE object to the form that looks like this. It will be more than likely called OLE1

Now you need establish a reference to the Microsoft Graph Object Library.

Before you get to carried away with programming the object, there are very important tests that you need to make.

Make the project into a excutable and try running it on other machines

Make a VB Install package using the Package and Deployment Wizard or your own Install Progam and try the software out on suitable trial computers.

The important point to remember is that the OLE objects need to be pre-installed on the computers where you want to run the software. The object packager does not add the MS Graph or MS Excel chart objects to the install kits. This solution is more suited in Intranets or Small Work groups where you have good control over you environment.

You are now ready to start programming the object.

For More Help Search VB 6 Help For

Programming with Active X Components

Click here for the download file if you own "The Toolshed" Else click here

Getting Data Into Your Graph Object Lesson 2

The easiest way to get data into the graph object is to use the import file option that come with MS Graph.

Simply compile and run the VB project. You will end up with a form that looks like this. Apart from adding the MS Graph object, add a text field like the Double Click To Start Field that I have added.

Double click on the graph to start it up. Select Menu Edit ... Import File

Click on picture to view at full size

Now you can import a number of useful formats into your graph as in the example shown to the right. In this example we will use a consolidated query from the Graf-FX data mining demo data. This query has been stored in a small Excel 97 file that you can download by clicking here.

There are a one or two standard MS file import steps in between but the graph now looks as follows.

So in summing up, you now have a small program for charting that required no programming and no need to train some how to use a spreadsheet. I will expand on how you can start programming data imports in the next lesson.

Download Useful Microsoft Help Files

Microsoft Graph8 VB Help File Here Its called vbagrp8.hlp and you may or may not have it.

Microsoft Graph 8 Users Guide Help File Its called graph8.hlp and you may or may not have it.

Microsoft Automation Help Files Its called Auto97.hlp

Alternative Import Method - Cut and Paste

1) Select the table of data in an alternative program such as an Access query. Copy the data using the clipboard.

2) Switch to the graph program that you have just created .

3) Right-click the chart and choose open.

4) If necessary, select the 'View' menu and select 'Datasheet' to view the datasheet.
NOTE: If sample values are still inserted into the datasheet, click in the upper-left shaded cell to highlight the entire datasheet and press DELETE.

5) Select the cell in the datasheet where the copied data is to be placed.

6) Select the 'Edit' menu and select 'Paste'.
TIP: To paste selected text more quickly, press CTRL+V.

Click here for the download file if you own "The Toolshed" Else click here

Getting Data Into Your Graph Object Lesson 3

Now we come to the section where we need to add our own data in a programmatic fashion to the object. I thought this was going to be an easy one but No No No. The usual wild search through MSDN etc and finally I came up with something that showed me how to add data.

For this example, add a new button to the graph which I have called cmdAddData

Now add the following code to the Form_Load event.

Private Sub Form_Load()
' Set the standard format for the graph
' Search CF_Text for more info on formats supported

With OLE1
.Format = "CF_TEXT"
.SizeMode = vbOLESizeStretch
.CreateEmbed "", "MSGRAPH"

End With

Now you are going to build a string that allows you to transfer the data to the object. How you get this data in the first place is not part of this lesson but the datatext method initiates the transfer.

Private Sub cmdAddData_Click()
' Add some data to the graph

Dim msg, NewLine, Tabb

Tabb = vbTab
NewLine = vbNewLine

msg = Tabb + "Beer" & Tabb & "Wine" & Tabb & "Spirits"
msg = msg & NewLine + "Australia" & Tabb & 100 & Tabb & 65 & Tabb & 14
msg = msg & NewLine + "USA" & Tabb & 120 & Tabb & 85 & Tabb & 33
msg = msg & NewLine + "UK" & Tabb & 130 & Tabb & 53 & Tabb & 24

With OLE1
'   Use this if you want the object to remain hidden until you click on the data button
'   .DoVerb vbOLEShow

    If .AppIsRunning Then

      .DataText = msg
      .Update
    Else

      MsgBox "Graph is not active"
    End If

End With

End Sub

Other options worth looking into would be using arrays, the flex-grid control and automating transfers from text files.

Switching The Standard Data Format

As it is highly likely that you will be transferring your data from a recordset, you will need to change the project setup from the standard Series in rows to Series in Columns. To do this, Edit the graph object and select the Data menu as in the screen shot below.

Click here for the download file if you own "The Toolshed" Else ???

Changing The Way MS Graph Looks Lesson 4

In this lesson, I will show you some of the visual basic code required to manipulate MSgraph

The following picture shows some of the properties that you can manipulate in the Chart OLE object.

First we will look at a declaration that I made in the Form_Load event to make referencing easier

Private Sub Form_Load()
' Define an object so that reference can be used throughout the form

Set MyGrafObj = Me![chart1].object.Application.Chart

end sub

' Note I tried this again later and the object was called me![OLE1] so watch for the object name ED

To add a text string to the title of the graph, I have added a button at the bottom of the form.

Private Sub cmdAddTitle_Click()

' Toggle the title on and off

With MyGrafObj
    .HasTitle = Not .HasTitle

    If .HasTitle Then

'      Display the title
      .ChartTitle.Text = CStr(Me!MainTitle)
    End If
End With
End Sub

To change the styles of the graph, try the following to change the form to Cylinders as above or maybe stacked 3D bars.

Private Sub BarSt_Click()

MyGrafObj.ChartType = xlCylinderCol
End Sub

Private Sub BarSt3D_Click()
MyGrafObj.ChartType = xl3DBarStacked
End Sub

Some of the other constants that you can use for the graph are show in the table below

ChartType	97 Constant
Bar	xlBarClustered
Stacked Bar	xlBarStacked
Line	xlLine
3d Line	xl3DLine
Area	xlAreaStacked
3D Area	xl3DAreaStacked
Column	xColumnClustered
3D Column	xl3DColumn
Pie	xlPie
3D Pie	xl3DPie

To toggle the graphs legend on and off, try the following

Private Sub tglLegend_Click()

' Toggle the legend
With MyGrafObj

.HasLegend = Not .HasLegend
End With
End Sub

And to complete the lesson off, I have added horisontal and vertical slider bars to the form so that you can rotate and tilt 3D Graphs. The code for the horizontal rotation is as follows

Private Sub grafRotate_Click()

On Error GoTo grafRotate_error

With MyGrafObj
.Rotation = Me!grafRotate
End With

Exit Sub

grafRotate_error:

MsgBox "This graf does not support rotations ... " & Error, vbInformation, "Try Another Graph"
Exit Sub

End Sub

graphspin.gif (10776 bytes)

Click here for the download file if you own "The Toolshed" Else click here

Printing the MSGraph Form Lesson 5

We are finally getting to the point where the graph form is starting to be useful. But we need to print it out. To the form I have now added added a print button with the following code. The important method here is PrintForm. This will only be suitable if the form is not cluttered with other objects as printform literally prints the form AS IS.

Private Sub PrintIt_Click()

On Error GoTo ErrorHandler

' First turn of all the controls that do not have a Tag property of Print

Call PrintVisibility(False)

' Now print the objects that are still visible
PrintForm

' And then turn all the other objects on again
Call PrintVisibility(True)

Exit Sub
ErrorHandler:

MsgBox "The form can't be printed."
Resume Next
End Sub

The little trick that I used here was to hide all the objects in the form that do not have a tag property of Print. When the form has been printed, turn them all back on again with the same form. Add the Tag property manually by opening each object that should be printed and typing in Print in the tag field.

Private Sub PrintVisibility(visState As Boolean)
   Dim i As Integer

' First turn off the other controls

For i = 0 To Me.Count - 1
    If Me(i).Tag <> "Print" Then
      Me(i).Visible = visState
    End If
Next

End Sub

That's it for controlling the MS Graph Object from Visual Basic 6.

Hope you gained something useful from it !!!

Garry Robinson

Click here for the download file if you own "The Toolshed"

Return to the main page