This page has 0 threads | Add post

Blogs

Finding our blogs useful? Please consider helping us to share more!

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

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

Worksheets("Sheet1").Select

Worksheets("January").Select

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:

Sheet1.Select

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

Changing the codename:

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

JanSales.Select

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


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.

Some other pages relevant to the above blog include:

This blog has 0 threads

Add a new post