CODEWITHSUNDEEP
vbafunctionexcelexcel-formula
Stupid_Intern
Stupid_Intern

Reputation: 3450

Function Returns #Value Error sometimes

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

Upvotes: 0

Views: 252

Answers (2)

Rosetta
Rosetta

Reputation: 2725

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

Upvotes: 1

user3598756
user3598756

Reputation: 29421

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

Upvotes: 2

Related Questions