Excel VBA Tutorial: Unlock the Secrets of Worksheet Code Names
Last updated on July 5, 2018 By Victor Chan
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 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.
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
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.
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.
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
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:
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).
And in this screenshot, Dewey is (1), Huey is (2) and Louie is (3). Names stay the same, index changes.
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:
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
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.
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).
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?
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:
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).
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
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)
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
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
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
Here are references you might find useful.
- PDF Book: Goal Kicker 2018 Excel VBA Professional Tips & Secrets (Section 10.3 – Worksheet .Name, .Index or .CodeName)
- Paul Kelly (The Complete Guide To The VBA Worksheet)
- Chip Pearson (Code Module And Code Names)
- Stack Overflow (Excel tab sheet names vs. Visual Basic sheet names)
- Microsoft MSDN (Visual Basic Naming Rules)
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…
Related Posts:
Author: Victor Chan
Victor expertly teaches Microsoft Excel to people all over the world. He has millions of views of his popular Excel explainer videos on YouTube. These show time-saving shortcuts and real-world applications explained with easy-to-follow visuals.
Victor has over 20 years of experience using Excel as a professional for Big 4 Audit Firm Deloitte and two global tech companies. He knows firsthand that being more productive with Excel can lead to greater job satisfaction and career growth.
Discover the PROVEN Blueprint for transforming your Excel skills, supercharging your productivity, and standing out in your career! My course helps you to learn Excel VBA and save hours of time even if you have zero prior experience with programming.
Solve tricky Excel problems and take your work to the next level! Get customized solutions for your unique needs. Save time and gain insights with truly expert Excel solutions from only $97 per task.
Get a clear overview of your project progress using the Excel project timeline. Use it to communicate the big picture, track task progress, and stay on top of your project goals. Stay organized with our project timeline!
Our cheat sheets provide quick and easy reference to commonly used Excel VBA concepts and code snippets.
Unlock new levels of productivity and efficiency with our cheat sheets, and write VBA code like a pro in no time.
RECOMMENDED READING
Are you looking to upskill and stay ahead of the curve? Excel is a powerful tool that keeps growing in demand. We round up the best online courses for learning Excel.
Are you looking to up your spreadsheet game? Excel is an invaluable tool that can help you stay organized and save time. From data analysis to budgets, Excel can do it all!
Today, having Excel skills is more critical than ever. Those who know how to use Excel are more likely to find higher-paying jobs. And get promoted faster.