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

Download CalcPaper