Shorthand for a workbook (). worksheets ()?
I may be blind, but I've been working with VBA for several years now, but I still write out
Workbook("Book1").Sheets("Sheet1").Range("A1").Value
or (after dimming Book1 as workbook and Sheet1 as string
Book1.Sheets(Sheet1).Range("A1").Value
Is there a way you can shorten the "workbook.sheets" part (without following the "C" instruction)?
source to share
Sure. Just do it wrong:
Sheet1.Activate
Range("A1").Value = 42
Unqualified in the standard code unit, Range
is a member _Global
that implements the property Range
, returning the specified range depending on which worksheet is active ... if any (in the worksheet code - this is implicitly referring to Me.Range
, that is, the range on that worksheet ).
If you're going to be implicitly working with ActiveSheet
, you can also defer runtime type resolution with a less efficient late call, and make the host application (Excel here) evaluate the expression in square brackets for even faster input:
[A1].Value = 42
Heck, the type Range
has a default member that points to it Value
, so you can even do this:
[A1] = 42
As you can see, less code isn't always better than code. Qualify your calls to members Worksheet
and use the default members deliberately and intelligently.
Every time someone makes an implicit call _Global
, the child's unicorn dies, and two new stack overflow questions involving errors caused by unqualified calls on the sheet are called out of the dark.
Sarcasm refusal, if you constantly cling to such calls Workbook("Book1").Sheets("Sheet1").Range(...)...
, then you are constantly playing the same objects over and over: this is not only redundant, but also redundant.
If you are working with a ThisWorkbook
(workbook with code) you never have a legitimate reason for doing this to dereference a worksheet that exists at compile time. Use its codename instead:
Sheet1.Range(...)...
If the workbook only exists at runtime, or otherwise not ThisWorkbook
, then at some point in time your code has opened or created that workbook - there is no need to ever detach it from the collection Workbooks
... if you saved the link in the first place:
Set wbExisting = Workbooks.Open(path)
Set wbNew = Workbooks.Add
Ditto for worksheets created at runtime in other workbooks of your code: keep this object reference!
Set wsNew = wbNew.Worksheets.Add
This leaves only 1 scenario where you need a string to dereference a specific sheet: the sheet already exists in the workbook, which is not ThisWorkbook
.
If that workbook structure is not secure (or cannot), avoid hardcoding the index or sheet name if you can:
Set wsExisting = wbExisting.Worksheets(1) ' user may have moved it!
Set wsExisting = wbExisting.Worksheets("Summary") ' user may have renamed it!
TL; DR
Working with objects. Declare objects, assign and work with object references, pass them as arguments to your procedures. There is no reason to constantly play objects like you. If you need to do this, you only need to do it once.
source to share
Ok, I'll take the name "Book1" literally and assume that you are writing code to work with a new book - perhaps something like:
Dim myWorkbook As Workbook
Workbooks.Add
Set myWorkbook = Workbooks("Book1")
This is already a bad start, because:
- The user's language will determine the "Book" part of the name.
- The numerical suffix will increase with each additional new book
So many inexperienced coders try to do this:
Dim myWorkbook As Workbook
Workbooks.Add
Set myWorkbook = ActiveWorkbook
But this is also open. What if there are event handlers that want to change the active workbook? What does the user change the active workbook when going through the code?
The best way to assign the variable myWorkbook is as follows:
Dim myWorkbook As Workbook
Set myWorkbook = Workbooks.Add
And, as with adding a new book, you should follow the same approach when opening an existing book:
Dim myWorkbook As Workbook
Set myWorkbook = Workbooks.Open("C:\Foo.xlsx")
In both cases, you know that you have a link to the correct book, but you don't care what it is called or whether it is active . You just made your code more robust and efficient.
Alternatively, if your VBA is working with the workbook it is in, you can simply use ThisWorkbook
either the codename of the sheet (s).
Debug.Print ThisWorkbook.Name
'By default, a sheet named "Sheet" has a codename of 'Sheet1'
Debug.Assert Sheet1.Name = ThisWorkbook("Sheet1").Name
'But give meaningful names to your sheet name and
'sheet codename, and your code becomes clearer:
Debug.Assert AppSettings.Name = ThisWorkbook("Settings").Name
You will probably find that most of your code deals with the book it is in, or existing books that your code opens, or new books that your code creates. All these situations are described above. On the rare occasion that your code needs to interact with books that are already open or are being opened by some other process, you will need to refer to the book by name, or list the collection of books:
Dim myWorkbook As Workbook
Set myWorkbook = Workbooks("DailyChecklist.xlsx")
For Each myWorkbook In Workbooks
Debug.Print myWorkbook.Name
Next myWorkbook
The only exception is add-ins, which cannot be listed using the collection Workbooks
, but can be specified usingWorkbooks("MyAddin.xlam")
source to share