formulas

["Walter Lake" <wlake@xxxxxxxxx>]

Hi Lars

Glad that you liked the coding.

One of the problems I've been working with is learning different ways to
code Excel "in-cell" formulas into VBA code. For example one of Ton's
Profit/Loss Index formulas:

"... -Where the Formula for the "Units" (col I) cell on Row 3 is:
              =ABS(IF(A3>C3;((A3)/C3)+1;IF(A3<C3;((-C3)/A3)-1;0)))  ..."

Using the Macro Recorder and changing the ";" to "," you get this:
ActiveCell.FormulaR1C1 =
"=ABS(IF(RC[-7]>RC[-5]((RC[-7])/RC[-5])+1,IF(RC[-7]<RC[-5]((-RC[-5])/RC[-7])
-1,0)))"

Not many of the reference books i.e., Walkenbach, WROX, Steve Roman, 21
Days, etc. have much on writing formulas in VBA code. A new book "Excel 2000
Developer's Handbook" by Marion Cottingham at

 www.sybex.com
http://www.sybex.com/cgi-bin/rd_bookpg.pl?2328back.html

Has the following chapters which are quite complete:

"Part II: Naming Formulas, Functions, and Constants
Chapter 4: All About Formulas and Functions
Chapter 5: But Names Will Never Hurt Me!
Chapter 6: Using Constants
etc ..."

In Chapter 5, the author writes extensively about "naming" the formula when
you enter it into Excel and then using the name in your VBA code. Lots of
good ideas especially for Metastocker's who have indicator code etc. to
write into Excel. It also makes documenting much easier.

Of course, it's the "filling down" part that always messes me up and I end
up with 65,000 + rows filled <G>.

Best regards

Walter