Expression too complex

B

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
 
B

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
 
G

Guest

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

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

Guest

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
 
B

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 ;)
 
B

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
 
D

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

Dave Peterson

I used this:

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

Are
 
N

NickHK

Bryan,
Whilst I have in the (recent) past used that method of determining if an
array is initialized, I have no abandoned it because of this error occurring
at seemingly random places.
If you use Dave's method, the error will go away.

NickHK
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top