Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 - |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 - |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 - |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 - |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 - |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
complex color fill conditions- if statements or complex formula? | Excel Discussion (Misc queries) | |||
Complex Index Match Help (or at least complex to me) | Excel Discussion (Misc queries) | |||
Expression | New Users to Excel | |||
Can I use more complex logical expression for sumif as creteria? | Excel Worksheet Functions | |||
LINEST - "Expression too complex" | Excel Programming |