Help!what's the problem of my user form

[Ms.Sigma]

I want to use user form to display the correlation matrix of several series, and also the value of VaR (VaR of each series and portfolio) according to different confidence level.

My macro and user form cannot work together, could you please help me to find out my mistake of my macro? Thanks a lot!

Private Sub cmdClear_Click()
TextBox1.Value = ""
RefEdit1.Value = ""
ListBox1.Value = ""

End Sub


Private Sub cmdCorrelation_Click()
Dim series As Range
Dim correlation As Variant
Set series = Range(RefEdit1.Text)
correlation = CorrelationMatrix(series)

With ListBox1
.Clear
.Font.Size = 9
.List() = correlation
End With
End Sub

Private Sub RefEdit1_BeforeDragOver(Cancel As Boolean, ByVal Data As MSForms.DataObject, ByVal x As stdole.OLE_XPOS_CONTAINER, ByVal y As stdole.OLE_YPOS_CONTAINER, ByVal DragState As MSForms.fmDragState, Effect As MSForms.fmDropEffect, ByVal Shift As Integer)

End Sub

Private Sub cmdVAR_Click()
Dim r
r = 20000
Dim time
time = 1

Dim v As Variant
Dim cf As Variant
Dim varresult

If OptionBarclay Then v = 0.022116817
If OptionBP Then v = 0.00275482
If OptionBA Then v = 0.020048479
If OptionICI Then v = 0.015174208
If OptionHSBC Then v = 0.003364417
If Option95 Then cf = 0.95
If Option99 Then cf = 0.99
varresult = VaRAsset(r, v, time, cf)

With TextBox1
.Value = varresult
End With

If OptionAll And Option95 Then TextBox1.Value = (11796.01978)
If OptionAll And Option99 Then TextBox1.Value = (16683.33588)
End Sub


Function CorrelationMatrix(rng As Variant) As Variant
' Returns correlation matrix of a range
Dim i As Integer, j As Integer, K As Integer, ncols As Integer, nrows As Integer
Dim r1vector() As Variant
Dim r2vector() As Variant
Dim Cmatrix() As Variant
ncols = rng.Columns.Count
ReDim Cmatrix(ncols, ncols)
nrows = rng.Rows.Count
ReDim r1vector(nrows)
ReDim r2vector(nrows)
For i = 1 To ncols
For K = 1 To nrows
r1vector(K) = rng(K, i)
Next K
Cmatrix(i, i) = 1
For j = i + 1 To ncols
For K = 1 To nrows
r2vector(K) = rng(K, j)
Next K

Cmatrix(i, j) = Application.WorksheetFunction.Correl(r1vector, r2vector)
Cmatrix(j, i) = Cmatrix(i, j)
Next j
Next i

CorrelationMatrix = Cmatrix

End Function

Function VaRAsset(r, v, time, cf)
' This function returns VAR estimate for each asset
Dim alpha, sigma
alpha = -Application.WorksheetFunction.NormSInv(1 - cf)
sigma = Sqr(v)
VaRAsset = r * (alpha * sigma * Sqr(time))

End Function

 

[Ruddles]

My macro and user form cannot work together

What do you mean by this? Are you getting an error? If so, what is the error message and which line is it occurring on?

If you're not getting the expected results, have you tested the functions on their own? That is, have you tried calling them directly and checking that they produce the correct results?

What makes you think your "macro and user form cannot work together"?

 

[Ms.Sigma]

Hello,

I mean the user form cannot display the correlation matrix and all other expected values. Nothing happened when I click command buttons.

What do you mean by this? Are you getting an error? If so, what is the error message and which line is it occurring on?

If you're not getting the expected results, have you tested the functions on their own? That is, have you tried calling them directly and checking that they produce the correct results?

What makes you think your "macro and user form cannot work together"?

 

[Ms.Sigma]

Please help me! Many Many Thanks!!!!

 

[Ruddles]

1) Test your functions in isolation and satisfy yourself that they're producing the expected values.

2) Place a MsgBox command before the first executable command in each Sub and Function and confirm whether they're actually being called when you click the buttons.

3) Place a breakpoint at the first executable statement of each Sub and Function and step through the code in order to check that it's following the path you expect it to follow.