User-defined functions causing slow recalc

[wadjssd]

I have a file with half a dozen or so sheets, which I am converting from 123 on behalf of someone else. To get around the differences in the database functions between these two apps, I have written a couple of my own functions to do some DCOUNTS and DSUMS. There are probably a thousand occurences in total of these 2 formulae, but whenever anything changes, or whenever I enter a value in a cell, the recalculation process takes forever (well okay, a few minutes!). Any ideas on why this is happening and what can be done about it?

Cheers
wadjssd

 

[xlbo]

I think you answered your own question - you have a thousand occurances of 2 database functions. Any formulae that rely on criteria are going to be slower to calculate and if you have a thousand of them ...well, it's gonna take a while. I would suggest that you need to look at the structure of the workbook. If everything is in logical order, then look at your formulae...do you need DCOUNT and DSUM....could it be done with lookups ?? I would suggest array formulae but they're gonna take as long if not longer than the DSUMs etc. The last thing to think about would be, if you are using a lot of database functions, presumably, you are doing some aggregate / grouping analysis, in which case, you might want to look into pivot tables which may be able to do the whole lot in 1 go
HTH
~Geoff~

 

[cheerio]

I agree with the previous reply.

In the short term set calculation to manual and then force a recalc as necessary using F9.

Any VBA function is going to run far slower than a native Excel function.

To be honest if you have so much data that you need 2000 separate calculations I would be thinking about the use of a database approach perhaps using Access. Holding data in a spreadsheet when there are a few hundred records is ok but beyond that you should use the right tool for the job.

Ken