Using Worksheet CodeNames in Other Workbooks

Posted on by

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:

Function GetWsFromCodeName(wb As Workbook, CodeName As String) As Excel.Worksheet
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:

Dim wsPivot as Excel.Worksheet
Set wsPivot = GetWsFromCodeName(Workbooks(“Data.xlsx”), “wsPivot”)

and away you go.

19 thoughts on “Using Worksheet CodeNames in Other Workbooks”

    • Sam, As long as it’s in the same workbook you could. In fact, you can just use

      wsPivot...

      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!

      Reply ↓

  2. 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:

    Option Explicit
    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.

    Reply ↓

    • 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.

      Reply ↓

      • 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.

        Reply ↓

    • 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

      Reply ↓
  5. Function SheetName(Wb As Workbook, CodeName As String) As String
    SheetName = Wb.VBProject.VBComponents(CodeName).Properties("Name").Value
    End Function
    Reply ↓

    • 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:

      Function ProgrammaticAccessAllowed() As Boolean
      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
      Reply ↓

  6. 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.

    Reply ↓

  7. Some useful information on this page – thanks, peeps.

    I did stumble across this:

    ActiveWorkbook.VBProject.VBComponents("<CodeName>").Name
    Reply ↓

  8. 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

    Reply ↓

  9. 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

    Reply ↓

Speak Your Mind

Your email address will not be published. Required fields are marked *

To post code, do this: <code> your vba here </code>