Reputation: 53
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
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