Subscript out of range error in a sub with arrays

[seigna]

Hi everyone,

I am working on the following code and I am getting without stop this funny error: Subscript out of range. I have no clue how to fix it. It seems I get it each time I try working with arrays... It is pretty upsetting since I get it really almost EACH time.

Could someone please help me fix it?

Sub MatriceVarCov()
    Dim i As Integer, j As Integer, k As Integer
    Dim Valeurs As Integer
    Dim SomCov As Double
    Dim N As Integer
    Dim CoursActionbis As Range
    Dim nb_Cours As Integer
    Dim nb_Actions As Integer
    Dim Indice As Integer
    Dim Rendement As Double
    Dim Rendement2 As Double
    Dim Cova As Double
 


    nb_Cours = Worksheets("Actions").Cells(Rows.Count, 2).End(xlUp).Row - 1 'THIS LINE ERROR
     nb_Actions = Worksheets("Actions").Cells(1, Columns.Count).End(xlToLeft).Column - 1 'THIS LINE ERROR
    
    
    ReDim ValeursAction(1 To nb_Cours) As Double 'THIS LINE ERROR
    ReDim Esperance(1 To N) As Double 'THIS LINE ERROR
    'N = 4
            For i = 2 To N 
                Indice = Sheets("Classement").Cells((nb_Actions + 7 + (i - 2)), 7).Value
                Esperance(i) = Sheets("Analyse Statistique").Cells(Indice + 1, 2) 




            With Sheets("Ponderation Sortino")
                .Cells(4, 11 - i) = Esperance(i)
            End With


    
    For j = i To N
    
 
        SomCov = 0
    
        
        For k = 2 To nb_Cours
            ' Recuperation des rendements à partir du tableau calculé et affiché dans la sub rend
            Rendement = Sheets("Ponderation Sortino").Cells(5 + k, i)
            Rendement2 = Sheets("Ponderation Sortino").Cells(5 + k, j)
            SomCov = SomCov + (Rendement - Esperance(i)) * ((Rendement2) - (Esperance(j)))
            Cova = (1 / nb_Cours) * SomCov
        Next k
        
      
        With Sheets("Ponderation Sortino")
        .Cells(i + 5, j + 9) = Cova
        End With
    Next j


Next i
End Sub

 

[mvsub1]

Hello seigna,

what happens if you delete the comment at
'N = 4

and shouldn't it be above the redim Esperance statement?

 

[p45cal]

As a first step, change all instances of Integer to Long.
Hopefully that will get you through the first few erroring lines.

 

[Rick Rothstein]

As a first step, change all instances of Integer to Long.
Hopefully that will get you through the first few erroring lines.

The OP should definitely do that, but I do not think that is the source of his current error. He says he gets a "Subscript out of range" error whereas an Overflow error would be what I would expect if his returned row numbers were larger than an Integer could handle.

 

[Rick Rothstein]

I am working on the following code and I am getting without stop this funny error: Subscript out of range.

That error for the lines of code you indicated you are getting it on would mean to me that you do not have a worksheet named "Actions" in the active workbook.

 

[seigna]

Would you mind looking at it here? I have applied so many changes that I am going crazy now.. It is the actual file.. with a few spreadsheets deleted.. but my problem is there... https://docs.google.com/file/d/0Bwjh7B2eKQEQbV84UXMyX0JBVjA/edit?usp=sharing

 

[Rick Rothstein]

Would you mind looking at it here? I have applied so many changes that I am going crazy now.. It is the actual file.. with a few spreadsheets deleted.. but my problem is there... https://docs.google.com/file/d/0Bwjh7B2eKQEQbV84UXMyX0JBVjA/edit?usp=sharing

If that is the workbook you are using the code you post in, then I was correct... you do not have a worksheet named "Actions" in it... that means the red highlighted part of this line of code would generate the "Subscript out of range" error you have reported...

nb_Cours = Worksheets("Actions").Cells(Rows.Count, 2).End(xlUp).Row - 1 'THIS LINE ERROR

Did you perhaps mean "Allocation" instead of "Actions" (you have a worksheet with that name in the workbook)?

 

[seigna]

Strange cause I actually do have Actions in the project.. I'll check again... It is meant to be Actions..

Yes I checked - it is the FIRST worksheet in the file..

 

[p45cal]

1. Move N=4 to before the line:
ReDim Preserve Esperance(1 To N)
(though you're not preserving anything).

2. Indice = Sheets("Classement").Cells((nb_Actions + 7 + (i - 2)), 7).Value
You have no sheet called Classement

 

[Rick Rothstein]

Yes I checked - it is the FIRST worksheet in the file..

I'm sorry, yes, you are right... it is the first sheet. The file you posted had it out-of-view with "Analyse Statistique" showing at the left and I just did not think to see if there was a worksheet out-of-view to its left. Sorry. Now, when I put the code you posted in a general Module and run the macro, I do not get an error on any of the first 3 lines you indicated you got errors on; rather, I get the "Subscript out of range" error on this line...

ReDim Esperance(1 To N) As Double 'THIS LINE ERROR

And that would probably be because N has not been assigned a value at that point in time (so it is zero).