View Full Version : [SOLVED:] Help understanding With End-With
YellowLabPro
07-16-2006, 12:09 PM
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
07-16-2006, 12:24 PM
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
07-16-2006, 12:29 PM
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
07-16-2006, 01:39 PM
Great, thanks to both!
YLP
malik641
07-16-2006, 01:52 PM
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
07-16-2006, 01:57 PM
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
07-16-2006, 02:06 PM
Thanks Malcom :thumb
YellowLabPro
07-17-2006, 04:44 AM
Thanks MD and Malik.
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.