Image Image Image Image Image Image Image Image Image Image

Being Brunel |

Scroll to top

Top

No Comments

CalcPaper for Excel

CalcPaper for Excel

About the Author

Ed Dablin

  • Ed Dablin is a Structural Engineer in the Civil & Infrastructure Business Line of AECOM. Since graduating in 2009, he has worked on the design, inspection and assessment of a number of bridges on a range of projects including including the new Forth Replacement Crossing, Manchester Metrolink tram expansion and a new ring road to Jeddah. He came ‘Highly Commended’ in the 2010 NCE Graduate of the Year Awards.
  • Find out more: Twitter

“Within minutes of the finalists being announced Tony Gee graduate Tom Wallace had researched just about all there was to know about his rivals and, on his personal blog, he had analysed the qualities of the last 10 graduate award winners.”

This is as reported in the 13.12.12 NCE. If Tom is anything like me, he won’t thank me for posting this; (Ed[itor]: no, no I won’t…) I found my article highly cringeworthy. I was very curious to see Tom’s analysis and if I fit into this. Ah, no visit to a sewerage works. This year’s winner Jamie Radford must have had it wrapped up the moment he mentioned faecal sludge. Fair play!

Similarly irritated by excel, I thought I too would post some useful excel macros that I have developed to overcome this.

Being Brunel – Tom is not short of audacity. Yet this, like Brunel, could make him a great engineer. I have to say I am enjoying reading his blog, it is evident that Tom puts in a lot of effort. Tom’s thoughts and ramblings certainly resonate with me and I think there is a common sense of humour amongst engineers which Tom’s posts embody. I have subscribed via RSS and I note there is no link for RSS visible on the page but if you too want to subscribe this is it: http://www.beingbrunel.com/feed/ (Ed[itor]: you can also subscribe by e-mail on the right-hand sidebar, but enough about me…).

Having commented on the Awards analysis post, Tom asked if I would like to write a post. I have been impressed with Tom’s office add-ins. Similarly irritated by excel, I thought I too would post some useful excel macros that I have developed to overcome this.

Calculation Paper

I’m a VB novice and much of what I write is started by using Excel’s record macro feature and looking for commands from Google to develop it into something more sophisticated. Often it takes me too long to write and I don’t really save that much time, if at all. However, a couple of people in the office now use it to speed up their work too. One of the first things I wrote was a subscript/superscript macro. However, I have now superseded it with Tom’s EngCel which is a lot less clunky; mine involved user forms.

Anyway, I have modified the stock Excel calculation paper that gets used in the office to something which now enables me to type calculations a lot quicker in Excel. Please see this link if you wish to download the file and have a play. Run setup.bat so it deploys template sheets in your excel template file.

Download CalcPaper

This is a list of the currently working macros (for a list of hotkeys see the excel sheet):

Macro Description
Page Count Numbers all your pages automatically regardless of sheet or page numbers.
PDF to desktop Make a pdf of selected sheets and send to desktop
Select last sheet Like Alt + Tab in windows but with sheets
Unit Picker Shows a user form with common units e.g. N/mm2
Quick Script Quickly superscript and subscript text in cell (I now use Tom’s EngCel add-in over this)
New View Opens another windows and arranges vertically. Very useful when referencing another sheet.
Appendix View Same as above but new view opens in appendix
TOC Automatically create table of contents based on sheet headings
Insert… Inserts pages (Cover Sheet, TOC, New Sheets, Appendices)
Merge and Wrap I like to keep rows and columns fixed and for blocks of text use this feature to quickly make text flow over lines.
Centre Simple but saves useful hotkey.

There are other various optimisations such as print settings and font but I won’t go into those. I have a number of ideas to improve the spreadsheet such as a common symbols user form, an advanced cut and paste facility that will paste even if there are merged cells and optional side reference columns. See log.txt for a list. Unfortunately, I struggle to get the time to implement them but the more people that are interested in it, the more likely I am to develop it a bit more. Any suggestions, please let me know in the comments.

How to Record a Macro

If you don’t know how to record a macro in excel, its alarmingly simple. This is the process I go through:

How to record an Excel Macro

Download CalcPaper

Submit a Comment

Leave a Reply