Worksheet and chart sheet objects are also members of the Sheets collection.
If you wish to process a workbook in the order that the sheets occur, you can step through the Sheets collection.
Doing this you can examine the Type property of individual objects within the collection to determine what type of object it is.
Excel defines four types of objects that can belong to the Sheets collection:
Excel doesn't always return what you would expect for the Type property. Instead, if you examine the Type property for a chart, it returns a value equal to xlExcel4MacroSheet. This can cause problems for any macro.
The way around this, then, is to compare the name of each item in the Sheets collection against those in the Charts collection. If the name is in both collections, than it is safe to assume that the sheet is a chart. If it is not in both, then you can analyze further to see if the worksheet is one of the other types. The following macro, SheetType, follows exactly this process:
When this macro is run, you will see a single message box that shows the name of each sheet in your workbook, along with what type of sheet it is.
This code will tell you what types of sheets are in a workbook; it doesn't tell you what type of workbook they are contained in. (In other words, the macro doesn't care what version of Excel you are working in. Sheet types haven't changed since the introduction of VBA years and years ago.)
Microsoft Accredited IT Training Provider
Microsoft Certified Training Materials
IT Training Courses delivered by Microsoft Accredited Facilitators
Microsoft MOS Test Centre London
No comments:
Post a Comment