Listing Futile Attempt to Iterate over Settings

' This does not work. The Settings object does ' not natively know how to iterate over all of ' the objects it contains. Sub BadPrintOutA11Settings() Dim oSettings As Settings Dim oSetting As Setting Dim nIndex As Integer Dim ws As Worksheet

Set oSettings = New Settings

' this does not work

For Each oSetting In oSettings

Set oSetting = oSettings.Item(nlndex) Debug.Print oSetting.Name & " = " & oSetting.Value


Set oSetting = Nothing Set oSettings = Nothing End Sub

Real collection objects support this method of iterating over the objects in the collection. If you try this using the Settings object you get the following error.

Microsoft Visual Basic

Riri-trne error M38';

Object doesn't support this property or method

End | |r gebug j|


i i

To iterate over all of the Setting objects in the Settings collection, you need to use a For.. .Next loop from 1 to a count of the number of Setting objects. Then you need to use the Item property with the index value to retrieve each Setting object. Because most collection objects are one-based rather than zero-based, I implemented the Settings object as a one-based collection. Listing 12.4 demonstrates an example of this method of iterating over the Setting objects.

0 0

Post a comment