How to fix a Runtime Error 9 on Range Activation that occurs on Office 2013, but not on Office365

Question

A line of code, that seems to work fine in Office 2016 and Office 365, runs into a Runtime Error 9 when using Office 2013.

Workbooks.Open Filename:="W:\06 Project Management Master\Project Resource Tool\Backend Database\Backend Database.xlsm"

Workbooks("Backend Database").Worksheets("Home").Activate
Workbooks("Backend Database").Worksheets("Home").Range("D15").Activate

Specifically, it is the line that activates Range("D15") in a second workbook that is not the workbook that contains the VBA code. The workbook and worksheet exist, as is evident since it works on the other versions of Office, but for the life of me, I can't work out what the issue is here - other than the version of Office.

I have tried putting the statement into a With Workbooks("Backend Database").Worksheets("Home") statement, but it runs into the Runtime Error when Range("D15").Activate line arrives.

Does anyone have any suggestions, apart from update the computer on Office 2013 to Office 365? I tried asking IT for that and got understandably laughed out, lol. I'm hoping someone has come across this before and knows how to address it.

To `.Select`or `.Activate` a range both the workbook and worksheet where the range is must be on the focus or it will throw an error, try to first activate the workbook and worksheet before this. Anyway, you should try to avoid using `.Select` or `.Activate` — Damian, Apr 02 '19 at 14:43
Always include the file extension on the name when using `Workbooks()`. Better still, set a variable when you open the workbook and use that. — Rory, Apr 02 '19 at 14:45

score 1 · Answer 1 · answered Apr 02 '19 at 14:49

Avoid using Select or Activate statements completely (unless in the rare cases you absolutely cannot avoid using these)

This SO thread will help you avoiding those in the future

If you want to change the value of a range, you could do:

'Workbooks("Backend Database").Worksheets("Home").Range("D15").Activate 'Disregard
Workbooks("Backend Database").Worksheets("Home").Range("D15").Value = "NewValue"

Additionally, but not related to your question, you could (perhaps should, for your own sake), use With...End With if you repeatedly refer to an object or range. For example:

With Workbooks("Backend Database").Sheets("Home") 
    .Range("A1").Font.Bold = True
    .Range("D15").Value = "NewValue"
End With

score 0 · Answer 2 · answered Apr 02 '19 at 15:06

The Workbooks.Open method returns a reference to the opened Workbook object, but you're discarding it. Simply capture it as hinted by Rory:

Dim wb As Workbook
Set wb = Workbooks.Open(Filename:="W:\06 Project Management Master\Project Resource Tool\Backend Database\Backend Database.xlsm")

Now you don't need to dereference the object from the Workbooks collection anymore and hard-code the file name in 20 places - just use that wb variable whenever you need to refer to anything in that workbook - same for worksheets:

Dim homeSheet As Worksheet
Set homeSheet = wb.Worksheets("Home")

With homeSheet
    .Range("D15").Value = "Hello"
    '...
End With

I'll second Tim Stack in heavily encouraging you to avoid Select and Activate whenever possible.

How to fix a Runtime Error 9 on Range Activation that occurs on Office 2013, but not on Office365

2 Answers2