You may have noticed I introduced the Worksheet Object in my Workbook Object example. The Worksheet Object represents the worksheets in your file. Sheet1, Sheet2 and Sheet3 are examples of Worksheet names and are the default names of a new workbook.
Much like the Workbook Object, the Worksheet Object is a member of the Worksheets Collection, so get used to seeing it written in plural.
You can refer to your worksheets in many ways. The following macros are all equivalent. Each one places a “5” in cell “A2” of worksheet “Demo,” which is the second sheet in the Excel Workbook.
Sub WorksheetDemo() Worksheets("Demo").Range("A2") = 5 End Sub
Sub WorksheetDemo() Sheets("Demo").Range("A2") = 5 End Sub
Sub WorksheetDemo() Sheet2.Range("A2") = 5 End Sub
Sub WorksheetDemo() Worksheets(2).Range("A2") = 5 End Sub
By now you’re probably starting to notice a theme; VBA offers several ways of performing the same task. The best part is that worksheet “Demo” does not have to be selected for VBA to know where to place the “5!” You’ll find this adds a lot of flexibility to what you’re able to perform in VBA.
Continue reading the CodeName Property section to understand how Example 3 works.
In Example 3,
Sheet2 is the CodeName of Worksheet “Demo.” How would you know this? Just check your VBA Editor. The first name of each worksheet under Microsoft Excel Objects is the CodeName. The second is the sheet name.
You can also return the CodeName of your Worksheet with the CodeName Property.
Sub WorksheetDemo() MsgBox (Worksheets("Demo").CodeName) End Sub
Create New Worksheet
Creating a new worksheet is simple with the Worksheets.Add Method.
Sub WorksheetDemo() Worksheets.Add End Sub
The following VBA example will delete worksheet “Sheet1.”
Sub DeleteWorksheet() Application.DisplayAlerts = False Worksheets("Sheet1").Delete Application.DisplayAlerts = True End Sub
Application.DisplayAlerts property prevents the following message from displaying:
You can rename a worksheet with the Worksheet.Name Property
Sub WorksheetDemo() Worksheets("Sheet4").Name = "Disney" End Sub
Let’s put it all together. Say you want to add a new sheet named “Vacation” and you want it placed at the end of your workbook. The following code does just that.
Sub WorksheetDemo() Worksheets.Add (After:=Worksheets(Worksheets.Count)).Name = "Vacation" End Sub
To print an entire worksheet, use the Printout Method just like you would print a range!
Sub WorksheetDemo() Worksheets("Vacation").Printout End Sub
Declare Worksheet Object
This is where you come in. I want you to try this one yourself. Remember how we declared Workbook Objects in the last lesson? I want you to do the same with the Worksheet object and play around until you get comfortable with it! The format is almost identical. I’ll even get you started with a hint:
Sub WorksheetDemo() Dim sh1 As Worksheet Set sh1 = Worksheets("Sheet1") sh1.Range("A1") = "Hello World" End Sub
Declaring your worksheet objects like this shortens your code and reduces errors, so why not practice until you get good at it!
Today you learned about the Worksheet Object. You now know 4 different ways of referring to a worksheet in your VBA code. You learned how to add a sheet, rename a sheet and reorder your sheets. You also learned how to print an entire worksheet.
I hope it’s all starting to come together. Shoot me a message if you have a question about anything you’ve learned so far. I’m here to help you.