+ Reply to Thread
Results 1 to 6 of 6

Subscript out of range error in a Sub procedure with ranges and arrays

Hybrid View

  1. #1
    Forum Contributor
    Join Date
    04-07-2013
    Location
    France
    MS-Off Ver
    Excel 2011
    Posts
    166

    Subscript out of range error in a Sub procedure with ranges and arrays

    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
    Attached Files Attached Files
    Last edited by seigna; 05-05-2013 at 02:03 PM. Reason: Clearer explanation

  2. #2
    Forum Expert
    Join Date
    01-15-2007
    Location
    Brisbane, Australia
    MS-Off Ver
    2007
    Posts
    6,591

    Re: Subscript out of range error in a Sub procedure with ranges and arrays

    Hi

    Your sub must be taking forever to run.

    I made a change as below
    With Worksheets("Actions")
                ValeursAction = .Range(.Cells(1, 1), .Cells(.Rows.Count, 1).End(xlUp)).Value
            End With
    to cut down the size of the range being actioned.

    I found the error on the line
        alpha = Worksheets("Performance des fonds").Cells(3, 2).Value
    In your example file there is no sheet Performance des fonds. Is that a problem with the example file, or is that the real error?

    rylo

  3. #3
    Forum Contributor
    Join Date
    04-07-2013
    Location
    France
    MS-Off Ver
    Excel 2011
    Posts
    166

    Re: Subscript out of range error in a Sub procedure with ranges and arrays

    no, that's just me who didn't change it. It's supposed to be the first sheet. Performance. In the original file there's no such mistake and still it doesn't read well..

  4. #4
    Forum Expert
    Join Date
    01-15-2007
    Location
    Brisbane, Australia
    MS-Off Ver
    2007
    Posts
    6,591

    Re: Subscript out of range error in a Sub procedure with ranges and arrays

    Hi

    OK, changed that and found it is failing on the next line
    Var = ValeursAction(Int(NB * alpha))
    Looking at the values in the variables:
    NB: 14
    alpha: 0.05
    When you multiply those, you get 0.7 and the INT then gives you 0. As this isn't a valid index for the array valeursaction (lbound is 1) then you are getting the error.

    Does this help in knowing what to do to correct your code?

    rylo

  5. #5
    Forum Contributor
    Join Date
    04-07-2013
    Location
    France
    MS-Off Ver
    Excel 2011
    Posts
    166

    Re: Subscript out of range error in a Sub procedure with ranges and arrays

    Isn't Int supposed to round UP? to 1?

  6. #6
    Forum Expert
    Join Date
    01-15-2007
    Location
    Brisbane, Australia
    MS-Off Ver
    2007
    Posts
    6,591

    Re: Subscript out of range error in a Sub procedure with ranges and arrays

    Hi

    No, INT just takes the integer component of the number (for positive numbers). From the help file
    Rounds a number down to the nearest integer
    rylo
    Last edited by rylo; 05-06-2013 at 08:56 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1