Array Problem
 

Hello,

I'm trying to create a function that will output the present value of
a stream of cash flows. I was able to get it to work for a range in a
column but I haven't been able to get it to work for a range in a
row. I tried to get rid of the transpose in various ways without any
luck. Below is the code that works for data in a column.

I think that it would also be neat if I could put something in the
code to determine if the stream of cash flows are in a column or in a
row. I was thinking that if I were able to determine the row using
Excel's row function of the beginning cell in the range and the the
row of the last cell in the range, I could create some logic to
determine if the cashflows are in a row or in a column.
------------------------------------------------------------------------------------------------------------------
Function pval_T(irate_T As Double, rngIn As Range) As Variant
Dim myArr As Variant, i As Long

myArr = Application.WorksheetFunction.Transpose(rngIn)

For i = LBound(myArr) To UBound(myArr)

pval_T = pval_T + myArr(i) / (1 + irate_T) ^ i

Next i

End Function
---------------------------------------------------------------------------------------------------------------------
Suggestions are appreciated.

Array Problem
 

Using your Range name, consider this structure...

If rngIn.Rows.Count 1 Then
' Multiple rows only
Else rngIn.Columns.Count 1 Then
' Multiple columns only
Else
' Multiple rows and columns
End If

Note the "s" on the end of the keyword Rows and Columns

Rick


"Jonas" wrote in message
ups.com...
Hello,

I'm trying to create a function that will output the present value of
a stream of cash flows. I was able to get it to work for a range in a
column but I haven't been able to get it to work for a range in a
row. I tried to get rid of the transpose in various ways without any
luck. Below is the code that works for data in a column.

I think that it would also be neat if I could put something in the
code to determine if the stream of cash flows are in a column or in a
row. I was thinking that if I were able to determine the row using
Excel's row function of the beginning cell in the range and the the
row of the last cell in the range, I could create some logic to
determine if the cashflows are in a row or in a column.
------------------------------------------------------------------------------------------------------------------
Function pval_T(irate_T As Double, rngIn As Range) As Variant
Dim myArr As Variant, i As Long

myArr = Application.WorksheetFunction.Transpose(rngIn)

For i = LBound(myArr) To UBound(myArr)

pval_T = pval_T + myArr(i) / (1 + irate_T) ^ i

Next i

End Function
---------------------------------------------------------------------------------------------------------------------
Suggestions are appreciated.

Array Problem
 

Not sure you need the transpose and it is simple to see
from the resulting array what the range input was:

Sub test()

Dim arr

'row input range
arr = Range(Cells(1), Cells(10))
MsgBox UBound(arr), , UBound(arr, 2)

'column input range
arr = Range(Cells(1), Cells(10, 1))
MsgBox UBound(arr), , UBound(arr, 2)

End Sub


RBS


"Jonas" wrote in message
ups.com...
Hello,

I'm trying to create a function that will output the present value of
a stream of cash flows. I was able to get it to work for a range in a
column but I haven't been able to get it to work for a range in a
row. I tried to get rid of the transpose in various ways without any
luck. Below is the code that works for data in a column.

I think that it would also be neat if I could put something in the
code to determine if the stream of cash flows are in a column or in a
row. I was thinking that if I were able to determine the row using
Excel's row function of the beginning cell in the range and the the
row of the last cell in the range, I could create some logic to
determine if the cashflows are in a row or in a column.
------------------------------------------------------------------------------------------------------------------
Function pval_T(irate_T As Double, rngIn As Range) As Variant
Dim myArr As Variant, i As Long

myArr = Application.WorksheetFunction.Transpose(rngIn)

For i = LBound(myArr) To UBound(myArr)

pval_T = pval_T + myArr(i) / (1 + irate_T) ^ i

Next i

End Function
---------------------------------------------------------------------------------------------------------------------
Suggestions are appreciated.

Array Problem
 

On Oct 17, 10:10 am, "RB Smissaert"
wrote:
Not sure you need the transpose and it is simple to see
from the resulting array what the range input was:

Sub test()

Dim arr

'row input range
arr = Range(Cells(1), Cells(10))
MsgBox UBound(arr), , UBound(arr, 2)

'column input range
arr = Range(Cells(1), Cells(10, 1))
MsgBox UBound(arr), , UBound(arr, 2)

End Sub

RBS

"Jonas" wrote in message

ups.com...



Hello,

I'm trying to create a function that will output the present value of
a stream of cash flows. I was able to get it to work for a range in a
column but I haven't been able to get it to work for a range in a
row. I tried to get rid of the transpose in various ways without any
luck. Below is the code that works for data in a column.

I think that it would also be neat if I could put something in the
code to determine if the stream of cash flows are in a column or in a
row. I was thinking that if I were able to determine the row using
Excel's row function of the beginning cell in the range and the the
row of the last cell in the range, I could create some logic to
determine if the cashflows are in a row or in a column.
---------------------------------------------------------------------------*---------------------------------------
Function pval_T(irate_T As Double, rngIn As Range) As Variant
Dim myArr As Variant, i As Long

myArr = Application.WorksheetFunction.Transpose(rngIn)

For i = LBound(myArr) To UBound(myArr)

pval_T = pval_T + myArr(i) / (1 + irate_T) ^ i

Next i

End Function
---------------------------------------------------------------------------*------------------------------------------
Suggestions are appreciated.- Hide quoted text -

- Show quoted text -

This is good but I need to be able to have the number of cells in the
range be variable and dependent on the range that is chosen by the
user. Is there a way to convert a range to an array? The above code
does so by using "Transpose." I can't get the code to work if I just
type "myArr = rngIn." I need to find a way to put the values of the
range "rngIn" into an array.

Array Problem
 

The input can be variable. Still can't see why you need the transpose:

Sub test()

Dim rng
Dim arr

Set rng = Application.InputBox("Pick the required range, one column or one
row.", _
"", , Type:=8)

arr = rng
MsgBox UBound(arr), , UBound(arr, 2)

End Sub


RBS


"Jonas" wrote in message
ps.com...
On Oct 17, 10:10 am, "RB Smissaert"
wrote:
Not sure you need the transpose and it is simple to see
from the resulting array what the range input was:

Sub test()

Dim arr

'row input range
arr = Range(Cells(1), Cells(10))
MsgBox UBound(arr), , UBound(arr, 2)

'column input range
arr = Range(Cells(1), Cells(10, 1))
MsgBox UBound(arr), , UBound(arr, 2)

End Sub

RBS

"Jonas" wrote in message

ups.com...



Hello,

I'm trying to create a function that will output the present value of
a stream of cash flows. I was able to get it to work for a range in a
column but I haven't been able to get it to work for a range in a
row. I tried to get rid of the transpose in various ways without any
luck. Below is the code that works for data in a column.

I think that it would also be neat if I could put something in the
code to determine if the stream of cash flows are in a column or in a
row. I was thinking that if I were able to determine the row using
Excel's row function of the beginning cell in the range and the the
row of the last cell in the range, I could create some logic to
determine if the cashflows are in a row or in a column.
---------------------------------------------------------------------------*---------------------------------------
Function pval_T(irate_T As Double, rngIn As Range) As Variant
Dim myArr As Variant, i As Long

myArr = Application.WorksheetFunction.Transpose(rngIn)

For i = LBound(myArr) To UBound(myArr)

pval_T = pval_T + myArr(i) / (1 + irate_T) ^ i

Next i

End Function
---------------------------------------------------------------------------*------------------------------------------
Suggestions are appreciated.- Hide quoted text -

- Show quoted text -

This is good but I need to be able to have the number of cells in the
range be variable and dependent on the range that is chosen by the
user. Is there a way to convert a range to an array? The above code
does so by using "Transpose." I can't get the code to work if I just
type "myArr = rngIn." I need to find a way to put the values of the
range "rngIn" into an array.

Array Problem
 

Does this code do what you want?

Function pval_T(irate_T As Double, rngIn As Range) As Variant
Dim i As Long
With rngIn
If rngIn.Rows.Count 1 Then
For i = .Row To .Rows.Count - 1
pval_T = pval_T + .Cells(i, 1) / (1 + irate_T) ^ i
Next i
Else
For i = .Column To .Columns.Count - 1
pval_T = pval_T + .Cells(1, i) / (1 + irate_T) ^ i
Next i
End If
End With
End Function

It should loop through the cells of the passed-in (column oriented or row
oriented) range automatically.

Rick


"Jonas" wrote in message
ps.com...
On Oct 17, 10:10 am, "RB Smissaert"
wrote:
Not sure you need the transpose and it is simple to see
from the resulting array what the range input was:

Sub test()

Dim arr

'row input range
arr = Range(Cells(1), Cells(10))
MsgBox UBound(arr), , UBound(arr, 2)

'column input range
arr = Range(Cells(1), Cells(10, 1))
MsgBox UBound(arr), , UBound(arr, 2)

End Sub

RBS

"Jonas" wrote in message

ups.com...



Hello,

I'm trying to create a function that will output the present value of
a stream of cash flows. I was able to get it to work for a range in a
column but I haven't been able to get it to work for a range in a
row. I tried to get rid of the transpose in various ways without any
luck. Below is the code that works for data in a column.

I think that it would also be neat if I could put something in the
code to determine if the stream of cash flows are in a column or in a
row. I was thinking that if I were able to determine the row using
Excel's row function of the beginning cell in the range and the the
row of the last cell in the range, I could create some logic to
determine if the cashflows are in a row or in a column.
---------------------------------------------------------------------------*---------------------------------------
Function pval_T(irate_T As Double, rngIn As Range) As Variant
Dim myArr As Variant, i As Long

myArr = Application.WorksheetFunction.Transpose(rngIn)

For i = LBound(myArr) To UBound(myArr)

pval_T = pval_T + myArr(i) / (1 + irate_T) ^ i

Next i

End Function
---------------------------------------------------------------------------*------------------------------------------
Suggestions are appreciated.- Hide quoted text -

- Show quoted text -

This is good but I need to be able to have the number of cells in the
range be variable and dependent on the range that is chosen by the
user. Is there a way to convert a range to an array? The above code
does so by using "Transpose." I can't get the code to work if I just
type "myArr = rngIn." I need to find a way to put the values of the
range "rngIn" into an array.

Array Problem
 

On Oct 17, 10:30 am, Jonas wrote:
On Oct 17, 10:10 am, "RB Smissaert"
wrote:





Not sure you need the transpose and it is simple to see
from the resulting array what the range input was:

Sub test()

Dim arr

'row input range
arr = Range(Cells(1), Cells(10))
MsgBox UBound(arr), , UBound(arr, 2)

'column input range
arr = Range(Cells(1), Cells(10, 1))
MsgBox UBound(arr), , UBound(arr, 2)

End Sub

RBS

"Jonas" wrote in message

oups.com...

Hello,

I'm trying to create a function that will output the present value of
a stream of cash flows. I was able to get it to work for a range in a
column but I haven't been able to get it to work for a range in a
row. I tried to get rid of the transpose in various ways without any
luck. Below is the code that works for data in a column.

I think that it would also be neat if I could put something in the
code to determine if the stream of cash flows are in a column or in a
row. I was thinking that if I were able to determine the row using
Excel's row function of the beginning cell in the range and the the
row of the last cell in the range, I could create some logic to
determine if the cashflows are in a row or in a column.
---------------------------------------------------------------------------**---------------------------------------
Function pval_T(irate_T As Double, rngIn As Range) As Variant
Dim myArr As Variant, i As Long

myArr = Application.WorksheetFunction.Transpose(rngIn)

For i = LBound(myArr) To UBound(myArr)

pval_T = pval_T + myArr(i) / (1 + irate_T) ^ i

Next i

End Function
---------------------------------------------------------------------------**------------------------------------------
Suggestions are appreciated.- Hide quoted text -

- Show quoted text -

This is good but I need to be able to have the number of cells in the
range be variable and dependent on the range that is chosen by the
user. Is there a way to convert a range to an array? The above code
does so by using "Transpose." I can't get the code to work if I just
type "myArr = rngIn." I need to find a way to put the values of the
range "rngIn" into an array.- Hide quoted text -

- Show quoted text -

I was able to change the code. The only thing that I am lacking now
is the ability to count the number of cells in "rngIn." Below is the
new code. I want to replace 3 with the number of cells in the range
"rngIn."

-------------------------------------------------------------------------------------------------------------------------
Function pval_T(irate_T As Double, rngIn As Variant) As Variant
Dim myArr As Variant, i As Long


For i = 1 To 3


pval_T = pval_T + rngIn(i) / (1 + irate_T) ^ (i)

Next i

End Function

Array Problem
 

I was able to change the code. The only thing that I am lacking
now is the ability to count the number of cells in "rngIn." Below
is the new code. I want to replace 3 with the number of cells in
the range "rngIn."

Did you try the code I posted earlier?

Rick

Array Problem
 

Rick Rothstein (MVP - VB) wrote:
Using your Range name, consider this structure...

If rngIn.Rows.Count 1 Then
' Multiple rows only
Else rngIn.Columns.Count 1 Then
' Multiple columns only
Else
' Multiple rows and columns
End If

Note the "s" on the end of the keyword Rows and Columns

Rick

It doesn't compile unless the first Else is changed to ElseIf; and then
it doesn't work; it shows a rectangular range (i.e., multiple rows and
columns) as multiple rows only.

Alan Beban

Array Problem
 

Jonas wrote:. . . .
I was able to change the code. The only thing that I am lacking now
is the ability to count the number of cells in "rngIn." Below is the
new code. I want to replace 3 with the number of cells in the range
"rngIn."

-------------------------------------------------------------------------------------------------------------------------
Function pval_T(irate_T As Double, rngIn As Variant) As Variant
Dim myArr As Variant, i As Long


For i = 1 To 3


pval_T = pval_T + rngIn(i) / (1 + irate_T) ^ (i)

Next i

End Function


rngIn.Count

Alan Beban

Array Problem
 

The Else instead of ElseIf was a typo. As for the other problem... good
catch! This should work...

If rngIn.Rows.Count 1 and rngIn.Columns.Count 1 Then
' Multiple rows and columns
Else rngIn.Columns.Count 1 Then
' Multiple columns only
Else
' Multiple rows only
End If

Rick


"Alan Beban" wrote in message
...
Rick Rothstein (MVP - VB) wrote:
Using your Range name, consider this structure...

If rngIn.Rows.Count 1 Then
' Multiple rows only
Else rngIn.Columns.Count 1 Then
' Multiple columns only
Else
' Multiple rows and columns
End If

Note the "s" on the end of the keyword Rows and Columns

Rick

It doesn't compile unless the first Else is changed to ElseIf; and then it
doesn't work; it shows a rectangular range (i.e., multiple rows and
columns) as multiple rows only.

Alan Beban

Array Problem
 

Alan Beban pointed out a problem with the "structure" I used in my first
posting in this thread (which I used in my subsequent coded postings here).
This is the modified code correcting for that structural problem...

Function pval_T(irate_T As Double, rngIn As Range) As Variant
Dim i As Long
With rngIn
If rngIn.Rows.Count 1 And rngIn.Columns.Count 1 Then
' Handle the Range-size error (multiple rows AND columns here)
Exit Sub
ElseIf rngIn.Rows.Count 1 Then
For i = .Row To .Rows.Count - 1
pval_T = pval_T + .Cells(i, 1) / (1 + irate_T) ^ i
Next i
Else
For i = .Column To .Columns.Count - 1
pval_T = pval_T + .Cells(1, i) / (1 + irate_T) ^ i
Next i
End If
End With
End Function

Rick


"Rick Rothstein (MVP - VB)" wrote in
message ...
I was able to change the code. The only thing that I am lacking
now is the ability to count the number of cells in "rngIn." Below
is the new code. I want to replace 3 with the number of cells in
the range "rngIn."

Did you try the code I posted earlier?

Rick

Array Problem
 

I may be wrong, but it appears to work if you pass the range either
vertically, or horizontally,

Sub Test()
Debug.Print WorksheetFunction.NPV(0.05, [A1:A3])
Debug.Print WorksheetFunction.NPV(0.05, [C1:E1])
End Sub

--
Dana DeLouis



"Jonas" wrote in message
ups.com...
Hello,

I'm trying to create a function that will output the present value of
a stream of cash flows. I was able to get it to work for a range in a
column but I haven't been able to get it to work for a range in a
row. I tried to get rid of the transpose in various ways without any
luck. Below is the code that works for data in a column.

I think that it would also be neat if I could put something in the
code to determine if the stream of cash flows are in a column or in a
row. I was thinking that if I were able to determine the row using
Excel's row function of the beginning cell in the range and the the
row of the last cell in the range, I could create some logic to
determine if the cashflows are in a row or in a column.
------------------------------------------------------------------------------------------------------------------
Function pval_T(irate_T As Double, rngIn As Range) As Variant
Dim myArr As Variant, i As Long

myArr = Application.WorksheetFunction.Transpose(rngIn)

For i = LBound(myArr) To UBound(myArr)

pval_T = pval_T + myArr(i) / (1 + irate_T) ^ i

Next i

End Function
---------------------------------------------------------------------------------------------------------------------
Suggestions are appreciated.

Array Problem
 

I guess the 'Exit Sub' is a typo <g

Apart from that, though I don't know what the function is supposed to do,
those For..To loops don't look right.

With rngIn
For i = .Row To .Rows.Count - 1

..Row returns row number of first row in rngIn, relative to topleft cell ?
presumably should loop from 1 to .Rows.Count

the i in .Cells(i,1) refers to row number relative to rngIn

If I (partially) follow, need to establish the input range is one row or
column, if it is could simply loop each cell in rngIn, eg

Function pval_Tb(irate_T As Double, rngIn As Range)
Dim i As Long
Dim dblTmp As Double
Dim cell As Range
On Error GoTo errExit
If rngIn.Rows.Count 2 And rngIn.Columns.Count 1 Then
pval_Tb = CVErr(xlErrRef)
Else
For Each cell In rngIn
i = i + 1 ' ???
dblTmp = dblTmp + cell.Value / (1 + irate_T) ^ i
Next
pval_Tb = dblTmp
End If
Exit Function
errExit:

pval_Tb = CVErr(xlValue)
End Function

I have no idea if the i = i + 1 is correct for the calculation, but I
imagine simple to adapt as necessary.

BTW is rngIn.Count = 1, a single cell, OK

Regards,
Peter T


PS to Jonas, in your OP you have

For i = LBound(myArr) To UBound(myArr)
pval_T = pval_T + myArr(i) / (1 + irate_T) ^ i

but myArr(i) will be a 2D array, even if only one row or column, so would
need
myArr(i, 1) or myArr(1, i) depending if vertical or horizontal

I looked at that trying to work out what the i should be, I assume it starts
at one ?








"Rick Rothstein (MVP - VB)" wrote in
message ...
Alan Beban pointed out a problem with the "structure" I used in my first
posting in this thread (which I used in my subsequent coded postings
here).
This is the modified code correcting for that structural problem...

Function pval_T(irate_T As Double, rngIn As Range) As Variant
Dim i As Long
With rngIn
If rngIn.Rows.Count 1 And rngIn.Columns.Count 1 Then
' Handle the Range-size error (multiple rows AND columns here)
Exit Sub
ElseIf rngIn.Rows.Count 1 Then
For i = .Row To .Rows.Count - 1
pval_T = pval_T + .Cells(i, 1) / (1 + irate_T) ^ i
Next i
Else
For i = .Column To .Columns.Count - 1
pval_T = pval_T + .Cells(1, i) / (1 + irate_T) ^ i
Next i
End If
End With
End Function

Rick


"Rick Rothstein (MVP - VB)" wrote in
message ...
I was able to change the code. The only thing that I am lacking
now is the ability to count the number of cells in "rngIn." Below
is the new code. I want to replace 3 with the number of cells in
the range "rngIn."

Did you try the code I posted earlier?

Rick

Array Problem
 

Not sure what is wrong with the code I suggested.
In simple words:
1. Assign the range to variant array
(this is the fastest way in any case, better than looping through the range)
2. Determine from the Ubound of the array if we have a row or a column
3. Loop through the array and do whatever needs doing.

RBS


"Peter T" <peter_t@discussions wrote in message
...
I guess the 'Exit Sub' is a typo <g

Apart from that, though I don't know what the function is supposed to do,
those For..To loops don't look right.

With rngIn
For i = .Row To .Rows.Count - 1

.Row returns row number of first row in rngIn, relative to topleft cell ?
presumably should loop from 1 to .Rows.Count

the i in .Cells(i,1) refers to row number relative to rngIn

If I (partially) follow, need to establish the input range is one row or
column, if it is could simply loop each cell in rngIn, eg

Function pval_Tb(irate_T As Double, rngIn As Range)
Dim i As Long
Dim dblTmp As Double
Dim cell As Range
On Error GoTo errExit
If rngIn.Rows.Count 2 And rngIn.Columns.Count 1 Then
pval_Tb = CVErr(xlErrRef)
Else
For Each cell In rngIn
i = i + 1 ' ???
dblTmp = dblTmp + cell.Value / (1 + irate_T) ^ i
Next
pval_Tb = dblTmp
End If
Exit Function
errExit:

pval_Tb = CVErr(xlValue)
End Function

I have no idea if the i = i + 1 is correct for the calculation, but I
imagine simple to adapt as necessary.

BTW is rngIn.Count = 1, a single cell, OK

Regards,
Peter T


PS to Jonas, in your OP you have

For i = LBound(myArr) To UBound(myArr)
pval_T = pval_T + myArr(i) / (1 + irate_T) ^ i

but myArr(i) will be a 2D array, even if only one row or column, so would
need
myArr(i, 1) or myArr(1, i) depending if vertical or horizontal

I looked at that trying to work out what the i should be, I assume it
starts
at one ?








"Rick Rothstein (MVP - VB)" wrote in
message ...
Alan Beban pointed out a problem with the "structure" I used in my first
posting in this thread (which I used in my subsequent coded postings
here).
This is the modified code correcting for that structural problem...

Function pval_T(irate_T As Double, rngIn As Range) As Variant
Dim i As Long
With rngIn
If rngIn.Rows.Count 1 And rngIn.Columns.Count 1 Then
' Handle the Range-size error (multiple rows AND columns here)
Exit Sub
ElseIf rngIn.Rows.Count 1 Then
For i = .Row To .Rows.Count - 1
pval_T = pval_T + .Cells(i, 1) / (1 + irate_T) ^ i
Next i
Else
For i = .Column To .Columns.Count - 1
pval_T = pval_T + .Cells(1, i) / (1 + irate_T) ^ i
Next i
End If
End With
End Function

Rick


"Rick Rothstein (MVP - VB)" wrote in
message ...
I was able to change the code. The only thing that I am lacking
now is the ability to count the number of cells in "rngIn." Below
is the new code. I want to replace 3 with the number of cells in
the range "rngIn."

Did you try the code I posted earlier?

Rick

Array Problem
 

No, the Exit Sub was there on purpose. The Op seemed to indicate that only a
row of cells or a column of cells were a valid range, so if the count of the
rows and columns in the range are both greater than 1, I'm assuming the
range is not a valid one for the OP's purposes; hence the comment to "handle
the error" and, once it has been handled, exit the routine. As for the
For..To loops... they will either process all the cells in a range
consisting of cells in a column or a row (depending on the tested count
property). The row or column number of the first cell in a range of cells is
returned by the Row or Column property of the Range; hence the starting
value in the For..To statement. However, I did leave something out in the
ending value for the For..To loop... I forgot to add the starting cell's row
or column into the value I posted. Here is what the code should have been...

Function pval_T(irate_T As Double, rngIn As Range) As Variant
Dim i As Long
With rngIn
If rngIn.Rows.Count 1 And rngIn.Columns.Count 1 Then
' Handle the Range-size error (multiple rows AND columns here)
Exit Sub
ElseIf rngIn.Rows.Count 1 Then
For i = .Row To .Row + .Rows.Count - 1
pval_T = pval_T + .Cells(i, .Column).Value / (1 + irate_T) ^ i
Next i
Else
For i = .Column To .Column + .Columns.Count - 1
pval_T = pval_T + .Cells(.Row, i).Value/ (1 + irate_T) ^ i
Next i
End If
End With
End Function

where the ending value for the For..To loop is calculated by adding the
starting row or column to the row or column cell count and subtracting 1. In
addition to **that** mistake, I had also screwed up the column number
reference inside the Cells property (correct in the above code). So, yes,
you were right... my For..To loops didn't "look right". Thanks for noting
that so I could correct my code.

Now, in thinking about your post, yes, I don't see why a For..Each loop
can't be used instead of a "counting" For-Next loop that I used (I kind of
got "mentally trapped" by the OP's attempt to use an array). As a matter of
fact, I think it is the better way to go. Just one note on your posted code
though; you have this statement....

If rngIn.Rows.Count 2 And rngIn.Columns.Count 1

I think the '2' should be a '1'.

Rick


"Peter T" <peter_t@discussions wrote in message
...
I guess the 'Exit Sub' is a typo <g

Apart from that, though I don't know what the function is supposed to do,
those For..To loops don't look right.

With rngIn
For i = .Row To .Rows.Count - 1

.Row returns row number of first row in rngIn, relative to topleft cell ?
presumably should loop from 1 to .Rows.Count

the i in .Cells(i,1) refers to row number relative to rngIn

If I (partially) follow, need to establish the input range is one row or
column, if it is could simply loop each cell in rngIn, eg

Function pval_Tb(irate_T As Double, rngIn As Range)
Dim i As Long
Dim dblTmp As Double
Dim cell As Range
On Error GoTo errExit
If rngIn.Rows.Count 2 And rngIn.Columns.Count 1 Then
pval_Tb = CVErr(xlErrRef)
Else
For Each cell In rngIn
i = i + 1 ' ???
dblTmp = dblTmp + cell.Value / (1 + irate_T) ^ i
Next
pval_Tb = dblTmp
End If
Exit Function
errExit:

pval_Tb = CVErr(xlValue)
End Function

I have no idea if the i = i + 1 is correct for the calculation, but I
imagine simple to adapt as necessary.

BTW is rngIn.Count = 1, a single cell, OK

Regards,
Peter T


PS to Jonas, in your OP you have

For i = LBound(myArr) To UBound(myArr)
pval_T = pval_T + myArr(i) / (1 + irate_T) ^ i

but myArr(i) will be a 2D array, even if only one row or column, so would
need
myArr(i, 1) or myArr(1, i) depending if vertical or horizontal

I looked at that trying to work out what the i should be, I assume it
starts
at one ?








"Rick Rothstein (MVP - VB)" wrote in
message ...
Alan Beban pointed out a problem with the "structure" I used in my first
posting in this thread (which I used in my subsequent coded postings
here).
This is the modified code correcting for that structural problem...

Function pval_T(irate_T As Double, rngIn As Range) As Variant
Dim i As Long
With rngIn
If rngIn.Rows.Count 1 And rngIn.Columns.Count 1 Then
' Handle the Range-size error (multiple rows AND columns here)
Exit Sub
ElseIf rngIn.Rows.Count 1 Then
For i = .Row To .Rows.Count - 1
pval_T = pval_T + .Cells(i, 1) / (1 + irate_T) ^ i
Next i
Else
For i = .Column To .Columns.Count - 1
pval_T = pval_T + .Cells(1, i) / (1 + irate_T) ^ i
Next i
End If
End With
End Function

Rick


"Rick Rothstein (MVP - VB)" wrote in
message ...
I was able to change the code. The only thing that I am lacking
now is the ability to count the number of cells in "rngIn." Below
is the new code. I want to replace 3 with the number of cells in
the range "rngIn."

Did you try the code I posted earlier?

Rick

Array Problem
 

On Oct 17, 10:30 am, Jonas wrote:
On Oct 17, 10:10 am, "RB Smissaert"
wrote:





Not sure you need the transpose and it is simple to see
from the resulting array what the range input was:

Sub test()

Dim arr

'row input range
arr = Range(Cells(1), Cells(10))
MsgBox UBound(arr), , UBound(arr, 2)

'column input range
arr = Range(Cells(1), Cells(10, 1))
MsgBox UBound(arr), , UBound(arr, 2)

End Sub

RBS

"Jonas" wrote in message

oups.com...

Hello,

I'm trying to create a function that will output the present value of
a stream of cash flows. I was able to get it to work for a range in a
column but I haven't been able to get it to work for a range in a
row. I tried to get rid of the transpose in various ways without any
luck. Below is the code that works for data in a column.

I think that it would also be neat if I could put something in the
code to determine if the stream of cash flows are in a column or in a
row. I was thinking that if I were able to determine the row using
Excel's row function of the beginning cell in the range and the the
row of the last cell in the range, I could create some logic to
determine if the cashflows are in a row or in a column.
---------------------------------------------------------------------------**---------------------------------------
Function pval_T(irate_T As Double, rngIn As Range) As Variant
Dim myArr As Variant, i As Long

myArr = Application.WorksheetFunction.Transpose(rngIn)

For i = LBound(myArr) To UBound(myArr)

pval_T = pval_T + myArr(i) / (1 + irate_T) ^ i

Next i

End Function
---------------------------------------------------------------------------**------------------------------------------
Suggestions are appreciated.- Hide quoted text -

- Show quoted text -

This is good but I need to be able to have the number of cells in the
range be variable and dependent on the range that is chosen by the
user. Is there a way to convert a range to an array? The above code
does so by using "Transpose." I can't get the code to work if I just
type "myArr = rngIn." I need to find a way to put the values of the
range "rngIn" into an array.- Hide quoted text -

- Show quoted text -

I was able redo the code(below). All I need now is a way to count the
number of cells in the range "rngIn" rather than use the static number
3 as is shown below.

-------------------------------------------------------------------------------------------------------------------------

Function pval_T(irate_T As Double, rngIn As Variant) As Variant
Dim myArr As Variant, i As Long


For i = 1 To 3

pval_T = pval_T + rngIn(i) / (1 + irate_T) ^ (i)

Next i

End Function

Array Problem
 

In line -

I haven't studied the whole thread, so excuses and apologies in advance if
I'm out of order or plain wrong -

No, the Exit Sub was there on purpose.

Can't have Exit Sub in a Function, surely, what am I missing

The Op seemed to indicate that only a
row of cells or a column of cells were a valid range, so if the count of
the
rows and columns in the range are both greater than 1, I'm assuming the
range is not a valid one for the OP's purposes; hence the comment to
"handle
the error" and, once it has been handled, exit the routine. As for the
For..To loops... they will either process all the cells in a range
consisting of cells in a column or a row (depending on the tested count
property).

The row or column number of the first cell in a range of cells is
returned by the Row or Column property of the Range;

Agreed, but...

hence the starting
value in the For..To statement.

That would be fine if you went on to loop say ws.Cells(i, 1)
But you are looping cells qualified With rngIn

Set rngIn = Range("B10:B16")
With rngIn
Debug.Print .Row ' 10
Debug.Print .Cells(.Row, 1).Row ' 19 wrong
Debug.Print Cells(.Row, 1).Row ' 10 right
End With

similar impacts on the following

However, I did leave something out in the
ending value for the For..To loop... I forgot to add the starting cell's
row
or column into the value I posted. Here is what the code should have
been...

Function pval_T(irate_T As Double, rngIn As Range) As Variant
Dim i As Long
With rngIn
If rngIn.Rows.Count 1 And rngIn.Columns.Count 1 Then
' Handle the Range-size error (multiple rows AND columns here)
Exit Sub
ElseIf rngIn.Rows.Count 1 Then
For i = .Row To .Row + .Rows.Count - 1
pval_T = pval_T + .Cells(i, .Column).Value / (1 + irate_T) ^ i
Next i
Else
For i = .Column To .Column + .Columns.Count - 1
pval_T = pval_T + .Cells(.Row, i).Value/ (1 + irate_T) ^ i
Next i
End If
End With
End Function

where the ending value for the For..To loop is calculated by adding the
starting row or column to the row or column cell count and subtracting 1.
In
addition to **that** mistake, I had also screwed up the column number
reference inside the Cells property (correct in the above code). So, yes,
you were right... my For..To loops didn't "look right". Thanks for noting
that so I could correct my code.

Now, in thinking about your post, yes, I don't see why a For..Each loop
can't be used instead of a "counting" For-Next loop that I used (I kind of
got "mentally trapped" by the OP's attempt to use an array). As a matter
of
fact, I think it is the better way to go. Just one note on your posted
code
though; you have this statement....


If rngIn.Rows.Count 2 And rngIn.Columns.Count 1

I think the '2' should be a '1'.

Ooops, it was indeed a typo, thanks for correcting

FWIW I agree with RBS's comment that it's normally more efficient to assign
the range values to an array and work with that.

Regards,
Peter T

Array Problem
 

On Oct 17, 4:30 pm, "Peter T" <peter_t@discussions wrote:
In line -

I haven't studied the whole thread, so excuses and apologies in advance if
I'm out of order or plain wrong -

No, the Exit Sub was there on purpose.

Can't have Exit Sub in a Function, surely, what am I missing



The Op seemed to indicate that only a
row of cells or a column of cells were a valid range, so if the count of
the
rows and columns in the range are both greater than 1, I'm assuming the
range is not a valid one for the OP's purposes; hence the comment to
"handle
the error" and, once it has been handled, exit the routine. As for the
For..To loops... they will either process all the cells in a range
consisting of cells in a column or a row (depending on the tested count
property).
The row or column number of the first cell in a range of cells is
returned by the Row or Column property of the Range;

Agreed, but...

hence the starting
value in the For..To statement.

That would be fine if you went on to loop say ws.Cells(i, 1)
But you are looping cells qualified With rngIn

Set rngIn = Range("B10:B16")
With rngIn
Debug.Print .Row ' 10
Debug.Print .Cells(.Row, 1).Row ' 19 wrong
Debug.Print Cells(.Row, 1).Row ' 10 right
End With

similar impacts on the following







However, I did leave something out in the
ending value for the For..To loop... I forgot to add the starting cell's
row
or column into the value I posted. Here is what the code should have
been...

Function pval_T(irate_T As Double, rngIn As Range) As Variant
Dim i As Long
With rngIn
If rngIn.Rows.Count 1 And rngIn.Columns.Count 1 Then
' Handle the Range-size error (multiple rows AND columns here)
Exit Sub
ElseIf rngIn.Rows.Count 1 Then
For i = .Row To .Row + .Rows.Count - 1
pval_T = pval_T + .Cells(i, .Column).Value / (1 + irate_T) ^ i
Next i
Else
For i = .Column To .Column + .Columns.Count - 1
pval_T = pval_T + .Cells(.Row, i).Value/ (1 + irate_T) ^ i
Next i
End If
End With
End Function

where the ending value for the For..To loop is calculated by adding the
starting row or column to the row or column cell count and subtracting 1.
In
addition to **that** mistake, I had also screwed up the column number
reference inside the Cells property (correct in the above code). So, yes,
you were right... my For..To loops didn't "look right". Thanks for noting
that so I could correct my code.

Now, in thinking about your post, yes, I don't see why a For..Each loop
can't be used instead of a "counting" For-Next loop that I used (I kind of
got "mentally trapped" by the OP's attempt to use an array). As a matter
of
fact, I think it is the better way to go. Just one note on your posted
code
though; you have this statement....

If rngIn.Rows.Count 2 And rngIn.Columns.Count 1

I think the '2' should be a '1'.

Ooops, it was indeed a typo, thanks for correcting

FWIW I agree with RBS's comment that it's normally more efficient to assign
the range values to an array and work with that.

Regards,
Peter T- Hide quoted text -

- Show quoted text -

Thank you all for responding to my post. I wasn't able to read them
earlier and didn't seem to be able to post anything either. I have
copied my solution below in case anybody wants to look at it. I'm
going to look at your posts tomorrow. I'm sure that there is some
good stuff in them; I have a lot to learn. I addition to what I have
below, I think that I will create a section of code that checks for
text in the range.

-------------------------------------------------------------------------------------------------------------------------
Function PvalCF_Tyrone(irate_T As Double, rngInty As Range) As Variant
''''''calculates the present value of a range of cash flows'''

Set myArr = rngInty

For Each Mycell In rngInty

counter = counter + 1

PvalCF_Tyrone = PvalCF_Tyrone + Mycell.Value / (1 + irate_T) ^ counter

Next Mycell
End Function

Array Problem
 

Yep! I see what you meant now. Wow! I sure seem to have screwed this up
completely, didn't I? I didn't get a lot of sleep the night before, but I
thought I was more awake than my answer shows me to have been. Thanks for
picking up on all the problems in my response.

Rick


"Peter T" <peter_t@discussions wrote in message
...
In line -

I haven't studied the whole thread, so excuses and apologies in advance if
I'm out of order or plain wrong -

No, the Exit Sub was there on purpose.

Can't have Exit Sub in a Function, surely, what am I missing

The Op seemed to indicate that only a
row of cells or a column of cells were a valid range, so if the count of
the
rows and columns in the range are both greater than 1, I'm assuming the
range is not a valid one for the OP's purposes; hence the comment to
"handle
the error" and, once it has been handled, exit the routine. As for the
For..To loops... they will either process all the cells in a range
consisting of cells in a column or a row (depending on the tested count
property).

The row or column number of the first cell in a range of cells is
returned by the Row or Column property of the Range;

Agreed, but...

hence the starting
value in the For..To statement.

That would be fine if you went on to loop say ws.Cells(i, 1)
But you are looping cells qualified With rngIn

Set rngIn = Range("B10:B16")
With rngIn
Debug.Print .Row ' 10
Debug.Print .Cells(.Row, 1).Row ' 19 wrong
Debug.Print Cells(.Row, 1).Row ' 10 right
End With

similar impacts on the following

However, I did leave something out in the
ending value for the For..To loop... I forgot to add the starting cell's
row
or column into the value I posted. Here is what the code should have
been...

Function pval_T(irate_T As Double, rngIn As Range) As Variant
Dim i As Long
With rngIn
If rngIn.Rows.Count 1 And rngIn.Columns.Count 1 Then
' Handle the Range-size error (multiple rows AND columns here)
Exit Sub
ElseIf rngIn.Rows.Count 1 Then
For i = .Row To .Row + .Rows.Count - 1
pval_T = pval_T + .Cells(i, .Column).Value / (1 + irate_T) ^ i
Next i
Else
For i = .Column To .Column + .Columns.Count - 1
pval_T = pval_T + .Cells(.Row, i).Value/ (1 + irate_T) ^ i
Next i
End If
End With
End Function

where the ending value for the For..To loop is calculated by adding the
starting row or column to the row or column cell count and subtracting 1.
In
addition to **that** mistake, I had also screwed up the column number
reference inside the Cells property (correct in the above code). So, yes,
you were right... my For..To loops didn't "look right". Thanks for noting
that so I could correct my code.

Now, in thinking about your post, yes, I don't see why a For..Each loop
can't be used instead of a "counting" For-Next loop that I used (I kind
of
got "mentally trapped" by the OP's attempt to use an array). As a matter
of
fact, I think it is the better way to go. Just one note on your posted
code
though; you have this statement....


If rngIn.Rows.Count 2 And rngIn.Columns.Count 1

I think the '2' should be a '1'.

Ooops, it was indeed a typo, thanks for correcting

FWIW I agree with RBS's comment that it's normally more efficient to
assign
the range values to an array and work with that.

Regards,
Peter T

Array Problem
 

Thank you all for responding to my post. I wasn't able to read them
earlier and didn't seem to be able to post anything either. I have
copied my solution below in case anybody wants to look at it. I'm
going to look at your posts tomorrow. I'm sure that there is some
good stuff in them; I have a lot to learn. I addition to what I have
below, I think that I will create a section of code that checks for
text in the range.


Function PvalCF_Tyrone(irate_T As Double, rngInty As Range) As Variant
''''''calculates the present value of a range of cash flows'''

Set myArr = rngInty

For Each Mycell In rngInty

counter = counter + 1

PvalCF_Tyrone = PvalCF_Tyrone + Mycell.Value / (1 + irate_T) ^ counter

Next Mycell
End Function

That looks OK providing you don't need to establish rngInty is a single row
or column, or to cater for other potential input errors. Otherwise look at
Function pval_Tb() as posted previously with the correction noted by Rick -
If rngIn.Rows.Count 2 And rngIn.Columns.Count 1
the '2' should be a '1'.

If you want to stick with what you've got at the very least I'd declare your
variables
Dim counter as Long
Dim Mycell as Range
The function might as well return a Double instead of 'As Variant'
Personally I prefer to assign the result to the function when done so
perhaps -
Dim dblTmp
in the loop: dblTmp = dblTmp etc
when done: PvalCF_Tyrone = dblTmp

If calculation speed is important, if say the input range is a large or you
are using the function extensively, go with RBS's suggestion to assign the
range values to an array -

vArr = rngInty
for each v in vArr

Concerning checking for any text in the range, if that's effectively a
faulty input the error handler in the function I posted will cater for that.
However if you want to differentiate between text cells and other cells

if Typename(Mycell.value) = vbString Then' or TypeName(v)
' it's text
Else
'it's not text, probably a number but could be an error value
End If

Regards,
Peter T

Array Problem
 

vArr = rngInty
for each v in vArr

I think that for arrays it is faster to loop with For Next

RBS

"Peter T" <peter_t@discussions wrote in message
...
Thank you all for responding to my post. I wasn't able to read them
earlier and didn't seem to be able to post anything either. I have
copied my solution below in case anybody wants to look at it. I'm
going to look at your posts tomorrow. I'm sure that there is some
good stuff in them; I have a lot to learn. I addition to what I have
below, I think that I will create a section of code that checks for
text in the range.


Function PvalCF_Tyrone(irate_T As Double, rngInty As Range) As Variant
''''''calculates the present value of a range of cash flows'''

Set myArr = rngInty

For Each Mycell In rngInty

counter = counter + 1

PvalCF_Tyrone = PvalCF_Tyrone + Mycell.Value / (1 + irate_T) ^ counter

Next Mycell
End Function

That looks OK providing you don't need to establish rngInty is a single
row
or column, or to cater for other potential input errors. Otherwise look at
Function pval_Tb() as posted previously with the correction noted by
Rick -
If rngIn.Rows.Count 2 And rngIn.Columns.Count 1
the '2' should be a '1'.

If you want to stick with what you've got at the very least I'd declare
your
variables
Dim counter as Long
Dim Mycell as Range
The function might as well return a Double instead of 'As Variant'
Personally I prefer to assign the result to the function when done so
perhaps -
Dim dblTmp
in the loop: dblTmp = dblTmp etc
when done: PvalCF_Tyrone = dblTmp

If calculation speed is important, if say the input range is a large or
you
are using the function extensively, go with RBS's suggestion to assign the
range values to an array -

vArr = rngInty
for each v in vArr

Concerning checking for any text in the range, if that's effectively a
faulty input the error handler in the function I posted will cater for
that.
However if you want to differentiate between text cells and other cells

if Typename(Mycell.value) = vbString Then' or TypeName(v)
' it's text
Else
'it's not text, probably a number but could be an error value
End If

Regards,
Peter T

Array Problem
 

Hi Bart,

I had always thought so too but, as I really didn't want to have to go into
detail (again!) about the array being 2D and which dimension to loop
depending on whether it was horizontal or vertical, I thought I'd leave it
simple after doing a quick test.

If anything I found it slightly faster to loop the variant array with For
Each vs For Next.

Regards,
Peter T

"RB Smissaert" wrote in message
...
vArr = rngInty
for each v in vArr

I think that for arrays it is faster to loop with For Next

RBS

<snip
If calculation speed is important, if say the input range is a large or
you
are using the function extensively, go with RBS's suggestion to assign
the
range values to an array -

vArr = rngInty
for each v in vArr

Array Problem
 

Hi Peter,

OK, thanks for clearing that up.
Will do some testing later to see what exactly is the case.

RBS


"Peter T" <peter_t@discussions wrote in message
...
Hi Bart,

I had always thought so too but, as I really didn't want to have to go
into
detail (again!) about the array being 2D and which dimension to loop
depending on whether it was horizontal or vertical, I thought I'd leave it
simple after doing a quick test.

If anything I found it slightly faster to loop the variant array with For
Each vs For Next.

Regards,
Peter T

"RB Smissaert" wrote in message
...
vArr = rngInty
for each v in vArr

I think that for arrays it is faster to loop with For Next

RBS

<snip
If calculation speed is important, if say the input range is a large or
you
are using the function extensively, go with RBS's suggestion to assign
the
range values to an array -

vArr = rngInty
for each v in vArr

Array Problem
 

Not sure where I got it from but I always thought that for arrays For Next
was faster
than For Each, but it looks this is not true:

Option Explicit
Private lStartTime As Long
Private Declare Function timeGetTime Lib "winmm.dll" () As Long

Sub StartSW()
lStartTime = timeGetTime()
End Sub

Function StopSW(Optional bMsgBox As Boolean = True, _
Optional vMessage As Variant, _
Optional lMinimumTimeToShow As Long = -1) As Variant

Dim lTime As Long

lTime = timeGetTime() - lStartTime

If lTime lMinimumTimeToShow Then
If IsMissing(vMessage) Then
StopSW = lTime
Else
StopSW = lTime & " - " & vMessage
End If
End If

If bMsgBox Then
If lTime lMinimumTimeToShow Then
MsgBox "Done in " & lTime & " msecs", , vMessage
End If
End If

End Function

Sub test()

Dim r As Long
Dim c As Long
Dim v
Dim arr
Dim arrLong(1 To 10000, 1 To 255) As Long
Dim vResult

arr = Range(Cells(1), Cells(10000, 255))
arrLong(1, 1) = 1
arrLong(1, 2) = 2

StartSW
For Each v In arr
vResult = vResult + v
Next v
StopSW , "For Each Variant array"

StartSW
For r = 1 To 10000
For c = 1 To 255
vResult = vResult + arr(r, c)
Next c
Next r
StopSW , "For Next Variant array"

StartSW
For Each v In arrLong
vResult = vResult + v
Next v
StopSW , "For Each Long array"

StartSW
For r = 1 To 10000
For c = 1 To 255
vResult = vResult + arrLong(r, c)
Next c
Next r
StopSW , "For Next Long array"

End Sub


RBS



"Peter T" <peter_t@discussions wrote in message
...
Hi Bart,

I had always thought so too but, as I really didn't want to have to go
into
detail (again!) about the array being 2D and which dimension to loop
depending on whether it was horizontal or vertical, I thought I'd leave it
simple after doing a quick test.

If anything I found it slightly faster to loop the variant array with For
Each vs For Next.

Regards,
Peter T

"RB Smissaert" wrote in message
...
vArr = rngInty
for each v in vArr

I think that for arrays it is faster to loop with For Next

RBS

<snip
If calculation speed is important, if say the input range is a large or
you
are using the function extensively, go with RBS's suggestion to assign
the
range values to an array -

vArr = rngInty
for each v in vArr

Array Problem
 

RB Smissaert wrote:
Not sure where I got it from but I always thought that for arrays For
Next was faster
than For Each, but it looks this is not true:

Could you summarize the results?

Alan Beban

Array Problem
 

For variant arrays in the test as in the posted code the For Each is nearly
twice as fast.
For Long arrays it is about 20% faster.

RBS


"Alan Beban" wrote in message
...
RB Smissaert wrote:
Not sure where I got it from but I always thought that for arrays For
Next was faster
than For Each, but it looks this is not true:

Could you summarize the results?

Alan Beban

Array Problem
 

RB Smissaert wrote:
For variant arrays in the test as in the posted code the For Each is
nearly twice as fast.
For Long arrays it is about 20% faster.

RBS

Thanks.

Alan Beban

Array Problem
 

Not sure where I got it from but I always thought that for arrays For Next
was faster
than For Each, but it looks this is not true:

According to this link, For...Next is faster.

http://msdn2.microsoft.com/en-us/lib...ffice.10).aspx

Scroll down to the following table (about 2/3 of the way down)....

Table 15.4. Summary of the Results of the VBA Performance Tests

and look up 21 in the first (Test) column. You can look further down to see
the actual test code and comments in the section headed by...

Test 21: in arrays, For . . . Next is faster than For Each . . . Next

Rick

Array Problem
 

According to this link, For...Next is faster.

http://msdn2.microsoft.com/en-us/lib...ffice.10).aspx

I should have mentioned, this link is for VBA in Access, but one would think
the results should apply throughout the Office products.

Rick

Array Problem
 

Yes, it says there, but I see opposite in my test code.
It doesn't say how it was tested and that may make a difference. I have only
tested in Excel VBA.

RBS



"Rick Rothstein (MVP - VB)" wrote in
message ...
Not sure where I got it from but I always thought that for arrays For
Next was faster
than For Each, but it looks this is not true:

According to this link, For...Next is faster.

http://msdn2.microsoft.com/en-us/lib...ffice.10).aspx

Scroll down to the following table (about 2/3 of the way down)....

Table 15.4. Summary of the Results of the VBA Performance Tests

and look up 21 in the first (Test) column. You can look further down to
see the actual test code and comments in the section headed by...

Test 21: in arrays, For . . . Next is faster than For Each . . . Next

Rick

Array Problem
 

Hi Bart,

My earlier tests compare with yours (see below), though I hadn't tested the
Long array as can't assign that to the input range in a UDF. The tests were
geared towards for use in a UDF.

'Relative' time differences will vary according to the inner loop (input
range size) and outer loop (contrived to increase time), and decrease with
the more other work that's going on.

The tests demonstrate it's significantly faster to assign range values to a
variant array (already well known) and somewhat faster to loop the array
with For..Each vs For...To..Next. However the overall difference might not
be noticeable in practice and there could be other good reasons to loop
For..To..Next.

Simple to implement, just run Setup. The timer is a bit crude but the
overhead is irrelevant here.

Option Explicit
Public Declare Function GetTickCount Lib "kernel32" () As Long
Sub Setup()

Range("C1:C5").Value = Application.Transpose(Array(0, 1, 2, 3, 4))
Range("A1:A1000").Value = 123.456
Range("E1:E5").Formula = "=foo($A$1:$A$1000,C1)"

' entering above formula triggers a triple re-calc

' manually change A1 or do a recalc, see results in cells

End Sub

Function foo(rng As Range, d As Long)
' returns calculation time (ms) & description of loop method
Dim arr
Dim i As Long, a As Long, t As Long
Dim dbl As Double
Dim s As String
Dim v
Dim cell As Range

On Error GoTo errH
If rng.Count = 1 Or (rng.Rows.Count 1 And _
rng.Columns.Count 1) Then
foo = CVErr(xlErrRef)
Exit Function
End If

t = GetTickCount

For a = 1 To 100 ' change this
If d < 2 Then
arr = rng
If d = 0 Then
For Each v In arr
dbl = dbl + v
Next
ElseIf d = 1 Then
For i = 1 To UBound(arr)
dbl = dbl + arr(i, 1)
Next
End If
End If
If d = 2 Then
For Each cell In rng
dbl = dbl + cell.Value
Next
ElseIf d = 3 Then
For i = 1 To rng.Rows.Count
dbl = dbl + rng(i, 1).Value
Next

ElseIf d = 4 Then
For i = 1 To rng.Rows.Count
dbl = dbl + rng.Rows(i)(1).Value
Next
End If

Next
'foo = dbl '

t = GetTickCount - t

If d = 0 Then
s = " For...Each variant"
ElseIf d = 1 Then
s = " For...To variant.count"
ElseIf d = 2 Then
s = " For...Each cell in range"
ElseIf d = 3 Then
s = " For...To rng.count, rng(i, 1)"
ElseIf d = 4 Then
s = " For...To rng.count, rng.Rows(i)(1)"
End If

foo = t & s
Debug.Print t & s, , Application.Caller.Address(0, 0)
Exit Function
errH:

foo = CVErr(xlErrValue)
End Function

Regards,
Peter T

"RB Smissaert" wrote in message
...
Not sure where I got it from but I always thought that for arrays For Next
was faster
than For Each, but it looks this is not true:

<snip