Help me remove the Goto Statement

[Tkeller]

I am not so great at VBA and have been trying to do something very simple" replace this GOTO for some kind of conditional loop, but for some reason I keep getting error messages.

Function gauss()
        Dim fac As Double, R As Double, V1 As Double, V2 As Double
10    V1 = 2 * Rnd - 1
        V2 = 2 * Rnd - 1
        R = V1 ^ 2 + V2 ^ 2
        If (R >= 1) Then GoTo 10
        fac = Sqr(-2 * Log(R) / R)
        gauss = V2 * fac
End Function

I know this is simple to some of you here!

 

[Joe4]

Use a "Do While" loop, i.e.

Function gauss()
    
    Dim fac As Double, R As Double, V1 As Double, V2 As Double
        
    Do While R >= 1
        V1 = 2 * Rnd - 1
        V2 = 2 * Rnd - 1
        R = V1 ^ 2 + V2 ^ 2
    Loop
        
    fac = Sqr(-2 * Log(R) / R)
    gauss = V2 * fac
    
End Function

 

[Tkeller]

Thank you but somehow that gives me an error message when I run it. The one with Goto works.

 

[Joe4]

I think it is because R=0 at the beginning of the loop, so it would never enter the loop. Try this version instead:

Function gauss()
 
    Dim fac As Double, R As Double, V1 As Double, V2 As Double
     
    Do
        V1 = 2 * Rnd - 1
        V2 = 2 * Rnd - 1
        R = V1 ^ 2 + V2 ^ 2
        If R < 1 Then Exit Do
    Loop
     
    fac = Sqr(-2 * Log(R) / R)
    gauss = V2 * fac
 
End Function

 

[Peter_SSs]

Excel already has a GAUSS() function, so not a good idea to name a UDF with the same name.

I think it is because R=0 at the beginning of the loop, so it would never enter the loop.

So the check could just be moved to the end of the loop.
(I've also included a Randomize)

Function mygauss()
  Dim fac As Double, R As Double, V1 As Double, V2 As Double
  
  Randomize
  Do
    V1 = 2 * Rnd - 1
    V2 = 2 * Rnd - 1
    R = V1 ^ 2 + V2 ^ 2
  Loop While R >= 1
  fac = Sqr(-2 * Log(R) / R)
  mygauss = V2 * fac
End Function