The Cell Master.
from Melboune, Australia
Please introduce your self. You may be as thorough as you wish. Feel free to include or omit any detail about yourself.
I'm 34, married to Lucy, have two children, Rachael (2 years) and Jack (4 months) and live in Melboune, Australia. I'm a metallurgical engineer by background, with post graduate qualifications in finance and I work as a manager for BHP Billiton in the resources sector, mainly in evaluating large capital projects and transactions (M&A, joint ventures etc). I spend a large portion of my role as an internal consultant providing expert services in financial modelling (authoring and reviewing), and in presenting evaluation workshops where the key messages are financial model transparency, simplicity and full referencing to data sources. Somewhat ironically given my interest in VBA, I routinely advise people building financial models to steer clear of VBA, complex mega-formulas, array formulas and more controversially, range names.
I've spent the bulk of my on-line time at Experts Exchange in the Excel TA, and at VBAxExpress. I also visit Dick's Blog on a regular basis as it provides substantial fresh content from a wide range of experts and there are excellent reality checks provided by day to day users in the feedback. For some obscure reason that I can't place a finger on, I'm particularly interested in using Regular Expressions with Excel and I tend to think up excuses to over indulge in them. I haven't written a book, I don't have an Excel website, but I did get around to writing an free addin "The Duplicate Master", which a guy at Google Answers has profited more from than I have.
When do you remember using Excel for the very first time? Can you remember any specific details from that first time?
My previous company used Lotus 1-2-3, other business units in the company were using Excel and this was causing some grief so the entire corporation moved across to Excel in 1996. At that stage I was pretty annoyed, mainly as Lotus had (and still has) better 3D functionality than Excel, for example, with 1-2-3 you could paste transpose a 3D range across sheets into a single summary sheet. Plus I'd just taught myself macro programming with 1-2-3 and as I didn't have any real need to write code for my work, I wasn't prepared to learn a new macro language at that stage.
When do you remember writing your first formula or VBA code for Excel?
As above, my first formula was written in 1996. It took me around 5 years to get started in VBA, in 2001 I wrote a UDF to duplicate Excel's STDEV function. Later on I became interested in VBA as a tool to help audit poorly structured and formatted Excel models.
On average, how many hours per day do you spend working with Excel formulas and/or VBA code?
At work I spend around 5 hours a day with Excel formulas as part of my overall work in business evaluation. I use very little VBA at work outside of my own addins, although I do write code for the occasional task such as pulling survey data from outlook forms back to excel.
From mid 2002 till 2005, I spent around 2-3 hours at night and at weekends at Experts Exchange or VBAeXpress, focussing mainly on VBA, I've slowed down considerably in the last six months. In addition to this I spent much of a year over 2003/04 developing my duplicate addin in sporadic spurts of up to 8 hours at a time. Long haul flights in combination with potent airline coffee were an invaluable aid in getting the addin finished.
Which do you find most rewarding to work with: Formulas or VBA in Excel? Please tell us why?
VBA. For it's versatility, and the buzz I get from writing code that works
I use to build mega-formulas and I did enjoy that, but I realised I'd gone too far one day when I exceeded the character limit for a formula in a horrendously nested IF I haven't built what I'd term a complex formula for some time, mainly as I've become much more disciplined from a work perspective in designing models for others to use rather than just focussing on my preferences and Excel knowledge level. Although I still enjoy the occasional short, "clever" formula solution, and I do tinker with array formulas from time to time.
If you were going to give a novice, just starting out with Excel, some advice, what would it be?
Experiment. Experiment. Experiment some more.
When I started using VBA seriously in 2002 I decided to learn by helping others, I found it easier to learn once I had a specific outcome in mind, ie solving problems, rather than glancing over code and thinking "nice approach, I'll come back to that some time". I do look back and wince at my sledgehammer techniques in some of my early "solutions" and questions, but that's all part and parcel of moving up the learning curve.
Please provide a sample of your first work (either as a formula or vba code) in Excel and tell us about it.
Nothing too fancy. It would have been something like a weighted average of iron ore stockpile grades ported across from the old 1-2-3 model
Actually I'm surprised Excel doesn't have an in-built weighted average function
What is your mental attitude when you are preparing to write formulae or VBA code? And what is your working environment?
Its pretty much normal, as long as I've had at least one coffee in the last two hours I'm okay
If there special preparations that must be in place before you can begin, what are they?
In the absence of anything special I'd nominate coffee. But at eight to ten cups at day it's standard preparation
Finally, please give us something to think about - a reminder of your words here; a phrase that has helped you; a link to your own website. Anything that you think is important for the readers to remember.
One of my early general managers once quoted a version of Occam's Razor to me along the lines of, "As complex as you must, as simple as you can". Great advice which I have endeavoured to hold true to.
Thank you very much for answering the questions.
This Black belt is yours...