Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    LorneKetch
    Guest

    Excel VBA PageSetup

    Has anyone experienced very slow performance when using VBA PageSetup... both XL 95 and 2000 seem to totally bog down when processing one of these commands. Example

    With ActiveSheet.PageSetup
    .LeftMargin = Application.InchesToPoints(0)
    .RightMargin = Application.InchesToPoints(0)
    .TopMargin = Application.InchesToPoints(0.3)
    .BottomMargin = Application.InchesToPoints(0.3)
    end with

    Each Margin set takes forever.
    Is there a fix?

    Thanks !!!!

  2. #2
    3 Star Lounger
    Join Date
    Apr 2002
    Location
    Redditch, Worcestershire, England
    Posts
    233
    Thanks
    0
    Thanked 22 Times in 21 Posts

    Re: Excel VBA PageSetup

    Yes, I've seen a reference to it on Microsoft's support page - can't remember the id number - but there is definitely a memory leak when setting PageSetup from within VBA.

    Jeremy

  3. #3
    Plutonium Lounger Leif's Avatar
    Join Date
    Dec 2000
    Location
    U.K.
    Posts
    14,010
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel VBA PageSetup

    Try this one:

    <A target="_blank" HREF=http://support.microsoft.com/support/kb/articles/Q199/5/05.ASP>Q199505</A> XL2000: Macro Performance Slow When Page Breaks Are Visible

  4. #4
    LorneKetch
    Guest

    Re: Excel VBA PageSetup

    Thank Lief
    I should have mentioned that I did some homework on this before posting to the Lounge. I found and tried the Microsoft 'solution'. Neither ActiveSheet.DisplayAutomaticPageBreaks = False in XL95 or ActiveSheet.DisplayPageBreaks = False in XL2000 solves the problem. Interesting that this bug has been around for so many years but remains unresolved. Thanks for your input... much appreciated.

    Lorne

  5. #5
    Platinum Lounger
    Join Date
    Dec 2000
    Location
    Queanbeyan, New South Wales, Australia
    Posts
    3,730
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel VBA PageSetup

    Not a fix... but if you have to do this a few times, set up one page with the settings you want, and then copy that page into other blank pages. It can be hugely faster than recreating the settings on each page. (Of course, you might not be able to do it that way at all).

    If you can use this method, you can have your default page as a hidden worksheet which you don't need to recreate each time.
    Subway Belconnen- home of the Signboard to make you smile. Get (almost) daily updates- follow SubwayBelconnen on Twitter.

  6. #6
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel VBA PageSetup

    If the other things that have been suggested do not help your situation, there is one other "solution" that does seem to work in all cases where I know that it has been tried. Unfortunately, it is not a really great solution. What does seem to fix this is to put the PageSetup into an Excel 4 macro which current versions of Excell can still run.
    Legare Coleman

  7. #7
    3 Star Lounger
    Join Date
    Apr 2002
    Location
    Redditch, Worcestershire, England
    Posts
    233
    Thanks
    0
    Thanked 22 Times in 21 Posts

    Re: Excel VBA PageSetup

    do you have some instructions on how to do this?

    I have run into the memory leak problem myself, but I started programming with Excel 5.0, so have never seen Excel 4 macros!

    Jeremy

  8. #8
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel VBA PageSetup

    Sorry, I do not know Excel 4 macros or how to set them up in later versions. I have just seen this solution posted on the CompuServe MSOffice forum by Bill Manville (who seem to know much more about Excel than MS does). If someone else does not jump in with a suggestion, then you might try there if you have access.
    Legare Coleman

  9. #9
    Plutonium Lounger Leif's Avatar
    Join Date
    Dec 2000
    Location
    U.K.
    Posts
    14,010
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel VBA PageSetup

    I'm intrigued with this now.
    Running Excel 2K SR1 on W2K with the above code as the only macro.
    Nothing I can do can make it perform at other than 'very fast'.
    I've played around with most of the options, added sheets, changed printers etc.
    Have you tried the code on a new (blank) workbook?

  10. #10
    LorneKetch
    Guest

    Re: Excel VBA PageSetup

    Yes I did Lief. I do a fair amount of amateur programming both at home and at work. I'm used to banging my head on a wall until I get a solution. I'm out of ideas on this one. Unfortunately, my home computer is an antique Dell 133 Pentium running Win 98 (soon to be replaced). I test the 4 margin sets running in XL2000 (V9.0.2720) at 40 seconds... 10 seconds per margin set. Other VBA commands are fast. ActiveSheet.DisplayPageBreaks=False has no effect. I'll run a few tests on some my work computers. I believe that the Page Setups are tied to the active printer... maybe it has something to do with my HP720C deskjet. By-the-way... I seem to recal having the similar problem in the VB5 stand alone compiler a couple of years ago. Thanks for your interest.

    Lorne

  11. #11
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel VBA PageSetup

    I believe that the problem is also related to the brand/model of printer and how it is attached. When I had the problem, it was always an HP LaserJet and I think it was always network attached.
    Legare Coleman

  12. #12
    3 Star Lounger
    Join Date
    Apr 2002
    Location
    Redditch, Worcestershire, England
    Posts
    233
    Thanks
    0
    Thanked 22 Times in 21 Posts

    Re: Excel VBA PageSetup

    The memory leak problem associated with setting PageSetup properties from Excel 97/2000 VBA doesn't exist in Windows 2000.

    It IS a documented problem in Windows 95/98.

    Regards

    Jeremy

  13. #13
    LorneKetch
    Guest

    Re: Excel VBA PageSetup

    Just ran a test on a work machine... its a newer Dell than my own and running Office 95. No problems here. Looks like it's related to my home computer configuration.
    Lorne

  14. #14
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel VBA PageSetup

    I thought the discussion was about how slow the PageSetup worked, not about memory leaks. I have had the slow performance on 97 and 2000. I have not had any memory leaks that I am aware of.
    Legare Coleman

  15. #15
    3 Star Lounger
    Join Date
    Apr 2002
    Location
    Redditch, Worcestershire, England
    Posts
    233
    Thanks
    0
    Thanked 22 Times in 21 Posts

    Re: Excel VBA PageSetup

    ...but the Microsoft KB article that explains the poor performance when setting PageSetup via VBA explains that it is due to memory leaks caused by execution of an internal routine.

    Sorry I don't have the KB number to hand!!

    Jeremy

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •