Creating a new Excel chart type

[Heath]

Hi all,
sorry to dive straight in in my first post, but I joined up specifically
because I figured one of you would probably know how to do this and save me a
lot of messing about.
I have a fair amount of experience using VB.NET and would like to be able
to code a new type of chart in Excel (which I guess is most easily done using
VB script in Excel).

The Chart object accessible through VB script seems to be the obvious
starting place - I had imagined I would set a few simple properties (like
programatically setting the [Chart].Series.Values and [Chart].Series.XValues
propreties or pointing them to a cell range) and then call
ActiveSheet.Charts.Add method for my Chart object having overriden its Paint
method to provide the customised drawing.

Alas, no. For a start, VB script doesn't seem to really support OOP in the
same way .NET does, particularly when it comes to declaring and instantiating
a Chart object, so I've stumbled on even the first hurdle. Chart objects
can't be declared with the "new" keyword to instantiate them, so to avoid the
"object variable or with block not set" error I tried using the "set" keyword
to assign the return object from CreateObject("Excel.Chart") command to an
object variable. This seemedo far as it goes, but then every time I try to
access a property or method I get "Object does not support automation" errors
which Help tells me means that the Chart object doesn't expose the properties
and methods I want programatically (eems like a waste of time being able to
refer to it at all, if that's the case!) Maybe this is because the assignment
of the CreateObject object to an object variable using "set" is implicitly
late bound?

In any case, I want to be able to instantiate a Chart object and play with
its methods, properties and events programatically. In particular, to
override the events triggered when it redraws itself with custom code. Can
anyone help me out here?

Thanks for your time and expertise,
Heath.

 

[Peter T]

Hi Heath,

Have you looked at VBA.

Regards,
Peter T

 

[Heath]

Thanks for your reply, Peter.

I haven't used VBA for years (since changing to VB6 and then to .NET). I
remember some of it, but in any case, I have figured out a workaround after a
few hours of playing about and sifting through MSDN.

I still have a further question you (or others) might be able to help me
solve, though. The help articles about handling events from embedded charts
suggest creatinga new class module which contains the chart object as a
public object, and then linking the embedded chart to an instance of that
class from within any code module. In summary, make s class module named
myClassMod containing the declaration

Public WithEvents myChartObj as Chart

then from within any code module, use

Sub CreateChartWithEvents()
Dim ChartObjMod as New myClassMod
Set ChartObjMod.myChartObj = Charts.Add
End Sub

which executes fine and correctly creates a new chart and assigns it to the
chart object in the class object. However, Microsoft suggests that you can
then add event handling code to the class module, such as

Sub myChartObj_Resize( ... plus argument list... )
... some code here ...
End sub

that should handle events fired by the embedded chart. Well, I've tried,
and it doesn't. The events do not appear to trigger the code. Any clues?

If you can't think of anything, I'll post this as a new question to the group.

Grateful for your help,
Heath.

 

[Peter T]

Hi Heath

I had a feeling you might have forgotten about VBA, which is where of course
you can easily find all the properties and methods of a Chart, and in
particular for your purposes chart events. Work in VBA first and if needs
adapt to VB6 or .Net later.

Create a normal chart sheet, select some values and press F11. Right click
the chart sheet tab and view code. Look at the two drop downs at the top of
the code module.

For a ChartObject on a worksheet you need to create your own Class but not
quite as you have written. Insert a new normal Module and a new Class module
named "Class1" (right click your file name as shown in a panel to the left
in the VBE).

Copy following into the Class1

Public WithEvents cht As Excel.Chart

Private Sub cht_Select(ByVal ElementID As Long, _
ByVal Arg1 As Long, ByVal Arg2 As Long)
'' look up "GetChartElement" in help
MsgBox "ElementID " & ElementID & " Arg1 " _
& Arg1 & " Arg2 " & Arg2
End Sub

In a normal module

Public clCht As Class1 'at top of module

Sub SetUpCht()
Set clCht = New Class1
Set clCht.cht = ActiveSheet.ChartObjects(1).Chart
End Sub

Sub CleanUp()
Set clCht = Nothing
End Sub

With a ChartObject on the active sheet, run SetUpCht and click on the chart.
Best to clear public object ref's and terminate the Class when done, see
CleanUp().

You can use the object ref clCht.cht throughout your project, type an
additional dot to get the intellisense. Within the class module simply
"cht".

But you say you want to trap an event when a chart resizes. Excel does not
expose such an event as you can see from the drop down list. Chart items can
resize when series values change. It would be a lot of work but maybe you
could trap the chart Calculate event.. Compare internal dimensions before
and after.

Resizing internal chart items is not straightforward. As it happens recently
"Janwillem van Dijk" invited people to try his resize method. Search this ng
for his name on 03-Aug-05 and ask nicely!

Regards,
Peter T

 

[Heath]

Hi Peter,

thanks again - you rock. I've tried your code and that works fine, but I
still have one more problem. I think I might know the cause, but not the
solution.

Your suggested code in the normal module links the chart object from the
class module to an existing embedded chart in the active sheet. That is,

In a normal module

Public clCht As Class1 'at top of module

Sub SetUpCht()
Set clCht = New Class1
Set clCht.cht = ActiveSheet.ChartObjects(1).Chart
End Sub

What I REALLY want to do is create the chart programatically and override
its drawing behaviour (which will be another can of worms altogether and may
require some nasty tinkering with its Calculate method since, as your
suggestion reveals, it doesn't seem to expose a Paint method the way .NET
controls do). But that's a whole other issue. If I can trap any events at
all, then this problem will eventually yeidl to brute force

Now, as for creating the chart from code, I have found a way to create a
chart programatically; modifying the code snippet above to

Sub SetUpCht()
Set clCht = New Class1
Set clCht.cht = Charts.Add
End Sub

does the trick, and pops up a nice new chart when it executes. I can
programatically set chart type, labels, data ranges etc. However, although
the chart appears, the event subs no longer seem to be triggered. I suspect
that the object returned by the Charts.Add method is not linking properly to
the clCht.cht withevents object.

I'm wondering if Excel is creating a chart object, embedding it in the
sheet, but not returning a valid reference to its instance.

The problem definitely lies in the "Charts.Add" call not returning an
instance that has events, whereas the "ActiveSheet.ChartObjects(1).Chart"
does (except the chart has to be added manually before the call to the code
is made, and I want to add the chart via code).

Have you any further pearls of wisdom to cast before me? I really
appreciate your help - you've been generous with your experience and time.

Thanks,
Heath.

 

[Peter T]

Hi Heath,

Your Add method should work with chart events being triggered in the Class
module. However your code only creates an empty Chart sheet albeit linked to
the already instantiated (up-&-running) Class.

Assuming you want to Add a Worksheet.ChartObject.Chart to the already
initialized Class try something like this.

As before, code in "Class1" with a bit more than last time -

Public WithEvents cht As Excel.Chart

Private Sub cht_Select(ByVal ElementID As Long, _
ByVal Arg1 As Long, ByVal Arg2 As Long)
'' look up "GetChartElement" in help
MsgBox "ElementID " & ElementID & vbCr & _
" Arg1 " & Arg1 & vbCr & " Arg2 " & Arg2
End Sub

Private Sub cht_Calculate()
' change some series value
MsgBox "Chart calculate"
End Sub

Private Sub Class_Initialize()
' get rid of this
MsgBox "Class initialized"
End Sub
'''''''''''''

In a normal module

Public clCht As Class1 ' at top of module

Sub SetUpCht2()
Dim ws As Worksheet
Set ws = Worksheets("Sheet1")
Set clCht = New Class1

' add a ChartObject to Sheet1 and assign the Chart of the
' ChartObject to the class
Set clCht.cht = ws.ChartObjects.Add(50, 40, 200, 100).Chart

' adapted from VBA help, design and set source to the chart
clCht.cht.ChartWizard Source:=ws.Range("A1:B2"), _
Gallery:=xlColumn, Format:=6, PlotBy:=xlColumns, _
CategoryLabels:=1, SeriesLabels:=0, HasLegend:=1

End Sub

Sub CleanUp()
Set clCht = Nothing
End Sub

Instead of Adding' to the Class, in SetUpCht()

Dim myCht as Chart
Set myCht = ws.ChartObjects.Add(50, 40, 200, 100).Chart
design the chart to myCht & instantiate the class as above then

Set clCht.cht = myCht

But for the main object of your exersize, tracking & resizing chart
elements, you still have quite a task. The class calculate event should warn
of the possibility of resize due to value changes but resize can occur with
all sorts of other user changes.

Good luck,
Peter T