Different Methods to Refer Excel Sheet or Cell

Hierarchy:

As a User, When we edit data in excel document manually we use keyboard or mouse pointer to traverse through different worksheets or cells. It is not required to understand it’s object model. This is true with any software with a good GUI, not only with Excel.

But when we step in as VBA(Visual Basic for Applications) Developer, it would be useful to know the basics about the hierarchy of how object are arranged. In any Excel macro we have to refer a cell in the below order.

• Workbook
  • Worksheet
    • Cells
      • Row and Column

Now we have a route map. Lets get into some technical part starting with syntax for each component.

Workbook:

To refer a workbook in a Macro, we can use one the below listed methods.

1. ThisWorkBook:- This will refer the workbook in which the macro is getting executed. Examples: Thisworkbook.Sheets(1).Cells(1,1) or Thisworkbook.Name or Thisworkbook.Path
2. ActiveWorkbook : – This is to refer the workbook which is currently active at your desktop. Examples: Activeworkbook.Sheets(1).Cells(1,1) or Activeworkbook.Name or Activeworkbook.Path
3. WorkBooks:– For referring a workbook with its actual name or index, WorkBooks object can be used. Examples: Workbooks(1).Sheets(1).Cells(1,1) or Workbook(“Book1”).sheets(1).cells(1,1)

WorkSheet:

Now, we know how to reference a workbook in macro. Similar to this, a worksheet can be referred using below keywords.

1. Using Index of the Sheet: ThisWorkBook.Sheets(1).Name
2. Using Name of the Sheet: ThisWorkBook.Sheets(“Sheet1”).Name
3. ActiveSheet: ActiveSheet.Name

Cells:

1. Using Row & Column: ThisWorkBook.Sheets(1).cells(1,1) – Refer the value or cell in row 1 and column 1 (ie., cell A1)
2. Using Range: ThisWorkBook.Range(“A1”).Value – Refer the value or cell in row 1 and column 1 (ie., cell A1)

These are some of the very basic samples on how a value in a excel sheet can be referenced in a VBA Macro.

Sample Program:

• Create a new workbook.
• Press Alt+F11 to get VB Editor.
• Copy paste below Code.
• Press F8 (line by line interpreter) to know how each line refers a sheet in different styles.

Private Sub Testing()
    'Create a New excel workbook and place this code in VBE
    MsgBox ThisWorkbook.Name

    'Fetch Value from Cells A1
    MsgBox ThisWorkbook.Sheets(1).Cells(1, 1)
    MsgBox ActiveWorkbook.Sheets(1).Range("A1")
    VariableA1 = ThisWorkbook.Sheets(1).Cells(1, 1)
    MsgBox VariableA1

    'Change a Value in ExcelSheet
    ThisWorkbook.Sheets(1).Cells(1, 1) = 1
    ActiveWorkbook.Sheets(1).Range("B1") = "=A1*100"
    VariableB1 = ThisWorkbook.Sheets(1).Cells(1, 2)
    MsgBox VariableB1

End Sub

Note: 

Some Programs does not use ‘Workbooks’ or ‘Thisworkbook’ or even ‘Sheets’ object. It is implicit that they refer to ActiveWorkBook and ActiveSheet at such instances.