The Cell Master.
Jan Karel Pieterse
Please introduce your self. You may be as thorough as you wish. Feel free to include or omit any detail about yourself.
Hi, my name is Jan Karel Pieterse, born in the fabulous year of 1961.
I am a chemical engineer and have worked in that position for about 17 years.
Since November 2003 I run my on business called JKP Application Development Services (www.jkp-ads.com
and yes, the company name is too long ).
I develop customer specific solutions, mainly in Excel but I’ve also done some things combining Excel with e.g. Access and Outlook.
When do you remember using Excel for the very first time? Can you remember any specific details from that first time?
I started using spreadsheets as soon as the second Lotus version was released. It ran on an IBM PC with a 10 MB
hard disk back then (I think around ‘85 or ‘86), which was
revolutionary in those days. It even had a plotter attached to it so you could have your charts drawn with
colored pens. Now THAT was a fun thing to watch, they don’t use those things anymore these days. All in all this system was a huge investment for my department back then, it must have cost up to about $ 10.000!
My first Excel experience was in 1996 (with Excel 5). My company switched from Lotus to Excel back then. I must say at the beginning I cursed the application. Many things I used to be able to do in a snap with Lotus and Quattro Pro I had to learn all over again in Excel. Some things that were simple in those other two apps proved difficult in Excel. But things really seemed to turn for the worse when I started off trying to create my first macro. If any of you have ever written a keyboard style macro in the old Lotus versions, you may recognize this. The learning curve for writing VBA code is significantly steaper than for writing keyboard macros. All you needed to know for the latter was the keyboard shortcut keys to reach the function or method you needed and then you’d just punch in those letters into a cell and off you went.
But of course VBA proved to be much, much more powerful and I wouldn’t for the world turn back to the keyboard style stuff.
I remember calling Microsoft customer service back then to ask a question on how to do something with VBA. Their answer shocked me: We don’t support customers in that way, if you need help, go to the internet and find yourself a forum.
Well, as soon as I got my connection I did and found the Compuserve Excel forum.
I started reading messages there and posting my questions. Soon I found myself answering messages too. This is how I learnt most of Excel.
When do you remember writing your first formula or VBA code for Excel?
That would have been in 1996. My first formulas weren’t necessarily simple ones, because I had a huge amount of old Lotus and Quattro pro sheets to convert.
Since luckily Excel could read most of them, learning the Excel formulas wasn’t too hard.
On average, how many hours per day do you spend working with Excel formulas and/or VBA code?
It varies hugely, ranging from 0 to maybe 12 hours a day. On Average I’d say about 4 hours.
Which do you find most rewarding to work with: Formulas or VBA in Excel? Please tell us why?
I have no real preference. I like solving a puzzle and try to use the most efficient tool for the task.
But I also love a challenge and may go out of my way to AVOID using the most efficient tool for the job to try and solve the puzzle using something else.
I like to solve problems where e.g. the use of VBA is to be avoided. My
file demonstrates this.
If you were going to give a novice, just starting out with Excel, some advice, what would it be?
Find yourself a good introductory book and (and this is important!)a useful project to tackle using Excel. I have never learnt anything useful when having to work through “Hello World” type of study material.
Use Excel for your tasks where appropriate (and no, don’t use it for word processing, just because you want to use Excel ).
Get online and start reading messages in newsgroups or online boards. Try and solve a couple of those questions (before reading the replies others have given) and compare your solution with theirs.
Learn as much as you can about what is built into Excel: functions, menucommands and all the features you can think of, before considering starting VBA. You’ll hugely benefit from knowing Excel’s capabilities when you start exploring the realms of VBA. In fact it is the combination of Excel and VBA that makes it such an excellent developing environment.
If your not a novice and want to learn how to build applications in Excel, the only way to really learn the ropes is by finding a feature you think should have been in Excel (or needs serious improving) and trying to create a utility others can download and install. This will get you into contact with all possible subjects involved with creating utilities. Here are some I encountered when writing
my Autosafe addin, the Name Manager and Flexfind:
* Getting to know the Excel Object model;
* Setup and uninstall;
* User interface programming: adding, removing and restoring Excel features to/from the userinterface (menus, commandbars), creating dialogs and userforms;
* Setting and restoring Application settings (calculation, screen, etcetera)
* Adapting to the state the application is in (editing a workbook embedded in Word?, in print preview mode?, are any workbooks open? Is the sheet protected?,…)
* Getting at things Excel itself has no knowledge about, like system metrics, the user logon name, how many Excel instances are running;
* Working around bugs (!)
And of course when you think you’re ready for the more serious application development, get a hold of the excellent book Professional Excel Development by Stephen Bullen, Rob Bovey and John Green. Run! No-one should be left alone doing Excel development without it!
Please provide a sample of your first work (either as a formula or vba code) in Excel and tell us about it.
One of the first VBA routines I wrote was this one:
Dim myname As Object
For Each myname In ActiveWorkbook.Names
Selection.Value = myname.Name
Selection.Offset(0, 1).NumberFormat = "@"
Selection.Offset(0, 1).Value = myname.RefersTo
As you can see, I was still selecting things to do something with them.
Another thing that is obvious is that it mimics a built in feature: Insert, Names, Paste. Which proves the point I mentioned earlier: get to learn Excel’s built in stuff before starting off with VBA. It also shows my affinity with defined names goes way back. Code like this (and subsequent code I wrote to work with names) was the fundament of the (now famous)
I built together with
What is your mental attitude when you are preparing to write formulae or VBA code? And what is your working environment?
I need a problem to solve. And I love a challenge.
I do all of my work on a laptop running WinXP Pro. I have a copy of each Office version since Office 97 on it. To ease typing I use a normal keyboard when I’m at my home office.
If there special preparations that must be in place before you can begin, what are they?
I am particular about my Software settings, so I take quite some time tweaking it all before I can start working. Of course that is not needed on my own machine, I keep regular backups for that one so a crash is relatively easy to overcome.
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.
What has really helped me tremendously was the free help and advice I got at the Compuserve Excel forum from 1996 to about 2000 (when it was merged into the Office forum). Although Compuserve still hosts the Office forum in which I do some sysop work, I still think the Excel forum was one of the finest places to ask your Excel related questions in. I loved that place. In fact it was the answering of questions there that got me the Microsoft MVP reward in the end, one of my “achievements” I am most proud about.
I would like to take the opportunity to thank all MVP’s (and everyone else helping out others for free on newsgroups and fora) that have helped me get to where I am now:
joining the renown ring of cell masters!
Thank you very much for answering the questions.
This Black belt is yours...