Using Worksheet CodeNames in Other Workbooks
VBA worksheet codenames are a handy way to refer to sheets in the same workbook. Unlike regular sheet names, they can’t be changed by the user, and so is a reliable way to refer to worksheets in your code.
One thing about codenames is they’re not qualifiable. If you have a sheet codenamed “wsPivot,” you can’t refer to it as ThisWorkbbook.wsPivot. As a painfully verbose coder who declares variables as Excel.This and Office.That and can barely resist typing Application.WorksheetFunction.Max, wsPivot feels abrupt. Whose pivot is it anyways?
The fact that you’re using codenames often means you’re thinking about other users, and allowing them to rename worksheets without breaking your code. Since you’re obviously considerate, you’re probably also separating your code into an addin, so that you can maintain and improve it without disturbing users’ data. Unfortunately, because you can’t qualify codenames, you can’t code something like Workbooks(“Data.xlsx”).wsPivot. So to take advantage of the codenames in other workbooks I use this function:
Dim ws As Excel.Worksheet
For Each ws In wb.Worksheets
If ws.CodeName = CodeName Then
Set GetWsFromCodeName = ws
Exit For
End If
Next ws
End Function
You can then code something like:
Set wsPivot = GetWsFromCodeName(Workbooks(“Data.xlsx”), “wsPivot”)
and away you go.
Couldn’t you just do:
Sam, As long as it’s in the same workbook you could. In fact, you can just use
because within the same workbook you can refer to a sheet by its codename. However you can’t refer to a sheet in another workbook by its codename directly.
In this example, you have changed the sheet’s codename to “wsPivot” and are now trying to refer to it from another workbook, such as an addin. The method I covered here is to loop through the sheets in the workbook (“Data.xlsx”), checking for a sheet with the codename “wsPivot”. (I should mention that a sheet’s codename and name originally start out the same, but this example would still apply if the sheet’s codename was unchanged, e.g., “Sheet1″.)
By the way, Thanks for leaving my first comment!
That’s ok. Just a shame that I was obviously asleep when I read your post and left a comment which didn’t actually make sense!
Here’s a slightly more elegant way: implement your function as a property of the workbook, and return a VBA collection object so that you can return by name or by ordinal:
In the ThisWorkbook class module:
Public Property Get SheetsByCode() As VBA.Collection
Set SheetsByCode = New VBA.Collection
Dim wsh As Excel.Worksheet
For Each wsh In ThisWorkbook.Worksheets
SheetsByCode.Add wsh, wsh.CodeName
Next wsh
End Property
Save the workbook as Book1.xls and give the sheets codenames. I’m unimaginative, and I have a sheet called (by code name) ‘Main’ and a sheet called ‘Settings’. They’re still called Sheet1 and Sheet2.
Here’s some testing:
From within the project, you can invoke sheets by code name:
?Thisworkbook.SheetsByCode!Settings.name
Sheet2
?Thisworkbook.SheetsByCode(“Settings”).name
Sheet2
Within the scope of any other workbook or add-in, you can see the names as:
?Typename(Application.Workbooks(“Book1.xls”).SheetsByCode(“Settings”))
Worksheet
?Application.Workbooks(“Book1.xls”).SheetsByCode(“Settings”).name
Sheet2
?Application.Workbooks(“Book1.xls”).SheetsByCode!Settings.name
Sheet2
?Typename(Application.Workbooks(“Book1.xls”).SheetsByCode!Settings)
Worksheet
?Application.Workbooks(“Book1.xls”).SheetsByCode(2).name
Sheet2
Admittedly, it isn’t universal the way your function is: my code relies upon your implementing the function in each of your workbooks. That would require extending the Workbooks collection of the application, which is nontrivial; or a collection-of-collections returned by a function (the parent collection being workbooks keyed by name, and each daugter collection returned as a collection of worksheets keyed by VBA code name), which leaves me somewhat concerned by VBA’s limited ability to perform cleanup on object references embedded in collections.
But that approach does give you a closely-related option to retrieve workbooks by project name: if you try it, do let me know how you get on.
Nigel, thanks for a thorough explanation of this approach. Between this and your points over on DDoE I’ve learned a lot today about adding functionality to the ThisWorkbook class. I am kind of fond of this function as it just sits in a Utilities module which I drag into new projects, but it’s great to learn more about stretching the utility and elegance of VBA classes.
I envy you your freedom to blog what you’re doing: I think that all experienced developers have something useful to say, but rather fewer have the ability to communicate this in a useful way.
Also: I envy your DDOE pingback. This is ‘Leaps Tall Buildings With A Single Bound’ territory. All that remains for you to achieve in life is rediscovering lost civilisations*, slaying dragons**, and rescuing sacrificial virgins***.
* Please contact the Consultant for a schedule of fees. Terms and conditions apply.
**This service may be provided by carefully-vetted subcontrating companies.
***Fees are occasionally waived for this service.
Nice, worked really wall, thanks
Ross
I’m glad to hear it!