Help understanding With End-With

[YellowLabPro]

I have seen a lot of code using the With End-With method. Why, how,adn when is this used?
I did a short macro recording just to use as an example.



With ActiveSheet.PageSetup
.PrintTitleRows = ""
.PrintTitleColumns = ""
End With
ActiveSheet.PageSetup.PrintArea = ""
With ActiveSheet.PageSetup
.LeftHeader = ""
.CenterHeader = ""
.RightHeader = ""
.LeftFooter = ""
.CenterFooter = ""
.RightFooter = ""
.LeftMargin = Application.InchesToPoints(0.75)
.RightMargin = Application.InchesToPoints(0.75)
.TopMargin = Application.InchesToPoints(1)
.BottomMargin = Application.InchesToPoints(1)
.HeaderMargin = Application.InchesToPoints(0.5)
.FooterMargin = Application.InchesToPoints(0.5)
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintNoComments
.PrintQuality = 600
.CenterHorizontally = False
.CenterVertically = False
.Orientation = xlLandscape
.Draft = False
.PaperSize = xlPaperLetter
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = 100
.PrintErrors = xlPrintErrorsDisplayed
End With


Thanks,

YLP

[TrippyTom]

Well, I'm not an expert by any means, but to my understanding, instead setting each object or parameter line by line, like this:


ActiveSheet.PageSetup.LeftMargin = Application.InchesToPoints(0.75)
ActiveSheet.PageSetup.RightMargin = Application.InchesToPoints(0.75)
ActiveSheet.PageSetup.TopMargin = Application.InchesToPoints(1)
ActiveSheet.PageSetup.BottomMargin = Application.InchesToPoints(1)
ActiveSheet.PageSetup.HeaderMargin = Application.InchesToPoints(0.5)
ActiveSheet.PageSetup.FooterMargin = Application.InchesToPoints(0.5)

you can put it in a With/End-With statement for easier readability.



With ActiveSheet.PageSetup
.LeftMargin = Application.InchesToPoints(0.75)
.RightMargin = Application.InchesToPoints(0.75)
.TopMargin = Application.InchesToPoints(1)
.BottomMargin = Application.InchesToPoints(1)
.HeaderMargin = Application.InchesToPoints(0.5)
.FooterMargin = Application.InchesToPoints(0.5)
End With

Also, if you want to work with more than one object at a time might be a good reason to use it.

[mdmackillop]

Its a coding shortcut which saves retyping the same item and is I believe more efficient as it sets a reference to which the .items apply.

Your code without with would be something like


ActiveSheet.PageSetup.PrintTitleRows = ""
ActiveSheet.PageSetup.PrintTitleColumns = ""
ActiveSheet.PageSetup.PrintArea = ""
ActiveSheet.PageSetup.LeftHeader = ""
ActiveSheet.PageSetup.CenterHeader = ""
ActiveSheet.PageSetup.RightHeader = ""
ActiveSheet.PageSetup.LeftFooter = ""
ActiveSheet.PageSetup.CenterFooter = ""


and so on

Another scenario might be


With Sheets(2)
Cells(1, 1) = .Cells(3, 1) + .Cells(3, 2)
Cells(2, 1) = .Cells(4, 1) + .Cells(3, 2)
Cells(3, 1) = .Cells(5, 1) + .Cells(3, 2)
End With

This will put the contents of sheet 2 into the active sheet

[YellowLabPro]

Great, thanks to both!

YLP

[malik641]

In addition to what Malcom said, each dot you see in coding is a property or method call...and minimizing these (i.e. With End structures) increases run-time speeds, making your code work faster because you are minimizing the OLE References.

Also, I've learned that With End structures actually (behind the curtains, so-to-speak) create an object reference to what you are working with (while the With End structure is running). For example the Range object....

Take the following:


With ThisWorkbook.Sheets("Sheet1").Range("A1")
.Font.Bold = True
.Font.Underline = True
.Value = "hey"
End With
Is faster than:


ThisWorkbook.Sheets("Sheet1").Range("A1").Font.Bold = True
ThisWorkbook.Sheets("Sheet1").Range("A1").Font.Underline = True
ThisWorkbook.Sheets("Sheet1").Range("A1") = "Hey"

Because there are less Method & property calls.

I would give you the link to the Article with this info ("Make Your Code Faster & Shorter")...but sadly, the article section is still under construction :mkay


HTH

[mdmackillop]

I would give you the link to the Article with this info ("Make Your Code Faster & Shorter")...but sadly, the article section is still under construction
John's article is available at his own informative website
http://xlvba.3.forumer.com/index.php?showtopic=18

[malik641]

Thanks Malcom :thumb

[YellowLabPro]

Thanks MD and Malik.