Using sheet codenames in Microsoft Excel VBA
Do your macros keep falling over due to users renaming their worksheets in Excel? Read this article to find out if sheet codenames could be the solution you need.

Posted by Michael Allsop on 01 June 2011

You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

Using sheet codenames in Microsoft Excel VBA

A common way to reference Excel worksheets in Visual Basic for Applications (VBA) is to use their tab name. However, this alternative method has several advantages - and one disadvantage!

In the images below, the first shows the view you are likely to get when looking at the Visual Basic Editor for a workbook with default tab names for the sheets, and the second shows the view you would have when a user has modified the name of the first worksheet:

Tab names unchanged Tab names modified
Tab names unchanged Tab name modified on first sheet

When referring to the first sheet in your VBA code, you would have to use the following lines of code respectively:



However, if a user subsequently modified the tab name again - to "JanSales", for instance - then the existing code would generate a run-time error as VBA would not be able to find the sheet.

In order to avoid this common problem, you can use the sheet's codename (the codename is the part that remained as Sheet1 in the two examples above) in your VBA code, as shown below:


This isn't hugely descriptive though - but you can change this codename as follows:

Project Explorer window in Excel VBA

Changing the codename:


You can now refer to this sheet simply by typing the following in your code:


...and no matter what users do to the tab name or the order of the worksheets, any mention of JanSales will always take you to that worksheet.

Specific code name Tab name does not matter
Codename as JanSales New tab name doesn't matter

The only drawback of using this naming strategy is that a sheet's codename cannot be used when referring to it from a macro in another spreadsheet - in this scenario you would have to use the tab name.

This blog has 0 threads Add post