BLOGS BY TOPIC
BLOGS BY AUTHOR
BLOGS BY YEAR
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 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.