UDF Random Number Generator Type Mismatch

I've been trying for almost an hour wrapping my head around this problem but it seems to be getting nowhere.

Basically I wanted to try a better random number generator and found this code which I wanted to try to implement in my program

 Function RandNorm(Optional mean As Double = 0, _
              Optional Dev As Double = 1, _
              Optional Corr As Double = 0, _
              Optional bVolatile As Boolean = False) As Double()


Randomize
Dim z(0 To 1)   As Double
Dim U           As Double
Dim V           As Double
Dim S           As Double

If bVolatile Then Application.Volatile

Do
    U = 2 * [rand()] - 1
    V = 2 * [rand()] - 1
    S = U * U + V * V
Loop Until S < 1

S = Sqr(-2 * Log(S) / S)
z(0) = Dev * U * S + mean
z(1) = Dev * V * S + mean

If Corr <> 0 Then z(1) = Corr * z(0) + Sqr(1 - Corr ^ 2) * z(1)
RandNorm = z
End Function

As you can see, the function returns a number. When run in Excel, like =RandNorm(), all is fine. But when run in e.g such a simple code like this:

 Sub test()

    Dim x() As Double, i As Long

    ReDim x(1 To 10, 1 To 1)
    For i = 1 To 10
          x(i, 1) = RandNorm
    Next i

    Range("A1:A10") = x
End Sub

It's giving me "Type mismatch" error although it has been defined As Double. If I define RandNorm As Double in the Sub, it's giving me 0's. However, if I replace the x(i, 1) to Cells(i, 1), it works.

The function is in its own Module and the Sub too.

What did I miss? Any help would be really appreciated. Thanks in advance!

Upvotes: 2

Views: 100

Answers (1)

BigBen
BigBen

Reputation: 49998

Double is not the same as Double(): RandNorm returns an array. When used as a UDF in a single cell, RandNorm returns the first element of that array. For example, consider the following function.

Function foo() As Double()
    Dim z(0 To 1) As Double
    z(0) = 1
    z(1) = 2
    foo = z
End Function

When used as a UDF in a single cell, =foo() will always return 1. You'd need to enter it as an array formula in 2 cells with Ctrl+Shift+Enter to display the second element 2.

The easy fix might be changing Double() to Double and RandNorm = z to RandNorm = z(0).

Function RandNorm(Optional mean As Double = 0, _
              Optional Dev As Double = 1, _
              Optional Corr As Double = 0, _
              Optional bVolatile As Boolean = False) As Double
    ....
    If Corr <> 0 Then z(1) = Corr * z(0) + Sqr(1 - Corr ^ 2) * z(1)
    RandNorm = z(0)
End Function

Though it's hard to say that's what you're looking for - since you're effectively ignoring z(1).

Upvotes: 3

Related Questions