Excel VBA Tutorial: Unlock the Secrets of Worksheet Code Names

Summary: Few Excel users know about Worksheet Code Names… in this article you will learn how they differ from plain old Worksheet Names and why you should start using Code Names where possible in your VBA code. [Top secret – for your eyes only!]

Difficulty: Beginner

Worksheet code names

Worksheet Names in VBA

When you write VBA code for Excel you are going to deal with worksheets a lot. So it’s a good idea to know the best ways of referring to worksheets with VBA.

In this article we’ll first look at the Worksheet Name and Index properties. Then we’ll dive into Worksheet Code Names.

When you see VBA code, feel free to copy and paste it into your VBA Editor and try it out.

Contents

Click on the links below to learn more…

#1 – Name property – name in the Excel interface
#2 – Index property – numerical position of worksheet tab
#3 – Code name property – “secret” internal VBA name
#4 – Problems using Name and Index – what could go wrong?
#5 – Using Code Names in VBA – why you should do it
#6 – Code Names in other workbooks – use a custom function
#7 – Recap – remember what you learned
#8 – References – further reading to learn more

Excel VBA Cheat Sheets » Find it hard to write Excel VBA Macros? Think there’s too much to remember? You’re not alone! Check out my convenient one-page guides. They’ll help you write Excel VBA Macros better and faster.
Click here to find out more…

#1 – Name property – name in the Excel interface

» Back to contents

The Name property of the Worksheet object allows you to get or set the name that’s seen by the user in the main Excel interface.

By default Excel names all worksheets “Sheet1”, “Sheet2”, “Sheet3” etc. in the order you add them to the workbook.

[IMAGE] Default Excel Worksheet Names

The user can easily change the name of a worksheet. Double-click on the worksheet tab and enter a new name, or right-click on an existing worksheet name, then choose Rename from the context menu.

[IMAGE] Double click to change worksheet names

You can use VBA to change the name of a worksheet by assigning a new string value to the Name property.

Here’s a simple VBA example:

Sub Change_Worksheet_Name()
' PURPOSE : Show how to rename worksheets

    'Using the name property
    Worksheets("Sheet1").Name = "Huey"
    Worksheets("Sheet2").Name = "Louie"
    Worksheets("Sheet3").Name = "Dewey"
    
End Sub

And you can select worksheets using the Name property like this:

Sub Refer_Worksheet_Name()
' PURPOSE: Reference worksheet using Name Property

    'Select worksheets
    Worksheets("Huey").Select
    Worksheets("Louie").Select
    Worksheets("Dewey").Select
    
End Sub

#2 – Index property – numerical position of worksheet tab

» Back to contents

The Index property of the Worksheet object gives you the index number of that worksheet inside the Worksheets collection.

This is the same as the worksheet’s position in the Excel interface (left-most = 1, increasing to the right) like this:

[IMAGE] Excel VBA Worksheet index

The user can reorder the worksheets by clicking and dragging.

Just in case it’s not clear, let me say that the name of the worksheet doesn’t have any impact on the Index property – only the position of the worksheet determines the index number.

In this screenshot, Huey is (1), Louie is (2) and Dewey is (3).

[IMAGE] Worksheets-huey-louie-dewey

And in this screenshot, Dewey is (1), Huey is (2) and Louie is (3). Names stay the same, index changes.

[IMAGE] Worksheets-dewey-huey-louie

Here’s VBA code showing a simple message box that lists the Index property of the first three worksheets.

Sub Use_Worksheet_Index()
' PURPOSE : Show the Index property

    'Display Message Box with worksheet names
    Dim Msg As String
    Msg = "Worksheets (1) : " & Worksheets(1).Name & vbNewLine & _
          "Worksheets (2) : " & Worksheets(2).Name & vbNewLine & _
          "Worksheets (3) : " & Worksheets(3).Name
    MsgBox Msg
    
End Sub

Try running the code above with three worksheets in the Active Workbook. (note: you’ll get an error if you run it with only one or two worksheets).

Then drag one of the three worksheets to a different position, and try running the code again with the worksheets in the new order.

Notice the worksheet index for each worksheet changes while the name stays the same.

Below is an animated screenshot showing what this looks like:

[IMAGE] Rearrange worksheets index VBA

A possible reason to use the Index property is to loop through all worksheets one at a time, say if you wanted to change cell A1 of each worksheet to the name of the worksheet.

#3 – Code name property – “secret” internal VBA name

» Back to contents

[IMAGE] Excel VBA secret code names

Now for the awesome part.

If you’re new to Excel VBA you’re about to learn something that very few Excel users will ever know…

… Excel has secret code names for worksheets!

You can’t see worksheet code names in the standard Excel interface.

You need to go into the VBA Editor to see them.

If you look in the Project Explorer window (CTRL + R in the VBA Editor) you’ll see both the Code Name and the Worksheet Name listed.

[IMAGE] VBA Project Explorer Code Name

By default Excel assigns standard code names Sheet1, Sheet2, Sheet3 etc. to new worksheets.

You can change the code name and worksheet name of any worksheet in the Properties Window (F4 in the VBA Editor).

[IMAGE] VBA Properties Window Code Name

You can change the names to be more meaningful than Sheet1, Sheet2, Sheet3. So in our example we renamed “Sheet4” to “Agent_007”. This can make your code easier to read … and easier to write.

To change the code name, click on the box to the right of where it says (name), type the new code name and then press enter. Notice the code name is indicated by parentheses.

To change the worksheet name, click on the box to the right of where it says name, type the new worksheet name and then press enter.

Just remember… if you change the code names then make sure your code uses the updated names.

Here are some basic rules to follow for making code names.

  • You must use a letter as the first character.
  • You can’t use a space, period ( . ), exclamation mark ( ! ), or the characters @, &;, $, # in the name.
  • Name can’t exceed 255 characters in length.
  • Avoid using names that are the same as functions, statements, and methods in VBA.

And you can use the code name much like you would use another object in VBA. Let’s look at how we might use code names.

Here’s some VBA code that uses worksheet names to make the worksheet “James Bond” very hidden, kill the Bad Guys file, and then make the worksheet “James Bond” resurface to normal visibility.

Sub Activate_Worksheet_Name()
' PURPOSE : This version uses Worksheet Name

    'Let's take our agent underground
    Worksheets("James Bond").Visible = xlSheetVeryHidden
    
    'Kill some bad guys... save the world...
    Kill ("C:\Temp\Bad_Guys.xlsm")
    
    'And now our agent can come back
    Worksheets("James Bond").Visible = xlSheetVisible
    
End Sub

And here’s the equivalent VBA that uses code names instead of worksheet names:

Sub Activate_Code_Name()
' PURPOSE : This version uses Code Name

    'Let's take our agent underground
    Agent_007.Visible = xlSheetVeryHidden
    
    'Kill some bad guys... save the world...
    Kill ("C:\Temp\Bad_Guys.xlsm")
    
    'And now our agent can come back
    Agent_007.Visible = xlSheetVisible
    
End Sub

As you can see from the above example, using code names can shorten your code and make it easier to read because you no longer need to write “Worksheets”.

These aren’t the only advantages of using code names, as we’ll see in the next section.

#4 – Problems using Name and Index – what could go wrong?

» Back to contents

What could go wrong with using Name?

Users can easily modify the name of a worksheet in Excel, so we must be careful when referencing worksheet names using the Name property.

What if the user changes the worksheet name that you use in your VBA code?

For instance the user renames “James Bond” to “Bond… James Bond”.

If you attempt to reference worksheet “James Bond”, Excel returns a run-time error message “Subscript out of range” like this:

[IMAGE] Excel VBA Subscript out of range error

This happens because there is no longer a worksheet called “James Bond” because it was renamed to “Bond… James Bond”.

And of course your VBA code is using the old name so it gives an error.

What could go wrong with using Index?

Users can easily change the position of worksheets by clicking and dragging.

This can lead to a different type of error from above.

Before we introduced James Bond… we had three little ducky worksheets called “Dewey”, “Huey” and “Louie”. (In case you don’t know them, they’re the nephews of Donald Duck).

Let’s say the user reorders the three ducky worksheets and you were using the index property to refer to worksheet “Dewey” by its original position (1).

Worksheet “Dewey” becomes worksheet (2), and worksheet “Huey” becomes worksheet (1).

[IMAGE] Dewey is not worksheet 1

But your VBA code still refers to “Dewey” as worksheet (1) so it will operate on the wrong worksheet (“Huey”).

So your code still runs (without producing an error message) but it will do to Huey what you wanted to do to Dewey. Not good!

So using either the Name property or the Index property can lead to errors and confusion, simply because the user can change both Name and Index from the Excel interface.

#5 – Using Code Names in VBA – why you should do it

» Back to contents

OK if you’ve been reading along and not just skipped to this part, you’ll probably know exactly what I’m going to say next.

So here it is…

If you write VBA code that uses the Worksheet Name or Index to refer to specific worksheets, you will  find that changes to either worksheet names or positions can break your code.

But…

If you write VBA code using Worksheet Code Names to refer to specific worksheets, then it doesn’t matter whether the user makes changes to the worksheet names or reorders their positions… your code will still refer to the correct worksheets.

So use Code Names to make your code easy to read and safe from changes in worksheet names or tab positions.

But there is a small problem with Code Names… we’ll take a look in the next section.

#6 – Code Names in other workbooks – use a custom function (Difficulty: Intermediate)

» Back to contents

The one problem with using Code Names to refer to worksheets is that you can’t use Code Names for worksheets in a different workbook from your code.

You can only use Code Names for worksheets in the same workbook as your code.

While that’s a current limitation of VBA, there are ways around this.

For those of you who are interested, let’s look at one possible way to use Code Names for worksheets in other workbooks.

Here we use a User Defined Function (UDF) to set the worksheet object (the code goes in any VBA module in your macro-enabled workbook):

' This function gets the worksheet object from the Code Name
Function Set_Worksheet(The_Code_Name As String, Wbk As Workbook) _
        As Worksheet

    Dim Wks As Worksheet
    
    'Loop through Worksheets collection to find
    For Each Wks In Wbk.Worksheets
        If Wks.CodeName = The_Code_Name Then
           Set Set_Worksheet = Wks
           Exit For
        End If
    Next Wks

End Function

And here is code to show how you can call the UDF to set a worksheet in the workbook “Budget.xlsm”:

Sub Demo_Sheet_Function()
' PURPOSE: Show custom function Set_Worksheet in action

    Dim Wks As Worksheet
    
    'Get worksheet in another workbook using its codename
    Set Wks = Set_Worksheet("My_Code_Name", Workbooks("Budget.xlsm"))
    
    'Use the worksheet (your own code goes here)
    MsgBox "Wks now refers to worksheet: " & Wks.Name

End Sub

#7 – Recap – remember what you learned

» Back to contents

Here we’re going to review the main points to help you remember what you learned. And if you’re serious about learning this material, I strongly recommend you test your recall now by doing the exercises in Excel. Remember to have fun too!

Name Property

  • Use the Name Property to get or set the worksheet name (as seen in the main interface)
  • Can be changed by the user by double-clicking on worksheet tab
  • When possible, avoid using the Name Property to reference worksheets (use the Code Name instead)
  • Exercise: use the Worksheets Name property to change the name of three worksheets

Index Property

  • Use the Index Property to get the the index number of a worksheet.
  • Index number = position of the worksheet in the Excel interface (counting from left to right)
  • The user can reorder worksheets by clicking and dragging, and this changes their index numbers.
  • When possible, avoid using the Index Property to reference worksheets (use the Code name instead)
  • Exercise: use the Worksheet Index property to select each worksheet in your Active Workbook

Code Name Property

  • You need to be in the VBA Editor to see the code name.
  • You can change the code name of any worksheet in the Property Window (F4 in the VBA Editor).
  • The code name isn’t changed when the user changes the worksheet name.
  • Exercise: change the code name of a worksheet, and use that code name to select the worksheet.

Why use Code Names in VBA

  • Use worksheet code names to make your VBA code easy to read and safe from changes in worksheet names or tab positions.

Code Names in Other Workbooks (Difficulty: Intermediate)

  • One problem with using Code Names to refer to worksheets is that you can’t directly access a worksheet in another workbook with the Code Name.
  • You can get around this using the User Defined Function Set_Worksheet (provided in code above)
  • Exercise: use the UDF Set_Worksheet to refer to a worksheet in another workbook by its code name.

Please leave a comment below if you have any questions or need help with worksheet code names. I’ll try my best to help! 🙂

#8 – References – further reading to learn more

» Back to contents

Here are references you might find useful.

Excel VBA Cheat Sheets » Find it hard to write Excel VBA Macros? Think there’s too much to remember? You’re not alone! Check out my convenient one-page guides. They’ll help you write Excel VBA Macros better and faster.
Click here to find out more…
Victor Chan
Victor has used Excel a lot since 2002. He's a Chartered Accountant (Fellow of the ICAEW) with MEng in Manufacturing Engineering from the University of Cambridge. He's on a mission to help you master Excel and VBA with Launch Excel.
Join over 9,000 subscribers

GET BETTER AT EXCEL

Recent Posts

Leave a Comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Contact Us

Please send us an email and we'll get back to you, asap.

Not readable? Change text.