## Drawing in Excel 7 – Creating drawings from coordinates

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.

This entry was posted in Drawing, Excel, VBA and tagged , , , . Bookmark the permalink.

### 24 Responses to Drawing in Excel 7 – Creating drawings from coordinates

1. rbruins says:

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 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

Like

2. dougaj4 says:

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

Like

3. Alvin Piano says:

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.

Like

4. dougaj4 says:

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.

Like

5. Binh Pham says:

Like

6. jeremy scott says:

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!

Like

7. andre says:

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/

Like

• Tony says:

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

Like

• dougaj4 says:

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.

Like

• dougaj4 says:

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.

Like

8. 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,

Like

• dougaj4 says:

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.

Like

• Thanks for the advice.

I’ll keep a lookout for that.

Like

• Tony says:

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

Like

9. Thanks for the advice.

I’ll keep a lookout for that.

Like

10. 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

Like

11. Jan Verhelst says:

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!

Like

• dougaj4 says:

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.

Like

12. Charalampos Dala says:

An offset would be nice

Like

• dougaj4 says:

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.

Like

13. rva says:

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

Like

• dougaj4 says:

I can’t check on a Mac myself. If any Mac users can confirm if it works for them, that would be good.

Like

This site uses Akismet to reduce spam. Learn how your comment data is processed.