none
Run time error '9', subscript out of range RRS feed

  • Question

  • I am running the code below in Microsoft visual basic and i am getting the  error "Run time error '9' subscript out of range". Can someone help identify where the error is. When i debug, its highlighting this area " Worksheets("UnknownShareP").Activate"

    'This code calculates the returns,expected returns and standard deviation of return of each stock
    'Number of Datapoints assumed to be 30000
    'Number of shares assumed to be 300 max
    Dim MeanReturns(300) As Single
    Dim CentralMomnts As Double
    Dim Stdev(300) As Double

    Sub CalcReturns()
        Application.ScreenUpdating = False
        Dim Returns(300, 30000) As Single
        Dim ShareNames(300) As String
        Dim NumShares As Integer
        Dim NumDataPnts As Integer
        Worksheets("UnknownShareP").Activate
        NumShares = WorksheetFunction.CountA(Range("B2:IV2"))
        NumDataPnts = WorksheetFunction.CountA(Range("B2:B65536"))
        Dim Sum As Double
        For i = 1 To NumShares
        ShareNames(i) = Cells(1, i + 1)
        Sum = 0
        CentralMomnts = 0
        For j = 1 To NumDataPnts - 1
        If WorksheetFunction.IsNumber(Cells(j + 2, i + 1)) And WorksheetFunction.IsNumber(Cells(j + 1, i + 1)) Then
            Returns(i, j) = (Cells(j + 2, i + 1) - Cells(j + 1, i + 1)) / Cells(j + 1, i + 1)
        Sum = Sum + Returns(i, j)
        CentralMomnts = CentralMomnts + (Returns(i, j) - MeanReturns(i)) ^ 2
        Else: Returns(i, j) = 0
        End If
        Next j
        MeanReturns(i) = (Sum / (NumDataPnts - 1))
        Stdev(i) = (CentralMomnts / (NumDataPnts - 2)) ^ 0.5
        Next i
        Worksheets("UnknownShareR").Activate
        For i = 1 To NumShares
        Cells(NumDataPnts + 5, i + 1) = MeanReturns(i)
        Cells(NumDataPnts + 7, i + 1) = Stdev(i)
        Cells(1, i + 1) = ShareNames(i)
        For j = 1 To NumDataPnts - 1
        Cells(j + 2, i + 1) = Returns(i, j)
        Next j
        Next i

    End Sub

    Sunday, October 9, 2016 9:25 AM

Answers

  • You haven't explained what you mean by "problem" and by "solution", but the inner loop that fills UnknownShareR near the end of the code is

    For j = 1 To NumDataPnts - 1
    ...
    Next j

    so this will result in one row less than the data rows on UnknownShareP.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Sunday, October 9, 2016 6:55 PM

All replies

  • I am running the code below in Microsoft visual basic and i am getting the  error "Run time error '9' subscript out of range". Can someone help identify where the error is. When i debug, its highlighting this area " Worksheets("UnknownShareP").Activate"

    'This code calculates the returns,expected returns and standard deviation of return of each stock
    'Number of Datapoints assumed to be 30000
    'Number of shares assumed to be 300 max
    Dim MeanReturns(300) As Single
    Dim CentralMomnts As Double
    Dim Stdev(300) As Double

    Sub CalcReturns()
        Application.ScreenUpdating = False
        Dim Returns(300, 30000) As Single
        Dim ShareNames(300) As String
        Dim NumShares As Integer
        Dim NumDataPnts As Integer
        Worksheets("UnknownShareP").Activate
        NumShares = WorksheetFunction.CountA(Range("B2:IV2"))
        NumDataPnts = WorksheetFunction.CountA(Range("B2:B65536"))
        Dim Sum As Double
        For i = 1 To NumShares
        ShareNames(i) = Cells(1, i + 1)
        Sum = 0
        CentralMomnts = 0
        For j = 1 To NumDataPnts - 1
        If WorksheetFunction.IsNumber(Cells(j + 2, i + 1)) And WorksheetFunction.IsNumber(Cells(j + 1, i + 1)) Then
            Returns(i, j) = (Cells(j + 2, i + 1) - Cells(j + 1, i + 1)) / Cells(j + 1, i + 1)
        Sum = Sum + Returns(i, j)
        CentralMomnts = CentralMomnts + (Returns(i, j) - MeanReturns(i)) ^ 2
        Else: Returns(i, j) = 0
        End If
        Next j
        MeanReturns(i) = (Sum / (NumDataPnts - 1))
        Stdev(i) = (CentralMomnts / (NumDataPnts - 2)) ^ 0.5
        Next i
        Worksheets("UnknownShareR").Activate
        For i = 1 To NumShares
        Cells(NumDataPnts + 5, i + 1) = MeanReturns(i)
        Cells(NumDataPnts + 7, i + 1) = Stdev(i)
        Cells(1, i + 1) = ShareNames(i)
        For j = 1 To NumDataPnts - 1
        Cells(j + 2, i + 1) = Returns(i, j)
        Next j
        Next i

    End Sub

           
    Sunday, October 9, 2016 8:02 AM
  • When i debug, its highlighting this area " Worksheets("UnknownShareP").Activate"

    That suggests that the named worksheet does not exist. 

    Questions relating to VBA are best asked at Visual Basic for Applications  (VBA)

    Sunday, October 9, 2016 9:12 AM
  • The code runs on activeworkbook. Check the sheet "UnknownShareP" is visible in workbook from where you are running the code.

    Best Regards,
    Asadulla Javed,
    Jadavpore & Asansol

    Sunday, October 9, 2016 9:52 AM
    Answerer
  • Yes it is visible,  and when debugging its highlighting " Worksheets("UnknownShareP").Activate"
    Sunday, October 9, 2016 10:32 AM
  • Make sure that the name UnknownShareP is exactly correct. Perhaps there is a space before or after the name, so that it's actually " UnknownShareP" or "UnknownShareP ".

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Sunday, October 9, 2016 10:43 AM
  • Thank you, i finally managed to run the code.

    However, problem has 21 rows, where as after running the code its only giving 19 rows. I checked a more or less problem that was done, the solution has 22 rows. How can  i fix this?

    Sunday, October 9, 2016 4:47 PM
  • You haven't explained what you mean by "problem" and by "solution", but the inner loop that fills UnknownShareR near the end of the code is

    For j = 1 To NumDataPnts - 1
    ...
    Next j

    so this will result in one row less than the data rows on UnknownShareP.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Sunday, October 9, 2016 6:55 PM
  • Hi patrickmupam,

    Have your original issue related with “UnknownShareP” been resolved? If so, I suggest you try below code to get the current active workbook, and the worksheets exist in this workbook.

    Dim s As Worksheet
    For Each s In Worksheets
    Debug.Print s.Name, ActiveWorkbook.Name
    Next s

    For wrong rows, I agree with Hans. If your issue has been resolved, I would suggest you mark the helpful reply as answer, and then others who run into the same issue would find the solution easily.

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Wednesday, October 12, 2016 6:25 AM