When do I need to explicitly specify a workbook and worksheet in Excel VBA?

Question

Most of the Excel code that I write is pretty simple - it is contained in one book. For example, I will have code in a module Sheet1that will just read:

Private Sub DoSomethingButton_Click()

    'Run subroutine in Module1
    DoSomething  

End Sub

And then the DoSomethingsub in Module 1 will perform the actions that I want to perform on Sheet1. When I reference the cells inside Sheet1, I can use Cells(i, j)for my heart content, and if I need to access something on another sheet, I just use Worksheets(i).Cells(j, k).

• My question is: should I always indicate on which sheet I intend to work? When will I get in trouble if I don’t?

• In the same vein: if I use only one book, do I need to specify ThisWorkbook? And if I use several books, do I need to specify the name of the workbook for all interactions with sheets, or is it enough just to change the active workbook / worksheet, and then just use it Cells(i, j)?

• Finally, is there a difference between Cells(i, j)and ActiveWorksheet.Cells(i, j)?

Answer 1

• More thorough and effective practice fully references the range reference information, even if you are working on a local sheet. Then the code can

  • be more easily adapted
  • easier to read and
  • less likely to have problems

  code

  Dim ws As Worksheet
Set ws = ActiveSheet
ws.Cells(1, 1) = 10

  preferable

  Cells(1, 1) = 10

• , (1), Workbook ThisWorkbook. , addin, ThisWorkbook .

• . - , Cells.

Sheet1 Sheet2,

Set ws = Sheets(2)
Set rng1 = ws.Range(Cells(1, 1), Cells(10, 10))

Set ws = Sheets(2)
Set rng1 = ws.Range(ws.Cells(1, 1), ws.Cells(10, 10))

, Worksheet, ( ActiveSheet).