Previous Post, Drawing in Excel 6
The file PlotXYcontains routines to plot scale drawings in Excel from a list of XY coordinates of node points, node points for each shape, and formatting details for each shape type. The plot is scaled to fit within the selected plot range and, unlike Excel charts, is scaled equally in the X and Y directions, so circles will stay circular. Shapes may either be defined as polylines, with a series of points, or by using any of the built in Excel shapes.
Examples of the output are shown below, and data for the first three is included in the download. The final example was plotted from about 250,000 node points, using over 12,000 polyline shapes (using Excel 2007), illustrating that this tool is capable of producing drawings from very large data sets.
Super T bridge beam cross section
Finite Element Mesh for a Buried Precast Arch Structure
Concentric squares, or a square spiral?
Detail of a very large finite element model of a bat skull
Any comments or requests for additional features? Please let me know.
Filed under: Drawing, Excel, VBA | Tagged: Drawing, Excel, shapes, VBA
First of all, thank you for these routines. I’ve been dealing with drawing in excel for quite some time. Mostly in structural engineering.
And now I will never have to start and make boundary’s (x- x+ y- y+) scaling factors and drawing ranges, keeping the correct coordinates. Now is it just: start and select.
After trying these routines. I have found the following tell me your opinion on this:
Basicly I’ve found that us engineers see the world in nodes en lines.
The idea of lines are actually easy, two points and you’re done. The node is actualy different. It is infinitaly small, so we can’t see it. So we make a circle fill it up, (thus defining 2dimensions).
However there are different kinds of nodes. The mass for instance is big. A keypoint is smaller. and a node is smaller.
Here the problem in the routine starts.
Lets say I want a mass node on coordinate x,y, with height (h) and width (w) of 10. And defining it a circle (shape 9 if i remenbered it correctly). The routine starts at coordinate x,y and makes the circle. Thus placing the center at the coordinate at x+h/2 y+h/2. With a small value of h this isn’t a problem. You’ll won’t see that. but with large values you will. And one closely spaced on the outer border will run out of the drawing area, and find problems with deletion!
To make a workaround. You’ll have to make new node coordinates based on the type of shape you want. and taking into account the size of the shape. And thus increasing the number of totale node coordinates. And increasing error posibilities.
This plea is to create an new shape definition. Called a node with a single size. Incoorporate the workaround. I would also recommend creating a buffer area around the innerborder to prevend deletion difficulties.
Kind regards,
Rick Bruins
Rick – thanks for the comments.
Good point about the offset you get with shapes. Rather than create a new node shape type it seems to me that it would be better to modify the code so that the centre coordinates can be entered for circles, and the code calculates the top left coordinate.
Arcs are also a bit of a problem because they are handled differently in XL 2007 to previous versions. (more details here .. http://newtonexcelbach.wordpress.com/2008/07/26/drawing-in-excel-5-shape-list/), so some code to detect the Excel version and adjust the arc shape accordinly would be useful.
Time permitting, I’ll try and get a new version posted with these changes in a week or two.
Finally on the buffer, the routine for checking the scale at the moment just checks the limits of the nodes, without making any allowance for the size of the shapes. The easiest workaround is to specify an invisible line from one corner to the opposite one, ensuring that the limits of this line are outside the limits of all the shapes. Alternatively you could go into the VBA code and either increase the value of the constant cMargin, or reduce the value of UScale.
Doug
[...] Drawing in Excel – Using co-ordniates to generate huge drawings [...]