[AccessD] With / End With or not?

[Susan Harkins]

That's really interesting -- thank you!

Susan H. 


Arthur in some cases it is better in others  I don’t think so.

The ild school was that using With allowed the interpreter to not have to look things up so much.

Take Excel sort

The parent is the sheet, you want most of your code to act on the sort itself (and some on sort.sortfields

If I write

Wks.Sort.SortFields.Clear
Wks.Sort.SortFields.add ….
…
Wks.Sort.Apply

Once upon a time all those references to Wks and to wks.sort would cost some performance. So using

With wks.sort improved performance.

Just lately this code took all night to run and still didn’t finish, trying to delete most of over 52,000 styles that contaminated my macro workbook by copying client sheets and ranges:

Dim iStyle as long
Dim myStyles as Styles
Set myStyles = ThisWorkbook.Styles
With myStyles
   For iStyle = .Count to 1 Step - 1
    With .Item(IStyle)
      If. ….. then
           .Delete
      End If
    End With
   Next
End With

Writing instead
For each myStyle in ThisWorkbook.Styles
      With myStyle
           If. ….. then
           .Delete
           End If
       End With
Next


Orders of magnitude faster. I suspect (only) that by setting an object equal to a collection INSTEAD OF just referencing the collection in the For Next loop, that VBA wasn’t  “appreciating” the fact that with each deletion the myStyles object was shrinking. As opposed to using Thisworkbook.Styles which VBA has to evaluate after every Next statement, giving it a chance to hold less in memory with every loop.