THE CELL MASTERS

Chapter 08
The Cell Master.
Jon Peltier
from Massachusetts in the US

Jon Peltier's Home Page : www.geocities.com/jonpeltier/

interview date:24.Feb.2003

Please introduce your self. You may be as thorough as you wish. Feel free to include or omit any detail about yourself.

Hello, I'm Jon Peltier. I am a metallurgical engineer by formal training, working (as of this writing) in a metal forming company that is a major supplier to the aerospace and industrial parts industries.

But I've also started doing some freelance Excel programming after hours. Somehow I was nominated as a Microsoft Excel MVP in 2001.

But I've also started doing some freelance Excel programming after hours. Somehow I was nominated as a Microsoft Excel MVP in 2001.

When do you remember using Excel for the very first time? Can you remember any specific details from that first time?

The first time I used Excel was a very early version on a Mac, probably about 1990. I loaded the program, opened it up, and could make absolutely no sense of it. So I put it aside.

In 1993 I ditched the Mac for a PC at work, and it came loaded with Excel 2 or 3. I spent a couple of days with it and figured it out.

In 1993 I ditched the Mac for a PC at work, and it came loaded with Excel 2 or 3. I spent a couple of days with it and figured it out.

When do you remember writing your first formula or VBA code for Excel?

I wrote my first worksheet formulas the day I started with Excel. I started programming long before this newfangled VBA, though, recording and later writing lots of XLM macros.

When Microsoft introduced VBA, I tried it, but didn't completely get it, and I had invested too much time becoming good at XLM to want to switch. Finally I took a two-day course on Excel VBA, completely ignored the instructor, and played on the computer for two days without the distractions of my office.

When Microsoft introduced VBA, I tried it, but didn't completely get it, and I had invested too much time becoming good at XLM to want to switch. Finally I took a two-day course on Excel VBA, completely ignored the instructor, and played on the computer for two days without the distractions of my office.

On average, how many hours per day do you spend working with Excel formulas and/or VBA code?

At work in my present job, I spend up to about 5 hours working in Excel. At home, between the freelance work and answering questions in the news groups, I spend another 3 to 4 hours per day.

Which do you find most rewarding to work with: Formulas or VBA in Excel? Please tell us why?

I can't limit myself to one or the other, because an efficient solution must make use of both aspects of Excel programming.
Worksheet formulas make a much tighter application numerically, of course, but VBA can provide extraordinary enhancements.
But since tese enhancements are pretty cool, I guess I like using VBA more than writing formulas.

If you were going to give a novice, just starting out with Excel, some advice, what would it be?

Sit down with Excel and play with it. Try to figure out how to make it do what you normally spend hours doing manually.

Read the on line help, go to the Microsoft news groups and MSDN site, and find the good web sites. And keep experimenting.

Read the on line help, go to the Microsoft news groups and MSDN site, and find the good web sites. And keep experimenting.

Please provide a sample of your first work (either as a formula or vba code) in Excel and tell us about it.

Here is an old XLM UDF which accepts polynomial coefficients and temperature from a worksheet and returns the predicted materials property based on
a simplistic model.

I wrote this in 1993 or 94. I won't try to attach the next project I worked on, which was an elaborate XLM macro that input about 18 fitted coefficients and 4 more environmental conditions, and iterated through SOLVER to predict metal fatigue behavior in turbine engine parts.

MDD.POL5PH.AVG

Properties 01 (Young's Modulus), 02 (Shear Modulus), 03 (Poisson's Ratio), 04 (CTE), 05 (Specific Heat), 06 (Thermal Cond.), 09 (), 16 (Mono. Hardening Exp.), 29 (Cycl. Hardening Exp.), 30 (0.2% Cycl. YS), 33 ()

=ARGUMENT("Azero")

=ARGUMENT("Aone")

=ARGUMENT("Atwo")

=ARGUMENT("Athree")

=ARGUMENT("Afour")

=ARGUMENT("Afive")

=ARGUMENT("Delta")

=ARGUMENT("temp")

=SET.NAME("X",temp/Delta)

=SET.NAME("prop",Azero+Aone*X+Atwo*X^2+Athree*X^3+Afour*X^4+Afive*X^5)

=RETURN(prop)

The VBA equivalent to this XLM macro, which I translated in 1997, is shown below.

I wrote this in 1993 or 94. I won't try to attach the next project I worked on, which was an elaborate XLM macro that input about 18 fitted coefficients and 4 more environmental conditions, and iterated through SOLVER to predict metal fatigue behavior in turbine engine parts.

MDD.POL5PH.AVG

Properties 01 (Young's Modulus), 02 (Shear Modulus), 03 (Poisson's Ratio), 04 (CTE), 05 (Specific Heat), 06 (Thermal Cond.), 09 (), 16 (Mono. Hardening Exp.), 29 (Cycl. Hardening Exp.), 30 (0.2% Cycl. YS), 33 ()

=ARGUMENT("Azero")

=ARGUMENT("Aone")

=ARGUMENT("Atwo")

=ARGUMENT("Athree")

=ARGUMENT("Afour")

=ARGUMENT("Afive")

=ARGUMENT("Delta")

=ARGUMENT("temp")

=SET.NAME("X",temp/Delta)

=SET.NAME("prop",Azero+Aone*X+Atwo*X^2+Athree*X^3+Afour*X^4+Afive*X^5)

=RETURN(prop)

The VBA equivalent to this XLM macro, which I translated in 1997, is shown below.

a_0 = wksht.Cells(7, colnum) a_1 = wksht.Cells(8, colnum) a_2 = wksht.Cells(9, colnum) a_3 = wksht.Cells(10, colnum) a_4 = wksht.Cells(11, colnum) a_5 = wksht.Cells(12, colnum) delta = wksht.Cells(13, colnum) XX = Temp / delta pol5ph = a_0 + a_1 * XX + a_2 * XX ^ 2 + a_3 * XX ^ 3 _ + a_4 * XX ^ 4 + a_5 * XX ^ 5 End Function |

What is your mental attitude when you are preparing to write formulae or VBA code? And what is your working environment?

I recently bought a new laptop, and I have commandeered a small desk in the guest room. The entire house is wired into the cable modem, though, so I could work practically anywhere.

When I sit down to work, I go over my notes, scratch my head, wonder what the heck I was thinking, and dive in. I like new projects, because I have to use my imagination to come up with an approach to the solution.

When I sit down to work, I go over my notes, scratch my head, wonder what the heck I was thinking, and dive in. I like new projects, because I have to use my imagination to come up with an approach to the solution.

If there special preparations that must be in place before you can begin, what are they?

Sometimes I like to have a small bowl of snacks, so I'm not constantly walking down to the kitchen. Coffee is always good, too. But as long as I have a few pens, a pad of paper, and not too much background noise, I'm ready to go.

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.

The main takeaway is that nothing is completely impossible. Even if a supposed expert tells you that something cannot be done, keep looking. Another expert may have thought of a different approach.

I've put together a pretty extensive web site (http://www.geocities.com/jonpeltier), which has an emphasis on working with Excel charts. Many of the examples are techniques for achieving charting effects that at first glance seem impossible. The URL may change, but you'll be redirected from the Geocities page.

- Jon

I've put together a pretty extensive web site (http://www.geocities.com/jonpeltier), which has an emphasis on working with Excel charts. Many of the examples are techniques for achieving charting effects that at first glance seem impossible. The URL may change, but you'll be redirected from the Geocities page.

- Jon

Thank you very much for answering the questions.

This Black belt is yours...

This Black belt is yours...