Help with 1 x 2 array output

[jomni]

This shouldn't be hard for the excel experts.

here's my code:

Function RandNormCorr(mean1, sd1, mean2, sd2, corr)
'returns two random numbers that are based on different distributions
'and has a certain correlation

Dim xymat(1, 2) As Variant 'output array
Dim z1 As Variant
Dim z2 As Variant

z1 = gauss 'gauss is a random number generation function
z2 = gauss

x = z1 * Sqr((1 + corr) / 2) + z2 * Sqr((1 - corr) / 2)
y = z1 * Sqr((1 + corr) / 2) - z2 * Sqr((1 - corr) / 2)

xymat(1, 1) = mean1 + x * sd1
xymat(1, 2) = mean2 + y * sd2

RandNormCorr = xymat


End Function

I want a (1,2) array to show:
mean1 + x * sd1 on the left cell and
mean2 + y * sd2 on the right cell

Of course I highlight a 1x2 range, type the function, the
ctrl+shift+enter

this is what comes out:
0 0
pressing f2 f9 will reveal this
={0,0,0;0,-0.926298991003733,-1.12127338720803}
={0,0,0;0,-1.57926241712754,-0.0663085926997917

 

[Guest]

Your output array should be defined as follows
Dim xymat(0, 1) As Varian
Then
xymat(0, 0) = mean1 + x * sd1
xymat(0, 1) = mean2 + y * sd
An
Range("Left cell address") = xymat

 

[jomni]

Oh yeah! that's because I did not indicate Option Base 1
Thanks! Need to get used to this

 

[Dana DeLouis]

If you are returning a horizontal array to the worksheet, would this idea
work?

Function RandNormCorr(mean1, sd1, mean2, sd2, corr)
Dim z1 As Double 'Variant
Dim z2 As Double 'Variant
Dim x As Double
Dim y As Double

' gauss is a random number generation function
z1 = gauss
z2 = gauss

x = z1 * Sqr((1 + corr) / 2) + z2 * Sqr((1 - corr) / 2)
y = z1 * Sqr((1 + corr) / 2) - z2 * Sqr((1 - corr) / 2)
RandNormCorr = Array(mean1 + x * sd1, mean2 + y * sd2)
End Function


Just another version of your equation if I didn't make a mistake...

Function RandNormCorr(mean1, sd1, mean2, sd2, corr)
Dim z1 As Double
Dim z2 As Double
Dim x As Double
Dim y As Double

' gauss is a random number generation function
z1 = gauss
z2 = gauss

x = (Sqr(1 + corr) * z1 + Sqr(1 - corr) * z2) / Sqr(2)
y = x - z2 * Sqr(2 - 2 * corr)
RandNormCorr = Array(mean1 + x * sd1, mean2 + y * sd2)
End Function