xy scatter chart, macro to fill shapes

[Cameron]

Hi,

I am working on a macro in excel that will take several series and plot them
on a scatter chart. Then the series will be made into a shape and filled in
with a certain color.

The problem I am having now is that when I try to make a small rectangle
(For instance the coordinates: (0,0) (0,0.2) (1,0.2) (1,0)) the shape builder
skips the third point and ends up making a triangle. This problem goes away
once the plot area is made larger, or the points aren't as close together.
The four points are plotted in the correct locations, but the shape builder
is not doing something right.

Here is the piece of code that I believe is giving me trouble;

Xnode = Xleft + (mySrs.XValues(Ipts) - Xmin) * Xwidth / (Xmax - Xmin)
Ynode = Ytop + (Ymax - mySrs.Values(Ipts)) * Yheight / (Ymax - Ymin)
myBuilder.AddNodes msoSegmentLine, msoEditingAuto, Xnode, Ynode

Thank you for your help,
Cameron

 

[EricG]

Drawing very small shapes in any Office program can be hazardous to your
health! Office rounds coordinates of shapes to the nearest 0.75 point or
pixel or something like that. If your coordinates are near the rounding
limit, you will see cases where two very close points end up in the same spot.

One way to overcome this is to build a bigger shape, copy it to the
clipboard, and then paste it back in as "Enhanced Metafile". Then you can
scale it down as small as you want and it will retain its shape. Something
like this:

'
' Draw the shape
'
With ActiveSheet.Shapes.BuildFreeform(msoEditingAuto, 185.25, 121.5)
.AddNodes msoSegmentLine, msoEditingAuto, 272.25, 121.5
.AddNodes msoSegmentLine, msoEditingAuto, 272.25, 201#
.AddNodes msoSegmentLine, msoEditingAuto, 186.75, 201#
.AddNodes msoSegmentLine, msoEditingAuto, 185.25, 121.5
.ConvertToShape.Select
End With
'
' Send the shape to the clipboard
'
Selection.Cut
'
' Paste back in and then scale and translate as desired
'
ActiveSheet.PasteSpecial Format:="Picture (Enhanced Metafile)",
Link:=False _
, DisplayAsIcon:=False
Selection.ShapeRange.ScaleWidth 0.2, msoFalse, msoScaleFromTopLeft
Selection.ShapeRange.ScaleHeight 0.2, msoFalse, msoScaleFromTopLeft
Selection.ShapeRange.IncrementLeft 110.25
Selection.ShapeRange.IncrementTop 108#

HTH,

Eric