562 attributed reviews in the last 3 years
Refreshingly small course sizes
Outstandingly good courseware
Whizzy online classrooms
Wise Owls only (no freelancers)
Almost no cancellations
We have genuine integrity
We invoice after training
Review 30+ years of Wise Owl
View our top 100 clients
|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.|
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:
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:
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.
|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.
25 Aytoun Street