Sheet/Worksheet CodeNames
Category: [Excel] Demo Available
Sheet/Worksheet Code Names
Got any Excel/VBA Questions? Free Excel Help
Sheets Tab Name
If you have ever recorded a macro in Excel that references a specific sheet in the Workbook you will know that the code will only continue to work if the Sheet name(s) remain the same. For example, code like; Sheets("Budget").Select will no longer work should the Budget Sheet be re-named. This is because the macro recorder generates such code based on the Sheets tab name, the name we see when in Excel. If it makes you feel better, many VBA coders also use the Sheet tab names over two much better ways, as they know no better.
Index Number
A sheets Index number is determined by its position in the Workbook. The left most sheet will always have an Index number of 1, the next on the right will be 2 and so on. Excel VBA allows us to specify any Sheet by using it's Index number, but unfortunately this method is not used by Excel when we record a macro. It uses the Sheets Tab name like; Sheets("Budget").Select If this sheet was the third from the left we could use: Sheets(3).Select. This is often a better option than using the sheet tab name, but still has potential problems. By this I mean, the sheets position in the Workbook could change if we add, remove or move sheets.
Sheets Code Name
This is the method used by savvy VBA coders. Each Sheet in a Workbook is given a unique CodeName that does not change even when that sheet is moved, renamed or other sheets are added. Each sheets CodeName can only be seen by going into the Visual Basic Editor (Tools>Macro>Visual Basic Editor Alt+F11) and then displaying the Project Explorer (View>Project Explorer Ctl+R)
In the screen shot above, the CodeName for the sheet with a tab name of Budget is Sheet3. A sheets CodeName is always the name not inside the parenthesis when looking in the Project Explorer. We can reference this sheet with VBA code in the Workbook by using: Sheet3.Select as apposed to Sheets("Budget").Select or Sheets(3).Select
If your Workbook is already full of VBA code, recorded or written, that does not use the CodeName you can change it on a Project level (all code in all Modules in the Workbook) by going to Edit>Replace while in the VBE (Visual Basic Editor). Babes are engulfing stripper dudes cocks eag Stud enjoys playing with a large ass Joseline Kelly loves getting her teen pussy eaten and fucked Stripper gets his hard dong delighted by chick A ballet slipper wearing whore gets plowed After interview busty babe fucked Amazing redhead babe with big boobs Jessica R fucks with Latina boy Juan Largo xxnx india Naturally beautiful brunette in fishnets gives a great erotic solo show Pornstar wannabe teen wants to be famous Teen Jesse Parker Gets Spanked Hard Horny mom Saskia is interested in the young guy in towel Leaked Amateur Russian Sextape Porn cd xx porn
One Draw back
The only times you cannot use a sheets CodeName is when you reference a Sheet that is in a different Workbook to the one that the code resides.
See also:
See also: Index to Excel VBA Code; Index to Excel Freebies; Lesson 1 - Excel Fundamentals; Index to how to… providing a range of solutions
Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.