Microsoft Office Tutorials and References
In Depth Information
A Realistic Example That Uses Sub Procedures
Next, I needed to find out how many sheets were in the active workbook. I searched Help for the
word Count and found out that it’s a property of a collection. I activated the Immediate window
in the VBE and typed the following statement:
Error! After a little more thought, I realized that I needed to get a count of the sheets within a
workbook. So I tried this:
Success. Figure 9-9 shows the result. More useful information.
Figure 9-9: Use the VBE Immediate window to test a statement.
What about the sheet names? Time for another test. I entered the following statement in the
This told me that the name of the first sheet is Sheet3 , which is correct (because I’d moved it).
More good information to keep in mind.
Then I remembered something about the For Each-Next construct: It’s useful for cycling
through each member of a collection. After consulting the Help system, I created a short
procedure to test it:
For Each Sht In ActiveWorkbook.Sheets
Another success. This macro displayed three message boxes, each showing a different sheet