Book List

I would like to share some books which I find quite useful.

This book gives a good explanation of the more commonly used Excel objects. I highly recommend this book to novice Excel VBA programmers.

Excel Hacks (TM)

The information shared here is extracted from EXCEL HACKS: 100 Industrial-Strength Tips & Tools, from O'Reilly, by David and Raina Hawley. They are tips which I think are really useful. Hope you will find it as helpful as I do.

Hack#83: Use CodeName to Reference Sheets in Excel Workbooks

Sometimes you need to create a macro that will work even if the sheet names it references change. If you recorded a macro in Excel that references a specific sheet in your workbook, you know the code will continue to work only if the sheet name(s) remain the same. For example, if your worksheet is named Budget, and the code in your macro reads Sheets("Budget").Select and then you change the worksheet name, the macro will no longer work. This is because the macro recorder generates code based on the sheet's tab name or on the name you see when working in Excel.

To overcome this limitation, you have two options, the first of which is to use index numbers. A sheet's index number is determined by its position in the workbook. The leftmost sheet will always have an index number of 1, the next worksheet immediately to the right will always have an index number of 2, and so on. Excel VBA enables you to specify any sheet by using its index number, but unfortunately Excel does not use this method when you record a macro.

Also, although using an index number such as Sheets(3).Select is a better option than using Sheets("Budget").Select, the sheet's position in the workbook could change if you add, remove, or move sheets.

Instead of using index numbers, savvy VBA coders use CodeNames. Each sheet in a workbook is given a unique CodeName that does not change even when that sheet is moved or renamed, or when any other sheets are added. You can see a sheet's CodeName only by going into the VBE (select Tools->Macro->Visual Basic Editor, or press Alt-F11) and then displaying the Project window if necessary (select View->Project Explorer or press Ctrl-R).

The CodeName for the sheet with a tab name of Budget is, say Sheet3. A sheet's CodeName is always the name that appears outsid the parenthesis when you look in the Project Explorer. You can reference this sheet with VBA code in the workbook by using Sheet3.Select, as opposed to Sheets("Budget").Select or Sheets(3).Select.

If your workbook is already full of VBA code, recorded or written, that does not use a CodeName, you can change the code at the project level (all code in all modules in the workbook) by selecting Edit->Replaced... while in the VBE.

Caution: The only time you cannot use a sheet's CodeName is when you reference a sheet that is in a workbook different from the one in which the code resides.