Oh, I see what I was doing now. I was using Arr() As Variant instead
of Arr As Variant. I don't see why that would make a difference,
though.

-Bryan



On May 15, 2:18 pm, Bryan Loeper wrote:
Ok, now I'm confused. I've tried setting up the functions to accept a
Variant and then pass the data type I wanted to use (like you did),
but it usually gives me a type mismatch. I copied all of your
solution in and it worked just fine. Any ideas what I might be doing
wrong there?

PS "Count" will eventually be a property, but the datatype it's for
is proprietary, so that's why it worked its way into a test function.
Sorry 'bout that ;)

On May 15, 2:00 pm, 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- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -