WorkBooks and WorkSheets

Workbook->WorkSheet->Column->Row
WorkBook stays at the top of the hierarchy. If we want to retrieve the value from a cell then we have to follow the hierarchy to reach to that particular cell. The Entire Excel file is known as WorkBook and one WorkBook can contain many sheets, or to be more precise, many WorkSheets.
Note: While saving the Excel File , Save it as “Macro Enabled WorkBook” (Select Excel Macro Enabled WorkBook from the Save as type drop down in Save As Dialog box  ( FileName.xlsm))

You can access the WorkSheets by many ways:

1. Storing the instance of AcitveSheet

Dim work_Sheet as WorkSheet
Set work_Sheet = ActiveWorkSheet
Once we get the instance then we can use that reference wherever needed.
Ex: work_Sheet.Range(“A1:B3”).Select

2. Using ActiveSheet Property

We can directly use the AcitveSheet property instead of storing its instance but again we avoid using this option since in our macro we may navigate to many sheets and ActiveSheet will be keep on changing which might cause some trouble for us.
Ex: ActiveSheet.Range(“A1:B3”).Select

3. By Using the Sheet Name

We can access the WorkSheets by using the Sheet name and this is the most effective way to access a particular sheet.
Dim mainworkBook as WorkBook
Set mainWorkBook  = ActiveWorkBook
mainWorkBook.Sheets(“MyFirstMacro”).Range(“A1:B3”).Select
we will read it like, select all the Cells from A1 to B3 in the sheet which has the name “MyFirstMacro” under the ActiveWorkBook.

4. By Using the Sheet index Values of WorkSheet

We can access the worksheets by their index values but this is not an efficient way in most of the cases. Suppose u have written the macro for Sheet2 which is having the index value as 2 but if i add another sheet between Sheet1 and Sheet2 then the newly added sheet will be having the index of 2 and Sheet2 will have the new index of 3, which may create great confusion for the complier and we may end up by receiving some errors.
Dim mainworkBook as WorkBook
Set mainWorkBook  = ActiveWorkBook
mainWorkBook.Sheets(1).Range(“A1:B3”).Select

Open a Excel (WorkBook) from a particular location
Application.Workbooks.Open (Folderpath & "\" & CompleteFileName )

Saving a Excel (WorkBook)
Dim mainworkBook as WorkBook
Set mainWorkBook  = ActiveWorkBook
mainWorkBook.Save

Closing a Excel(WorkBook):
Dim mainworkBook as WorkBook
Set mainWorkBook  = ActiveWorkBook
mainWorkBook.Close

Also Read:

1. VBA-Excel: SUDOKU Solver
2. VBA-Excel: Get all the WeekDays or Working days in Specified Date Range, (excluding Satudays and Sundays)
3. VBA-Excel: Create or Add Worksheets at the Run time.
4. Introduction to Excel WorkBook
5. Excel-VBA : Change Passwords for all the WorkSheets in one shot