The Complete Guide To Worksheets in Excel VBA
“The visionary starts with a clean sheet of paper, and re-imagines the world” – Malcolm Gladwell
© Marek Uliasz | Dreamstime.com – Data Spreadsheet On Digital Tablet Photo
There three most important elements of VBA are the Workbook, the Worksheet and Cells. Of all the code your write, 90% will involve one or all of them. In a previous post( The Complete Guide to Workbooks in Excel VBA ) I dealt with the workbook. Now it is the turn of the worksheet.
The most common use of the worksheet in VBA is for accessing its cells. Sometimes you may use it to protect, hide or move a worksheet but mainly you will use it to perform some action on a cell or a range of cells.
Using Worksheets is more straightforward than using workbooks. With workbooks you may need to open them, find which folder they are in, check if they are in use and so on. With a worksheet, it either exists in the workbook you specified or it doesn’t.
Accessing the Worksheet
In VBA each workbook has a collection which contains the worksheets that belongs. This collection is called Worksheets and is used in a very similar way to a Workbooks collection. To get access to a worksheet all you have to do is supply the name.
The code below writes “Hello World” in Cell A1 of Sheet1, Sheet2 and Sheet3 of the current workbook.
Public Sub WriteToCell1()
' Write to cell A1 in Sheet1,Sheet2 and Sheet3
ThisWorkbook.Worksheets("Sheet1").Range("A1") = "Hello World"
ThisWorkbook.Worksheets("Sheet2").Range("A1") = "Hello World"
ThisWorkbook.Worksheets("Sheet3").Range("A1") = "Hello World"
End Sub
The following examples show how to hide/Unhide and Protect/Unprotect Sheet1.
Public Sub HideWorksheet()
ThisWorkbook.Worksheets("Sheet1").Visible = False
End Sub
Public Sub UnHideWorksheet()
ThisWorkbook.Worksheets("Sheet1").Visible = True
End Sub
Public Sub ProtectWorksheet()
ThisWorkbook.Worksheets("Sheet1").Protect Password:="MyPassword"
End Sub
Public Sub UnProtectWorksheet()
ThisWorkbook.Worksheets("Sheet1").Unprotect Password:="MyPassword"
End Sub
If you use a worksheet name that does not exist in the current workbook then you will get a “subscript out of range” error. This may happen if the worksheet name is changed or if you spell it incorrectly.
Using the Index to Access the Worksheet
So far we have been using the sheet name to access the sheet. You can also use an index like we did for Workbooks. The index refers to the sheet tab position in the workbook. As the position can easily be changed by the user it is not a good idea to use this.
' Using this code is a bad idea as sheet positions changes all the time
Public Sub UseSheetIdx()
With ThisWorkbook
' Left most sheet
Debug.Print .Worksheets(1).Name
' The third sheet from the left
Debug.Print .Worksheets(3).Name
' Right most sheet
Debug.Print .Worksheets(.Worksheets.Count).Name
End With
End Sub
In the example I used Debug.Print to print to the Immediate Window. To view this window select View->Immediate Window(or Ctrl G)
Using the code name of a Worksheet
The best method of accessing the worksheet is using the code name. Each worksheet has a sheet name and a code name. The sheet name is the name that appears in the worksheet tab in Excel.
Changing the sheet name does not change the code name meaning that referencing a sheet by the code name is a good idea.
If you look in the VBE property window you will see both names. In the image you can see that the code name is the name outside the parenthesis and the sheet name is in the parenthesis.
You can change both the sheet name and the code name in the property window of the sheet(see image below).
If your code refers to the code name then the user can change the name of the sheet and it will not affect your code. In the example below we reference the worksheet directly using the code name.
Public Sub UseCodeName2()
' Using the code name of the worksheet
Debug.Print CodeName.Name
CodeName.Range("A1") = 45
CodeName.Visible = True
End Sub
This makes the code easy to read and safer from the user changing the sheet name. However there is one drawback to using the code name. It can only refer to worksheets in the workbook that contains the code i.e. ThisWorkbook. However we can use a simple function to find the code name of a worksheet in a different workbook.
Public Sub UseSheet()
Dim sh As Worksheet
' Get the worksheet using the codename
Set sh = SheetFromCodeName("CodeName", ThisWorkbook)
' Use the worksheet
Debug.Print sh.Name
End Sub
' This function gets the worksheet object from the Code Name
Public Function SheetFromCodeName(sCodeName As String, bk As Workbook) _
As Worksheet
Dim sh As Worksheet
For Each sh In bk.Worksheets
If sh.CodeName = sCodeName Then
Set SheetFromCodeName = sh
Exit For
End If
Next sh
End Function
Using the above code means that if the user changes the name of the worksheet then your code will not be affected.
There is another way of getting the sheet name of an external workbook using the code name. You can use the VBProject element of that Workbook. You can see how to do this in the example below. I have included this for completeness only and I would recommend using the method in the previous example rather than this one.
Note: In this example when retrieving a worksheet name you always use the text “Name” for the Properties argument.
Public Function SheetFromCodeName2(sCodeName As String, bk As Workbook) _
As Worksheet
' Get the sheet name from the CodeName using the VBProject
Dim sheetName As String
sheetName = bk.VBProject.VBComponents(sCodeName).Properties("Name")
' Use the sheet name to get the worksheet object
Set SheetFromCodeName2 = bk.Worksheets(sheetName)
End Function
Using the ActiveSheet Object
The ActiveSheet object refers to the worksheet that is currently active. As the worksheet that is currently active changes all the time it is not a good idea to use this.
If you use this then it is only a matter of time before you read or write using the wrong Worksheet.
Accessing the Worksheet in a Nutshell
The following table gives a brief overview of the methods of accessing the worksheet
Access Method
Details of Use
.Worksheets(“Sheet1”)
The sheet with name Sheet1. Vunerable to sheet name changing. It is okay to use but CodeName is better.
CodeName
Code name of Worksheet. Use this in the workbook that contains the code. Safe if the sheet name changes.
SheetFromCodeName
Custom made function to get the sheet based on the code name. Use this with sheets that are not in the workbook that contains the code. Safe if the sheet name changes.
.Worksheets(1)
Index is based on worksheet order in workbook. Avoid this.
ActiveSheet
The worksheet that is currently active. Avoid this.
Declaring a Worksheet Object
Declaring a worksheet object is useful for making your code neater and easier to read. The next example shows code for updating ranges of cells. The first Sub does not declare a worksheet object. The second sub declares a worksheet object and the code is therefore much clearer.
Public Sub SetRangeVals()
Debug.Print ThisWorkbook.Worksheets("Sheet1").Name
ThisWorkbook.Worksheets("Sheet1").Range("A1") = 6
ThisWorkbook.Worksheets("Sheet1").Range("B2:B9").Font.Italic = True
ThisWorkbook.Worksheets("Sheet1").Range("B2:B9").Interior.Color = rgbRed
End Sub
Public Sub SetRangeValsObj()
Dim sht As Worksheet
Set sht = ThisWorkbook.Worksheets("Sheet1")
sht.Range("A1") = 6
sht.Range("B2:B9").Font.Italic = True
sht.Range("B2:B9").Interior.Color = rgbRed
End Sub
You could also use the With keyword with the worksheet object as the next example shows.
Public Sub SetRangeValsObjWith()
Dim sht As Worksheet
Set sht = ThisWorkbook.Worksheets("Sheet1")
With sht
.Range("A1") = 6
.Range("B2:B9").Font.Italic = True
.Range("B2:B9").Interior.Color = rgbRed
End With
End Sub
Adding a New Worksheet
The examples in this section show you how to add a new worksheet to a workbook. If you do not supply any arguments to the Add function then the new worksheet will be placed before the active worksheet.
When you add a Worksheet it is created with a default name like “Sheet4″. If you want to change the name then you can easily do this using the Name property. The following example adds a new worksheet and changes the name to “Accounts”. If a worksheet with the name “Accounts” already exists then you will get an error.
Public Sub AddSheet()
Dim sht As Worksheet
' Adds new sheet before active sheet
Set sht = ThisWorkbook.Worksheets.Add
' Set the name of sheet
sht.Name = "Accounts"
' Adds 3 new sheets before active sheet
ThisWorkbook.Worksheets.Add Count:=3
End Sub
In the previous example you are adding worksheets in relation to the active worksheet. This is never a good idea. You can specify the exact position to place the worksheet.
To do this you need to specify which worksheet the new one should be inserted before or after. The following code shows you how to do this.
Public Sub AddSheetFirstLast()
Dim shtNew As Worksheet, shtFirst As Worksheet, shtLast As Worksheet
With ThisWorkbook
Set shtFirst = .Worksheets(1)
Set shtLast = .Worksheets(.Worksheets.Count)
' Adds new sheet to first position in the workbook
Set shtNew = Worksheets.Add(Before:=shtFirst)
shtNew.Name = "FirstSheet"
' Adds new sheet to last position in the workbook
Set shtNew = Worksheets.Add(After:=shtLast)
shtNew.Name = "LastSheet"
End With
End Sub
Accessing all the Worksheets
The Worksheets member of Workbooks is a collection of worksheets belonging to a workbook. You can go through each sheet in the worksheets collection.
The following example shows you how to do this. It is very similar to code for going through all the open workbooks.
Public Sub WriteToCell2()
' Writes "Hello World" into cell A1 for each worksheet in thisWorkbook
Dim sht As Worksheet
For Each sht In ThisWorkbook.Worksheets
sht.Range("A1") = "Hello World"
Next sht
End Sub
You have seen how to access all open workbooks and how to access all worksheets in ThisWorkbook. Lets take it one step further. Lets access all worksheets in all open workbooks.
Note: If you use code like this to write to worksheets then back everything up first as you could end up writing the incorrect data to all the sheets.
Public Sub AllSheetNames()
' Prints the workbook and sheet names for all sheets in open workbooks
Dim wrk As Workbook
Dim sht As Worksheet
For Each wrk In Workbooks
For Each sht In wrk.Worksheets
Debug.Print wrk.Name + ":" + sht.Name
Next sht
Next wrk
End Sub
Using the Sheets Collection
The workbook has another collection similar to Worksheets called Sheets. This causes confusion at times among users. To explain this first you need to know about a sheet type that is a chart.
It is possible in Excel to have a sheet that is a chart. To do this
Create a chart on any sheet
Right click on the chart and select Move
Select the first option which is “New Sheet” and click Ok.
Now you have a workbook with sheets of type worksheet and one of type chart.
The Worksheet collection refers to all worksheets in a workbook. It does not include sheets of type chart.
The Sheet collection refers to all sheets belonging to a workbook including sheets of type chart.
There are two code examples below. The first goes through all the Sheets in a workbook and prints the name of the sheet and type of sheet it is. The second example does the same with the Worksheets collection.
To try out these examples you should add a Chart sheet to your workbook first so you will see the difference.
Public Sub CollSheets()
Dim sht As Variant
' Display the name and type of each sheet
For Each sht In ThisWorkbook.Sheets
Debug.Print sht.Name + " is type " + TypeName(sht)
Next sht
End Sub
Public Sub CollWorkSheets()
Dim sht As Variant
' Display the name and type of each sheet
For Each sht In ThisWorkbook.Worksheets
Debug.Print sht.Name + " is type " + TypeName(sht)
Next sht
End Sub
The Sheets collection is only really useful if you want to access all your sheets and some of them are chart sheets. If you do not have charts as sheets then you do not need to use it.
Summary
The following is a brief summary of the main points of this post
If you are referencing the worksheet containing the code then use the code name of the worksheet to directly access the sheet
If you are referencing an open workbook other than the one with the code then use the custom function SheetFromCodeName with the code name to get the worksheet
To reduce the number of times you have to type an object name in your code use the With keyword.
To make the purpose of your code clearer and avoid long winded names like ThisWorkbook.Worksheets(“Sheet1″).Range(“A1″) declare a worksheet object e.g. Dim sht as Worksheet.
To run through all worksheets in a workbook use For Each sht in thisWorkbook.Worksheets where sht is a worksheet object.
The Workbook collections Sheets and Worksheets differ in that Sheets also includes sheets that are Charts. The Worksheets collection only contains sheets that are Worksheets.
When referencing Worksheets avoid using ActiveSheet and Worksheets(Index) as the reference to a particular worksheet is temporary.
Conclusion
This was an in-depth post about using the Worksheet in VBA. Worksheets are member of workbooks so it is important to understand these also. If you haven’t already the feel free to check out my post on Workbooks.
Workbooks, Worksheets and Cells are the three most important parts of VBA as you will deal with them in almost every line of code you write. If you found this post beneficial then you may also want to check out my post on The Complete Guide to Ranges and Cells in Excel VBA.
If you have found these posts useful then please feel free to share them with others.
