J
James.Allnutt
This is in Excel 2002 - with Visual Basic 6.3.
I have two problems with a macro than uses this function. Neither are
killers, but both are annoying. I have seen similar problems
referenced in this group over the years, but never really saw an
answer (usually somebody had a different way of achieving the desired
outcome rather than a straight fix).
The first problem I have a workaround for but would really appreciate
some explanation as to what is going on.
Basically I have a macro to allow a "Monte Carlo" type simulation, but
also allow the same set of random numbers to be recreated for test
etc. The key line is:
Application.Run "ATPVBAEN.XLA!Random",
ActiveSheet.Range("RandomBlock"), 3, 41, 1, intSeed, 0, 1
(intSeed is a number that changes in a regular way through the
iterations.)
I can get this to run. But if i break the macro early (mainly due to
the second problem) and then try to run it again, i get the error:
The macro '' cannot be found.
This error also happens at other times for no reason I can understand
(for example, I recorded the macro to get the structure of the
equation and it worked fine either running with a macro button or
straight from the code - but then I changed the seed value from '1' to
'intSeed' and it began giving the error noted, so I changed it back to
'1' but the error kept happening until I tried the thing below).
The workaround is to now uncheck the add-in for "Analysis Tool-Pak
VBA" (and excel then says it can't to that because it is in use, but
seems to anyway) and then recheck it again. The macro now runs fine -
until I do something random and it all falls apart again.
The obvious question is "why?".
The second problem is that I don't really want to see all the
iterations happening (just the averaged results of the x simulations I
run). So I have an Application.ScreenUpdating = False line in the
macro. But the screen still updates every time that the
Application.Run line is hit. Is there any way to stop this happening?
Thanks in advance.
James
I have two problems with a macro than uses this function. Neither are
killers, but both are annoying. I have seen similar problems
referenced in this group over the years, but never really saw an
answer (usually somebody had a different way of achieving the desired
outcome rather than a straight fix).
The first problem I have a workaround for but would really appreciate
some explanation as to what is going on.
Basically I have a macro to allow a "Monte Carlo" type simulation, but
also allow the same set of random numbers to be recreated for test
etc. The key line is:
Application.Run "ATPVBAEN.XLA!Random",
ActiveSheet.Range("RandomBlock"), 3, 41, 1, intSeed, 0, 1
(intSeed is a number that changes in a regular way through the
iterations.)
I can get this to run. But if i break the macro early (mainly due to
the second problem) and then try to run it again, i get the error:
The macro '' cannot be found.
This error also happens at other times for no reason I can understand
(for example, I recorded the macro to get the structure of the
equation and it worked fine either running with a macro button or
straight from the code - but then I changed the seed value from '1' to
'intSeed' and it began giving the error noted, so I changed it back to
'1' but the error kept happening until I tried the thing below).
The workaround is to now uncheck the add-in for "Analysis Tool-Pak
VBA" (and excel then says it can't to that because it is in use, but
seems to anyway) and then recheck it again. The macro now runs fine -
until I do something random and it all falls apart again.
The obvious question is "why?".
The second problem is that I don't really want to see all the
iterations happening (just the averaged results of the x simulations I
run). So I have an Application.ScreenUpdating = False line in the
macro. But the screen still updates every time that the
Application.Run line is hit. Is there any way to stop this happening?
Thanks in advance.
James