Trying to optimize VBA code for Excel 365

[Groovicles]

Hi everyone,

Like many people, I have VBA code which worked smooth and fast using Excel 2010 but slowed to a crawl when we moved to Excel 365. I'm assuming the code needs to be optimize and I'm trying to think of ways how. Below is sample code which is structurally representative of much of the macros in the workbook:

Please Login or Register  to view this content.

I thought perhaps there's a way to apply multiple offsets to a single Find line but i'm not sure if that's a thing. Any ideas?

Thanks!!

[mjr veverka]

My proposal, although without an example of data it is difficult to find specific solutions:
1. Early binding could be used
2. You could improve the addressing of objects a bit - standardize them a bit
3. Are cells "A3" and "C25" involved in calculating some values ​​"on the fly" for formulas in other cells ?
4. Data could be loaded from a spreadsheet to memory - calculations would be faster - but see point 3, which complicates the matter
5. You can divide the "If" conditions into two separate conditions, it should work faster

For example:

Please Login or Register  to view this content.

[mc84excel]

I've noticed the same problem (code running fine in 2010 running noticeably slower in 365). I would be very interested to learn how this could be improved.

In regards to your sample code, I can see a few ways you could squeeze better performance out of this while still running it on a worksheet (Personally I prefer to read/write sheets twice only - read the data, process everything with an array(s) and then write it back once - this is exponentially faster. However this approach may not work for you if your code is heavily tied to the worksheets. I can't recommend this without knowing what you are doing)

Code below. I have made comments to explain what I have changed to improve the performance.

Please Login or Register  to view this content.