I’ve no idea how many of you know this little gem, but it’s something I use a great deal. If it’s common knowledge, forgive me for wasting your time!
When we’re in the VBE’s Immediate Window and code is not running, the term <em>ThisWorkbook</em> refers to the workbook that contains the VBA project currently selected in the Project Explorer. This is particularly useful when dealing with addins or hidden workbooks, as we can easily check or change their properties, or close them, directly in the Immediate window. For example, we can see exactly where an addin is loaded from by selecting it’s project in the Project Explorer and typing the following in the Immediate Window:
And we can close it using:
Just be careful about which project is selected at the time, as clicking a code pane window will select that project in the Project Explorer! Obviously, when VBA code is running and we’re in debug mode, ThisWorkbook refers to the workbook containing the executing code, regardless of which one is selected in the Project Explorer
if you write :
ThisWorkbook.Close false
you will close your project without saving the changes
Stephen –
I accidentally discovered this about a month ago, and now I’m amazed how much time it saves. I haven’t had to type the name of an add-in I wanted to close (without uninstalling) in a long time.
Good one, Stephen. Like Jon, I’ve been typing the filename to close an add-in. I’ll bet I’ve done that a thousand times.
thanks Stephen that is very useful. I always thought ThisWorkbook in the immediate window would refer to the open activeworkbook in the Excel window.
Thanks Stephen, that is helpful.
Just wanted to add that the name ThisWorkbook isn’t an intrinsic name. It is actually just the default name given to the Workbook object in a VBA project. Each of the Workbook’s Worksheet objects in the project is also given a name; they default to SheetX as new sheets are added, but their VBA project names do not have to match their Excel sheet names. Excel VBA then provides global Workbook and Worksheet object variables with these names for us to use within our VBA projects.
You can change the names of these global object variables in your VBA project by selecting the objects in the Project Explorer window under Microsoft Excel Objects and typing a new name in the Properties window. So, for instance, if you select the Workbook object in a VBA project, which is named ThisWorkbook by default, and change its name to MyWorkbook, you can now refer to it as MyWorkbook in the Immediate window and in your code instead of ThisWorkbook. You can also reference the Workbook’s Worksheet objects directly using these global object variables, though folks rarely do this (there’s real value in doing so, though, because it eliminates dependencies in the VBA code to Excel sheet names, allowing sheets to be renamed/moved in Excel without impacting any VBA code that might manipulate them).
Stephen: very useful thanks. I can’t tell you how many times I’ve closed Add-Ins by typing the full name out… Can I have my wasted years back please?
Eric W. Bachtal: << So, for instance, if you select the Workbook object in a VBA project, which is named ThisWorkbook by default, and change its name to MyWorkbook, you can now refer to it as MyWorkbook in the Immediate window and in your code instead of ThisWorkbook. >>
That is useful too, especially for Referenced Add-Ins.
Not only is ThisWorkbook the default project name given to workbooks, but it’s also a property of the Application object.
So after you’ve changed your workbook’s project name to MyWorkbook, ThisWorkbook.Name is still valid.
I use this quite a bit as well. Although I generally tend to type out the name anyway. As I do a lot of work in the VBE, sometimes I can have many projects open at once, and be flipping back and forth between them frequently. I have had the misfortune of closing a few projects and losing some work because the active project may not have been the selected project. Hehehe. Hence me typing it all out generally. LOL!
Anyway, yes, good tip. :)
Regards,
Zack Barresse