What is Excel VBA ThisWorkbook?
Excel provides the VBA ThisWorkbook property to represent the workbook where the VBA macro is running. It is beneficial when you have multiple workbooks open and want to reference the specific workbook that contains your macro, regardless of which workbook is active.
Let’s look at an example. In this example, we will rename the first worksheet of the workbook containing the macro code.
The macro initiates by declaring a subroutine named RenameFirstWorksheet. Within this subroutine, ThisWorkbook.Worksheets(1) is used to target the first worksheet of the current workbook. By utilizing the VBA ThisWorkbook.Names property, we are given the capability to either fetch or modify the name of the targeted worksheet.
We choose to set the worksheet’s name to “DataSheet.” Upon executing this macro, the first worksheet’s name will be changed to “DataSheet.”
Table of contents
- VBA ThisWorkbook in Excel VBA refers to the workbook where the macro code is housed, ensuring you always reference the correct source code.\
- VBA ThisWorkbook is a valuable tool when working with named ranges, protecting/unprotecting workbooks, and many other tasks.
- VBA ThisWorkbook is distinct from ActiveWorkbook, which refers to the currently active or viewed Excel workbook.
- VBA ThisWorkbook provides a stable and reliable way to refer to the workbook that contains your VBA code, irrespective of the currently active workbook.
- You can manipulate data, save changes, and interact with different elements of the workbook directly using VBA ThisWorkbook, without needing to activate or select the workbook.
Working with ThisWorkbook in Excel VBA
When programming in VBA, you often must reference a specific workbook, be it to retrieve data, write data, or manipulate its properties. VBA ThisWorkbook allows you to reference the workbook that contains your macro code directly.
Utilizing VBA ThisWorkbook effectively can enhance the functionality of your Excel macros, ensuring that they operate on the correct workbook, even if it is not the currently active one.
Below are some examples of working with VBA ThisWorkbook:
#1 – Accessing Worksheets:
You can access the worksheets in VBA ThisWorkbook directly.
Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets(“SheetName”)
In this example, we’re setting a reference to a specific sheet in VBA ThisWorkbook by its name.
#2 – Modifying Workbook Properties:
VBA ThisWorkbook provides access to a variety of workbook properties.
ThisWorkbook.Title = “My Workbook Title”
Here, we’re setting the title property of the workbook.
#3 – Saving the Workbook:
You can save the workbook using the VBA ThisWorkbook.Save method.
This line of code saves the workbook that contains the macro.
#4 – Closing the Workbook:
Similarly, you can close the workbook with the Close method.
This example closes ThisWorkbook without saving changes. Change SaveChanges to True if you want to save before closing.
#5 – Working with Named Ranges:
You can also interact with named ranges in ThisWorkbook.
Dim rng As Range
Set rng = ThisWorkbook.Names(“MyNamedRange”).RefersToRange
In this code, we are setting a reference to a named range called “MyNamedRange”.
#6 – Referencing Workbook Path and Name:
ThisWorkbook allows you to get information about the workbook’s location and name using VBA ThisWorkbook.Names and VBA ThisWorkbook.Path methods.
Dim path As String
Dim name As String
path = ThisWorkbook.Path
name = ThisWorkbook.Name
Here, the path will contain the path to the workbook, and the name will contain the name of the workbook.
#7 – Protecting and Unprotecting the Workbook:
You can protect and unprotect the structure and windows of the workbook.
ThisWorkbook.Protect Password:=”yourpassword”, Structure:=True, Windows:=False
In this example, we’re protecting the workbook structure with a password and then unprotecting it.
Excel VBA – All in One Courses Bundle (35+ Hours of Video Tutorials)
If you want to learn Excel and VBA professionally, then Excel VBA All in One Courses Bundle (35+ hours) is the perfect solution. Whether you’re a beginner or an experienced user, this bundle covers it all – from Basic Excel to Advanced Excel, Macros, Power Query, and VBA.
Example #1 – Displaying the Path of ThisWorkbook
In this example, we will see how to display a message box containing the file path of the workbook that contains this code.
Step 1: Open Excel and press ALT + F11 to open the VBA editor.
Step 2: Insert a new module by clicking on “Insert” in the menu and selecting “Module.”
Step 3: Here, we are declaring a new subroutine named DisplayPath. Every VBA code that performs an action is housed within a subroutine (or function).
Step 4: In this step, the MsgBox function is used to display a message box to the user. The content to be shown in the message box is ThisWorkbook.Path.
ThisWorkbook refers to the workbook where the VBA code is running.
“Path” is a property of a workbook object that returns the directory or folder where the workbook is located.
Together, VBA ThisWorkbook.Path retrieves the folder location of the workbook containing the VBA code.
Step 5: Upon executing this code, a message box in VBA pops up, showing the complete file path (directory location) of the workbook where this macro is stored, aiding users in quickly identifying where their file is saved on the computer.
Here is the full code:
Example #2 – Activating a Worksheet and Inputting a Value
In this example, we will understand how to access a specific worksheet named “Sheet2” within the workbook containing the macro, set it as the active worksheet, and then input the text “Hello, World!” into cell A1 of that worksheet.
Step 1: In the new module, we first create a subroutine named “ActivateSheetAndInputValue.”
Step 2: Here, we declare a variable ‘ws’ of type Worksheet. This variable will be used to store a reference to the worksheet we want to manipulate.
Step 3: We use ThisWorkbook to ensure that we are referencing the workbook that contains the VBA code. The Sheets(“Sheet2”) part is used to access the specific worksheet named “Sheet2.” We set our ‘ws’ variable to this worksheet.
Step 4: Now that ‘ws’ holds a reference to “Sheet2,” we use the Activate method in BA to make it the active worksheet in Excel.
Step 5: Finally, we use the Range(“A1”).VBA Value property to set the value of cell A1 to “Hello, World!”
Step 6: When this code is executed, Excel will navigate to “Sheet2” of the workbook that contains the macro, making it the currently visible and active sheet, and then it will place the string “Hello, World!” in cell A1 of “Sheet2.”
Here is the full code:
Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets(“Sheet2”)
ws.Range(“A1”).Value = “Hello, World!”
Example #3 – Saving the Workbook using ThisWorkbook
In this example, we will see how to trigger the save operation for the workbook that contains this specific macro, ensuring that all current changes are stored.
Step 1: Declare a new subroutine named SaveThisWorkbook.
Step 2: The Save method is called on VBA ThisWorkbook, which triggers a save operation for the workbook containing the macro. It is equivalent to manually clicking “Save” in Excel.
Step 3: Executing this code results in the immediate saving of the workbook, securing all recent modifications and updates made prior to the execution of the macro.
Here is the full code:
Active Workbook vs. ThisWorkbook in Excel VBA
VBA ActiveWorkbook and VBA ThisWorkbook are both objects in Excel VBA that refer to workbook objects, but they point to different workbooks depending on the context.
|Refers to the workbook that is currently active or in focus.
|Refers to the workbook that contains the VBA code.
|When you want to interact with the workbook the user is currently working on.
|When you want to interact with the workbook containing the macro, regardless of which one is active.
|It depends on user actions; it can change if the user activates a different workbook.
|It does not depend on which workbook is active; it stays constant.
|If a user has multiple workbooks open and activates a different one, ActiveWorkbook would refer to the newly activated workbook.
|Even if a user activates a different workbook, ThisWorkbook will still refer to the workbook containing the macro.
|It is less reliable if you want to specifically target the workbook containing the macro, as it can change based on user actions.
|It is more reliable for targeting the workbook containing the macro, as it does not change.
|Common Use Cases
|Creating a macro that applies to any open workbook.<br>- Automating tasks on the workbook that the user is currently using.
|Creating a macro that applies only to the workbook it is contained in.<br>- Accessing elements or properties specific to the workbook containing the macro.
Important Things To Note
- Always use VBA ThisWorkbook when you intend to reference the workbook containing your macro. This avoids unintentional data changes in the wrong workbook.
- Be cautious when using VBA ActiveWorkbook as users can inadvertently activate another workbook during the macro’s runtime.
- Using properties like VBA ThisWorkbook.Path or VBA ThisWorkbook.FullName can be helpful in file operations, like saving copies or retrieving data.
- You can use VBA ThisWorkbook.Save to programmatically save the workbook, ensuring that all changes are stored without requiring manual intervention from the user.
Frequently Asked Questions (FAQs)
If VBA ThisWorkbook isn’t working in VBA, it could be due to several issues. Ensure that macros are enabled in Excel, as disabled macros will prevent the execution of VBA code. Check for errors in your code and ensure that you are using VBA ThisWorkbook correctly.
VBA ThisWorkbook refers specifically to the workbook where the macro is stored, so ensure that your code is in the correct location and that there are no issues with the workbook itself that might be preventing the macro from running properly.
VBA ThisWorkbook is commonly used when you want to refer to the workbook that contains your macro, ensuring that your code always affects the correct workbook, regardless of which workbook is currently active. It is useful for automating tasks like saving the workbook, accessing or modifying data in specific sheets or ranges, changing workbook properties, and working with workbook-level events.
To protect a workbook, you can use VBA ThisWorkbook.Protect Password:=”yourpassword,”, and to unprotect it, use VBA ThisWorkbook.Unprotect Password:=”yourpassword.”
Replace “yourpassword” with your chosen password. Remember, protecting a workbook restricts users from making structural changes, like adding or deleting sheets.
Yes, VBA ThisWorkbook can be used to work with named ranges. You can refer to a named range and manipulate it using VBA code as follows:
Set myRange = ThisWorkbook.Names(“MyNamedRange”).RefersToRange.
It sets a reference to a named range called “MyNamedRange” in the workbook containing the macro.
This article must be helpful to understand the VBA ThisWorkbook, with its syntax and examples. You can download the template here to use it instantly.
This has been a guide to VBA ThisWorkbook. We learn to use ThisWorkbook property in excel VBA, its working, examples and vs. ActiveWorkbook. You can learn more from the following articles –