Error 9 VBA - Subscript out of Range appears every other run

Question

The code below, located in my access database, runs fine half of the time. The other half it gives me: "error 9 subscript out of range," and it highlights the "Set WKS = Workbooks(..." line as the problem. I know that the problem is with it being unsure which workbook it is referencing. However I don't know what to do to make it clearer.

Sub fixborderss() Dim WKS As Excel.Worksheet Dim lastrow As Long Set WKS = Workbooks("L3 PSR.xls").Worksheets("L-3 Project Status Report") lastrow = Range("I" & WKS.Rows.Count).End(xlUp).Row WKS.Range("A8:V" & lastrow).Borders(xlEdgeTop).Color = RGB(191, 191, 191) WKS.Range("A8:V" & lastrow).Borders(xlEdgeBottom).Color = RGB(191, 191, 191) WKS.Range("A8:V" & lastrow).Borders.LineStyle = xlContinuous End Sub

I'm guessing somewhere you have an object variable which references the active Excel.Application instance which has the workbook opened. Qualify Workbooks with that object variable ... Set WKS = objExcel.Workbooks( — HansUp
– HansUp, Commented Mar 29, 2016 at 19:42
Later, if you encounter a similar problem at lastrow = Range(, change that to lastrow = WKS.Range( — HansUp
– HansUp, Commented Mar 29, 2016 at 19:45
Hey HansUp - much thanks! Problem solved - it runs correctly 100% of the time. I used Set XL = GetObject("C:\Users\****\Desktop\L3 PSR.xls").Application to find the instance in question, and threw that in the front of the WKS variable assignment. Thanks again. — Alexander Bradley
– Alexander Bradley, Commented Mar 29, 2016 at 20:12

SierraOscar · Accepted Answer · 2016-03-29 20:17:43Z

You have a couple of options depending on how your code is constructed:

1) Make XL a public variable:

Public XL As Excel.Application '// Declare at top of module, outside of any subs/functions

2) Pass Excel to the other sub:

Set XL = New Excel.Application '// ... more code My_Other_Sub XL '// Call sub and pass Excel object '// Some more code End Sub Public Sub My_Other_Sub(ByRef XL As Excel.Application) Set ws = XL.Workbooks(1).Sheets(1) '// Other code End Sub

3) Use the GetObject() method if Excel is already open.

Set XL = GetObject(, "Excel.Application") If Not XL Is Nothing Then Set ws = XL.Workbooks(1).Sheets(1) End If

Collectives™ on Stack Overflow

Error 9 VBA - Subscript out of Range appears every other run

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related