run time error 9 subscript out of range when selecting workbook

Question

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?

Do a find & replace and get rid of all Excel. strings. You are working within the application object by default. And yes, the workbook has to be open in the same application instance but it does not have to be the active workbook. — user4039065
@ScottCraner - can you set a workbook object to an unopened workbook without opening it? I typically use set master = workbooks.open(... ) — user4039065

SierraOscar SierraOscar · Accepted Answer · 2018-03-20T21:19:00

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")

or

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")

run time error 9 subscript out of range when selecting workbook

1 Answers