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