Runtime error '16':
 

Hoping someone can help me with this problem...

Just for a bit of fun I am running the following code:

Sub TimeTest()
'100 million random numbers, tests, and math operations
Dim x As Long
Dim StartTime As Single
Dim i As Long
x = 0
StartTime = Timer
For i = 1 To 100000000
If Rnd <= 0.5 Then x = x + 1 Else x = x - 1
Next i
MsgBox Timer - StartTime & " seconds"
End Sub

Now, every now and again this throws an error with the following message:

Runtime error '16':

Expression too complex

The debugger indicates the problem lies with this line:

If Rnd <= 0.5 Then x = x + 1 Else x = x - 1


Can anyone suggest why this very simple expression be considered by VBA to
be too complex only some of the time??

Thanks

Dan

Runtime error '16':
 

I'm guessing but here is what I think

1) Change the following
from
For i = 1 To 100000000
to
For i = 1 To 100000000#

Excel may not be treating "100000000 " as a long

2) Go to worksheet and make sure the the Analayssi Tool Pak is enabled for
both the worksheet and VBA.

Worksheet menu : Tools - Addins

3) Excel may be having a problem comparing the following

If Rnd <= 0.5

RND returns a SINGLE. I'm not sure how 0.5 is being treated. Try the
following change

If Rnd <= 0.5!

An ! is a single precision constant and # is a double precision constant

4) Make the following change

from
If Rnd <= 0.5!
to
If Rnd() <= 0.5!

RND has a parameter which may be required. The parameter is used to specify
what type of number ot return


5) Add Randomize statement into your code to initiallize the random number
generator using the timer. Otherwise you get the same sequence every time
you run the macro.

from
StartTime = Timer
to
Randomize
StartTime = Timer

"Dan" wrote:

Hoping someone can help me with this problem...

Just for a bit of fun I am running the following code:

Sub TimeTest()
'100 million random numbers, tests, and math operations
Dim x As Long
Dim StartTime As Single
Dim i As Long
x = 0
StartTime = Timer
For i = 1 To 100000000
If Rnd <= 0.5 Then x = x + 1 Else x = x - 1
Next i
MsgBox Timer - StartTime & " seconds"
End Sub

Now, every now and again this throws an error with the following message:

Runtime error '16':

Expression too complex

The debugger indicates the problem lies with this line:

If Rnd <= 0.5 Then x = x + 1 Else x = x - 1


Can anyone suggest why this very simple expression be considered by VBA to
be too complex only some of the time??

Thanks

Dan

Runtime error '16':
 

Hey Joel,

Unfortunately none of the options fixed the problem. However the use of
<0.5! had a dramtic effect on performance!!

I really cant understand why this is occuring and even more frustrating is
that it works fine sometimes and falls over the next....

I'm not going to loose too much sleep over this. I've never had this
problem before and i cant see why id need to use the Rnd function in this way
too much!!

If anything else does occur to you in the future please drop a note.

Dan

"Joel" wrote:

I'm guessing but here is what I think

1) Change the following
from
For i = 1 To 100000000
to
For i = 1 To 100000000#

Excel may not be treating "100000000 " as a long

2) Go to worksheet and make sure the the Analayssi Tool Pak is enabled for
both the worksheet and VBA.

Worksheet menu : Tools - Addins

3) Excel may be having a problem comparing the following

If Rnd <= 0.5

RND returns a SINGLE. I'm not sure how 0.5 is being treated. Try the
following change

If Rnd <= 0.5!

An ! is a single precision constant and # is a double precision constant

4) Make the following change

from
If Rnd <= 0.5!
to
If Rnd() <= 0.5!

RND has a parameter which may be required. The parameter is used to specify
what type of number ot return


5) Add Randomize statement into your code to initiallize the random number
generator using the timer. Otherwise you get the same sequence every time
you run the macro.

from
StartTime = Timer
to
Randomize
StartTime = Timer

"Dan" wrote:

Hoping someone can help me with this problem...

Just for a bit of fun I am running the following code:

Sub TimeTest()
'100 million random numbers, tests, and math operations
Dim x As Long
Dim StartTime As Single
Dim i As Long
x = 0
StartTime = Timer
For i = 1 To 100000000
If Rnd <= 0.5 Then x = x + 1 Else x = x - 1
Next i
MsgBox Timer - StartTime & " seconds"
End Sub

Now, every now and again this throws an error with the following message:

Runtime error '16':

Expression too complex

The debugger indicates the problem lies with this line:

If Rnd <= 0.5 Then x = x + 1 Else x = x - 1


Can anyone suggest why this very simple expression be considered by VBA to
be too complex only some of the time??

Thanks

Dan