Hi all,

I am trying to create a User-Defined-Function to calculate a special type of
statistical variance. The basic gist of it is that it takes four vectors of
values (four different types of prices) and one integer (the number of
trading days) and spits out a single variance value. Similar to the VAR or
VARP function, except that it requires five inputs instead of one range.

I have appended the code that I have so far at the bottom of the post, but I
have a few questions if people don't mind answering them:

1) In the code below, was I correct in having defined the openingPrices,
highPrices, etc As Range? or should have i defined them as arrays As double,
like so:

Function YZVolatility(openingPrices() As Double, _
highPrices() As Double, lowPrices() As Double, _
closingPrices() As Double, numberOfTradingDays As Integer) As Double

Or am I wrong on both counts? The goal is to use the entries from the
vectors to create some new vectors to work on.

2) Are we able to do matrix operations in VBA such as addition, subtraction,
etc?

3) In the section where i'm using the For...Next statement, was that the
correct way to fill in the newly created arrays?

4) Was creating arrays the correct choice in this, considering that I wanted
to use Worksheet functions to do some of calculations? Well... if we can use
matrices in VBA, then I won't have to, since I prefer not to.

5) Is there anything else in that code which could have produced the error?
I can't seem to get it to work. I know it might be easier to just calculate
it manualy in excel using Array formulas, but i'm making this function for my
boss, who doesn't want to waste that much time.

Thank you so much for anyone who helps!

Kind Regards,

Iwan Juwono
--------------------------
Code
--------------------------
Function YZVolatility(openingPrices() As Double, _
highPrices() As Double, lowPrices() As Double, _
closingPrices() As Double, numberOfTradingDays As Integer) As Double
'Calculates the Yang Zhang Open-High-Low-Close Volatility

Dim nOpening As Integer
Dim nHigh As Integer
Dim nLow As Integer
Dim nClose As Integer

Dim sigma2 As Double
Dim sigma02 As Double
Dim sigmac2 As Double
Dim sigmars2 As Double
Dim k As Double
Dim lnOC() As Double
Dim lnCO() As Double
Dim lnHC() As Double
Dim lnHO() As Double
Dim lnLC() As Double
Dim lnLO() As Double
Dim rs() As Double
Dim i As Integer

'Calculate the count variables
nOpening = openingPrices.Count
nHigh = highPrices.Count
nLow = lowPrices.Count
nClose = closingPrices.Count

'Check if all length of all the vectors are the same.
If nOpening = nHigh And nLow = nClose And nOpening = nClose Then
ReDim lnOC(nOpening - 1) As Double
ReDim lnCO(nOpening - 1) As Double
ReDim lnHC(nOpening - 1) As Double
ReDim lnHO(nOpening - 1) As Double
ReDim lnLC(nOpening - 1) As Double
ReDim lnLO(nOpening - 1) As Double
ReDim rs(nOpening - 1) As Double

For i = 1 To noOpening - 1
lnOC(i) = Log(openingPrices(i) / closingPrices(i + 1))
lnCO(i) = Log(closingPrices / openingPrices(i))
rs(i) = Log(highPrices(i) / closingPrices(i)) * Log(highPrices(i) /
openingPrices(i)) _
+ Log(lowPrices(i) / closingPrices(i)) * Log(lowPrices(i) /
openingPrices(i))
Next i

sigma02 = (numberOfTradingDays) * Application.WorksheetFunction.Var(lnOC)

sigmac2 = (numberOfTradingDays) * Application.WorksheetFunction.Var(lnCO)

sigmars2 = (numberOfTradingDays) * Application.WorksheetFunction.VarP(rs)
k = 0.34 / (1 + (nOpening) / (nOpening - 2))

sigma2 = sigma02 + k * sigmac2 + (1 - k) * sigmars2
YZVolatility = sigma2

Else
YZVolatility = 0
End If

End Function