vlookup to .xlsm macro is incredibly slow

**jrussell** · 07-23-2009, 10:32 AM

This is designed to be run on bid proposals, to calculate special discounts. My idea was that whoever was designing the bid would open this file, which has the discount information and the macro, and then run the macro on the file containing the bid.

This macro works. It worked really fast when it was in my personal.xls and referenced an .xlsx file for the vlookup. It was slower, but still tolerable, when I moved it to the .xlsm file and it still refenced the .xlsx file (an oversight on my part). Now that I fixed the vlookup reference to the .xlsm file, the same one that the macro itself is in, it has slowed to a crawl. What is going on?

Here's the relevant code. The four lines at the end, the ones that begin with ActiveCell are the ones that are taking all the time.

Please Login or Register  to view this content.

**jrussell** · 07-23-2009, 01:06 PM

On rerunning this a couple of times, I've noticed that it was only slow on the four blue lines above when when I moved it to the .xlsm file and it still refenced the .xlsx file.

When I fixed the vlookup references to use the .xlsm, every line of the macro was slow.

But when I set Application.Calculation = xlCalculationManual at the beginning and Application.Calculation = xlCalculationAutomatic at the end, it speeds right up again.

The problem is, I really hate using xlCalculationManual, because if the macro crashes it leaves Excel in manual calculation mode. Since I'm making this macro for use by people who are not all that skilled in excel, they will be totally stumped if this happens.

So, is there another way to postpone calculation to the end of the macro?

Or is there a way to speed up calculation the way it is? I don't see why the calculation is so much slower when the macro is saved as part of the .xlsm as opposed to saved in my personal.xls.

**jrussell** · 07-23-2009, 03:43 PM

I figured this out. Turns out it was due to some external references in the workbook I was running the macro on. These were trying to recalculate any time I made any change in any open workbook. I got rid of those and now the macro's back to lightning fast. Funny, sometimes it's something completely different than you expect.