We all are aware of the basic methods of referencing worksheets in our VBA code. Two of such methods have been illustrated in the picture on the right. But there is one inconvenience in putting these methods to use: the differences between sheets is getting lost in the semantics.
Our very own Jordan Goldmeier (a.k.a. Option Explicit) has decided to solve this issue once and for all. Everything that you need to follow is given right below.
The first step is to make sure that the Properties Window is visible on your VBA interface. As shown in the image, go to View > Properties Window to enable it.
This is the most critical step in implementing the tip. And it’s very easy to follow. Under the properties of the related sheet, you will find one field with title “(Name)”. You can think of this field as the ‘code name’ of the sheet. Change this to something like “Dashboard” because Sheet1, in this example, is the dashboard of my spreadsheet file.
As you can see in the picture on the right, the sheet can be referred to directly as an object using just its name “Dashboard”.
Why should one go through all this hassle? Well, look at the sub-procedure defined in this sheet from the picture. Its association with “Dashboard” instead of “Sheet1” gives away a better picture of what the code is doing. The differences between objects of the same type are now not hidden behind the semantics; they are completely explicit.
Try it out, right now! It’s easy and useful.
Also, do not forget share this awesome tip with your friends.
Please log in again. The login page will open in a new window. After logging in you can close it and return to this page.