How to speed up a macro (excel 97)

Topic

New Reply

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

Reply | Quote

Replies

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

Reply | Quote

Application.LudicriousSpeed = True

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

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.

Reply | Quote

you did and I think you’re right. this might be worth a look.

Brooke

Reply | Quote

My code give the wrong answer. It should calculate 1 but it calculates 2. Can you tell me why without seeing the code?

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

Reply | Quote

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, , , , , , , , , , , , , )”)

Reply | Quote