Run-time error '9': Subscript out of range when creating matrix

Question

I'm trying to create a varcov matrix using VBA but despite hours of trying to track down the answer to this problem have been unable to solve it. My problem is that I keep getting the run-time error '9' on each of the below double-asterisked lines:

Sub varcovmmult()

    Dim returns()
    Dim trans()
    Dim Excess()
    Dim MMult()

    ReDim trans(ColCount, RowCount)
    ReDim Excess(RowCount, ColCount)
    ReDim MMult(ColCount, ColCount)
    ReDim returns(ColCount)

'Calculate mean, trans and excess arrays for dimensionalisation

'For mean:

    ColCount = Range("C6:H15").Columns.Count
    RowCount = Range("C6:H15").Rows.Count

    For j = 1 To ColCount
          **returns(j) = Application.Average(Range("C6:H15").Columns(j))
          Range("c30:h30").Cells(j) = returns(j)**
    Next j

'For excess:

    For j = 1 To ColCount
        For i = 1 To RowCount
            **Excess(i, j) = Range("c6:h15").Cells(i, j) - returns(j)
            Range("C36:H45").Cells(i, j) = Excess(i, j)**
        Next i
    Next j

'For tranpose:

    For j = 1 To ColCount
        For i = 1 To RowCount
            **trans(j, i) = Range("C36:H45").Cells(i, j)
            Range("C51:L56").Cells(j, i) = trans(j, i)**
        Next i
    Next j


'inject values into product array

    For i = 1 To ColCount
        For j = 1 To ColCount
            For k = 1 To RowCount
                **MMult(i, j) = MMult(i, j) + trans(i, k) * Excess(k, j)**
            Next k
        Next j
    Next i

'output product array values into varcov matrix and divide by n.years

    For i = 1 To ColCount
        For j = 1 To ColCount
            **Range("C62").Cells(i, j) = MMult(i, j)**
        Next j
    Next i

End Sub

score 1 · Answer 1 · answered Mar 31 '13 at 07:27

1

You need to put these lines:

ReDim trans(ColCount, RowCount)
ReDim Excess(RowCount, ColCount)
ReDim MMult(ColCount, ColCount)
ReDim returns(ColCount)

After these lines:

ColCount = Range("C6:H15").Columns.Count
RowCount = Range("C6:H15").Rows.Count

answered Mar 31 '13 at 07:27

Dan Metheus

1,418
9
16

score 0 · Accepted Answer · answered Mar 31 '13 at 07:35

I am trying to run the following code with value 1 in each cell in Range(C6:H15):

Sub varcovmmult()

    Dim returns()
    Dim trans()
    Dim Excess()
    Dim MMult()

    ColCount = Range("C6:H15").Columns.Count
    RowCount = Range("C6:H15").Rows.Count

    ReDim trans(ColCount, RowCount)
    ReDim Excess(RowCount, ColCount)
    ReDim MMult(ColCount, ColCount)
    ReDim returns(ColCount)

    For j = 1 To ColCount
        returns(j) = Application.Average(Range("C6:H15").Columns(j))
        Range("c30:h30").Cells(j) = returns(j)
    Next j

    For j = 1 To ColCount
        For i = 1 To RowCount
            Excess(i, j) = Range("c6:h15").Cells(i, j) - returns(j)
            Range("C36:H45").Cells(i, j) = Excess(i, j)
        Next i
    Next j

    For j = 1 To ColCount
        For i = 1 To RowCount
            trans(j, i) = Range("C36:H45").Cells(i, j)
            Range("C51:L56").Cells(j, i) = trans(j, i)
        Next i
    Next j

    For i = 1 To ColCount
        For j = 1 To ColCount
            For k = 1 To RowCount
                MMult(i, j) = MMult(i, j) + trans(i, k) * Excess(k, j)
            Next k
        Next j
    Next i

    For i = 1 To ColCount
        For j = 1 To ColCount
            Range("C62").Cells(i, j) = MMult(i, j)
        Next j
    Next i

End Sub

I am successfully able to run this code.

One error that I get was Type mismatch if value in any cell in this range is blank or non-numeric.

If you're getting subscript out of range then you may try using ColCount - 1 or RowCount - 1. Just check if appropriate value exists in Cell(i, j).

Hope this helps!

Vivek

Thanks so much to both of you for your help. Two additional questions have been generated: After making the change and then running the program, it still showed the same error. However, when I pressed 'end' instead of 'debug', and then ran the program again, it worked. Why does pressing 'end' allow it to work? Also, why does the error break on the parts with double-asterisks rather than the ReDim entries, where the array parameter variables were unknown to Excel? — Luke Denham, Mar 31 '13 at 11:56
@LukeDenham, good that it worked for you. Not sure why pressing end worked. It may have cleared previous running states (not sure, just a wild guess). — Vivek Jain, Mar 31 '13 at 15:38
@LukeDenham for your 2nd question, when ReDim was called without initialization, VBA created the counter variables and initialized them with 0. Note that there is no `Option Explicit` defined on the top. _It is a good practice to use `Option Explicit` in VB/A codes which does not allow the program to run without proper variable declarations_. Please accept an answer for other visitors' ease. — Vivek Jain, Mar 31 '13 at 15:42
@LukeDenham you may click on the tick-mark below the vote icons for each answer. [See here](http://meta.stackexchange.com/a/5235). Or, visit the [About section](http://stackoverflow.com/about) and read the **Ask questions, get answers, no distractions** section. This should give you an idea. Cheers! — Vivek Jain, Apr 01 '13 at 09:45

Run-time error '9': Subscript out of range when creating matrix

2 Answers2