Expression too complex

[bryan.loeper]

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

 

[Barb Reinhardt]

You don't mention where it has a problem. Could you shed some light on that?

 

[Bryan Loeper]

It varies. Sometimes it dies on Sum = Sum + Arr(i), sometimes on
Average = Sum(Arr) / Count(Arr). Neither of those _should_ be a
problem, but maybe I'm missing something.

-Bryan

 

[Barb Reinhardt]

Just a GUESS, but you are using function names that are built in functions.
Is there some reason you aren't using those? Alternatively, have you tried
changing the function name to something unique. Again, this is a GUESS.

 

[Bryan Loeper]

I added 'aaa' to every function name, still no dice.

 

[Dave Peterson]

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).

 

[Joel]

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

 

[Bryan Loeper]

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

 

[Bryan Loeper]

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

 

[Dave Peterson]

Dim Arr() As Variant
says that Arr will eventually be an array that can hold anything (each item is a
variant)

Dim Arr as Variant
means that that variable can be anything--an object, a long, a string, or even
an array.

 

[Dave Peterson]

I used this:

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

Are