The Cell Master.
from Los Gatos, California
known in the internet community as "zorvek"
Please introduce your self. You may be as thorough as you wish. Feel free to include or omit any detail about yourself.
I have been married to the same beautiful woman, Roslyn, for 20 years. I have a 13-year-old daughter Emily - a budding artist and engineer, two dogs, and a cat. My first programming experience was using Basic to work out physics problems in college in 1973 on a GA mini computer that booted from a paper tape. There was only enough memory for about 150 lines of code. I recall two projects: plotting magnetic field lines and a lunar landing game that emulated the Apollo lunar landing module. This was my first experience wasting hours hacking at a computer. There was no place to save the code so we had to re-enter it each shift - unless we were lucky and no one had used the partition since the last time. These first experiences led me to change my major from Chemistry to Computer Science and I never looked back.
Since then I earned a Computer Science undergraduate from Cal Poly in San Luis Obispo California, and an MBA from Sloan at MIT. I have worked for Burroughs, American Management Systems, Apple, PowerUp Software (Address Book Plus and Calendar Creator), Air Communications (the first commercially available cellular fax modem), started a company that made eRegCard, an electronic registration system for desktop software applications and games, worked for some internet companies, did some web site search engine optimization, and am now consulting for companies such as Gap, Chevron, and Informatica.
I have coded in so many different languages I can't remember them all. My first computer class was Basic and am still coding more Basic than anything else. VBA and VB are the predominant tools in my life. I use them to make world-class applications and to remind me when Battlestar Galactica is on.
When do you remember using Excel for the very first time? Can you remember any specific details from that first time?
I was at Apple in 1984 on a 512K Macintosh (nothing much ran on the 128K Mac except MacPaint and MacWrite). I was working on Macintosh interactive language products and the first non-modal graphical versions of Excel and Word showed up. They didn't work quite as advertised and crashed a lot but they were still pretty cool. Excel was pretty simple then and we used it only for the most basic things - simple number crunching.
When do you remember writing your first formula or VBA code for Excel?
The first spreadsheet application I used was VisiCalc while learning how to wear a suit at MIT in 1982. Business finance stuff. Sum this and take a percentage of that. It made crunching numbers and what-if analysis a lot easier. A little 1-2-3 in there somewhere and then to Apple and LisaCalc.
The Lisa was a very sweet machine. I did a lot of business stuff on LisaCalc
- I didn't have a choice. But I didn't care because it was the best thing available. It just cost a little too much.
When Excel started making the rounds at Apple on the Macintosh in 1984 I started using it the same way I used LisaCalc, 1-2-3, and VisiCalc. Numbers and formulas and more numbers. It was a bit rough compared to LisaCalc but LisaCalc only ran on the Lisa which became the Macintosh XL which became some landfill.
But the romance didn't start until sometime around 1992 when Excel version 4 came out. That was when the potential started to sink in. I recall thinking that that release was more fun than the hottest video games. And it got better after that. Macros. Multiple worksheets. VBA. Working with spreadsheets was becoming fun!
On average, how many hours per day do you spend working with Excel formulas and/or VBA code?
Right now I pay the bills building Excel/Access systems for various clients.
That's four to eight hours. There are the people I help at sites like Experts-Exchange - another hour or so a day (my wife and the other "experts"
on Experts-Exchange will disagree but that's my answer). And there is the personal time building cool stuff for my own use like adding to my code libraries or trying to find ways to do something new or difficult - another couple of hours.
Which do you find most rewarding to work with: Formulas or VBA in Excel? Please tell us why?
The answer depends on the context. I always get a thrill out of using formulas to do what others (and I used to) think can't be done without VBA.
But Excel formulas are very limited in comparison and, within the context in which we can use them, more than just a little arcane. Look at all the hoopla around SUMPRODUCT. On the one hand we espouse its amazing powers and use it to do some amazing things. But on the other hand one has to wonder why we have to go through such hoops to do something a SQL hack can do in their sleep. I'm not saying I don't like it-just that the context in which it lives makes it an enigma. The side benefit of course is being able to dazzle your friends and coworkers and for this I am grateful. But that's just more in-the-box thinking. Out-of-the-box thinking and VBA leads me to wonders far beyond formulas.
Excel is a truly amazing tool. I have my head, my computer, and Excel. In that order those are the most powerful tools I have. Not the car, bike, satellite PVR, microwave, power drill, or Swiss Army knife. Word lets me write documents. Access lets me accumulate large quantities of data and do some fun stuff with it. But Excel, that's a whole different matter. Cells on a grid. I can also put controls on that real estate. I can format it in any number of different ways. With a few merged cells to get around the row and column constraint I can make a sheet look like any paper form. And the kicker is that it's alive. It calculates. It runs macros. It can be animated. It does just about anything I want it to do. And in very short order. That's the key - I can make solutions that do something useful in hours versus days with VB and weeks with C#. I can't do that in Word or Access without a huge pile of code. I can do it with VB or C# with more work but Excel is right there with a ready-to-go user interface. The bottom line is Excel is the first tool I reach for when I have a problem or task that requires any type of automation.
My daughter came to me and asked if I would join her in the online game NeoPets. I played a few hours with her and began to realize that some automation might help her get more points. This is certainly not a new concept and hundreds of other nut cases without lives had done the same with various tools. I opened Excel, added my Internet modules, and started reading and posting web pages. A little formatting and pushing data to the sheet and she had a dashboard showing her status. More automation and she was accumulating points while taking English exams at school.
I have used to Excel to define and generate C++ code for customized solutions. I use it to scrape data from web pages such as when Chevron needed worldwide rainfall data. I pull Amazon and IMDB data to add information to my DVD inventory. A football pool that pulls stats from the web. Website domain searches. SEO page link and Google page rank analysis. A daily Excel job that pulls the next week's schedule of my favorite TV shows and sends it to my Blackberry. Running the area's Girl Scout cookie shop lottery and schedule - pushing out emails, reading emails, crunching the lottery, and tracking all the troop's schedules. Animated greeting cards:http://www.zorvek.com/downloads/expression.xls
If you were going to give a novice, just starting out with Excel, some advice, what would it be?
Keep it simple. Avoid using too many fonts, font styles, and font sizes.
Only use a few colors - like light grey for accent and light yellow for input cells. Be consistent. Don't password protect anything - it will only make matters worse in the end and give your enemies cause to snoop. Do use worksheet protection (without passwords) to enhance the user interface (only allow entry in specific cells).
Make lots of backups - Excel is about as stable as any application with millions of lines of code written by ordinary people like you and me. Use versioning (can be as simple as appending a version number to the file name) and keep older version just in case. Get a good workbook fixer like Workbook Rebuilder at www.vbusers.com
- you will need it at some point.
Format a few cells or all cells to the end of the column - anything else results in massive workbook bloat which makes emailing your treasures all the more difficult. Play with formulas. Experiment. Go to experts-exchange.com and ask questions. Get some books. My favorites are any of John Walkenbach's Excel Bible series (much of the content is repeated so any one of the three is good to start) and Professional Excel Development by Bullen, Bovey, and Green.
When comfortable with the above start using VBA. Use the macro recorder to see how Excel does tasks in VBA (99% of what you do in Excel can be done with macros and Excel records all of it while you do it). But don't lean too heavily on the generated macros - they are inefficient by most standards. Do use them to see how to manipulate Excel objects such as workbooks, worksheets, cells, ranges, shapes, charts, PivotTables, etc. Use the Object Browser (F2 while in the VBE) to peruse the various objects, methods, events, and properties of the Excel object model. Learn to use the debugger and the watch window - they will be your best friends from the start.
Avoid error handling - it's not very effective in VBA and leads to dreadful programming habits. Handle errors locally where you have the most context.
Don't mask errors with distant error traps that homogenize the error and prevent the user from seeing the Debug button - let the testers and users help you find all the errors by letting VBA find them now versus later. The best applications don't break at all - masking errors in any way, shape, or form only prolongs the pain and increases the long-term cost by making error resolution more elusive. The last five years I have essentially avoided error handling of any kind except where absolutely necessary (testing existence of a key in a collection for example) and my applications are rock solid with a mean time to failure of months if not years.
Preflight (assert) input variables before entering a sequence of code to ensure the code does not break.
Learn about classes - they can save you a lot of time and code.
Start building a set of standard libraries. Put any routines you use in more than one context into the libraries. When doing so, spend a little extra time making sure the routine is good enough to last a few years. Add a few more parameters that might be needed in the future. Do some preflight checks to make sure the routine does not break. Unit test it. Add some documentation so when you return in a month you will have more to read then just the routine name.
Find a support group. You will get frustrated and the best path to clarity is to have a network of people who know a few things you don't. Friends, online forums, books, Google. There is a wealth of information available - use it.
Please provide a sample of your first work (either as a formula or vba code) in Excel and tell us about it.
I searched my archives but all my early stuff is gone. If anyone is interested in my ad-hoc work over the past three years, go here:http://www.experts-exchange.com/viewAnswerHistory.jsp?mid=1677072&orderBy=5&sort=-1&page=1000
. That's the first page of many questions I helped people with at Experts-Exchange. There is plenty of material there to peruse and from which to learn. And over the three years since I started helping people there my Excel skills have increased considerably thanks to Brad, Patrick, Rory, Jeroen, Matt, Jim, Shane, Geoff, Nigel, Jaes, Wayne, and the other experts there.
What is your mental attitude when you are preparing to write formulae or VBA code? And what is your working environment?
After years of consulting I have learned that the best solutions are not designed by engineers. Engineers may think they know a thing or two about user interfaces but they don't. Engineering-centric design is death for any application. I talk to users. I get in their heads. I ask them what they are really trying to do. How they conceptualize the information with which they are working. I ask them what is not working, what is working, and where most of their time is spent. I look for opportunities to automate what is routine, and for new ways to present the data that gets the user closer to their desired outcomes faster.
I use Extreme Programming - an iterative approach to design and implementation that requires frequent interaction and reviews with the users as the application is being built. Much less rework for me - faster and more useable solutions for my clients. As mentioned above I employ little to no error handling (with everyone's VBE error trapping set to "Break in ClassModule") and, with a wee bit of training on how the Debug button is our friend, not our enemy, my users become an amazingly effective testing department. The icing on the cake is that, when the project is done, the user tend to embrace the application more than when I don't include them in the development and testing process.
If there special preparations that must be in place before you can begin, what are they?
I open a blank workbook, copy in my libraries, and off I go. Typical libraries include array manipulation, file and folder handling, environment control, menu control, string functions, and standard worksheet routines - pretty standard stuff for most Excel hacks. Mine are extremely robust after many years and the depth and breath of these standard functions increases my productivity tremendously. I can usually have a working application in a few days versus a few weeks.
Most important to me while developing is to have a robust set of development tools. I have my own set of utilities that includes everything from resetting the application environment settings (for when Application.ScreenUpdating gets stuck in the off position) to code generation to multiple workbook code comparison and merges.
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.
Thank you very much for answering the questions.
This Black belt is yours...