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