Show Buttons
Share On Facebook
Share On Twitter
Share On Google Plus
Share On Linkdin
Share On Reddit
Contact us
Hide Buttons

WorkBooks and WorkSheets

Work­Book stays at the top of the hier­ar­chy. If we want to retrieve the value from a cell then we have to fol­low the hier­ar­chy to reach to that par­tic­u­lar cell. The Entire Excel file is known as Work­Book and one Work­Book can con­tain many sheets, or to be more pre­cise, many Work­Sheets.
Note: While sav­ing the Excel File , Save it as “Macro Enabled Work­Book” (Select Excel Macro Enabled Work­Book from the Save as type drop down in Save As Dia­log box  ( FileName.xlsm))

Excel-VBA-MACRO-Saving in .xlsm

Excel-VBA-MACRO-Saving in .xlsm

You can access the Work­Sheets by many ways:

  1. Stor­ing the instance of AcitveSheet

Dim work_Sheet as Work­Sheet
Set work_Sheet = Active­Work­Sheet
Once we get the instance then we can use that ref­er­ence wher­ever needed.
Ex: work_Sheet.Range(“A1:B3”).Select

  1. Using ActiveSheet Property

We can directly use the AcitveSheet prop­erty instead of stor­ing its instance but again we avoid using this option since in our macro we may nav­i­gate to many sheets and ActiveSheet will be keep on chang­ing which might cause some trou­ble for us.
Ex: ActiveSheet.Range(“A1:B3”).Select

  1. By Using the Sheet Name

We can access the Work­Sheets by using the Sheet name and this is the most effec­tive way to access a par­tic­u­lar sheet.
Dim main­work­Book as Work­Book
Set main­Work­Book  = Active­Work­Book
we will read it like, select all the Cells from A1 to B3 in the sheet which has the name “MyFirst­Macro” under the ActiveWorkBook.

  1. By Using the Sheet index Val­ues of WorkSheet

We can access the work­sheets by their index val­ues but this is not an effi­cient way in most of the cases. Sup­pose u have writ­ten the macro for Sheet2 which is hav­ing the index value as 2 but if i add another sheet between Sheet1 and Sheet2 then the newly added sheet will be hav­ing the index of 2 and Sheet2 will have the new index of 3, which may cre­ate great con­fu­sion for the com­plier and we may end up by receiv­ing some errors.
Dim main­work­Book as Work­Book
Set main­Work­Book  = Active­Work­Book

Open a Excel (Work­Book) from a par­tic­u­lar loca­tion
Application.Workbooks.Open (Fold­er­path & “\” & CompleteFileName )

Sav­ing a Excel (Work­Book)
Dim main­work­Book as Work­Book
Set main­Work­Book  = Active­Work­Book

Clos­ing a Excel(WorkBook):
Dim main­work­Book as Work­Book
Set main­Work­Book  = Active­Work­Book



You may also like...

Leave a Reply

Your email address will not be published. Required fields are marked *

%d bloggers like this: