[AccessD] With / End With or not?
ssharkins at gmail.com
Wed Sep 15 18:40:20 CDT 2021
That's really interesting -- thank you!
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
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
For iStyle = .Count to 1 Step - 1
If. ….. then
For each myStyle in ThisWorkbook.Styles
If. ….. then
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.
More information about the AccessD