Excel VBA routine to UDF

Question

I'm doing an excercise for my finance class where I've written a routine in Excel VBA that works as intended without using MMULT or TRANSPOSE. I wanted to implement the results within an Excel Sheet by adapting the routine as an UDF but somehow it I'm only getting a value error.

I'm kind of missing something... any hints?

Here the routine:

Option Explicit
Public Sub CalcVola2()

Dim WeightedVola As Variant, Weights As Variant, Volatilities As Variant, Correlations As Variant
Dim i As Double, j As Double, CorrSum As Double, VarSum As Double
Dim CalcVola2 As Double


'===================================================================================================
' Load data
'===================================================================================================

Weights = ThisWorkbook.Worksheets("Stetig").Range("FR4:FR43")
Volatilities = ThisWorkbook.Worksheets("Stetig").Range("FS4:FS43")

Correlations = ThisWorkbook.Worksheets("Covar-Correl").Range("C13:AP52")

'===================================================================================================
' Resize weighted volatility array to fit the inputs and clean the data
'===================================================================================================

ReDim WeightedVola(1 To UBound(Weights, 1), 1 To 1)

For i = 1 To UBound(Weights, 1)
    If Weights(i, 1) = "" Then
        Weights(i, 1) = 0
    End If
Next i

For i = 1 To UBound(Volatilities, 1)
    If Volatilities(i, 1) = "" Then
        Volatilities(i, 1) = 0
    End If
Next i

'===================================================================================================
' Perform weighted vola calculations
'===================================================================================================

For i = 1 To UBound(Weights, 1)
   WeightedVola(i, 1) = Weights(i, 1) * Volatilities(i, 1)
Next i


'===================================================================================================
' Calculate the first sum of the portfolio volatility function by adding the squared weighted volas
'===================================================================================================

For i = 1 To UBound(Weights, 1)
    CorrSum = CorrSum + WeightedVola(i, 1) ^ 2
Next i


'===================================================================================================
' Calculate the second sum of the portfolio volatility function by the product of the weighted vola
' and the correlation
'===================================================================================================

For i = 1 To UBound(Weights, 1)
    For j = i + 1 To UBound(Weights, 1)
        CorrSum = CorrSum + WeightedVola(i, 1) * 2 * WeightedVola(j, 1) * Correlations(i, j)
    Next j
Next i

CalcVola2 = Sqr(CorrSum)

ThisWorkbook.Worksheets("Stetig").Range("FS46").Value = CorrSum
ThisWorkbook.Worksheets("Stetig").Range("FS47").Value = CalcVola2


End Sub

And here the UDF:

Option Explicit
Public Function CalcVola(Weights As Variant, Volatilities As Variant, Correlations As Variant) As Double

Dim WeightedVola As Variant
Dim i As Double, j As Double, CorrSum As Double, VarSum As Double


'===================================================================================================
' Resize weighted volatility array to fit the inputs and clean the data
'===================================================================================================

ReDim WeightedVola(1 To UBound(Weights, 1), 1 To 1)

For i = 1 To UBound(Weights, 1)
    If Weights(i, 1) = "" Then
        Weights(i, 1) = 0
    End If
Next i

For i = 1 To UBound(Volatilities, 1)
    If Volatilities(i, 1) = "" Then
        Volatilities(i, 1) = 0
    End If
Next i


'===================================================================================================
' Perform weighted vola calculations
'===================================================================================================

For i = 1 To UBound(Weights, 1)
   WeightedVola(i, 1) = Weights(i, 1) * Volatilities(i, 1)
Next i


'===================================================================================================
' Calculate the first sum of the portfolio volatility function by adding the squared weighted volas
'===================================================================================================

For i = 1 To UBound(Weights, 1)
    CorrSum = CorrSum + WeightedVola(i, 1) ^ 2
Next i


'===================================================================================================
' Calculate the second sum of the portfolio volatility function by the product of the weighted vola
' and the correlation
'===================================================================================================

For i = 1 To UBound(Weights, 1)
    For j = i + 1 To UBound(Weights, 1)
        CorrSum = CorrSum + WeightedVola(i, 1) * 2 * WeightedVola(j, 1) * Correlations(i, j)
    Next j
Next i

CalcVola = Sqr(CorrSum)


End Function

What would an example formula be with the UDF that gives you a #VALUE error? — Comintern
– Comintern, Commented Sep 5, 2016 at 13:43
You need to set breakpoints and see where the #VALUE! error gets returned. Frequently it is because a variable is not correctly typed, or a VBA function is getting incorrect arguments. For example, if you pass your Weights argument to your function as a one-dimension array, your routine will crash and return the #VALUE! error on that first Redim line, since it is looking for a 2D array. — Ron Rosenfeld
– Ron Rosenfeld, Commented Sep 5, 2016 at 13:47
@Comintern Well the formula is actually the portfolio volatility using correlations: d3plit93wdq7ew.cloudfront.net/assets/covar-300x105.png — Oscar Alberte
– Oscar Alberte, Commented Sep 5, 2016 at 14:07
@RonRosenfeld will try passing 1D arrays... Otherwise would have to check how to redim a 2D array input in an UDF. Apart from that, seems like my breakpoints do not work properly while coding the function. They work like charm in the routine though — Oscar Alberte
– Oscar Alberte, Commented Sep 5, 2016 at 14:09
Try declaring the function arguments as being of type range. And then making a variant variable equal to them in the function itself. This should result in the desired 2D variant array. — Ron Rosenfeld
– Ron Rosenfeld, Commented Sep 5, 2016 at 14:23

Ron Rosenfeld · Accepted Answer · 2016-09-06 02:10:02Z

You need to set breakpoints and see where the #VALUE! error gets returned. Frequently it is because a variable is not correctly typed, or a VBA function is getting incorrect arguments. For example, if you pass your Weights argument to your function as a one-dimension array, your routine will crash and return the #VALUE! error on that first Redim line, since it is looking for a 2D array.

If your arguments are being passed as ranges, a similar problem will occur.

If that will always be the case, pass the arguments as ranges and then, in your code, something like:

Public Function CalcVola(rWeights As Range, rVolatilities As Range, rCorrelations As Range) As Double

Dim Weights, Volatilities, Correlations

Weights = rWeights
Volatilities = rVolatilities
Correlations = rCorrelations

...

End Function

If the arguments might be passed as either Ranges or Arrays, then you'll need to have your function arguments as type Variant, and test to see what they are, and make the appropriate transformation, before executing the rest of your UDF.

Collectives™ on Stack Overflow

Excel VBA routine to UDF

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related