Worksheets in Excel VBA
Worksheets in excel are the sheets that have rows and columns in which we work. Whenever we open excel, we work on them. Now when working in excel, we use functions that are called worksheet functions, and we can use similar functions in VBA. In this article, we will learn about various functions in VBA to work on the worksheet. The most important thing to remember is while working in VBA; we have to always use the worksheet function as the data in excel is stored in the worksheet itself.
A worksheet is basically an object in VBA. Now there are two different methods of using this object. One is by referring to the Worksheets method, and another is by referring to the Sheets method. Since this article is about worksheets, we will move further with the worksheets object. There are different objects to use a worksheet object. The proper way to use this object is explained below,
Worksheets( “ Sheet Name “ ). Methods
Now we need to remember all the different methods out there to use them in our code. We will capture some of the important and daily use of them by a few examples.
How to Use VBA Worksheets in Excel?
We will learn how to use a VBA Worksheets Function with few examples in excel.
Example #1 – VBA Worksheets
First, let us try to select a worksheet using the worksheet object in VBA.
Step 1: Before we move ahead, we need to open VB Editor from the developer’s tab as shown in the image below; it is available in the code’s section
Step 2: Now, once we click on it, we are in the VB Editor, and we can insert a new module from the insert tab where we will be writing all of our examples,
Step 3: Now define a subprocedure as shown below,
Code:
Sub Example1() End Sub
Step 4: Since we can see that we have three different worksheets in our workbook as sheet 1, sheet 2 and sheet 3. Let us select sheet 2 for this example,
Step 5: The method to select sheet 2 using the worksheet object is as follows,
Code:
Sub Example1() Worksheets("Sheet2").Select End Sub
Select the method we are using for this object.
Step 6: Execute the above code and see the result in the workbook as follows,
We can see that sheet 2 has been selected.
Example #2 – VBA Worksheets
Now we have sheet 2 selected, but we want to put data in cell A1 of sheet 1. To do this, we can either select sheet 1 as shown above and then use the command to put a random value in the active sheet, which is sheet 1. But that is twice the work. We can use the Activate method and put a random value.
Step 1: In the same module, define another subprocedure for our next example as follows,
Code:
Sub Example2() End Sub
Step 2: First, let us activate sheet 1 using the worksheet object as follows,
Code:
Sub Example2() Worksheets("sheet1").Activate End Sub
Step 3: Now, we will use the range property to put a random value in cell A1 as shown below,
Code:
Sub Example2() Worksheets("sheet1").Activate Range("A1").Value = 10 End Sub
Step 4: Execute the above code and see the result in sheet 1 as follows,
Since our selection was sheet 2 in the first example, This method not only selected sheet 1 for us but also we were able to put a value in cell A1.
Example #3 – VBA Worksheets
Now let us try to rename a worksheet name using the worksheet object. We will rename sheet 3 to Sheet Third.
Step 1: Again in the same module, define the next subprocedure for the third example,
Code:
Sub Example3() End Sub
Step 2: In order to change the worksheet 3 name, we need to use the .Name method as follows,
Code:
Sub Example3() Worksheets("Sheet3").Name = "Sheet Third" End Sub
Step 3: Now, if we can see the original name of the sheet before this code, as shown below,
Step 4: Execute the above code and see the result in the workbook as follows,
Example #4 – VBA Worksheets
Now in this method, first, we will add another worksheet manually using the + sign in the workbook and delete it using the worksheet object in VBA.
Step 1: First, let us create another worksheet in the workbook as shown below,
Step 2: We can see that now there is another sheet as sheet 4. Now define subprocedure for our fourth example.
Code:
Sub Example4() End Sub
Step 3: To delete the worksheet from the workbook, we will use the .Delete method to worksheet object as shown below,
Code:
Sub Example4() Worksheets("Sheet4").Delete End Sub
Step 4: Now execute the above code again, and we can see a pop up which asks us for the confirmation to delete the worksheet,
Step 5: Click on Delete, and it will delete the sheet 4 for us.
Example #5 – VBA Worksheets
Now let us use another cool feature of this object. In this example, we will count the number of worksheets present in our workbook and display them.
Step 1: In the same module, define another subprocedure as follows,
Code:
Sub Example5() End Sub
Step 2: Declare a variable as an integer as follows,
Code:
Sub Example5() Dim COunt As Integer End Sub
Step 3: Now in Count store the value of the number of worksheets in the workbook using the count method as shown below,
Code:
Sub Example5() Dim COunt As Integer COunt = Worksheets.COunt End Sub
Step 4: Display the value stored in the variable using the msgbox function as follows,
Code:
Sub Example5() Dim COunt As Integer COunt = Worksheets.COunt MsgBox COunt End Sub
Step 5: Execute the above code to get the count of worksheets in our workbook as follows,
Things to Remember
- A worksheet is an object in VBA.
- We use different methods to refer to different worksheets in VBA.
- If we enter the incorrect name of the sheets, we will encounter an error.
- The argument in the worksheet object for sheet name is treated as a string.
Recommended Articles
This is a guide to VBA Worksheets. Here we discuss how to use the Excel VBA Worksheets function along with practical examples and a downloadable excel template. You can also go through our other suggested articles –
