Possible to speed up a Monte Carlo Simulation?

[jerseyguy1996]

I wrote a program to run the simulation 6 times. Each simulation runs through 10,000 possible scenarios with each scenario stretching out yearly for 30 years. I timed it and it takes just over an hour to run through it all. Is this typical for vba or have I just managed to write the most inefficient code of all time? I am a complete amateur so it is possible and don't be afraid to tell me because I would love to know a better way.

[shg]

See if this is faster:

Please Login or Register  to view this content.

Runs in a fraction of a second...

Do you expect an answer based on the information provided?

[shg]

One thing you're doing (from your other thread) is using WorksheetFunction.NormInv to generate random numbers with normal distribution. That's very slow; this runs about 100 times faster:

Please Login or Register  to view this content.

[jerseyguy1996]

Ahhh...I had a breakthrough. A combination of Shg (thank you very much), and a quick read on how to use arrays. What was slowing me down was that I was writing the value to each cell as I was calculating it. This time I run all of the calculations and then write them all at once to the cells. 10 minutes is now down to about 5 seconds.

This stuff is so much fun!

Please Login or Register  to view this content.

[shg]

That's very good.

If you replace

Please Login or Register  to view this content.

with

Please Login or Register  to view this content.

... you should get significant additional speed.

Also

Please Login or Register  to view this content.

should be just

Please Login or Register  to view this content.