CODEWITHSUNDEEP
vbaloopsexcel
Cutter
Cutter

Reputation: 1820

How to adapt a loop's structure to a user's preference?

I'm using a loop to fill a collection. There are a few properties for each item of the collection, but some of these properties are optional. The user is prompted to choose which properties will be copied to the collection. Is it possible to omit the code for the optional properties if the user has chosen to ignore them?

Sub fillcoll()
    Dim coll as Collection
    Set coll = New Collection
    Dim NewItem as Class1

    For each r in Selection.Rows
        Set NewItem = New Class1

        If Userform1.Checkbox1.Value = True then
            NewItem.Property1 = somearray1(r.Row)
        End If

        If Userform1.Checkbox2.Value = True then
            NewItem.Property2 = somearray2(r.Row)
        End If

        If Userform1.Checkbox3.Value = True then
            NewItem.Property3 = somearray3(r.Row)
        End If

    Next r
End Sub

With this code, the Checkboxes' values are read at each iteration. I fear that this may slow down the program's execution unnecessarily. The checkboxes could be read once and the loop's contents would adapt to the checkboxes' values. Is this possible?

Thanks in advance.

Upvotes: 1

Views: 75

Answers (1)

user1054204
user1054204

Reputation:

Read the checkboxes at the beginning,out of the loop, and assign their values to three booleans or an array of booleans. then you just read from the boolean variables every time.

this will improve performance since you do not need to access any object variable, but just a boolean that lays inside your class/object.

Upvotes: 1

Related Questions