Sheet Referencing in VBA

Just like referring to Ranges, you can refer to worksheets with Excel VBA in many different ways.  In my opinion there are no wrong ways that are more right than others.  These opinions are not shared by all but I will try and explain why I have formed this opinion.  The following Excel VBA example refers to A1 to C1 on Sheet1;

Sub SheetRef() 'Excel VBA to refer to cells.
Sheets("Sheet1").Range("A1")=1
[Sheet1!B1]=2
Sheets(1).[D1]=3
Sheet1.[c1]=4
End Sub

The first two lines of VBA are fine, however they are subject to error if the sheet name changes for any reason. The third method will change the value on the left most sheet in the workbook no matter what the name so if the sheet you intend to manipulate moves then it won't work either. The last VBA example;


Sheet1.[c1]=4


cannot be corrupted by the sheet name changing, or the position of the sheet changing, so this is the method of choice in my opinion if you want flexible, error free sheet referencing. This is using Excel's Sheet Code Name. In a similar article on Ozgrid, Dave Hawley describes the user of the this technique, "This is the method used by savvy VBA coders".


A link to the article can be found here;

http://www.ozgrid.com/VBA/excel-vba-sheet-names.htm


Where ever possible I try and use the sheet code name in my VBA. The only time I find I cannot use the code name is when I open other workbooks. At this point I tend to use the Sheet Name of the opened workbook. Examples of this can be seen at importing opening files page under Export Data to Individual Files.