We can reference workbook in different ways. Let us see how to reference workbook in many ways. While reference Workbooks there might be some confusion, need to know which reference method is best or most efficient to apply. Each reference method has its own value to it.
Let us see active Workbook reference in the following way. This active Workbook may or may not contain VBA code. We can represent active workbook as ‘ActiveWorkbook’. For example we may want to know the active workbook name while handling with multiple workbooks.
'VBA Active Workbook Reference in Excel Sub VBA_Active_Workbook_Reference() MsgBox "Active Workbook Name is : " & ActiveWorkbook.Name, vbInformation, "Active Workbook Name" End Sub
Here is the output screen shot of above macro.
The following example shows the ThisWorkbook reference in the following way. ThisWorkbook contains the current VBA code. We can represent this workbook as ‘ThisWorkbook’. For example we may want to know the thisworkbook name while handling with multiple workbooks.
'VBA This Workbook Reference in Excel Sub VBA_This_Workbook_Reference() MsgBox "This Workbook Name is : " & ThisWorkbook.Name, vbInformation, "This Workbook Name" End Sub
Let us see the output screen shot of above macro.
We can reference Workbook by Index by index value in the following way. When we open workbook every time it assigns index value to each workbook. The index value starts with 1. Assign value one(1) for first opened workbook, value two(2) to second opened workbook and value n for nth opened workbook.
When we reference workbook with its index value, we should be more cautious. Need understand which Workbook is opened after which Workbook to avoid mistakes.
'VBA Reference Workbook by Index in Excel Sub VBA_Reference_Workbook_by_Index() 'Activate first Workbook Workbooks(1).Activate 'Activate Third Workbook Workbooks(3).Activate End Sub
In the above example 1 and 3 values represents Workbook index values. First it activates the first Workbook and secondly it activates the third Workbook.
The below example shows how to reference Workbook by object. In the below example ‘oWorkbook’ is an object which is referencing to Workbook.
'VBA Reference Workbook by Object in Excel Sub VBA_Reference_Workbook_by_Object() 'Variable declaration Dim oWorkbook As Workbook Dim sFilePath As String sFilePath = "D:\VBAF1\VBA Functionsa.xlsm" 'Reference Workbook by Object named oWorkbook Set oWorkbook = Workbooks.Open(sFilePath) End Sub
Let us see the reference Workbooks in Workbooks Collection in the following way.
'VBA Reference Workbooks in Workbooks collection in Excel Sub VBA_Reference_Workbooks_in_Workbooks_collection() 'Variable declaration Dim wWorkbook As Workbook 'Loop through Workbooks in Workbooks collection For Each wWorkbook In Workbooks Workbooks(wWorkbook).Name Next wWorkbook End Sub
It is very helpful when we work with multiple workbooks. The below example helps to activate workbooks by its Workbook name.
'VBA Reference Workbook Explicitly in Excel Sub VBA_Reference_Workbook_Explicitly() 'Variable declaration Dim sWorkbook1 As String Dim sWorkbook2 As String 'Activate first Workbook Workbooks(sWorkbook1).Activate 'Activate second Workbook Workbooks(sWorkbook2).Activate End Sub
Here are the instructions to use above macro procedure in Visual basic editor.
- Open Visual Basic Editor(VBE) by clicking Alt +F11
- Go to code window by clicking F7
- Copy above specified macro or procedure
- Paste above copied code in code window
- Run macro by clicking F5 or Run command
- You can see output on the screen
- Find above output screenshot of the specified procedure.
You may also like the related Workbook Object articles.