Using Worksheet CodeNames in Other Workbooks

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:

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.

12 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!

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

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

  2. Function SheetName(Wb As Workbook, CodeName As String) As String
    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:

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

Speak Your Mind

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

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