Previous Post – Drawing in Excel 7

Download PlotXY-xls.zip (XL 2003 and earlier)

Download PlotXY-xlsb.zip (XL 2007)

The previous post presented VBA routines to plot drawings in Excel from a series of coordinates, a list of connected points, and a table of formatting information. The drawings were scaled to fit the selected plot area.

This post covers a modification of these routines that allows the scale and centre of the plot to be changed, with lines and polylines being trimmed where necessary to fit inside the plot area.  This is not as straightforward as it might seem because trimming polylines will often change the number of line segments; for instance in the plot of a triangle shown below increasing the scale factor to 1.6 increases the number of sides of the plot shape from 3 to 7.

Triangle with scale factor = 1

Trimmed triangle with scale factor = 1.6

The trimming of lines and polylines is carried out by the function IPBox(Line1, Box), where Line1 is a list of XY coordinates and Box is a 2 x 2 array listing the bottom left and top right coordinates of the plot area, to the same scale as the shape to be trimmed.

The procedure is as follows:

1. For each node find the sector containing the node.
2. For each line find any intersection points with the limits of the plot box
3. Step through the nodes until the first inside the plot box
4. For this and each subsequent line segment:
5. If both ends are inside the plot box then save the start coordinates
6. If the line crosses the plot box limits then save the intersection point coordinates
7. For line ends outside the plot box, project to the nearest line, or nearest corner
8. For the last line segment close the shape with the first node or intersection point
9. Delete duplicate points  from the list of saved coordinates
10. Plot the resulting shape.

Examples of the output from this routine are shown below.

Super-T Bridge Beam Cross Section

Scaled and Trimmed Super-T

Part of a large finitge element mesh

Finite element mesh at 20 x magnification