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.
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.
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:
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.