Tom’s Tutorials For Excel: VBA Help — Usually Right, Sometimes Wrong
The above picture shows a simple macro that calls a Message Box to display a tab name by referring to the active sheet’s CodeName property. Suppose you’ve never before heard the term CodeName, but now you see it in a macro and you want to know more about it.
The Visual Basic Editor (VBE, shown in these pictures) is where Excel’s programming code is housed. From the VBE you can access the Help file for Visual Basic For Applications (VBA) by hitting your keyboard’s F1 key, or by clicking the Help menu item as shown in the next picture.
Take a close look at this next picture, where the green arrow is pointing. Notice an I-bar looking character that really is the blinking cursor where I clicked my mouse into the term CodeName.
When you hit F1 with a VBA term selected, VBA’s help topic will realize that and show you the Help information for that particular item. This is a convenient feature that provides you the option to avoid typing a precise keyword into VBA Help’s search field.
A word to the wise:
Do not regard Excel’s Help as the unquestioned source of Excel information.
Excel’s Help is usually correct, but not all the time and this Help topic for CodeName is an example of where Help is wrong. Notice the last sentence in the first paragraph:
“You cannot programmatically change this property at run time.”
Well, yes you can, with this macro for example, and with trusted access to the VBE:
Sub ChangeOneCodeName()
ThisWorkbook.VBProject.VBComponents(ActiveSheet.CodeName) _
.Properties("_CodeName") = "TomsTutorials"
End Sub
Furthermore, this next macro will loop through a specified group of worksheets (all sheets except the first and last) and change all their CodeNames.
Sub ChangeManyCodeNames()
Dim wks As Worksheet
With ThisWorkbook
For Each wks In .Worksheets
If wks.Index > 1 And wks.Index < .Worksheets.Count Then _
.VBProject.VBComponents(wks.CodeName).Properties("_Codename") = _
"TomsTutorials" & wks.Index
Next wks
End With
End Sub
The point is, please do not rely solely on Help as accepted wisdom. Experiment for yourself to find out what information is and is not actually correct.
Tom –
Even when VBA help isn’t wrong, it’s all too often not helpful at all. If the code sample is nothing more than a repeat of the syntax, then it doesn’t add any value.
BTW, your little code sample only works because the sheet names and code names are the same. Change the active sheet’s name to “Sheet One’ and Sheets(ActiveSheet.CodeName) is undefined. The argument of Sheets() has to be either a number (the index) or the sheet name that appears on the sheet tab.
Excel’s VBA Help is an embarassment. I love their syntax suggestion of
Worksheets("Sheet2").Range("B4").Selectfor selecting another sheet which of course will error if you are not on Sheet2.
To reliers of Excel’s Help: Caveat Helpor.
Regarding your comment about my smaller code sample, either I am not understanding what you are saying, or whatever you are seeing is something I have not had a problem with. No matter what the sheet tab name is or what its index is (including your Sheet One and SheetOne), or what the codename is or was…in every case the codename of the active sheet got changed no problem. I wonder what you are seeing that is not happening for me…I cannot duplicate your error but maybe I am missing something simple.