Hi,When running a Monte Carlo sim in VBA I often find that the program stops when executing the following piece of code:rand(0)=Application.NormInv(Rnd(),0,1)Even though there is nothing wrong when you look at it in debug mode, Moreover if you start the code going again the simulation usually rund to the end and produces the correct answer.Any tips on how to get round this very annoying bug?Baz

Use the sum of 12 random nbrs generated from unifrom distribution minus 6.

You should check for rnd() producing exactly 0 or 1, neither of these should be used in the simulation as the produce infinity, or pick a random number generator that doesn't produce 0 or 1.John

I have seen many health warnings on VBA's random number generator. Might be an opportunity to use a better generator instead.

It might just be a typo on the post but I think your missing a s on the normsinv.Wrt the sum of 12 rand number minus six I remember reading about some problems with the distribution of this but can't remember in what book I read it. It could have been Advanced modelling in finance using Excel and VBA, but I'm not sure

Actually found that reference. It was in Jackel's Monte Carlo methods in finance pg 19. I also see my point about the typo was incorrect.

First of all, you should implement a Box-Muller in VBA, makes things a lot faster. I think the problem occurs when you generate a number close to 0 or to 1, and NormInv has troubles handling that. You could circumvent it with:On error goto ErrorHandlerfor i = 1 to SimCount...next iErrorHandler:i = i - 1resume nextend sub/function

Hi.Maybe you want to try this VBA code.Function BoxMuller() As Double' Box-Muller transformation for quasi normally distributed random numbers' Use Rnd() if you don´t have anything more appropriate, such as' Faure, Sobol, Halton, or ... algorithms. Const TwoPi = 6.28318530717959 Static Status As Variant Static Save As Double Dim X1, X2 As Double If IsEmpty(Status) = True Then Status = 0 If Status = 0 Then Status = 1 X1 = Sqr(-2 * Log(Rnd())) X2 = TwoPi * Rnd() Save = X1 * Sin(X2) BoxMuller = X1 * Cos(X2) Else Status = 0 BoxMuller = Save End IfEnd FunctionCheers Moritz

cheers Moritz I owe you one

Very welcome!