VBA Blogs: Common Traps 4

Welcome back to the VBA blog series! We’re going to continue with last month’s blog theme, focusing on common errors and issues that people run into.

When we record macros, we’ll often see references to ranges and sheets and so forth. We discussed the pitfalls of using “A1” style references in VBA and how to replace them with Named Ranges in Common Traps 2.

This time, we’re going to look at the sheet naming convention in a recorded macro. Let’s take a look:

Here, we have recorded a macro that selected Sheet1, go to cell A1, put a value of 10 in the cell and hit enter.

Now, suppose an enterprising analyst decides that the innocuously named “Sheet1” should actually be named something more descriptive, such as “Output”. This results in an error occurring:

When we click on Debug, we can see the specific area that has fallen over:

The way we are referring to the sheet here, as “Sheet1”, means that the VBA code will be looking for the sheet named “Sheet1”, which no longer exists.  In the Project window, we can see how this works:

The name inside the brackets (i.e. Output, Sheet2) is effectively the tab name, from the perspective of the user (i.e. what you consider the sheet name to be, that you might rename below the grid). The name in front of the brackets (i.e. Sheet1, Sheet2) refers to the name of the VBA Worksheet object.

So when we refer to Sheets(“Sheet1”), what we’re actually doing is going to the list of all of the sheets, and finding the one that is labelled “Sheet1”. To improve this, what we can consider doing instead is going to the VBA object called “Sheet1” instead, by calling on it as follows:

This way, we are working directly with the sheet in question, and it won’t break if someone changes the name on the Excel side. This isn’t foolproof, of course, as someone can still rename it in VBA and it will still break in that instance, but hopefully it reduces the likelihood of unintentional errors being caused.

See you next week for more VBA tips!