Excel VBA Video Training/ EXCEL DASHBOARD REPORTS

Sheet/Worksheet CodeNames

Back to: Excel VBA . 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).

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.

Excel Dashboard Reports & Excel Dashboard Charts 50% Off Become an ExcelUser Affiliate & Earn Money

Special! Free Choice of Complete Excel Training Course OR Excel Add-ins Collection on all purchases totaling over $64.00. ALL purchases totaling over $150.00 gets you BOTH! Purchases MUST be made via this site. Send payment proof to [email protected] 31 days after purchase date.

Instant Download and Money Back Guarantee on Most Software

Excel VBA Video Training/ EXCEL DASHBOARD REPORTS

Excel Trader Package Technical Analysis in Excel With $139.00 of FREE software!

Microsoft � and Microsoft Excel � are registered trademarks of Microsoft Corporation. OzGrid is in no way associated with Microsoft