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
    'Activate Third Workbook
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
    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
    'Activate second Workbook
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

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