Avoiding the use of worksheet.activate in order to execute code

Question

This should be easy. I would like to reduce the usage of the code worksheets("Sheet1").activate as it is cumbersome and faulty oftentimes, however all the attempts I have tried out they do not work. So far I have used the set worksheet option and with worksheet option but unless I am strictly on the worksheet sheet 1 on Excel , the code does not execute.

I have tried some of the options listed in this link but none of them worked: How to avoid using Select in Excel VBA. I am really starting to think that I need to use activate all the time.

For example, a code like:

Sub test()
Dim Cols As integer
Cols = Worksheets("Sheet1").Range(Cells(1, 1), Cells(1, 1).End(xlToRight)).Count
End sub

I would expect that this code execute even if I am not situated on Sheet1 since I have specifically defined the worksheet in which it must be executed. However, unless I am on that worksheet, the code does not work. As background information, I run most of my codes under subroutines in the modules section of VBA.

score 2 · Accepted Answer · answered May 29 '19 at 09:26

You're quite close to doing it right.

You need to remember to refer to a WB and WS for each range object. Otherwise, VBA will refer to the active WB and WS

In Worksheets("Sheet1").Range(Cells(1, 1), Cells(1, 1).End(xlToRight)).Count I count two Cells range objects that need referencing.
Additionally, your workbook is not specified for the sheet you're referring to. If there is no sheet named "Sheet1" in the active workbook, you will get a Subscript out of Range error.

To prevent such errors, it is good practice to use a With...End With statement. That way you will only have to specify the workbook and -sheet first, preventing cluttered code.

So, it would be:

With Workbooks(REF).Sheets("Sheet1")
    .Range(.Cells(1, 1), .Cells(1, 1).End(xlToRight)).Count
End With

score 1 · Answer 2 · answered May 29 '19 at 09:26

See if this helps understanding what was missing:

Option Explicit
Sub test()

    'Your code:
    Cols = Worksheets("Sheet1").Range(Cells(1, 1), Cells(1, 1).End(xlToRight)).Count 'Cells inside the range are not qualified

    'Corrected code:
    Cols = Worksheets("Sheet1").Range(Worksheets("Sheet1").Cells(1, 1), Worksheets("Sheet1").Cells(1, 1).End(xlToRight)).Count

    'Above code is too long right?

    'Option 1        
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("Sheet1")
    Cols = ws.Range(ws.Cells(1, 1), ws.Cells(1, 1).End(xlToRight)).Count

    'Option 2
    With ThisWorkbook.Sheets("Sheet1")
        .Range(.Cells(1, 1), .Cells(1, 1).End(xlToRight)).Count
    End With

    'Changing your code works with any option:
    With ThisWorkbook.Sheets("Sheet1")
        .Cells(1, .Columns.Count).End(xlToLeft).Column 'This option goes from right to left to find the last column with data
    End With

End Sub

Also, always qualify worksheets and workbooks might seem that with the worksheet is enough, but if you work with more than 1 workbook not qualifying it will be a problem too.

Avoiding the use of worksheet.activate in order to execute code

2 Answers2