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

Head office

Kingsmoor House

Railway Street

GLOSSOP

SK13 2AA

London

Landmark Offices

99 Bishopsgate

LONDON

EC2M 3XD

Manchester

Holiday Inn

25 Aytoun Street

MANCHESTER

M1 3AE

Site map

Terms & Conditions

Book any course as classroom, onsite or online training, or as one-to-one consultancy

Free resources

Paid services

Getting help

Where we are

Where you are

Online

Transparent reviews

Delivery of courses

Nicer to work with

Reliable and established

Our training courses

C#

Excel

MySQL

Power Apps

Power Automate

Power BI and DAX

Python

Report Builder

SQL

SQL Server

VBA Macros

Other training resources

Blogs

Exercises

YouTube tutorials

YouTube shorts

Test your skills

Consultancy

Courseware

Self-paced courses

Newsletters

Publications

Our training venues

London

Manchester

Across the UK

At your site

Online

Why we are different

Hundreds of published reviews

Small class sizes

Outstanding courseware

Online classroom

(Almost) no cancellations

Wise Owl trainers only

Genuine integrity

Invoices after training

30+ years of Wise Owl

Our top 100 clients

BLOGS BY TOPIC

BLOGS BY AUTHOR

Using sheet codenames in Microsoft Excel VBA

Head office

London

Manchester