|
PureBytes Links
Trading Reference Links
|
Hi Lars, Angel and others who have written
Here's Marion Cottinham's variations for writing "in-cell" formulas in VBA:
1. Original worksheet code:
=IF(B10>B5,((B4-B3+1)*C4) .... etc.
macro recording:
2. ActiveCell.FormulaR1C1 = _
" =IF(RC[-1]>R[-5]C[-1],((R[-6]C[-1]-R[-7]C[-1]+1)*R[-6]C) ... etc.
3. using the "cells" property of worksheet (see the "." before "cells")
With Worksheets("Sheet1")
If (.Cells(10, 2).Value > .Cells(5, 2).Value) ... etc.
4. using the "range" property and names
If (Range("GrossPay").Value > Range("TaxBand3").Value) Then
Range("Tax") = (Range("TaxBand2") - Range("TaxBand1") + 1) ... etc.
5. using a simplified version of names
If GrossPay > TaxBand3 Then
Tax = (TaxBand2 - TaxBand1 +1) * TaxLevel1 ... etc.
==========
I prefer the "cells" method especially because the "in-cell" code often has
worksheet references associated with it, i.e.,
=IF(INDEX(DailyPrices,MATCH(WeeklyPrices!$A3,DailyPricesDate)+2,5)>INDEX(Dai
lyPrices,MATCH(WeeklyPrices!$A3,DailyPricesDate)+1,6),"Up",IF(INDEX(DailyPri
ces,MATCH(WeeklyPrices!$A3,DailyPrice .... etc.
plus, it's also difficult to names 300 rows or 3,000 rows of "entry prices"
etc. <G>
Best Regards
Walter
----- Original Message -----
From: Walter Lake <wlake@xxxxxxxxx>
To: <metastock@xxxxxxxxxxxxx>
Sent: Monday, November 15, 1999 8:28 AM
Subject: Re: formulas
| Hi Lars
|
| Will read the chapters again and then post the "cells" and "range" methods
| for VBA coding of formulas, in addition to the "name" method.
|
| Best regards
|
| Walter
|
|
|
|
| ----- Original Message -----
| From: Lars Andersson <Lars.Andersson@xxxxxxxxxxxxxxxxx>
| To: <metastock@xxxxxxxxxxxxx>
| Sent: Monday, November 15, 1999 4:54 AM
| Subject: Re: formulas
|
|
| | Hi Walter.
| | I know what you mean. I have not learn this yet and I donīt think I
ever
| do that. The book you mention seems to be very good
| | so perhaps I buy it.
| | Thatīs not fun when you get 65 000 + rows filled just wonder how to do
| this.
| | Best regards.
| | Lars
| |
| | Walter Lake skrev:
| |
| | > 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
| |
|
|