run time error 9 subscript out of range when selecting workbook

I am trying to build a data entry form in Excel 2010 using VBA. During form initialization I am setting an object to refer to another worksheet so I can access it. This is what I have:

Private Sub UserForm_Initialize()

Dim master As Excel.Workbook
Dim masterworksheetFmn As Excel.Worksheet
Dim masterworksheetAdv As Excel.Worksheet
Dim masterworksheetTechs As Excel.Worksheet

Dim i As Integer

Set master = Excel.Workbooks("ServiceReturnsMaster.xlsm")
Set masterworksheetFmn = master.Worksheets("Foremen")
Set masterworksheetAdv = master.Worksheets("Advisors")
Set masterworksheetTechs = master.Worksheets("Techs")

When I run the macro, I get "Run Time error9: Subscript out of range". This occurs at this line:

Set master = Excel.Workbooks("ServiceReturnsMaster.xlsm")

HOWEVER, the error does not occur if I open the 2nd workbook before running the macro. I am assuming I have to activate the second workbook or something first or my reference is not written correctly. I can find lots of references to this run-time error, but none that directly address what I'm trying to do. What am I doing wrong?

1 answer

  • answered 2018-03-20 21:19 Macro Man

    When you use Excel.Workbooks you are referring to a generic Workbooks collection. If you want to access the Workbooks collection that is in your current application, then you need to use Application.Workbooks.

    You can also use it without the Application qualifier as it's assumed you are working in the current application instance, so

    Set master = Application.Workbooks("ServiceReturnsMaster.xlsm")


    Set master = Workbooks("ServiceReturnsMaster.xlsm")

    Should both work identically.

    Alternatively, if your workbook isn't open at runtime, then you need to use the Workbooks.Open() method instead:

    Set master = Workbooks.Open("C:\Path\to\ServiceReturnsMaster.xlsm")