Custom charts in EXCEL using Shapes

I am trying to create a plot in EXCEL using shapes from VBA (after giving up on the Chart object because it is too restrictive).

Using shapes gives you absolute control - The problem I have is that the basis for the chart is an XY scatter with up to 15k points - Chart can plot this quite quickly, but doing it in a using Shapes.AddShape(...) is slow.

BTW, I have considered using a hybrid approach, i.e. plot XY with the Chart object and then use shapes to plot over it, but this brings its own problems - e.g. there is no .top or .left for a Point object (which I need to anchor shapes to), so...

- is there fast way in VBA to plot many shapes - would have been nice if you can use the .AddShapes with an Array....
- would be API calls be much faster?

Any ideas...?
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Anne TroyEast Coast ManagerCommented:
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. :)
EtienneCilliersAuthor Commented:

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.


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!]


Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

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)

EtienneCilliersAuthor Commented:
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

'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

For Each objShape In Me.Shapes

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
        .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
End With

End Sub

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

EtienneCilliersAuthor Commented:
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....
Anne TroyEast Coast ManagerCommented:
(Damn Excel geeks. LOL!!)
Geeks (entry level) use VBA:
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
PAQed, with points refunded (250)

Community Support Moderator

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial

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
Anne TroyEast Coast ManagerCommented:
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.
EtienneCilliersAuthor Commented:

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

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...
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)
    Select datapoint
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
EtienneCilliersAuthor Commented:
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.


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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Office

From novice to tech pro — start learning today.