Pros/Cons of For..Next with With...End With Combinations?

[Rufus Clupea]

In reviewing/rewriting some code, I've come across a bit of a (for me) conundrum.

I realize this is probably another, "It depends..." situation, so if anyone could talk about (or point me to an article on) the pros & cons...

Here are the 2 situations...

Public Sub Sample1(Low As Long, High As Long)

Dim i As Long

For i = Low To High
    With UserForm1.Controls("OptionButton" & i)
        If .Value = True Then
            .Font.Bold = True:
            .ForeColor = &H80000012:
            .TabStop = True
        Else
            .Font.Bold = False:
            .ForeColor = &H80000011:
            .TabStop = False
        End If
    End With
Next i
 
End Sub
 

Public Sub Sample2(Low As Long, High As Long)

Dim i As Long

With UserForm1
    For i = Low To High
        If Controls("OptionButton" & i).Value = True Then
            Controls("OptionButton" & i).Font.Bold = True:
            Controls("OptionButton" & i).ForeColor = &H80000012:
            Controls("OptionButton" & i).TabStop = True
        Else
            Controls("OptionButton" & i).Font.Bold = False:
            Controls("OptionButton" & i).ForeColor = &H80000011:
            Controls("OptionButton" & i).TabStop = False
        End If
    Next i
End With

End Sub

The first example (WITH/END WITH inside the FOR/NEXT loop) seems briefer, and more readable, but the WITH/END WITH is executed i times. (Is that good or bad practice?) Granted, there aren't (usually) many OptionButtons in a group, but if the Sub were manipulating data in a large array or something...?

The second example (WITH/END WITH outside the FOR/NEXT loop) is... 'wordier', but the WITH/END WITH only need be executed once. Again, I spoze one criterion is the size of i; are there any others?

FWIW, I did try the search facility--and google; neither seems to like (combination of) search terms.

Once again, TYA

 

[Kyle123]

The first should be more efficient since you are creating a variable once and referencing it. The latter requires finding the correct control multiple times per iteration. The use of With (and the loop) is kind of irrelevant here (aside from the fact that you aren't actually using the with in the second sub), the question is really which is more efficient:

Dim a as Object
Set a = SomeControl
a.Value1 = "abc"
a.Value2 = 234

Or

SomeControl.Value1 = "abc"
SomeControl.Value2 = 234

The first should be more efficient, the second depends on the costliness of returning SomeControl. Specifically in your example:

Controls("OptionButton" & i).TabStop = False

You have to look up the control object from a collection multiple times, this is clearly less efficient than returning it once and using that pointer to reference its properties.

The effect is simply compounded when using a loop.

 

[Rufus Clupea]

The first should be more efficient since you are creating a variable once and referencing it. The latter requires finding the correct control multiple times per iteration. The use of With (and the loop) is kind of irrelevant here (aside from the fact that you aren't actually using the with in the second sub),

Actually, I am--I (hurriedly) wrote the example incorrectly; those "Controls" should have been ".Controls" (My bad. )

the question is really which is more efficient:

Sorry, that's what I thought I was asking.

You have to look up the control object from a collection multiple times, this is clearly less efficient than returning it once and using that pointer to reference its properties.

The effect is simply compounded when using a loop.

I'm sure that's "clear" to some/many; I did not know/understand that. Unfortunately, I number among those who are trying to learn these subtleties; that's why I ask.

 

[Kyle123]

I may be wrong, but I feel that having read my post back, it comes across as condescending. That certainly wasn't the intention, so I apologise if it was taken that way. I was attempting to clarify and reframe the question.

99/100 it probably doesn't matter whichever way you do it. People have a tendency to prematurely optimise when it comes to Excel on these forums, this would probably be one of the lower priority things when looking how to optimise code - the only exception being if you knew it was particularly expensive to get a handle to the object. I can't think of any specific examples built into Excel right now, but I have been in this situation where I have written objects that would be expensive to requery.

 

[Rufus Clupea]

99/100 it probably doesn't matter whichever way you do it. People have a tendency to prematurely optimise when it comes to Excel on these forums, this would probably be one of the lower priority things when looking how to optimise code - the only exception being if you knew it was particularly expensive to get a handle to the object. I can't think of any specific examples built into Excel right now, but I have been in this situation where I have written objects that would be expensive to requery.

IC. The greatest number of OptionButtons I'll be checking at a time is 14--no biggie. However, I'll have similar handling Subs for CheckBoxes (~75) and SpinButtons (~300), so I can see the possibility of things bogging down (or not) depending on which way I go. (Breaking those down into smaller groups will affect functionality, so I'd prefer to optimize from the get-go.)

You've given me some things to think about/explore.

TA