The Cell Master.
from Ostersund, Sweden
Please introduce your self. You may be as thorough as you wish. Feel free to include or omit any detail about yourself.
I am Dennis Wallentin, also known as XL-Dennis on the Internet, and I live with my family (wife and two beautiful daughters) in a small city,
Ostersund, in the northern part of Sweden (above the polarcircle). I enjoy very much mountainbiking, reading Science-Fiction and playing chess.
I have a Master-degree in business management & accounting and 80 % of a Master in Computer Science (which probably never will bee finished...).
I have been working fulltime in my own company since 1989. In the beginning the main business was education, especially for Excel.
Over the years the main focus has switched to consultancy, where I use Excel and other tools to build professional business-solutions for small as well as large companies and for the public sector here in Sweden.
From time to time I publish articles for some magazines here in Sweden. I also moderate an Office/Excel-forum here in Sweden,
. I have also a non-public obscure (!) e-mail based list for "XL-geeks" in Swedish.
I also maintain my own website (in Swedish): Tips & tricks for MS Excel http://www.xldennis.com
When do you remember using Excel for the very first time? Can you remember any specific details from that first time?
In 1984 I received my first IBM-compatible computer and the first software I worked professional with was Lotus 1-2-3.
At that time I was working in an accounting-department and it was amazing to move all the manual work to the computer-based environment.
Since then I, almost, likes all kind of spreadsheets-software (also on the Linux-platforms).
In late 1987 when I was working for a shipping company, who used 1-2-3, my boss at that time came into my office and handed over a package,
with the title "MS Excel 2.1c". My first reaction was very negative since I was used and pleased with 1-2-3. The GUI (graphic user interface) was something new
but I was not happy at all because I was forced to convert / re-write all files to Excel.
When do you remember writing your first formula or VBA code for Excel?
The first thing I tested in 1987 was with formulas and the event-control calculation with IF-, AND-, OR- and XOR-statements.
The main reason was to assure that my "Lotus-approach" could be fully implemented in Excel. However,
it may sound good while reading it but at that time I was quite worried about the change of tool...
One of my 1st VBA programs was to do with animation, I simulated A scene from the TV show "Stargate SG1" and also one of my favourites the Original (1st series) of Star Trek.
I simulated the Transportation sequence with the sound effects. I lost these files when I didn't follow the golden rule and failed to backup!
I lost a lot of original work I had built up over these years. There's a lesson there! We always learn the hard way don't we! LOL
On average, how many hours per day do you spend working with Excel formulas and/or VBA code?
On the average I spend about 9-11 hours working in front of the screen, where about 70-80 % of the work involves Excel.
My wife says I spend more time with the computer then with her. Since it is true I ask her to send me an e-mail if she want me something.....
Which do you find most rewarding to work with: Formulas or VBA in Excel? Please tell us why?
Since I has built up (and still doing that!) my knowledge and skillness inside the spreadsheet-world it is natural for me to defend the built-in functions/tools and formulas.
However, the best solution includes many time both using these tools/functions together with VBA.
The built-in tools and functions are part of the Excel.exe, which means that they all are compiled. VBA, on the other hand, interpretate the code each time it is executed.
That means that the built-in tools and functions will always be executed faster then any kind of solution /tool based on VBA.
From my point of view it is much more an intellectual challenging to explore the world of formulas then with VBA.
If you were going to give a novice, just starting out with Excel, some advice, what would it be?
#1 Plan your work - Work Your plan Use paper and pen to design your solution before you start the work in Excel. These tools are still valid and I highly recommend them.
#2 KISS - Keep it Simple Silly. A logical, simple structure and design is always recommended. Avoid too complex a formula because they can be very time-consuming to maintain after a while.
In general we can divide any sheet-based solution in two sections, an input-section and an output-section, i e simple structure.
Don't mix with the end-users Excel-interface, no one really appreciates it all.
# F1 Use the F1-button frequently, it is your best friend when beginning with Excel (Your best friend in this case is not your wife/man or pet) and it is always available 24-hours per day.
#Try, try, try and explore Since there are no really shortcuts to knowledge we are forced to do all the hard work by our selves. The only border we have to face is two-folded: Our knowledge of Excel and our imagination what can be done.
# Different tools for different problems Excel have many tools available and even if You have some favourites among them keep in mind to use the best tool that fit the needs for the actual problem. In order to use different tools we are forced to learn about them before we can use them.
# Learning from the best Take some time and visit boards and communities related to Excel regular on the internet. It offer also good possible to train on other peoples Excel-related problems.
Please provide a sample of your first work (either as a formula or vba code) in Excel and tell us about it.
First work: too long ago!
But I can tell you about one formula:
Instead of supplying something old I prefer to provide a free add-in, which convert formulas / functions between local language and English, "FF-Translator
(Functions-/Formula Translator)", which may be useful in several situations for you. The utility is available here
at Colo's site.
What is your mental attitude when you are preparing to write formulae or VBA code? And what is your working environment?
I usually load the CD-player with Led Zeppelin, The Rolling Stones, ZZ Top (one of the first albums), Thin Lizzy or with Ted Nugent before I start up the work.
Coffee is extremely important and that the computer starts up as expected :-). Finally, pens and paper must always be available.
Nevertheless, the best advice I have is:
If you are stucked with a problem - take a break
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.
(In memorial of George Simms, a friendly and polite gentleman and one of the best ever when it comes to clever formulas and formula-based solutions in Excel).
Thank you very much for answering the questions.
This Black belt is yours...