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.

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.

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?
Share this:
Click to share on LinkedIn (Opens in new window)
Click to share on Facebook (Opens in new window)
Click to share on Twitter (Opens in new window)
Click to share on Reddit (Opens in new window)
Click to email a link to a friend (Opens in new window)
Like this:
Like Loading...