我正在为我的金融课程做练习,其中我在Excel VBA中编写了一个例程,该例程可以按预期工作,而无需使用MMULT或TRANSPOSE。我想通过将例程改编为UDF来在Excel Sheet中实现结果,但是不知何故,我只得到了一个值错误。
我有点失落……有什么提示吗?
这是例行程序:
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
这里是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
您需要设置断点,并查看#VALUE!
错误返回的位置。通常是因为变量的类型不正确,或者VBA函数获取的参数不正确。例如,如果将Weights
参数作为一维数组传递给函数,则例程将崩溃并#VALUE!
在第一Redim
行返回错误,因为它正在寻找2D数组。
如果将您的参数作为范围传递,则会发生类似的问题。
如果总是这样,请将参数作为范围传递,然后在您的代码中输入以下内容:
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
如果参数可以作为Ranges或Arrays传递,则在执行其余的UDF之前,需要将函数参数的类型设为Variant,并进行测试以查看其含义并进行适当的转换。
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句