Slow Macro... Funny Story... and a Vague Question

[enny81]

Hi guys,

I have been gifted with a spreadsheet/macro created by someone else - let's call him Stu. (I hope there are no Stus on here - if so, I apologize!)

The spreadsheet is a 'standard format' kind of thing that I am supposed to use for my assigned location. This spreadsheet has a data entry worksheet, and the macro transfers entries to another worksheet that is being used as a database. (Why we're not using a real database is a whole other discussion).

I added some reports to the spreadsheet - some used macros of my own making, and some used index/match. My macros did not interact with his macros in any way.

After only a few days of use, Stu's macro got super, super slow. It still worked, but took 10 min instead of <10 sec. Mine were still fine. I emailed it back to Stu and asked for help. He wrote me back and said that my macros were breaking his macros, and he had deleted them, and now everything was better.

Punch Line: All he did was delete the buttons! Macros are still there.

However, the macros on the "corrected" spreadsheet are working quickly again. All of my stuff is broken -- all my named ranges and links are pointing to a local directory, instead of within the spreadsheet.

So now I'm stuck. I didn't write the problem macro... and I suspect Stu didn't, either. I can follow what it is doing (lots of index, match, vlookup functions) but am not sure what would cause it to be slow. I don't see how my macros could be slowing it down if they are not calling/being called by it. I didn't modify the data entry or database worksheets in any way, except to name some ranges on the database worksheet. ALL of my stuff is on separate, new worksheets.

Any ideas of where to start looking for the real problem? I know that is a REALLY vague question without having the macro & spreadsheet in front of you, but I appreciate any vague answers you may have.

Thanks,
e.

 

[Fazza]

No idea, sorry. How about setting up your worksheets & code in a totally separate workbook? Or maybe even in MS Access as it sounds like you're just running reports. Your file (Excel or Access) could just pull the data from the other file - wouldn't need to open it even - and you do your work independently.

 

[enny81]

Thank you for responding, and for the suggestion!

I went back to his "corrected" spreadsheet and re-broke it when I fixed some messed up cell references. Apparently the "correction" involved breaking a bunch of things so that the spreadsheet couldn't calculate anymore. That led me to turn off auto-calculate from within the macro and it fixed (ok, masked) the problem.

I didn't notice the problem when I first developed my macro because I always have auto-calculate off (due to other spreadsheets I use with very slow connections to various databases).

I would much rather use MS access but I don't have that option at this time. The report is actually for someone else to run... it's kind of a mess.

thanks again
e