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 use Macro

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.

Related Workbook Object articles

You may also like the related Workbook Object articles.

VBA Workbook Object




3 thoughts on “VBA Workbook Reference in Excel”

  1. Hi , I do believe this is an excellent blog. I stumbled upon it on Yahoo , i will come back once again. Money and freedom is the best way to change, may you be rich and help other people.

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

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

Leave a Comment

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