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.
Lads…what’s the DDOE link you’re referencing above? I can’t see a pingback, and I want to know more.
Jeff, I’m not sure. Maybe this one? http://dailydoseofexcel.com/archives/2012/02/13/extending-built-in-classes/
Hi all,
A special thanks to Nigel, great post that has helped me to improve my code on an App that I have been developing since 2012.
I have a question about this collection: is it possible to use this from a closed workbook?
I mean, if this could work:
?Application.Workbooks(CurrentPath & “Book1.xlsb”).SheetsByCode(“Settings”).name
Note that I am trying to read this from a binary book
Thanks in advance!!!
Gustavo
Hi Gustavo. No, you’re not going to be able to access those properties in a closed workbook.
Nice, worked really wall, thanks
Ross
I’m glad to hear it!
Something like this also might work from other worksheets
SheetName = Wb.VBProject.VBComponents(CodeName).Properties("Name").Value
End Function
Kevin, that is a nice way to do it. The only trick is that the user needs to have enabled “Trust access to the VBA project object model” in Macro>Security.
Here’s a little function you can run to test whether access is enabled:
Dim vbTest As VBIDe.vbComponent
On Error Resume Next
Set vbTest = ThisWorkbook.VBProject.VBComponents(1)
If Err.Number = 0 Then
ProgrammaticAccessAllowed = True
End If
End Function
Thanks! That really saved my day. I had the situation where I need to write some data back to the calling workbook and I wanted to use the CodeName for obvious reasons. So I was pretty surprised when I found out it’s not working!
I got inspired by the information on this page and came up with this:
Example usage in my add-in code to get a cell value :Sheets(UCN(gSheetName.sSpecificName)).Cells(993, 1 + i))
' Procedure : CreateSheetNameCollection
' Author : me :-)
' Date : 26.08.2013
' Purpose : Create a collection containing codenames and their matching sheet names
'---------------------------------------------------------------------------------------
'
Public Sub CreateSheetNameCollection()
'Declaration
Const cstrProcedure = "CreateSheetNameCollection"
Dim sCollectionKey As String
Dim i As Integer 'Index for collection key array
Dim sSheetName As String
'Initialisation
Erase aCollectionSheetName 'Create an empty array
aCollectionSheetName = ReferenceData.Range("_CodeNames") 'Read range with codenames into array
'<Start>
Set cSheetNames = New Collection
With cSheetNames
For i = 1 To UBound(aCollectionSheetName, 1)
If Not aCollectionSheetName(i, 1) = C_EMPTY Then
sSheetName = GetSheetName(aCollectionSheetName(i, 1)) 'Use the code name to get the sheet name
.Add Item:=sSheetName, Key:=aCollectionSheetName(i, 1) 'Add sheet name and codename to collection
End If
Next i 'Next in aCollectionSheetName
End With
'<End>
End Sub
Private Function GetSheetName(sCodeName) As String
GetSheetName = Application.Workbooks(ActiveWorkbook.name).SheetsByCode(sCodeName).name
End Function
Public Function UCN(sCodeName) As String 'Use CodeName
With cSheetNames
UCN = .Item(sCodeName)
End With
End Function
This solution is also using SheetsByCode from Nigel.
A list of relevant codenames is stored in in a named range in the add-in. I’m reading this names and create a collection of all matching sheet names of the calling workbook. Then I use this collection to get the sheet names by using the codenames as key. The key is currently taken from a user defined type “gSheetName”. This is still a bit clunky but good enough for now.
Some useful information on this page – thanks, peeps.
I did stumble across this:
I know this is super old but… any ideas why the original code in the post would work perfectly every time when running from the IDE (Alt-F11), but would fail every time when running from Alt-F8?
The calling sub opens a workbook from a shared drive (set myWB = workbooks.open(“…”)), assigns Sheet2 to a worksheet variable and starts to do something to the cells on there. When the calling subroutine was getting error 91 / object variable not set, I added a msgbox in there for debugging and sure enough it gives the sheet name when running from Alt-F11 and gives error 91 when running from Alt-F8.
I’m in Excel 365, 32-bit, in case that matters. Thanks for any additional help getting this working!
Tai
Function GetWsFromCodeName(wb As Workbook, CodeName As String) As Excel.Worksheet
”https://yoursumbuddy.com/using-worksheet-codenames-in-other-workbooks/
‘You can then code something like:
‘
‘Dim wsPivot As Excel.Worksheet
‘Set wsPivot = GetWsFromCodeName(Workbooks(“Data.xlsx”), “wsPivot”)
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
MsgBox GetWsFromCodeName.Name
End Function
As a PS to my not-yet-published comment… I added a few lines to open the visual basic editor during the function – and this makes it work every time. I still would be interested to know why it works with VBE open and not with it closed. Thanks!
Tai
Function GetWsFromCodeName(wb As Workbook, CodeName As String) As Excel.Worksheet
”https://yoursumbuddy.com/using-worksheet-codenames-in-other-workbooks/
‘You can then code something like:
‘
‘Dim wsPivot As Excel.Worksheet
‘Set wsPivot = GetWsFromCodeName(Workbooks(“Data.xlsx”), “wsPivot”)
Dim ws As Excel.Worksheet, x As Variant
‘seems to always work with VBE showing, but never without…
x = Application.VBE.MainWindow.Visible
Application.VBE.MainWindow.Visible = True ‘http://www.vbaexpress.com/kb/getarticle.php?kb_id=331
For Each ws In wb.Worksheets
If ws.CodeName = CodeName Then
Set GetWsFromCodeName = ws
Exit For
End If
Next ws
‘MsgBox GetWsFromCodeName.Name ‘why does this only work when IDE (Alf-F11) is open???
Application.VBE.MainWindow.Visible = x ‘set it back to how it was, visible or not
End Function