[AccessD] With / End With or not?

[Arthur Fuller]

Thanks from me too.

On Wed, Sep 15, 2021 at 7:40 PM Susan Harkins <ssharkins at gmail.com> wrote:

> 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.
>
>
>
>
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> https://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>


-- 
Arthur