Setting Workbook object I get error '9': subscript out of range

Question

I want to copy information from cells in M79to PAlysis.

My Sub PopulateFields is located in PAlysis.

What is wrong with my reference to a different file?

Sub PopulateFields()
    Dim Mur As Workbook, TOMS As Workbook, i As Integer, LastRow As Integer, j As Integer

    Set Mur = Workbooks("S:\M\ BPM\M79.xls")
    Set TOMS = Workbooks("S:\M\BPM\PAlysis.xlsm")
    Set TOMSPos = TOMS.Worksheets("Positions")
    Set TOMSAna = TOMS.Worksheets("Analysis")
    Set MurexWs = Murex.Worksheets("BB_Overview")

    LastRow = Murex.Cells(MurexWs.Rows.Count, 1).End(xlUp).Row

    j = 3
    For i = 3 To LastRow - 1
        If Mur.MurexWs.Cells(i, 2).Value = "Bond" Then
            Mur.MurexWs.Cells(j, 6).Copy TOMS.TOMSPos.Cells(i + 1, 1)
            j = j + 1
        Else
            j = j + 2
        End If
    Next i
 End Sub

In the lineSet Mur = ... I get

Error 9: Subscript out of range.

Note that you need to use `Long` instead of `Integer` for row counting variables. Excel has more rows than `Integer` can handle! It is recommended [always to use Long instead of Integer](https://stackoverflow.com/a/26409520/3219613) in VBA. • Also note that `Set Mur = Workbooks("S:\M\ BPM\M79.xls")` assumes that the file is already opened. Otherwise you need to use the [Workbooks.Open Method](https://learn.microsoft.com/en-us/office/vba/api/excel.workbooks.open) to open a file from hard disk. — Pᴇʜ, Oct 18 '18 at 12:31

score 1 · Answer 1 · answered Oct 18 '18 at 13:12

You could use the following to either get an already opened workbook, or open it if it is not opened.

Sub test()   
    Set mur = GetOrOpenWorkbook("S:\M\BPM\", "M79.xls")
    Set toms = GetOrOpenWorkbook("S:\M\BPM\", "PAlysis.xlsm")
End Sub

Public Function GetOrOpenWorkbook(Path As String, Filename As String) As Workbook
    'test if workbook is open
    On Error Resume Next
    Set GetOrOpenWorkbook = Workbooks(Filename)
    On Error GoTo 0

    'if not try to open it
    If GetOrOpenWorkbook Is Nothing Then
        Set GetOrOpenWorkbook = Workbooks.Open(Filename:=Path & Filename)
    End If
End Function

FunThomas · Accepted Answer · 2018-10-18T12:52:51.540

I assume that you want to open the workbooks: You have to use Workbooks.open. This opens a workbook in Excel (basically the same as opening it via File->Open in Excel)

Set Mur = Workbooks.open("S:\M\ BPM\M79.xls")

(not sure about the space before BPM - check if this is a typo.

If your workbook is already open, the command would be

Set Mur = Workbooks("M79.xls")

This is the syntax for VBA Collections where you can access an object either by (numeric) index or via it's name. The name of a workbook within the Workbooks-collection is the filename, but without the path (this is the reason that you cannot open 2 workbooks with the same name, even if they are stored in different folders).
When you try to access a member of a collection that doesn't exist, VBA will throw the Runtime Error 9.

Setting Workbook object I get error '9': subscript out of range

2 Answers2