VBA Workbook Reference in Excel. 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.
Reference Workbook by Index
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.
Reference Workbook by Object
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
Workbooks in Workbooks Collection Reference
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
Reference Workbook Explicitly(By Name)
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
Instructions to Run VBA Macro Code or Procedure:
You can refer the following link for the step by step instructions.
Instructions to run VBA Macro Code
Other Useful Resources:
Click on the following links of the useful resources. These helps to learn and gain more knowledge.
VBA Tutorial VBA Functions List VBA Arrays in Excel Blog
VBA Editor Keyboard Shortcut Keys List VBA Interview Questions & Answers
4 thoughts on “VBA Workbook Reference in Excel”
Hi, I do believe this is an excellent site. I stumbledupon it
😉 I may revisit once again since I saved as a favorite it.
Money and freedom is the greatest way to change, continue to guide other people.
Hi there, every time i used to check weblog posts here early in the
morning, as i like to gain knowledge of more and more.
I simply had to appreciate you again. I’m not certain the things that I would have carried out in the absence of these suggestions discussed by you over such area. This has been an absolute daunting setting in my circumstances, but being able to see this well-written technique you managed the issue made me to leap for joy. I’m happy for this assistance and then expect you are aware of a great job you are carrying out educating others all through your webpage.
A lot of thanks for every one of your labor on this website. My mom really likes working on research and it is obvious why. We know all of the powerful method you render sensible tactics on this web site and in addition encourage response from people on this subject matter then our princess is undoubtedly studying a lot. Have fun with the rest of the year. Your performing a tremendous job.