ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   Expression too complex (https://www.excelbanter.com/excel-programming/389439-expression-too-complex.html)

bryan.loeper
Expression too complex
 
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
Expression too complex
 
You don't mention where it has a problem. Could you shed some light on that?

"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

Bryan Loeper
Expression too complex
 
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

On May 15, 12:26 pm, Barb Reinhardt
wrote:
You don't mention where it has a problem. Could you shed some light on that?



"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- Hide quoted text -

- Show quoted text -

Barb Reinhardt
Expression too complex
 
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" wrote:

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

On May 15, 12:26 pm, Barb Reinhardt
wrote:
You don't mention where it has a problem. Could you shed some light on that?



"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- Hide quoted text -

- Show quoted text -

Bryan Loeper
Expression too complex
 
I added 'aaa' to every function name, still no dice.

On May 15, 12:44 pm, Barb Reinhardt
wrote:
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" wrote:
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

On May 15, 12:26 pm, Barb Reinhardt
wrote:
You don't mention where it has a problem. Could you shed some light on that?

"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- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

Dave Peterson
Expression too complex
 
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

joel
Expression too complex
 
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

Bryan Loeper
Expression too complex
 
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 -

Bryan Loeper
Expression too complex
 
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 -

Dave Peterson
Expression too complex
 
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.

Bryan Loeper wrote:

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 -

--

Dave Peterson

Dave Peterson
Expression too complex
 
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


All times are GMT +1. The time now is 12:19 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com