Chapter 30   The Cell Master.  Ken Puls  from British Columbia

interview date:10.Mar.2006
Please introduce your self. You may be as thorough as you wish. Feel free to include or omit any detail about yourself.
I'm 32 years old, married to my wife Deanna, and have a 2 year old daughter named Annika. We live in Nanaimo, British Columbia, on Vancouver Island, which is only a couple of hours short of being as far left in Canada as you can go before getting really wet.

I received my Certified Management Accountant's designation in 2000, and for the past 6 ? years I've worked at a resort as the Accounting Supervisor/Systems Administrator, where we have a hotel, golf course, 3 food and beverage outlets, a 400 slip marina and a whole bunch of bare land to develop. My responsibilities include developing and generating financial reports, creation, maintenance and review of internal controls, and maintaining the computer systems for the entire resort.

One of the biggest attrctants to Excel for me was that I stumbled on to the automation that was possible in VBA, which really struck a chord with me. I hate doing repetitive tasks, and was shocked to find out that Excel had that much power under the hood just waiting to be tapped. Before I opened that door, Excel was a tool. Now it's become my hobby and my passion.

When do you remember using Excel for the very first time? Can you remember any specific details from that first time?
The very first time I used Excel was when I visited my father at work, and did some data entry for him. If I recall correctly, it was one of those "take your kid to work" type days. All I did was punch in lab results into a spreadsheet. No formulas, just numbers. I must have been in my early teens, at the time, and I wouldn't use Excel again for many years afterwards. My next memory of Excel was using Excel 97 at a client's place, and having the cells auto-complete for me when I was typing a list. I remember being blown away by that feature.

When do you remember writing your first formula or VBA code for Excel?
I used Lotus for a few years before my second introduction for Excel, and become rather familiar with basic If, Average, Sum and Round formulas, so my first formula in Excel didn't leave much of an impression on me.

My first look at VBA came in mid 1999 when we had a summer student write a macro for us. After they left, as the computer guy, it became my job to maintain it. I basically only updated a range if we needed to expand it or shrink it. I started recording code, and making very minor edits to it in the beginning of 2001, mostly just to roll a working paper forward. (Copy ending balance to opening and clear the data input range.)

In late 2002, though, we had our staff cut by 33% in our offices, and were asked to produce the same amount and quality of work. By that time, I knew that automation could help us, and dedicated myself to learning it. I remember being very frustrated much of the time, until I finally found my first forum in early 2003...and that's when I really started to learn.

On average, how many hours per day do you spend working with Excel formulas and/or VBA code?
At work, I probably average about 3-4 hours per day in some capacity. I also average about 3 hours per night at home, although much of that is also reading forum posts, testing solutions and experimenting with new things.

Which do you find most rewarding to work with: Formulas or VBA in Excel? Please tell us why?
Actually, the solutions that I'm most proud of are where I've applied them together. If a native formula exists, it is almost always faster than using VBA. I try to figure out a formula solution first, but there are things that native formulas cannot do. I believe that the trick to best leveraging Excel is knowing when to use formulas, when to use VBA, and when to use VBA to put a formula in place (even if it's only temporary). I also get a rush from controlling other applications like Word, Powerpoint and Internet Explorer from within Excel.

If you were going to give a novice, just starting out with Excel, some advice, what would it be?
The best piece of advice I could give is this: Find a forum, and when you do...

1) Search, lurk and post. I fought VBA for 2 years on my own before I found my first forum, and I probably doubled my VBA knowledge in 20 posts. In addition, I started learning copious amounts of new formulas that I never even knew existed. Search the boards for your issues, because you're seldom the first with the question. Lurk around and read others posts, as they can help you learn more. Post when you can't find what you're looking for.

2) Be courteous. You have nothing to lose in a forum, except the respect of others, but everything to gain. They are absolutely full of very talented people eager to share their knowledge with you.

3) Give back where you can. Nothing teaches us more than when we try to teach others. Chances are someone will show you a different way to accomplish something from what you suggest, allowing you to learn even more. Apart from that, experiment. I've found that there's usually more than two ways to do anything in Excel.

Please provide a sample of your first work (either as a formula or vba code) in Excel and tell us about it.
My first formula in Excel was probably something along the lines of =Round(Sum(A1:A10),2)

The first macro that I actually wrote without the macro recorder was used to copy data to a historical field and clear cells from a data entry template. It would have looked suspiciously like this:

Sub IHopeThisWorks()
Selection.Pastespecial Paste:=xlPasteValues
End Sub

What is your mental attitude when you are preparing to write formulae or VBA code? And what is your working environment?
I prefer to be left alone so that I can focus on what I'm doing. I always have a pad of paper beside me so that if I am interrupted, I can quickly scribble down where I was, and where I was going. Most of the time I can even figure out what my notes mean...

If there special preparations that must be in place before you can begin, what are they?
This completely depends on the complexity of the project. In the case of a simple data entry template, I prefer to just start designing it, and then stare at it on screen and move the fields around till they are where I like them. If I'm designing a more complex set of templates for someone else, though, I'll sit them down and draw out a rough picture first. With code, I almost always to grab a pad of paper and at least think through and mark down the major steps in my end goal. Again, the depth to which I map it out depends on the complexity. Once I have an outline, I tend to dive in and make the rest up as I go. Sometimes it does make for re-writes, but that's all part of learning for 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.
One of the most important pieces of advice that I ever got was "Never be afraid to say that you don't know something, but make sure you know where to look to find out." There is a wealth of Excel information available for free on the web, from blogs to sites devoted to tips and tricks. In addition, newsgroups and forums are full of experts who volunteer their expertise to those with questions. Don't be afraid to use them, they are there to help make you look good!

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