Array Problem

[Peter T]

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

 

[RB Smissaert]

vArr = rngInty

for each v in vArr

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

RBS

 

[Peter T]

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]

Hi Peter,

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

RBS

 

[RB Smissaert]

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

 

[Alan Beban]

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

 

[RB Smissaert]

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]

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

 

[Rick Rothstein \(MVP - VB\)]

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/library/aa188211(office.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

 

[Rick Rothstein \(MVP - VB\)]

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

http://msdn2.microsoft.com/en-us/library/aa188211(office.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

 

[RB Smissaert]

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

 

[Peter T]

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

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>