Do you reference sheets in the correct way in your VBA scripts?
What I’m talking about relates to when you are writing or recording macros. Specifically, whether they are dynamic enough to cope with a sheet being renamed or moved in the workbook.
This blog is part of our Exceptional level and is aimed at a more advanced user of Excel. It assumes an understanding of Microsoft Excel Visual Basic for Applications.
There are many ways to select a sheet in VBA, here are two very common ways I have seen used to select a sheet:
With (1) being the sheet with the left most position in the workbook.
Now let’s look at some scenarios:
Scenario 1 – Moving a sheet
What happens to our macros if we move a sheet.
Well, in the first example of using a sheet name, in fact, very little will happen. The macro shall still find the sheet name no matter what its place is in the workbook.
However, were we to have used the sheet positional number to define the macro we would be frantically debugging our macro code, to find out why it’s not working. As, unfortunately, there is no real link between Excel and VBA. What we mean is, if you move a sheet in Excel, the code is independent. VBA does not automatically assume you wish to update your code to reflect where the sheet has moved to.
Scenario 2 – Renaming a sheet
Now how would our macros be affected if we change the name of the sheet we are referencing.
Well, as you probably guessed, if we have used the user defined sheet name then we will be in trouble. As said previously VBA will not automatically adjust to changes in Excel. Thus if a sheet name is changed the Macro will still be looking through all the sheets, attempting to find the specific name it has been instructed with.
Had the user simply used sheet positional references, the macro would still have worked out fine. The change of sheet name would have no impact on the macro running.
The winning method – Dynamic
Wouldn’t it be great if a sheet reference did just what it said on the tin, and didn’t break based on something that happened in Excel?!
To enjoy a macro that references sheets that could be moved or renamed at any given time we need to use the Code Name of the sheet.
The Code Name is the inherent name Excel gives the sheet in a given workbook, it is not impacted by either the Excel sheet name or the positional reference of the sheet.
In this screenshot of Excel let me draw your attention to the left-hand panes:
Therefore, the option of selecting the required sheet using the default Code Name looks like this:
Notice how no brackets or apostrophes are required, reference is just given to the code name in the workbook and nothing else.
VBA is not something we focus on heavily here at Exceptional Excel, however, we believe that it is these small tips that will save people hours of pain if they are stepping into the world of VBA.
For those of you writing your own macros, has this blog been useful? Please leave a comment below if so.