Chapter 32   The Cell Master.  Brad Yundt  from Missouri, USA
  Known in the internet community as "Byundt"

interview date:8.Nov.2006
Please introduce your self. You may be as thorough as you wish. Feel free to include or omit any detail about yourself.
I am a mechanical engineer who has spent his entire career working for process equipment manufacturers. So my day job involves calculating flows and heat transfer in pipes, heat exchangers and agitated vessels.

In the early Seventies, I wrote many of the programs used by my first employer for these calculations, using FORTRAN running on a mainframe via time-sharing. These programs replaced hand calculations that took an experienced engineer the better part of a day to complete. There was no structuring in FORTRAN at this time. Instead, you numbered the statements at the beginning and end of each block-and either jumped around the block or else executed the code it contained. Needless to say, there were a lot of line numbers and the resulting spaghetti code was difficult to follow even when you were the author.

Once these programs were done in the mid-Seventies, I stopped writing code and concentrated on running the calculations. These programs (some of them 50 pages long) were later ported by another engineer to the newly introduced IBM PC running in DOS mode. I believe they are still in daily use.

In 1994 my second employer licensed some technology to another firm, and I was asked to write the software needed to size the resulting equipment. Basically, the program kept count of the number of ice crystals made by the machine and melted by the user over the course of a week. As I recall, the software specifications ran at least ten pages-none of which defined the expected inputs and outputs or calculation approach. Instead, the specifications demanded compiled software written in C++ running under Windows 3.1, and a whole bunch of details on how to create schedules, documentation and a technical support forum.

After looking at a page of C++ code, I decided that it was completely unreadable by me or anyone else who didn't know the language. I told the client that complying with their specs would cost several times their budget, but that code written in Visual Basic would meet their real needs and could be delivered on budget. These were foolishly brave words from someone who wasn't a programmer and had never written anything in Visual Basic. To define the required inputs and outputs, I dummied up an Excel 5 spreadsheet. It was then that I discovered that the arcane Excel 4 macro language had been replaced by VBA-and VBA had definite possibilities.

Armed with comments from the client on the input/output dummy, I learned VBA by writing the necessary 50 page program. I broke the task into a number of user-defined functions, each of which could be written and debugged independently from the others. And I decided to use logically structured code to eliminate the jumble caused by FORTRAN line numbers. The Excel worksheet was used for input and output only. I didn't know anyone who used VBA, and relied upon weekly phone calls to Microsoft technical support to develop workarounds for all the problems I uncovered. Each phone call would usually solve about half of the four to six problems discussed.

The biggest problem was the fact that the calculations required several nested trial and error loops. Running these loops on a 486 CPU with an interpreted language required an unacceptable five minutes each time an input changed. Rather than give up, I improved the algorithm by making smarter guesses for the inputs to each iteration. I also learned that returning an entire array of results using an array formula was much faster than returning results with many separate function calls. The net result was a calculation time resulting in a fully optimized system around 20 seconds.

When do you remember using Excel for the very first time? Can you remember any specific details from that first time?
I first used Excel in 1988. I had previously used MultiPlan (Macintosh) and Lotus 123 & Quattro (both in DOS). Excel 2.0d ran in "run-time" Windows, so it had sort of a GUI interface. I used Excel for cost projections and very simple engineering calculations. I didn't think Excel was useful for most of my engineering calculations because it couldn't handle looping very well-something needed for many heat transfer problems.

Even though I used the most popular dot matrix printer of the day, I found it necessary to use special margin settings for Excel because the worksheet otherwise printed low and to the left. When I complained to Microsoft, their response was that it was up to the printer manufacturer to produce the drivers. Not impressed with this answer, I skipped version 3 and didn't upgrade until version 4 (for Windows 3.0), which did come with a properly formatted printer driver.

When do you remember writing your first formula or VBA code for Excel?
I'm sure that any Excel formulas I wrote back in 1988 involved basic arithmetic, along with some nested IF and VLOOKUP formulas for cost estimating. On the engineering side, there were probably some LN, SIN, COS and LINEST formulas. Excel was basically the PC version of my pocket calculator, with the principal advantages being the ease of producing nice looking printed records.

On average, how many hours per day do you spend working with Excel formulas and/or VBA code?
Today, I spend about 8 to 10 hours a day working with Excel. About half of that is using Excel workbooks I have created for my day job, with the rest being workbook and code development. Most of the development work is for questions posted in one of the tech support forums, though I do continue to refine my engineering tools for my day job.

Which do you find most rewarding to work with: Formulas or VBA in Excel? Please tell us why?
I find myself drawn to formula problems more so than to VBA. Lots of times, you can do the same thing with an array formula that would otherwise require VBA code. And contrary to what you might think, there are still things being discovered.

If you were going to give a novice, just starting out with Excel, some advice, what would it be?
To anyone who thinks they are pretty good at Excel, I strongly suggest participating in one of the technical support forums. You may have mastered the techniques you use regularly in your job, but they represent only a small fraction of what Excel can do.

When I first started participating in technical support forums, I could answer no more than a quarter of the problems posted. But the learning pace was extremely rapid because I tried to understand suggestions posted by other people and also because of my efforts developing and researching possible solutions. Three years later, I still find myself learning.

Please provide a sample of your first work (either as a formula or vba code) in Excel and tell us about it.
Here is a user-defined function I developed for the ice crystal counting program mentioned in the first question. It returns the hourly temperature variation given the expected high and low temperatures. Note the absence of variable typing-a poor practice I followed until 2003, when I started participating in technical forums. If I were writing the same function today, I might use a VLOOKUP function inside the code. I would also use continuation characters to make multi-line comments look neater. The code commenting is typical of my current practice, and is much improved over my FORTRAN programs.

I'd post a link to the entire program, but unfortunately the earliest version requires Excel 95 to run. There were a number of syntax changes to VBA in Excel 97, so I needed to produce a separate version of the code. It broke once again with Excel 2000. Excel 2002 and 2003 introduced no new issues.

Function Ambient(Hr, High, Low)
'Returns ambient temperature (F)
'Method: assume same proportional variation between daily high and low as in Atlanta
'from mid-July to early August.
'Why Atlanta? The only answer is that data was readily available
'and ASHRAE handbook contained nothing suitable of more generic nature
'Hr is military time of day (decimal number) or serial number (date code)
'High is the high temperature (F) for the day
'Low is the low temperature (F) for the day
    Dim ApparentTemp, AtlantaTempRange, MilitaryHour
    If (Hr <= 24 And Hr >= 0) Then      'User input time in military hours
        MilitaryHour = Hr
    Else                                'User input serial number time & date code
        MilitaryHour = Hour(Hr)         'Convert to military time
    End If
    ApparentTemp = Array(88.5, 87.75, 86, 85, 84.5, 83.75, 83.75, 83.5, 84.5, _
                         87.5, 90, 94, 95.25, 96.25, 96.5, 97, 97.5, 98, 97, _
                         94, 92.5, 90.75, 90, 89.25)
    'ApparentTemp (F) in Atlanta between 7/19 and 8/4/95 as reported in
    'Wall St. Journal 8/23/95 p. B-1
    'Includes effects of both temperature and humidity
    'Atlanta's high and low apparent temperatures were 98F and 83.5F
    AtlantaTempRange = 14.5     'The variation between high and
                                'low apparent temperatures
    Ambient = ((ApparentTemp(MilitaryHour) - 83.5) / AtlantaTempRange) * _
              (High - Low) + Low
End Function

What is your mental attitude when you are preparing to write formulae or VBA code? And what is your working environment?
I get energized when writing code or formulas, especially the tricky stuff. I'll think about the possible approaches, pick one and then dig right into it. And if that first approach fails, I'll begin again with one or more of the alternatives. When I get really stuck, doing something else allows my mind to ponder over the problem. Those quiet moments when I am trying to fall asleep often provide the inspiration for a new round of attack the following morning.

My first solution to a complex code or formula problem can always be improved. In VBA code, I usually declare more variables than I needed and tend to break the problem up into unnecessarily small steps. So I like to re-examine my efforts to see where it can be made either more general or more compact. I'll also comment the code at this time.

I believe that I can work in almost any quiet and well-illuminated space. Since other family members usually occupy all the desks in the house, I retreat to the kitchen table which has a free space about double the footprint of my laptop. My Excel working space at the office isn't much bigger. Though my computer and mouse fit easily in a small space, my imagination roams widely.

The work environment needs to be free of interruptions. One question every two to three minutes is guaranteed to prevent me from accomplishing any work on Excel?because no sooner do I regain a train of thought, than it is interrupted.

If there special preparations that must be in place before you can begin, what are they?
The only special preparation is that I must not be overly tired. And the tougher the problem, the more important it is to be fully rested.

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.
I participate in a technical support forum From time to time, some people ask why a person should spend time trying to answer the questions on the forum. Here is a paraphrase of my response:

If you value knowledge and skills, participation in a support forum by answering questions is its own reward. No matter how much you know when you first start answering questions, you will quickly learn more. The people posting questions frequently use different parts of their hardware and software than you are familiar with. And the experts trying to answer questions have different backgrounds than you do. So participation in the forum is a continuous stream of new experiences.

You will learn by trying to figure out the answer on your own. You will learn by researching the issue using on-line help and the internet as information sources. You will learn by watching how other experts try to answer the question, sometimes using undocumented tricks to solve common problems. You will improve your communications skills, because a confused Asker may prefer a better worded explanation from another expert. You will improve your diagnostic skills because often times the hard part is figuring out what the real question is. You will earn the gratitude of the people you help, no small thing in an age of impersonalization. And you will earn kudos from your peers when you teach them something new.

I can attest to all of the above benefits from personal experience. Knowledge came extremely rapidly at first, and I just loved getting that stream of "Good Answer" e-mails. Three years and thousands of answers later, I still manage to learn several new tricks every week through participation in the forum. I have made friends all over the world. The knowledge gained from forum participation helps me do my job better. And I would certainly discuss my accomplishments with a potential client or new employer.

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