When to use With - End With?

[Adrian D. Bailey]

I have a large set of books and loads of macros - they all work OK, but I've
been trying to tweak some of them to make them go a little faster.
I've discovered that sometimes adding the With - End With construct to a
macro can make it go faster (as I expected) but othertimes it makes it go
slower.
What are the rules?

Here is an example of a change that made things slower (part of a
function)...
Original:
Set rng = Workbooks("data.xls").Worksheets("students").Range("1:1")
lkcol = Application.WorksheetFunction.Match(lookupkey, rng, 0)
rtncol = Application.WorksheetFunction.Match(rtnkey, rng, 0)
With Workbooks("data.xls").Worksheets("students")
Set rng2 = .Range(.Cells(1, lkcol), .Cells(65535, lkcol))
End With
rtnrow = Application.WorksheetFunction.Match(lookupval, rng2, 0)
Sinfo = Workbooks("data.xls").Worksheets("students").Cells(rtnrow, rtncol)

Revised, and markedly slower:
With Workbooks("data.xls").Worksheets("students")
Set rng = .Range("1:1")
lkcol = Application.WorksheetFunction.Match(lookupkey, rng, 0)
rtncol = Application.WorksheetFunction.Match(rtnkey, rng, 0)
Set rng2 = .Range(.Cells(1, lkcol), .Cells(65535, lkcol))
rtnrow = Application.WorksheetFunction.Match(lookupval, rng2, 0)
Sinfo = .Cells(rtnrow, rtncol)
End With
--
Adrian D.Bailey, Information and Systems Manager, Dept.Human Sciences
Loughborough University, Loughborough Leics, LE11 3TU, UK.
(e-mail address removed) Tel: 01509 223007 Fax: 01509 223940

Community Warden, Storer and Burleigh Areas. Out-of-hours Tel: 01509 563263
--

 

[NickHK]

Adrian,
Apart from the question of whether you feel it improves or degrades
readability of code, With block speed code up if they reduce the levels of
the hierarchy that the compiler needs to resolve. You can tell this by
counting the periods (.).

e.g.
ThisWorkbook.Worksheets(1).Range("A1").Borders(xlEdgeTop).LineStyle =
xlContinuous

'4 periods

With ThisWorkbook.Worksheets(1).Range("A1").Borders(xlEdgeTop)
.LineStyle = xlContinuous
End With

'Still 4 periods, so no gain

But:
ThisWorkbook.Worksheets(1).Range("A1").Borders(xlEdgeTop).LineStyle =
xlContinuous
ThisWorkbook.Worksheets(1).Range("A1").Borders(xlEdgeTop).Weight = xlThin
ThisWorkbook.Worksheets(1).Range("A1").Borders(xlEdgeTop).ColorIndex =
xlAutomatic
'Total 12 periods

With ThisWorkbook.Worksheets(1).Range("A1").Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
'Total 6 periods

You can also define your own variables to help the compiler and cut down the
number of levels
Dim brder As Border

Set brder = ThisWorkbook.Worksheets(1).Range("B2").Borders(xlEdgeTop)

With brder
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
'Although in this case you gain nothing from the With, only from the
variable.

Not aware of a downside to either method, so at a loss to explain your
slower execution.
In a related comment, avoid using the generic "Object", unless you need to,
e.g. because of late binding.

NickHK

 

[Peter T]

Like Nick I don't understand why your With - End With construct slows things
down, that has never been my experience.

I wonder if your observation is purely related to the worksheet functions in
your code. Although in cell formulas they are very fast, when used in VBA
they are relatively slow. But I would have thought when using them any
difference between w/w-out the With construct would be insignificant.
Sometimes it can be faster to convert cell values to an array and simulate
the worksheet function in code. Another way if using several in the same
function -

Dim wfn as WorksheetFunction
Set = Application.WorksheetFunction
result = wfn.somefunction(args)

Regards,
Peter T

 

[Peter T]

typo

Dim wfn as WorksheetFunction
Set = Application.WorksheetFunction
result = wfn.somefunction(args)

Dim wfn as WorksheetFunction
Set wfn = Application.WorksheetFunction
result = wfn.somefunction(args)

Peter T

Like Nick I don't understand why your With - End With construct slows things
down, that has never been my experience.

I wonder if your observation is purely related to the worksheet functions in
your code. Although in cell formulas they are very fast, when used in VBA
they are relatively slow. But I would have thought when using them any
difference between w/w-out the With construct would be insignificant.
Sometimes it can be faster to convert cell values to an array and simulate
the worksheet function in code. Another way if using several in the same
function -

Dim wfn as WorksheetFunction
Set = Application.WorksheetFunction
result = wfn.somefunction(args)

Regards,
Peter T