CODEWITHSUNDEEP
excelvba
Shyam
Shyam

Reputation: 19

VBA 2016 compatibility issue

The code works fine in Excel 2013 and Excel 2010 - but in Excel 2016 it shows error:

Run time error 9 - subscript out of range

Can you help. The code just sorts a column and hides it.

Sub abc()
    Sheets("Top_Issue").Visible = True
    Sheets("Top_Issue").Select
    lastrow = Cells(Rows.Count, "a").End(xlUp).Row
    Range(Cells(1, "p"), Cells(lastrow, "p")).Select
    ActiveWorkbook.Worksheets("Top_Issue").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Top_Issue").Sort.SortFields.Add Key:=Range("P1"), _
    SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Top_Issue").Sort
        .SetRange Range("A2:S" & lastrow)
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Sheets("Top_Issue").Visible = False
End Sub

Upvotes: 2

Views: 241

Answers (2)

Mathieu Guindon
Mathieu Guindon

Reputation: 71187

You have implicit references to the ActiveSheet here:

lastrow = Cells(Rows.Count, "a").End(xlUp).Row
Range(Cells(1, "p"), Cells(lastrow, "p")).Select

The above unqualified Range member calls are equivalent to this:

lastrow = ActiveSheet.Cells(ActiveSheet.Rows.Count, "a").End(xlUp).Row
ActiveSheet.Range(ActiveSheet.Cells(1, "p"), ActiveSheet.Cells(lastrow, "p")).Select

Code that uses .Select/.Activate and implicitly refers to the active sheet is very likely to eventually blow up whenever an assumption is broken, for example when the ActiveWorkbook isn't the workbook the code is assuming.

You're pulling the object reference for that Top_Issue sheet 6 times; sometimes from ActiveWorkbook, sometimes using the Sheets collection, other times using the Worksheets collection, with .Select calls in between.

Start with a With block, and make sure all Range and Worksheet member calls are properly qualified:

With ActiveWorkbook.Worksheets("Top_Issue")
    .Visible = True
    .Select
    lastrow = .Cells(.Rows.Count, "a").End(xlUp).Row
    .Range(.Cells(1, "p"), .Cells(lastrow, "p")).Select
    .Sort.SortFields.Clear
    .Sort.SortFields.Add Key:=.Range("P1"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With .Sort
        .SetRange .Range("A2:S" & lastrow)
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    .Visible = False
End With

If the Top_Issue sheet exists at compile-time in ThisWorkbook, set its (Name) property (in the Properties toolwindow F4) to, e.g. TopIssueSheet, and use that identifier instead of pulling from the Worksheets collection.

Note that ActiveWorkbook (the currently active book) may or may not be ThisWorkbook (the book that contains this code) - if the wrong book is active, and it doesn't have a Top_Issue sheet, that's why you're getting run-time error 9.

Using a code name for the worksheet, you no longer need to care about which workbook might be active:

With TopIssueSheet
    ...
End With

You should only ever need to pull a worksheet from a workbook's Worksheets collection when you're working off a workbook that isn't ThisWorkbook.

Upvotes: 2

Mihai Adrian
Mihai Adrian

Reputation: 654

Try to use Sheets code instead of Worksheets name. Sheet1, etc...

Also, note that Sheets collection and Worksheets collection are two different things.

Upvotes: 1

Related Questions