I have written the following VBA Code, and at one point I place values from a Range (CoursAction) into an array(ValeursAction). Later on I use the values in the array to obtain a threshold value (Var). However, I get a "Subscript out of range" error though I think I have defined everything.
Or is it ValeursAction() As Variant that is incorrect? It is supposed to be an array... Or should I Re-dimension it? I am sorting it with Call Tri1(ValeursAction).
Option Explicit
Sub Performance()
Dim N As Long
Dim EnsembleActions As Range
Dim Nb_Actions As Integer
Dim Action As Range
Dim CoursAction As Range
Dim i As Integer
Dim SharpeRatio As Double
Dim TauxRf As Double
Dim RendsEcart As Double
Dim NomAction As String
Dim ValeursAction() As Variant
Dim NB As Integer
With Worksheets("Actions")
Nb_Actions = .Cells(1, Columns.Count).End(xlToLeft).Column
End With
With Worksheets("Actions")
Set EnsembleActions = .Range(.Cells(2, 1), .Cells(.Rows.Count, Nb_Actions).End(xlUp))
End With
For Each Action In EnsembleActions.Columns
i = i + 1
Set CoursAction = Action
TauxRf = Worksheets("Performance").Cells(2, 2).Value
RendsEcart = WorksheetFunction.StDev(CoursAction)
NB = WorksheetFunction.Count(CoursAction)
'We place values from the range in a table
With Worksheets("Actions")
ValeursAction = .Range(.Cells(1, 1), .Cells(.Rows.Count, 1)).Value
End With
Call Tri1(ValeursAction)
Dim alpha As Double
Dim Var As Double
alpha = Worksheets("Performance").Cells(3, 2).Value
Var = ValeursAction(Int(NB * alpha))
NomAction = Worksheets("Actions").Cells(1, i).Value
With Worksheets("Performance")
.Cells(4 + i, 1) = NomAction
.Cells(4 + i, 2) = Var
End With
Next Action
End Sub
Sub Tri1(plaga As Variant)
Dim ligne_Deb As Long
Dim ligne_Fin As Long
ligne_Deb = LBound(plaga)
ligne_Fin = UBound(plaga)
Dim i As Long, J As Long
Dim tmp As Long
For i = ligne_Deb To ligne_Fin - 1
For J = ligne_Fin To i + 1 Step -1
If plaga(J, 1) < plaga(J - 1, 1) Then
tmp = plaga(J, 1)
plaga(J, 1) = plaga(J - 1, 1)
plaga(J - 1, 1) = tmp
End If
Next J
Next i
End Sub
Bookmarks