VBA Workbook Reference

VBA Workbook Reference in Excel

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.

ActiveWorkbook Reference

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.
Active Workbook Reference

ThisWorkbook Reference

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.
This Workbook Reference

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”

  1. 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.

  2. 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.

  3. yeezy boost 350 v2

    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.

Leave a Reply