October 2

Change Sheets Names – Excel VBA Tips

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.

So, let’s get started!

 1 – Properties Window

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.change1

2 – Changing Name

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.

change2 

3 – Let’s Test It!

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”.change3

4 – What’s the Utility?

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.

Get the Download

About the author 

Jamani Arsalan

I am a healthcare consultant, currently based in Middle East, at an international professional services firm. My work largely revolves around project management, and statistical analysis. And my professional interests include developing my knowledge within the discipline of health analytics.

{"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}

Use this Bottom Section to Promote Your Offer

Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Ut enim ad minim 

>