Excel 2013 VBA alternative to using Activate and S

Question

So i took a look at how to avoid using Select and Activate in the other post Here:

How to avoid using Select in Excel VBA macros

However, that other post still fails to mention how I could avoid using Activate or Select in some VBA code like this example below:

ThisWorkbook.Sheets("Data").Activate
ThisWorkbook.Sheets("Data").Select
Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
With Selection.Interior
    .PatternColorIndex = xlAutomatic
    .ThemeColor = xlThemeColorDark1
    .TintAndShade = -0.149998474074526
    .PatternTintAndShade = 0
End With

I need to figure out how I can avoid using .Activate and also how to avoid using .Select because this code gives me an error on BOTH of these.....

these errors started happening after I upgraded from Excel 2010 to Excel 2013...

Please help !

Answer 1

The key is to always use the full reference in a way such as:

ExcelFile.SheetInFile.CellOrRangeOnSheet.PropertyOfSaidCellOrRange = something

If you do that then you don't have to select anything first but you can directly address the cell or the range's property (be it its value or interior color or anything else).

With the above your code essentially transforms to the following:

With ThisWorkbook.Sheets("Data")
    With .Range(.Range("A1"), .Range("A1").End(xlToRight)).Interior
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = -0.149998474074526
        .PatternTintAndShade = 0
    End With
End With

Note, that your code merely requires selection in between because whenever you fail to explicitly reference something then the currently active or selected cell is used. If you explicitly state the file, sheet, cell, and range in full each time then you don't have to select them anymore because VBA does not have to assume that Range(Selection, Selection.End(xlToRight)).Select refers to ActiveWorkbook.ActiveWorksheeet.Range.....etc..