Function Returns #Value Error sometimes

Question

This method is based on the following 3 steps algorithm :

1 - generate two uniform numbers on the [-1,1] interval that you will call U1 and U2

2 - calculate S = U1 ^2 + U2^2

3 - If S < 1 the normal number is given by U1 * square root (-2 ln (S)/S) otherwise go back to step 1 until S < 1.

Program this function in VB and give it the name BoxMuller.

This is the function I wrote based on above steps I am not sure whether it's correct or not because sometimes it returns #Value error

I pass following values to the function =BoxMuller(Rand(),Rand())

Function BoxMuller(U1 As Double, U2 As Double) As Double
Dim S As Double

Do
    U1 = WorksheetFunction.NormInv(U1, 0, 1)
    U2 = WorksheetFunction.NormInv(U2, 0, 1)
    S = U1 * U1 + U2 * U2
    
    If S < 1 Then
        BoxMuller = U1 * Sqr(-2 * Log(S) / S)
        Exit Function
    End If

Loop Until S < 1
End Function

is the Loop Until S < 1 condition right because I think that maybe the real cause of the error.

Also tried the following :

Function BoxMuller() As Double
Dim S As Double
Dim U1 As Double
Dim U2 As Double
Do

U1 = WorksheetFunction.RandBetween(-1, 1)
U2 = WorksheetFunction.RandBetween(-1, 1)

    S = U1 * U1 + U2 * U2
    
    If S < 1 Then
        BoxMuller = U1 * Sqr(-2 * Log(S) / S)
        Exit Function
    End If

Loop
End Function

And Called =BoxMuller() Still #Value Error

I don't think S will be negative because S is the sum of square of U1 and U2 so the square will always return positive numbers — Stupid_Intern
– Stupid_Intern, Commented Feb 27, 2016 at 5:08
Right right. Try a while wend instead of a loop like you're suggesting then — Cody Geisler
– Cody Geisler, Commented Feb 27, 2016 at 5:10
I mean... You also can't pass log(0) and if Rand gives you .0001 * .0001 you're starting to get to number sizes larger than double could hold? I'd write a debug if statement to see if s is ever less than like .0001 — Cody Geisler
– Cody Geisler, Commented Feb 27, 2016 at 5:14

user3598756 · Accepted Answer · 2016-02-27 09:54:42Z

2

KS Sheon workflow is right

but

WorksheetFunction.RandBetween(-1, 1) returns an integer between -1 and 1

while VBA Rnd() function returns a random double between 0 and 1
VBA Log() function actually returns natural logarithm

I post two solutions (BoxMuller1 and BoxMuller2) that, along with what above, only differs in coding style and both use recursive calls

Function BoxMuller1(mu As Double, sigma As Double) As Double
    Application.Volatile
    Dim U1 As Double, U2 As Double, S As Double

    Do While GetS(Rnd, Rnd, U1, U2, S) >=1
        Randomize
    Loop
    BoxMuller1 = U1 * Sqr(-2 * Log(S) / S) * sigma + mu

End Function

Function GetS(Rnd1 As Double, Rnd2 As Double, U1 As Double, U2 As Double, S As Double) As Double
    U1 = 2*Rnd1 - 1
    U2 = 2*Rnd2 - 1
    S = U1 * U1 + U2 * U2
    GetS = S
End Function




Function BoxMuller2(mu As Double, sigma As Double) As Double
    Application.Volatile
    Dim U1 As Double, U2 As Double, S As Double

    Randomize
    U1 = 2*Rnd -1
    U2 = 2*Rnd -1
    S = U1 * U1 + U2 * U2

    If S >= 1 Then
        BoxMuller2 = BoxMuller2(mu, sigma)
    Else
        BoxMuller2 = U1 * Sqr(-2 * Log(S) / S) * sigma + mu
    End If

End Function

edited Feb 27, 2016 at 9:54

answered Feb 27, 2016 at 9:20

user3598756

29.5k4 gold badges20 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Rosetta Over a year ago

Haha yes u r right, i wasnt thinking. Btw U1 shud be allowed to be negative number i. e. (-1,1). Rnd only range within (0,1).

Rosetta Over a year ago

Yes recursive function is more elegant. But just don't want to confuse him.

user3598756 Over a year ago

corrected U1 and U2 setting: added "-1" to return doubles between -1 and 1

Rosetta · Accepted Answer · 2016-02-27 10:04:38Z

1

i have made some adjust to the final output, The output is not standard distribution but distribution of sample, so multiply sigma then plus mu. Otherwise the function wouldn't require any input.

Rnd is the native VBA to generate random number, it always fall within (0, 1).

Instead of doing a do...loop, you can use GoTo so that you don't have to call exit function to end the loop.

application.volatile will ensure the function recalculates every time you hit press F9. Remove this if you don't it.

Function BoxMuller(mu As Double, sigma As Double) As Double
    Application.Volatile
    Dim U1 As Double, U2 As Double, S As Double

ReCalc:

    Randomize
    'U1 = Rnd 'this is not correct for the function, leaving it here for reference.
    'U2 = Rnd
    'U1 = WorksheetFunction.RandBetween(-1, 1) 'this is wrong too, RandBetween only returns interger
    'U2 = WorksheetFunction.RandBetween(-1, 1)
    U1 = Rnd * 2 - 1
    U2 = Rnd 'the BoxMuller formula don't require U2 to be negative.
    S = U1 * U1 + U2 * U2

    If S < 1 Then
        BoxMuller = U1 * Sqr(-2 * (Log(S) / S) * sigma + mu
    Else
        GoTo ReCalc
    End If

End Function

edited Feb 27, 2016 at 10:04

answered Feb 27, 2016 at 7:57

Rosetta

2,7351 gold badge17 silver badges30 bronze badges

2 Comments

Rosetta Over a year ago

ah, i see ur second attempt, perhaps U1 = WorksheetFunction.RandBetween(-1, 1) is more appropriate

Stupid_Intern Over a year ago

Thanks for the help but Still it returns #Value Error.

Collectives™ on Stack Overflow

Function Returns #Value Error sometimes

2 Answers 2

3 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related