I used this:

Private Function Average(Arr As Variant) As Double
not
Private Function Average(Arr() As Long) As Double

Are


Joel wrote:

Dave: I made this modification and it is failing on the conversion of a Long
to a double. The same thing happens if you use cdbl(LSum).

Private Function Average(Arr() As Long) As Double
Dim LSum As Long
Dim DSum As Double

LSum = Sum(Arr)
DSum = LSum
Average = Sum(Arr) / Count(Arr)

End Function

"Dave Peterson" wrote:

I had the same problems (xl2003).

I have no idea why it causes the intermittent errors, but if I passed variants,
I didn't get those errors.

Option Explicit
Option Base 1
Private Function Sum(Arr As Variant) As Long
Dim i As Long
Dim mySum As Long
mySum = 0
If IsArray(Arr) Then
For i = LBound(Arr) To UBound(Arr)
mySum = mySum + Arr(i)
Next i
End If
Sum = mySum
End Function
Private Function myCount(Arr As Variant) As Long
If IsArray(Arr) Then
myCount = UBound(Arr) - LBound(Arr) + 1
Else
myCount = 1 '???
End If
End Function
Private Function Average(Arr As Variant) As Double
Average = Sum(Arr) / myCount(Arr)
End Function
Public Sub Test()
Dim x(1 To 30) As Long
Dim i As Long
Dim j As Long

For j = 1 To 1000
For i = LBound(x) To UBound(x)
Randomize
x(i) = CInt(30 * Rnd) + 1
Next i
Debug.Print Format(j, "0000") & ": " & Average(x)
Next j
End Sub


Ps. I don't like using function names that match property names (like .Count).




"bryan.loeper" wrote:

I'm getting intermittent "Runtime error '16': Expression too complex"
using the following code. It seems like if I step it through slowly,
it'll work for a while. What's wrong? I'm using Excel 11.8033.8036
with VBA 6.4.9972 if that matters, on Server 2003.

Option Explicit
Option Base 1

Private Function Sum(Arr() As Long) As Long
Sum = 0
If (Not Arr) < -1 Then
Dim i As Long
For i = LBound(Arr) To UBound(Arr)
Sum = Sum + Arr(i)
Next i
End If
End Function

Private Function Count(Arr() As Long) As Long
Count = UBound(Arr) - LBound(Arr) + 1
End Function

Private Function Average(Arr() As Long) As Double
Average = Sum(Arr) / Count(Arr)
End Function

Public Sub Test()
Dim x(1 To 30) As Long
Dim i As Long
For i = LBound(x) To UBound(x)
Randomize
x(i) = CInt(30 * Rnd) + 1
Next i
Debug.Print Average(x)
End Sub

--

Dave Peterson


--

Dave Peterson