Microsoft Excel
Looping over collections exercise
Average difficulty

This Microsoft Excel LOOPING OVER COLLECTIONS exercise is provided to allow potential course delegates to assess their suitability for the Wise Owl EXCEL VISUAL BASIC course only, and may not be reproduced in whole or in part in any format without the prior written consent of Wise Owl.

Exercise: Renaming worksheets to bob1, etc

A full answer to this exercise is provided as part of the training course!

1)	Create a new workbook, and save this as Lots of bobs.xls. The workbook should automatically have 3 worksheets in, but if it doesn't, add some.
2)	Write a macro which renames the worksheets to Bob1, Bob2 and Bob3.  Your macro should initially: Declare an integer variable called something like SheetCounter Set the value of this to zero at the start of your program
3)	Your macro should then loop over all of the worksheets, for each: increasing the value of the variable by 1 (SheetCounter = SheetCounter + 1) setting the name of this worksheet equal to Bob followed by the varaible value
An alternative method would be to use the fact that any item in a collection has an Index property giving its ordinal number - but please only do this if you have the previous method working and some spare energy.
4)	Save the file and close it down.

Thank you for showing an interest in this Excel Visual Basic training course! As mentioned above, full answers to this and other exercises are provided on the course. On all Wise Owl scheduled courses delegates can take away these model answers (together with their own efforts!) on a free USB stick.

Return to the list of exercises for this topic