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.
Newton Excel Bach, not (just) an Excel Blog 
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.
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!
Lets say I want a mass node on coordinate x,y, with height (h) and width
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
LikeLike
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 .. https://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
LikeLike
Pingback: Excel Links of the Week - Do you want to run 10k with me edition | Pointy Haired Dilbert - Chandoo.org
Can anyone show me a quick tuturiol of even how to “draw” on excel? That would be great! Currently, my task in school is requiring me to draw a shape of an I-Beam.
LikeLike
Hi Alvin – In 2007/2010 go to the Insert Tab, click on the Shapes Icon, and have a play with the shapes there. You could just draw three rectangles but also try the “Freeform” shape and then try editing the corners with the “edit shape” icon. With the freeform shape you click in each corner of your shape, then double click to indicate the last point. If you double click close enough to the first point it will draw a closed shape.
One trick useful for your exercise is to hold down the shift key as you draw each line segment. This will force the line to be either horizontal, vertical or at 45 degrees.
Let us know if you have any problems.
LikeLike
Pingback: Daily Download 8: Drawing in Excel | Newton Excel Bach, not (just) an Excel Blog
Excellent spreadsheet!
LikeLike
Very nice post. I simply stumbled upon your blog and wanted to
say that I’ve really enjoyed surfing around your blog posts. In any case I’ll be subscribing in your rss feed and I hope you write once more very soon!
LikeLike
Great job! I haven’t entered into detail yet but I think it will be very helpful spreadsheet for me.
Maybe someone find useful also this site and routines: http://www.bowdoin.edu/~rdelevie/excellaneous/
LikeLike
Andrew, this link you posted links to a page that doesnt allow to browse out of it…. this is not nice! Please refrain from suggesting such pages….. besides, some of the info they claim as excel pitfall is wrong.. I don’t think they are good at math
LikeLike
Tony – I don’t see anything wrong with that site. Using IE you can just click the back button to get out, but even if that doesn’t work in some browsers surely you can just close the window?
Could you be more specific about their claims that are wrong?
I have no connection with the site, and I would be happy to delete the link if there was good reason, but at the moment it seems to be a useful Excel related site for scientists and engineers.
LikeLike
Tony – WordPress only allow three levels of reply, so this is a reply to your message today!
You can e-mail me at dougaj4 at gmail.
LikeLike
Hello – thanks for all the help with drawing in Excel.
I’m trying to put together a finite element mesh preprocessor (for buried culvert analysis using CANDE). I’d like to do it in Excel because I am likely not the only one who will be using it.
Any direction you could provide on where to look for 2D meshing libraries/algorithms that can be implemented in VBA would be greatly appreciated. Keep in mind that although I’ve done my share of writing code in VBA and C++, I’m a civil engineer and not a software developer,
LikeLike
Ricky – all I can suggest is looking at the code from the open source FEA programs, and seeing if there is anything you can use, but I haven’t found any that I could recommend.
I’ll probably do something on this myself some time in the future, but I can’t put a date on it.
LikeLike
Thanks for the advice.
I’ll keep a lookout for that.
LikeLike
Hey Doug, I couldn’t find the button to reply to the comment you left me. I just entered that website and again, it took me more than one click on the back button to get out. The reason I like to use the back button is I can get where I was before, whereas if I close the window I have to start all over. In regards of the wrong claim, I will send you an excel sheet with the explanation. send me your email address
Cheers
LikeLike
Thanks for the advice.
I’ll keep a lookout for that.
LikeLike
Howdy! Would you mind if I share your blog with my twitter group?
There’s a lot of folks that I think would really enjoy your content.
Please let me know. Many thanks
LikeLike
Hi,
First of all thanks for the file. It’s awesome.
I’m trying to construct an image with it but only have the coordinates.
Is there a way i can find the nodes for myself?
Thanks a lot!
LikeLike
I presume you need the list of connected nodes. I don’t know of any easy way. I had a similar problem plotting a buckyball:
https://newtonexcelbach.wordpress.com/2010/09/04/drawing-a-buckyball-in-excel/
For that I used Strand7 (a finite element program) that has a good graphical editor, so I could connect the nodes by trial and error, and then get a listing of the connected node numbers. If you don’t have access to a similar program the best I can suggest is to use the spreadsheet to create a text box with the node number at each node location, and use that to work out which nodes need to be connected.
LikeLike
An offset would be nice
LikeLike
I doubt I’ll have any time to work on this spreadsheet in the near future, but one of the advantages of working with a spreadsheet is it’s pretty easy to generate coordinates for an offset line on the worksheet, then you can use the VBA plotting routines to plot that line.
LikeLike
Hi, I try to run this on a macos Excel 16.13 and it starts in a compatibility mode, but I can’t see any window. Please can you, or someone confirm this, and tell me how to solve this to get it work on a Excel for mac? Thank you very much
LikeLike
I can’t check on a Mac myself. If any Mac users can confirm if it works for them, that would be good.
LikeLike
Honestly, amazing post! Nothing better than finding well structured and well working code for your problem. Thanks a lot!
Cheers from the other side of the globe!
LikeLike