Results 1 to 6 of 6
  1. #1
    2 Star Lounger
    Join Date
    Jul 2001
    Location
    Kuala Lumpur , MALAYSIA
    Posts
    154
    Thanks
    0
    Thanked 0 Times in 0 Posts

    How to speed up a macro (excel 97)

    Hello again guys,

    Don't know if this possible..I run 1 of my macro
    and it take nearly 3 minutes to finish..How can
    I speed it and make it run a little(more faster is better)..
    I know "application.screenupdating" will not works in this
    method coz i have try it..it will only hide what's going
    on but not increasing the code peed..any ideas..
    my range that i run using the macro is from b9:ab78
    its not big though..but its took me nearly 3 minutes to
    run the macro..any help will be appreaciated

    ps: if you know the method..please guide me where should i put
    it..i.e maybe after the variable , or before the end sub or etc..thanks again

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: How to speed up a macro (excel 97)

    It is impossible to say without looking ay the code. Could you let us see what the code is doing ?

    In the meantime you could try

    Application.Calculation = xlCalculationManual

    before the code runs and reset to

    Application.Calculation = xlCalculationAutomatic

    when finished and see if that helps.

    Andrew C

  3. #3
    2 Star Lounger
    Join Date
    Feb 2001
    Posts
    141
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How to speed up a macro (excel 97)

    Application.LudicriousSpeed = True <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

    I agree with Andrew. We'd need to see the code.

    <img src=/S/lightbulb.gif border=0 alt=lightbulb width=15 height=15>One thing I can think of is to use the With statment. The less periods you drill down, the better performance you get. At least I think I had heard that somewhere.

  4. #4
    5 Star Lounger
    Join Date
    Dec 2000
    Location
    Reading/Swindon, Berkshire, United Kingdom
    Posts
    664
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How to speed up a macro (excel 97)

    you did and I think you're right. <A target="_blank" HREF=http://www.cpearson.com/excel/optimize.htm>this</A> might be worth a look.

    Brooke

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

    Re: How to speed up a macro (excel 97)

    My code give the wrong answer. It should calculate 1 but it calculates 2. Can you tell me why without seeing the code? <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

    That is just as easy as what you are asking without showing your code.
    Legare Coleman

  6. #6
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How to speed up a macro (excel 97)

    Are you doing any page setups in that code? That is notoriously slow. It is better to use the ancient XL4 macro command for that:

    application.ExecuteExcel4Macro("PAGE.SETUP(head, foot, left, right, top, bot, hdng, grid, h_cntr, v_cntr, orient, paper_size, scale, pg_num, pg_order, bw_cells, quality, head_margin, foot_margin, notes, draft)")

    This command (e.g.) shows the gridlines:

    Application.ExecuteExcel4Macro ("PAGE.SETUP(, , , , , , , true, , , , , , , , , , , , , )")
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

Posting Permissions

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