With ............... end with statement

[prajul89]

Hi all,

I usually never used With......... end with statement.

But I have noticed if I record a macro It uses With............ end with statements a lot, even you guys(professionals) use it a lot.

So what is the real use of this statement actually?

Is it because its easier to write it with less key strokes and it has a more vertical representation of codes?

Or it saves some memory or something and runs macros faster?

 

[GTO]

FWIW, IMO it can be much easier to read in some cases, and can make the code easier to follow and later edit. It does not save time or memory. It is less than likely that you'll run into anything where it noticeably slows things down though.

 

[Jon von der Heyden]

VBA knows that anything starting with a dot is either a property or a method of an object. Using the With block construct you only have to qualify the object once and within expose properties and methods. This is said to yield micro-optimisation.

For me what is more important is how tidy is makes code. Say I am working with an external workbook, dealing with its many sheets, a different ranges within each sheet:

With wkbExternal
    With .Sheets(1)
        .AutoFilterMode = False
        Set rngFilter = .Range("A1:B" & .Range("A" & .Rows.Count).End(xlUp).Row
        With rngFilter
            .AutoFilter Field:=2, Criteria1:="<>"
            .SpecialCells(12).EntireRow.Delete
            .AutoFilter
        End With
        'Do something with a different range
    End With
    With .Sheets(2)
        'Do something with this sheet
    End With
End With

If you study this code you will see that the With block construct has helped compartmentalise the code into a logical order and the blocks make it alot easier to follow...

 

[Peter_SSs]

I use it a lot. Here's another example. To me Part 1 seems a lot simpler and easier to read, type and follow than Part 2. I'm no expert in speed but it seems to me that Part 1 should be quicker since I assume the code only has to establish the range once instead of 4 times.

'Part 1
With Range("A1", Range("A" & Rows.Count).End(xlUp))
    .ClearContents
    .FormulaR1C1 = "=RC[1]"
    .Value = .Value
End With

'Part 2
Range("A1", Range("A" & Rows.Count).End(xlUp)).ClearContents
Range("A1", Range("A" & Rows.Count).End(xlUp)).FormulaR1C1 = "=RC[1]"
Range("A1", Range("A" & Rows.Count).End(xlUp)).Value = Range("A1", Range("A" & Rows.Count).End(xlUp)).Value

 

[prajul89]

Thanks Everyone for your response, its means a lot!!!!

 

[prajul89]

I use it a lot. Here's another example. To me Part 1 seems a lot simpler and easier to read, type and follow than Part 2. I'm no expert in speed but it seems to me that Part 1 should be quicker since I assume the code only has to establish the range once instead of 4 times.

'Part 1
With Range("A1", Range("A" & Rows.Count).End(xlUp))
    .ClearContents
    .FormulaR1C1 = "=RC[1]"
    .Value = .Value
End With

'Part 2
Range("A1", Range("A" & Rows.Count).End(xlUp)).ClearContents
Range("A1", Range("A" & Rows.Count).End(xlUp)).FormulaR1C1 = "=RC[1]"
Range("A1", Range("A" & Rows.Count).End(xlUp)).Value = Range("A1", Range("A" & Rows.Count).End(xlUp)).Value

Mostly I used to go for the Part 2, But as you say Part 1 looks more organized, better to understand and easy to edit.
Even I would got Part 1 from now on.

 

[GTO]

EDIT!

YIKES! What I posted went a bit opposite of what I expected. Sorry about that