Macros in Excel : Learn Excel VBA : Objects

It is important to understand Visual Basic Grammar to better understand the macros recorded by you. If you are new to Macros in excel I suggest that you go through earlier posts on Macros
1. Automating Tasks in Excel : Using Macros in Excel
2. Recording Excel Macros / Writing excel macros (VBA)


Visual Basic is object oriented language, it means all items in Excel are considered as objects. There are lots of them but following example will help us understand what an Object is.

   - WorkBook
   - WorkSheet
   - Range
   - Chart
   - Legend

In a Macro Range("A1") is an object. An object can contain other objects.
WorkBook is the larger object followed by WorkSheet and Range.

Workbooks("Macros Book.xls").Worksheets("Data").Range("A1")

Code above refers to Cell "A1" on worksheet "Data" in "Macros Book.xls" file. This is long description but can easily be shortened. The worksheet containing the cursor is called Activesheet, similarly workbook containing active sheet is called Activeworkbook

ActiveWorkbook.ActiveSheet.Range("A1")

There is no difference in the previous line and line above in your macro code.

In case you do not refere larger object, Excel Macro will use ActiveWorkBook and ActiveSheet. So if you write Range("A1") it means same if you have your cursor at Worksheet "Data" in "Macros Book.xls" file while running this code.

Table below will explain the use of various objects on Excel VBA

WorkBooks - Referes to all workbooks currently open in Excel
WorkBooks.Item(1) - Referes to first workbook
WorkBooks.(1) - Referes to first workbook
WorkBooks.(Macros Book.xls) - Referes to Macros Book.xls file open

Sheets - Referes to all sheets in workbook both chart sheets and worksheets
Sheets(1) - Referes First sheet on tab bar.
Sheets("Data") - Refer to Sheet called Data