Solved

Custom charts in EXCEL using Shapes

Posted on 2003-12-12
21
1,207 Views
Last Modified: 2012-05-04
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 for..next 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...?
0
Comment
Question by:EtienneCilliers
  • 7
  • 5
  • 3
  • +2
21 Comments
 
LVL 22

Expert Comment

by:Dreamboat
Comment Utility
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. :)
0
 

Author Comment

by:EtienneCilliers
Comment Utility
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
0
 
LVL 8

Expert Comment

by:stochastic
Comment Utility
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

0
 
LVL 8

Expert Comment

by:stochastic
Comment Utility
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
0
 

Author Comment

by:EtienneCilliers
Comment Utility
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
0
 
LVL 8

Expert Comment

by:stochastic
Comment Utility
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

0
 

Author Comment

by:EtienneCilliers
Comment Utility
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.
0
 
LVL 13

Expert Comment

by:cri
Comment Utility
Dreamboat, you can even _paint_ with shapes....
http://www.j-walk.com/ss/excel/odd/odd22.htm
0
 
LVL 22

Expert Comment

by:Dreamboat
Comment Utility
(Damn Excel geeks. LOL!!)
0
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
LVL 13

Expert Comment

by:cri
Comment Utility
Geeks (entry level) use VBA: http://www.j-walk.com/ss/excel/odd/odd17.htm
0
 
LVL 8

Expert Comment

by:stochastic
Comment Utility
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
0
 

Accepted Solution

by:
PashaMod earned 0 total points
Comment Utility
PAQed, with points refunded (250)

PashaMod
Community Support Moderator
0
 
LVL 8

Expert Comment

by:stochastic
Comment Utility
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
0
 
LVL 22

Expert Comment

by:Dreamboat
Comment Utility
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.
0
 

Author Comment

by:EtienneCilliers
Comment Utility
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
0
 
LVL 8

Expert Comment

by:stochastic
Comment Utility
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
0
 

Author Comment

by:EtienneCilliers
Comment Utility
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
0
 
LVL 8

Expert Comment

by:stochastic
Comment Utility
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
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

How many times recently have you prepared a presentation or emailed a document to a client and you have found that they have older versions of MS Office and they can not open the file you have prepared.  Although most visitors to this site are exper…
Problem: You created a new custom form in Outlook for your contacts (added fields, deleted fields, changed the layout of fields, whatever) and made it the default form for contacts. The good news is that all new contacts will utilize the new form. T…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

7 Experts available now in Live!

Get 1:1 Help Now