Codename to the Rescue! – The best way to refer to a Worksheet whilst developing a Spreadsheet Application


Have you ever wondered why there are two properties called “Name” for a spreadsheet? Go ahead, open up the Visual Basics Editor; go to the Project Explorer Window, and select a sheet from the ‘Microsoft Excel Objects’ Node. You will notice that there is a ‘(Name)’ and a ‘Name’ property.

Let us suppose that you are developing a basic spreadsheet application. You decided collect all the inputs in one sheet and display the results in another. The ‘Name’ property is that string of text which appears on those little “tab headers” in the Excel Window. We use it to remind users about the main purpose of every worksheet. So you go ahead and name the worksheets ‘Input Sheet’ and ‘Output Sheet’ respectively.

Name Property

This name comes in handy in your VBA Code, for you can use it to refer to sheets using the Worksheets() property of a Workbook object.

Sub Example1()
Worksheets("Input Sheet").Range("A1").Value = "Struggling to Excel?"
Worksheets("Output Sheet").Range("A1").Value = "Less of a Struggle now!"
End Sub

I suppose this is how most budding developers refer to specific sheets in their code. Even a relatively simple spreadsheet application would consist of a lot of worksheets, sheets interacting with one another, sheets being referred to several times in code. It is worth having a simple identifier for these Worksheet Objects, so they could be accessed easily. One way of accomplishing this is to declare worksheet objects with appropriate names and then “Link” them to the desired worksheets.

Sub Example2()
'Declare the Objects
Dim shtInputSheet As Worksheet
Dim shtOutputSheet As Worksheet
'Initialize the objects
Set shtInputSheet = Worksheets("Input Sheet")
Set shtOutputSheet = Worksheets("Output Sheet")
'Perform Actions
shtInputSheet.Range("A1").Value = "Struggling to Excel?"
shtOutputSheet.Range("A1").Value = "Less of a Struggle now!"
End Sub

However, there is a tiny limitation when it comes to going with this method: you would have to declare them in every procedure. You could go with Public Worksheet objects; write a dedicated procedure to initialize the sheets and call the procedure whenever required. However, there is an easier and better way to accomplish this, avoiding all this hassle.

The Codename:

This is where the other ‘(Name)’ property, which is also called ‘Codename’, comes to the rescue. It could be used to directly assign a name to any existing worksheet within the VBA framework. Once assigned, there is no need for declaring that worksheet object in the code explicitly. For instance, let us set the ‘(Name)’ property of the sheets to ‘InputSheet’ and ‘OutputSheet’ respectively. Note that I have left out the spaces here, for this name should adhere to all the rules a variable name should.

CodeName Property

Now you can refer to the sheets using these names, and save a lot of keystrokes!

Sub Example3()
InputSheet.Range("A1").Value = "Struggling to Excel?"
OutputSheet.Range("A1").Value = "Less of a Struggle now!"
End Sub

If you have worked with forms already, you can compare the distinction between these properties to the difference between Name and Caption properties of the Label or Command-Button controls.

It is a good practice to declare all your variables and objects explicitly. I strongly advise all budding spreadsheet application developers to use the ‘Option Explicit’ statement in every module. This reduces the chances of inadvertently “creating” new variables and makes the code more tractable, both for you and others who might look at it later on.

How would you refer to spreadsheets from now on?

Advertisements

8 thoughts on “Codename to the Rescue! – The best way to refer to a Worksheet whilst developing a Spreadsheet Application

  1. Thanks a lot!
    I have made this error while coding in vba and at some point I was affraid to change the name of the worksheets because I would have to parse all of my code to update the name. This is a great improvement in my work.

    Liked by 1 person

  2. Pingback: Initialize Local Range Names in VBA Quicker | Struggling To Excel

If you liked it, let me know. If you didn't make sure you let me know!

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s