We delete worksheets and many other things we do with it.Įnter the object “Worksheets” and put a dot to see all the options with them. Example #4 – Get the Count of Total Sheets in the WorkbookĪ worksheet is an object, and we can use all the properties and methods associated with it.
#Api vba set worksheet code#
Still, our code refers to the same sheet as long as it is not changing the in the visual basic editor. Now it doesn’t matter whoever changes the name of the worksheet. Now we can refer this sheet by “WS1” name. One interesting thing here is even though we have changed the name of the worksheet from “Worksheet 1” to “WS1,” we can still see the same name in the workbook. In these properties, the window changes the name of the worksheet to your name. Now select the sheet name and press the F4 key to see the properties window. To solve this issue go to the visual basic editor by pressing the ALT + F11 key. If someone changes the name of the worksheet, then again, we will get the “Subscript out of range error.” There is one more problem with referring the sheets by its name. Run this code through the F5 key or manually and see the result. For example, if we do not have a variable named x but use the msgbox function on x, we will receive a subscript out of range error. This will select the exact sheet it doesn’t matter where it is placed in the workbook.īut if you tried to access the chart sheet with the “Worksheets” object, we will get “ Subscript out of range error Subscript Out Of Range Error Subscript out of range is an error in VBA that occurs when we attempt to reference something or a variable that does not exist in the code.
![api vba set worksheet api vba set worksheet](https://docs.aspose.com/cells/java/add-a-library-reference-to-vba-project-in-workbook/add-a-library-reference-to-vba-project-in-workbook_1.png)
For example, if we want to select the sheet “Worksheet 3,” then you can use the below code. Selecting the sheets by name their name is the accurate way of referring the sheet. Now it will select the exact third sheet. To select the third sheet of all the sheets in the workbook, use Sheets object. As I told earlier, the “Worksheets” object considers only worksheets, not chart sheets. This is because I have used the Worksheet object, not Sheets object.
![api vba set worksheet api vba set worksheet](https://www.exceltip.com/wp-content/uploads/2020/01/Capture-25.jpg)
If you look at the above image, it had selected the 4 th worksheet when I asked to select the 3 rd worksheet. Now see what happens when you run the code manually or using the F5 key code. I will run this code using the F5 key or manually and see the result. Select means it will select the second worksheet of the workbook. If I use the numbering to select the worksheet, then I can use the number as the worksheet reference.
#Api vba set worksheet download#
You can download this VBA Worksheet Object Template here – VBA Worksheet Object Template Example #1įor example, assume you have a total of 5 sheets in your workbook, and the name of those worksheets are “Worksheet 1”, “Worksheet 2”, “Worksheet 3”, “Chart Sheet 1”, and “Chart Sheet 2”. Syntax of VBA WorksheetsĪs I told, the worksheet is an object variable. So, as part of the code, if you want to use worksheets, objects remember this point. Here all the sheets are worksheets, so the count of both “Worksheet” and “Sheets” is 3. Here “Worksheet” count is 3, and the “Sheets” count is 2. Out of these 5 sheets, 3 are worksheets, and 2 are chart sheets.
![api vba set worksheet api vba set worksheet](https://www.automateexcel.com/excel/wp-content/uploads/2019/06/vba-activesheet.png)
In the above, we have a total of 5 sheets. “Sheets” considers all the worksheets in the workbook, including the chart sheet. The worksheet tab in excel considers only the worksheets in the workbook except for chart sheets. In excel, we can see two kinds of sheets, one is regular worksheets, and another one is chart sheets. I know your question is what the difference between them is. There are two ways of referring to the worksheet one using the “Worksheet” object and another one using the “Sheets” object. All the collections of a worksheet are called “Worksheets”. In normal excel file, we call it as sheets but in VBA terminology it is called as “Worksheet”. It is very important to understand the concept of Worksheets in VBA because all the time we work with worksheets.
![api vba set worksheet api vba set worksheet](https://www.dataquest.io/wp-content/uploads/2019/09/xlqings-image-3.jpg)
Excel is a workbook and in that workbook, it contains worksheets or sheets.