[AccessD] With / End With or not?

[Bill Benson]

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.






On Mon, Sep 13, 2021 at 4:20 PM Arthur Fuller <fuller.artful at gmail.com>
wrote:

> I find that. in the most charitable way, a provocation to write a test
> case. I plan to create a form with a few dozen nonsense controls and then
> to code it both ways ans timethe results. Either way, I guess we will be
> dealing with milliseconds, but now my interest has been tweaked . I shall
> confine my tests to the immediate case -- no lookups or other distractions
> I shall have to flush the buffer so every call is fresh. But suddenly I am
> interested n this subject.
> I will report back with results.
>
> On Mon, Sep 13, 2021 at 3:20 PM Stuart McLachlan <stuart at lexacorp.com.pg>
> wrote:
>
> > I strongly suspect that it's purely  a source code thing and that the
> > compiler will compile both
> > to the same set of instructions using pointers to the appropriate
> objects.
> >
> > I find it hard to visualise any other way of it being done.
> >
> >
> >
> > On 13 Sep 2021 at 13:59, Arthur Fuller wrote:
> >
> > > Somewhere I read or heard or just intuited that given a block of code
> > > that frequently references Me, it's faster and better towrap te the
> > > block in a With Me / End With than to explicitly refer to Me. Is this
> > > true? Or is it just to make the code ceaner? Has anyone bothered to
> > > benchmark the difference? I' could easily build a test case, but if
> > > you've already done it, then I would be interested in your results.
> > >
> > > --
> > > Arthur
> > > --
> > > AccessD mailing list
> > > AccessD at databaseadvisors.com
> > > https://databaseadvisors.com/mailman/listinfo/accessd
> > > Website: http://www.databaseadvisors.com
> > >
> >
> >
> > --
> > AccessD mailing list
> > AccessD at databaseadvisors.com
> > https://databaseadvisors.com/mailman/listinfo/accessd
> > Website: http://www.databaseadvisors.com
> >
>
>
> --
> Arthur
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> https://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>