Chapter 24   The Cell Master.  Rob Bovey  from USA
  Application Professionals

interview date:14th.Feb.2005
Please introduce your self. You may be as thorough as you wish. Feel free to include or omit any detail about yourself.
I'm an independent computer programming consultant specializing in Excel, VB6 and SQL Server. I've been a full-time programmer for ten years. Prior to that I worked as a corporate financial analyst, manufacturing quality control supervisor, printing press operator and auto mechanic. It took me a long time to discover the career I really wanted to spend the rest of my life doing, but I eventually discovered that it was computer programming. The moral of this story is don't be afraid to change careers if you don't like what you're doing now. It took me five tries to figure it out, but I'm very glad I didn't stop until I got it right. I'm very happy doing what I do now and I'd be very unhappy if I was still doing any of those other things.

When do you remember using Excel for the very first time? Can you remember any specific details from that first time?
During the DOS era I used Lotus 123. I first began using Excel 4.0 when I switched to Windows 3.0 in the late 1980's. I don't remember exactly when. At that time a spreadsheet was still just one of many tools I used to get my real job done.

When do you remember writing your first formula or VBA code for Excel?
In the late 1980's I was working as the quality control supervisor in a mid-sized manufacturing plant. One of my technicians brought me copies of Windows 3.0 and Excel 4.0. He was very excited about them and he talked me into trying them out. My computer at the time was very low powered. It was an IBM 8086 with 640KB of RAM and a 10MB hard disk. We weren't even sure Windows and Excel would run on it. But we installed them and everything worked. It was very, very slow, but I immediately saw the potential of Windows and Excel.

After that I started converting all my spreadsheets from Lotus to Excel and learning how to write Excel formulas. I never liked the idea of continuing to use Lotus formula syntax in Excel, even though Excel would let me do so (and still will, even today). If I was going to switch to this brave new Windows/Excel environment I wanted to learn all about how Excel worked and do things the Excel way.

On average, how many hours per day do you spend working with Excel formulas and/or VBA code?
I currently divide my time roughly 50/50 between Excel and SQL Server.

Which do you find most rewarding to work with: Formulas or VBA in Excel? Please tell us why?
I like them both, and other features as well. When I have a task to accomplish in Excel I treat it like a puzzle. What is the best way to solve the puzzle? Worksheet formulas? VBA? Charts? Controls? Other features? They all have their strengths and weaknesses. Discovering the best solution to the puzzle is the rewarding thing for me. It doesn't matter what features the solution turns out to use.

If you were going to give a novice, just starting out with Excel, some advice, what would it be?
There are three pillars required to become an Excel master. They are equally important, but are best learned in the following order:

* You must have a deep understanding of the Excel user interface. The prime directive in Excel development is to let Excel be Excel. Know and use the features Excel provides you. Don't try to reinvent the wheel.

* You must understand the Excel object model well enough so you don't program Excel badly. Most beginning Excel developers learn to program Excel using the macro recorder. This is fine. The macro recorder is a very powerful tool. Just keep in mind that the macro recorder produces very poor quality code. Take the time to understand why its code is poor and learn how to correct the mistakes it makes.

* You must know good general programming techniques so you don't write bad code. All non-trivial Excel applications require you to understand programming techniques that have nothing to do with Excel but are critical to the quality of your code. For example, you need to understand how to properly declare and use variables, write solid conditional statements and loops and make good use of functions and subroutines (among many other things). Without this fundamental programming knowledge your code will always be fragile and difficult to maintain and upgrade.

Please provide a sample of your first work (either as a formula or vba code) in Excel and tell us about it.
The very first application I wrote in Excel VBA was my XY Chart Labeler utility. At the time I wrote it I was a corporate financial analyst and I constantly needed to apply non-value data labels to XY charts in Excel (a feature I clearly remembered being available in Lotus, but which is unavailable in Excel to this day). I wrote the XY Chart Labeler in VBA using the very first release of Excel 5.0. VBA in Excel 5.0 was full of bugs. It was almost impossible to write useful programs with it until Excel 5.0c was released. However, I was able to get XY chart labeling to work. This utility is now in its 6th major version and is available on my web site at:

What is your mental attitude when you are preparing to write formulae or VBA code? And what is your working environment?
I enjoy solving problems. When I sit down to write an application in Excel I first break it down into a series of problems that need to be solved. I then break each problem down into its simplest components. Next I determine the best way to perform each of the actions required to solve each problem. Lastly, I determine the best way to organize all of the pieces and put them back together to form a complete application.

My working environment is my home office. I'm very picky about my working environment. Every time I've had to work in an unfamiliar environment my productivity has plunged. Therefore, I don't do any actual work at client locations. I will visit a client to gather information and to help set up completed applications if necessary, but all programming is done back at my home office.

If there special preparations that must be in place before you can begin, what are they?
All preparations I required are always available in my home office so I can walk right in and begin working. The things I am particular about are what combinations of programs are installed on each computer and how those programs are configured. The specific list of preparations would be too long to enumerate here and probably not very useful to anyone but me.

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.
Make sure you always have new goals that you want to achieve. Never settle for where you are now. The world of technology is a rapidly evolving world. If you do not continuously move forward you will inevitably be left behind.

Thank you very much for answering the questions.

This Black belt is yours...

| HOME |
Copyright © - colo's junk room All Right Reserved
Tips and Information about Microsoft Excel|Masaru Kaji aka Colo