Two problems with Application.Run "ATPVBAEN.XLA!Random"
 

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

Two problems with Application.Run "ATPVBAEN.XLA!Random"
 

This helps with Solver:

Run "atpvbaen.xla!auto_open"

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


wrote in message
ups.com...
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

Two problems with Application.Run "ATPVBAEN.XLA!Random"
 

On Jun 18, 10:56 pm, "Jon Peltier"
wrote:
This helps with Solver:

Run "atpvbaen.xla!auto_open"

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. -http://PeltierTech.com
_______



I put that in the macro and it did all sort of weird things (it
wouldn't 'clear' the results block anymore - just gave me an error
"Range method of _global failed" or something).

Thanks anyway.

James