What is Excel VBA Worksheets?
VBA Worksheet is an object that is part of the VBA Workbook. VBA Worksheet is an object that helps us select any specific worksheet; we can insert a new sheet, reference any worksheets and Worksheets.
Sheets: In Excel, we have worksheets and chart sheets.
Worksheets: VBA Worksheets reference only worksheets and do not consider chart worksheets.
Table of contents
- A VBA Worksheet is an object that can be used to reference worksheets and play around with properties, methods, and objects.
- We can activate any worksheets by index number or using the worksheet name.
- We can give the name to the worksheet in the Visual Basic editor to avoid users changing the worksheet’s name.
- VBA worksheets will add new worksheets. Similarly, we can delete and rename worksheets.
Syntax of VBA Worksheets
Following is the syntax of the VBA worksheets object.
We can give the worksheet name in double quotes or its index number in the syntax. For example, the following code will activate the 1st worksheet of the workbook.
Excel VBA – All in One Courses Bundle (35+ Hours of Video Tutorials)
If you want to learn Excel and VBA professionally, then Excel VBA All in One Courses Bundle (35+ hours) is the perfect solution. Whether you’re a beginner or an experienced user, this bundle covers it all – from Basic Excel to Advanced Excel, Macros, Power Query, and VBA.
How to Use Worksheets Objects in VBA?
Let us show you a step-by-step approach to using the VBA worksheet object.
Before we start the steps, we have the following worksheets in our Excel workbook. We will show you different ways of using the VBA worksheets object.
Step 1: Start the sub-procedure by naming the macro.
Step 2: Inside the sub-procedure, enter the Worksheets object name, and we can see that in the IntelliSense list.
Step 3: Open the parenthesis for the Worksheet object, and you can see the syntax for the VBA Worksheet.
Step 4: Assume we must activate the worksheet “Sheet2.” Then, we can pass the index number 2 because the worksheet “Sheet2” is in the 2nd list.
Step 5: Close the parenthesis enter dot and choose the “Activate” method of the worksheet.
Before we run this code, let us select the 4th worksheet.
Now execute the code, which should activate the VBA Worksheet “Sheet2”.
However, the problem with using an index number to activate any worksheet is that whenever their position in the worksheet list is changed, the VBA code with the index number will no longer choose the “Sheet2” worksheet; instead, it will choose whichever is in the second position.
For example, let us swap the position of the 2nd worksheet and the 5th worksheet.
Now, we have the “Cost” worksheet in second place and the “Sheet2” worksheet in fifth place. Let’s run the code, and it will activate the following worksheet.
This time, it activates the “Cost” worksheet because while executing the code, the “Cost” worksheet is in the second place. Hence, using index numbers to select any worksheets would lead to error-prone work.
Instead, we can use the worksheet name to activate any worksheets.
Now, if we run this code, it will always activate the worksheet “Sheet2,” irrespective of the position in the workbook.
Examples of Excel VBA Worksheets
Let us show you various examples of using the worksheets object in the practical world.
Example #1: Select Worksheets by Name
Referring to the worksheet by their name is the most accurate way of selecting any worksheet. For example, look at the following worksheets in an Excel workbook.
We have five different worksheets. Let’s write a code to select the worksheet “Sales.” Inside the sub-procedure, enter the VBA worksheets object.
Enter the desired worksheet name in double quotes.
Close the bracket and enter to access all the properties, objects, and methods associated with the VBA worksheets object.
The problem with accessing the worksheet directly using the worksheet object is we don’t get to see the properties, objects, and methods associated with the VBA worksheets object.
Hence, we will use the variables to set worksheet reference to a variable and then use the variable going forward instead of the long worksheet name. Define a variable using the DIM keyword.
Once the variable is defined, we must assign a VBA data type. Let’s assign a Worksheet variable since we will work with a VBA worksheet object.
Next, use the “Set” keyword and assign the worksheet reference to this variable.
The variable WS holds the reference of the worksheet “Sales.” Hence, we can use the variable name instead of the full worksheet name. Enter the worksheet variable name and enter a dot.
As we can see, the IntelliSense list shows all the properties, objects, and methods associated with the VBA worksheet object.
From the IntelliSense list, choose the “Activate” method.
Dim Ws As Worksheet
Set Ws = Worksheets(“Sales”)
Let’s execute this code, and it should activate the worksheet “Sales”.
Example #2: Problem with Worksheet Name
One of the issues of dealing with worksheet names to activate worksheets is if somebody changes the name of the worksheet, then the written code no longer functions, leading to a subscript out-of-range error. For example, let’s take the same code from the previous example.
Dim Ws As Worksheet
Set Ws = Worksheets(“Sales”)
This code will select the worksheet named “Sales.”
Assume you sent the file to someone, and they need to be made aware of the automation of the work through VBA, and they change the worksheet name to something else.
The worksheet name has been changed from “Sales” to “Sales Report.” Let’s execute the code and see what happens.
We have the “Run Time Error ‘9’ Subscript out of range.”
In the code, we have given the worksheet name “Sales,” but the worksheet name has been changed to “Sales Report.” Hence, the code didn’t recognize the worksheet in the Excel workbook. To deal with such scenarios, we can use special techniques. Let’s follow the steps listed below.
Step 1: Press ALT + F11, which will take us to the Visual Basic Editor window.
Step 2: On the left side, the “Microsoft Excel Objects” section contains all the worksheets this workbook has.
Choose the worksheet name “Sales” and press the F4 key. It will open the properties window.
Step 3: Choose the (Name) property and give the name as “Sales.”
Step 4: Now, in the code, instead of using the VBA worksheets object name and the worksheet name, use the name “Sales.”
If the user changes the worksheet name from “Sales,” it will not impact the code’s functionality. It will still activate the worksheet “Sales” only.
Example #3: Get the Count of Total Sheets in the Workbook
We can use the VBA worksheet object to get the count of the total number of worksheets in the given workbook.
Let’s look at the following worksheets image.
In total we have 6 worksheets. We can use the following code to get the total count of these worksheets.
‘Define a variable to assign worksheets count
Dim WS_Count As Long
‘Get the worksheet count and assign to variable
WS_Count = Worksheets.Count
‘Show the count in a message box
Execute the code.
It is interesting, as we have six worksheets, but we got the count as 5.
We have a chart sheet, and the VBA Worksheet will not consider this. Hence, it excludes that from the count and retrieves only 5.
Now look at the following image.
Including the chart sheet, we have 5 in total.
Let’s execute the code now and see what happens.
Interesting! If we exclude the chart sheet, we should get the worksheets count as 4. Instead, we got the count as 5.
The VBA worksheet object counts the hidden worksheets whenever we use the Count method.
The worksheet “Sheet1” is hidden hence we have got the count as 5 not as 4.
Example #4: Methods using Worksheet Object
Let’s explore some of the common methods to be used in worksheet objects, and the following are some of the commonly used methods in worksheets.
- VBA Worksheets Add
- VBA Worksheets Delete
- VBA Worksheets Copy
VBA Worksheets Add: We can use this method to add new worksheets. The following is the syntax of the worksheet method.
- Before: We can choose before which worksheet we need to add the new worksheet.
- After: We can choose after which worksheet we need to add the new worksheet.
- Count: How many worksheets we must add.
Like this we can make use of these syntax expressions and add a new sheet.
For example, the following code will add two worksheets after the worksheet “Intro.”
Worksheets.Add After:=Worksheets(“Intro”), Count:=2
Let’s execute this code, and we will get two new worksheets after the worksheet “Intro.”
VBA Worksheets Delete: We can use this method to delete any worksheets. The syntax of this method is below.
Worksheets(“Name of the worksheet”).Delete
We need to enter the worksheet name, which is to be deleted from the workbook.
It will delete the worksheet “Sheet1”.
If we do not provide any of the worksheet’s names and use the delete method, it will
delete all the worksheets.
It will delete all the worksheets except chart sheets.
VBA Worksheets Copy: We can copy the entire worksheet to a new workbook by using the copy method of the worksheet. The following is the syntax of the VBA Worksheets Copy method.
Like the Add method, we can choose before and after worksheets. The following code will copy all the worksheets to the new workbook.
The following code will copy only the “Sales” worksheet to the new workbook.
Important Things to Note
- VBA Worksheet object will show run time error ‘9’ Subscript Out of Range error if the given worksheet name is incorrect or not recognized by VBA.
- Since the worksheet is an object, we need to use the “Set” keyword to set the worksheet reference to any of the variables.
- Worksheets.Delete method will result in deleting all the worksheets of the active workbook. Hence, be specific in deleting any of the worksheets.
- VBA worksheets will not consider chart sheets as worksheets.
Frequently Asked Questions (FAQs)
We must use the Array function inside the VBA Worksheet object to select multiple worksheets in Excel VBA.
The following code will select worksheets, “Sheet1”, “Sheet2”, and “Sheet3”.
Worksheets(Array(“Chart1”, “Sheet1”, “Intro”, “Sales”)).Select
We need to specify the worksheet name and choose the activate method to switch between worksheets.
Sheets: In Excel, we have worksheets and chart sheets. As part of VBA Sheets, all these sheets are considered as VBA Sheets.
Sheets = Worksheets + Sheets
Worksheets: VBA Worksheets references only worksheet and do not consider chart worksheets as VBA worksheets.
Worksheets = All Worksheets – Chart Sheets
To unhide all worksheets, we need to use FOR EACH loop, which will go through all the worksheets and unhide them.
The following is the code to unhide all worksheets in one go.
Dim Ws As Worksheet
For Each Ws In ActiveWorkbook.Worksheets
Ws.Visible = xlSheetVisible
This article must be helpful to understand the VBA Worksheet, with its syntax and examples. You can download the template here to use it instantly.
This has been a guide to VBA Worksheets. We learn how to use Worksheet object in excel VBA with its syntax, examples & errors. You can learn more from the following articles –