Custom charts in EXCEL using Shapes

I'm going to comment and hope (truly) that I don't offend you.

I have been in the MS Office arena--with fairly extensive experience in all the applications--for about 7 years now. For the past 5 years, I have worked with others who code VBA and managed many VBA projects myself (tho I don't code). I have NEVER seen anyone use shapes to replace Excel charts. That leads me to believe that it's just not necessary. Even some of the hardest charts (like a waterfall chart) are done in VBA.

I don't see any questions here about getting the chart objects to work, so assume that *research* occurred before you came to EE. If you are in any position to continue testing with the chart method, I think you'll find your answer or "it cannot be done".

I, for one, would love to hear about those difficulties you had with the Excel chart objects.

Otherwise, feel free to completely ignore my post, Etienne. :)

Hi

Dreamboat, no offense taken - just realise there are ALWAYS cases (even if it isn't this example) that cannot be catered for in ANY object model - that's life :)

Below is a summary of a part of the chart I am trying to build :

- Start off with a XY scatter plot.
- Visualise floating boxes that are overlayed over these scatter ponts (The XY position and size of the boxes represent some statistical groupings of the data). The boxes may overlap and it should be possible to colour (partly) and label them individually, etc. There could be up to about such 10 boxes.

If you can explain to me how to do this using the chart object, I would be grateful.

Regards

Etienne,

I tend to agree with dreamboat completely, but also respect the fact that you perhaps have a special need.

Is it possible for you to send a hand-crafted sample of what your desired result will look like? I'm willing to give it a try _still_ within VBA and ChartObjects.

[does this site policy allow giving out email addresses for direct communication with attachments? I am returning here after many months!]

stochastic

Thanks, Dreamboat. I have done as you advised - put the email address in my profile.

Etienne, send me the sample if you like, thru email. (You will need to see my profile)

cheers
stochastic

Thanks for the offer of assistance. I agree with the need to explore exposed functionality, but its also important to explore alternative strategies - sometimes you discover something really useful that could save time/effort.

You can run the code below to see roughly how it needs to look. (Paste the code into an empty chart object). The code also at least shows how it easy it is to incorporate shapes in a chart (as the code stands its clearly not robust (and there are certain caveats to be aware of), but it is a basis).

Thanks again.

'Paste the code below in an empty chart object
Option Explicit

'Run this
Public Sub BoxesExample()

'Clean the chart up from previous runs
CleanUp

'Draw the basic plot
With Me.SeriesCollection.NewSeries
    .ChartType = xlXYScatter
    .MarkerBackgroundColor = vbBlack
    .MarkerForegroundColor = vbBlack
    .MarkerSize = 5
    .MarkerStyle = xlMarkerStyleSquare
    .XValues = "100,200,300,400,500,600,600,600"
    .Values = "100,200,200,300,300,400,500,600"
    .Name = "Data"
End With

AddRectangle 50, 50, 200, 200, vbRed, "Ultra"
AddRectangle 150, 100, 300, 500, vbGreen, "Low"
AddRectangle 275, 190, 500, 600, vbYellow, "Medium"
AddRectangle 480, 250, 650, 400, vbBlue, "High"

End Sub

'Removes all existing series and shapes
Private Sub CleanUp()
Dim objSeries As Series
Dim objShape As Shape

For Each objSeries In Me.SeriesCollection
    objSeries.Delete
Next

For Each objShape In Me.Shapes
    objShape.Delete
Next

End Sub


'Adds a rectangle shape based on chart coordinates
Private Sub AddRectangle(dblFromX As Double, dblFromY As Double, dblToX As Double, dblToY As Double, lngRGB As Long, strName As String)
Dim objShape As Shape
Dim dblShapeTop As Double
Dim dblShapeLeft As Double
Dim dblShapeHeight As Double
Dim dblShapeWidth As Double

With Me.Axes(xlCategory)
    dblShapeWidth = (dblToX - dblFromX) / (.MaximumScale - .MinimumScale) * .Width
    dblShapeLeft = .Left + dblFromX / (.MaximumScale - .MinimumScale) * .Width
End With

With Me.Axes(xlValue)
    dblShapeHeight = (dblToY - dblFromY) / (.MaximumScale - .MinimumScale) * .Height
    dblShapeTop = .Top + (.MaximumScale - dblFromY) / (.MaximumScale - .MinimumScale) * .Height - dblShapeHeight
End With

Set objShape = Me.Shapes.AddShape(msoShapeRectangle, dblShapeLeft, dblShapeTop, dblShapeWidth, dblShapeHeight)
With objShape
    With .Fill
        .Visible = msoTrue
        .Solid
        .ForeColor.RGB = lngRGB
        .Transparency = 0.5
    End With
    .Line.Visible = msoFalse
    With .TextFrame.Characters
            .Text = strName
            With .Font
                .Size = 7
                .Bold = False
            End With
    End With
    '.etc....
End With


End Sub

Etienne,

thanks for sending the sample. I ran it and saw what you need.

I don't have a solid suggestion yet, but still thought to say a couple of things:

> is there fast way in VBA to plot many shapes
an elementary suggestion - have you tried  
Application.ScreenUpdating = false
before beginning to draw, and then =true after?

If you haven't already tried this, it would make a huge difference to speed of drawing
(but not to speed of creating the shape objects)

> would be API calls be much faster?
Possible, but I doubt it. I will try that out.

You had said you want to draw "upto 10 such boxes" - do you mean upto 15k points but only 10 boxes?
Or 10 boxes for each xy point? If the latter, have you checked whether you might run into Excel's limit of
how many shapes it will allow?

> would have been nice if you can use the .AddShapes with an Array....
hmm that's an interesting idea. Will explore that too. You may be able to pre-create a shapes collection object
before beginning to draw, and then just assign the reference. But I'm not sure this will help the speed any.

I know I haven't contributed much yet - will get back if and when I have something that's not merely wasting your time!

- stochastic

To clarify, only about 10 boxes in total, so not very heavy.

Tried .Screenupdating=False :)

The reason I think that .AddShapes is slow is the shape object is quite sophisticated and therefore memory intensive - your point about the limit (definite or memory based) is relevant. I have plotted 15k points and "seems" ok. I would be happy to live with a less sophisticated object but that is quick to work with.

What does work VERY fast is .AddPolyline - but these are lines not points and there is no way I can get the lines to disappear and leaving the points to give the effect of a scatterXY or to convert the Polyline from "Lines" to "Rectangles" or something.

I am not completely "unhappy" with the shape overlay hybrid along the lines of the code, the only problem (apart from some plotting issues, like off-screen shapes) is that it is now not simple to pick up the XY scatter point on e.g. a mouse click (so that a msgbox can come up with some info on the point) since the rectangle is now obscuring the points - if they were all shapes, this could easily be dealt with by controlling the Z-order.

If there are no chartobject solutions, I may have to pursue this strategy and to store the plot coordinates of each XY point and check the mouse coords against that on a mouse click event.

Dreamboat, you can even _paint_ with shapes....
http://www.j-walk.com/ss/excel/odd/odd22.htm

(Damn Excel geeks. LOL!!)

Geeks (entry level) use VBA: http://www.j-walk.com/ss/excel/odd/odd17.htm

Sorry folks, for dropping the ball. I didn't get any new bright ideas after Etienne's last comment so this kinda fell through the cracks.

If at all I find something useful to share about this question in future I will post; I guess Dreamboat will archive this as a PAQ.

- stochastic

Etienne,

Knowledge_Able has a very interesting suggestion. I'll leave it to him to post
a comment. Just checking whether comment posting is still possible after the
question gets PAQed.

- stochastic

Hi, sto!

Of course, posting is still allowed. However, it doesn't make sense. Ettiene never replied to anything posted since 12/17, he's gotten his points back. If he wants to pursue it, I think he'd ask again. Meanwhile, we're just *spamming* everyone by posting for the advantage of someone who may not even be listening. But I very much appreciate your intention.

Hi

Still here. Knowledge_Able, can you post your suggestion - I assume I can still allocate points or must I start a new q?

Thanks
Etienne

Posting what Knowledge_Able sent me. (Don't give _me_ the points :-) )

I will try to get him to post himself (am in touch with him on email)

Hi stochastic
Sorry to Intrude.
Noticed your very interesting graph requirements.
Got curious :-)

How about this approach...
 
Setup
Create an X-Y scatter with the central locations for each rectangle.
Pre-create a single rectangle.
Store the properties for each rectangle as a list on a worksheet

For each rectangle
    Change the rectangles properties (height, width and colour)
    Rectangle.copy
    Select datapoint
    Datapoint.paste
Next
_________________
 
I built a quick version using this technique and it does work.  As to speed - you'll need to be the judge.
I like the fact that the final product is still a normal excel graph - rather than drawn rectangles.
It's a pity you can't just code this by direct object manipulation - pretty certain that copy and paste via code is required.
Hope this helps,
 
Regards - Knowledge_Able

Thanks for the post.

If I understand this correctly, its a variation on the approach outlined in code before, the difference being that instead of having to work out the coords for the rectangles, you first position a XY datapoint onto which you paste your rectangles, saving you the hassle of finding the plot position for the rectangle.

Assuming my understanding is correct, its a neat alternative, but
- you are still left with a "hybrid" chart (the original code also started with a simple XY chart which then gets "modified")
- you still have to do some math to work out the width and height of the rectangle.
- you still have to worry about off screen plotting.

Regards
Etienne

Etienne,

Yes, you are right. I noticed that this still doesn't give you what you desired
ideally. Still I thought this was useful additional information and a new line of thought.

I wonder why Knowledge Able doesn't post here :-)

- stochastic