A few questions - UDF's, Bloating, File Size

[easybpw]

Hi all,

I have read a lot about bloating issues and soloutions on this board and yet I still have questions. Currently I have a workbook that is 5.2MB and growing. I have followed a lot of the tips on here and can't seem to find a resolution. Maybe there isn't any but I need to ask. My questions are:

1) I think I read somewhere about someone making a UDF for vlookup. Can a UDF be made with a sumif function? If so what are the pro's and con's? Currently my workbook uses easily a couple thousand sumif's. I would like to simplify things if possible and hopefully shrink my workbook size down and allow calculations to move more quickly and saves to happen faster. They were going very slow (15 minutes) and then I changed my sumproduct formula's to sumif's which works nicely but still takes longer than what I would like with calculations and saves.

2) Does saving a workbook increase the size of it? Or what about when you e-mail it back and forth, would that increase it's size? Maybe that is a dumb question but...

3) I noticed my PC at work saves and calculates my workbook MUCH faster than here at home. I run 98SE at work and Windows XP at home with the same version of Excel on both computers and more memory at home and a faster processor. I have no clue why this happens but it is a fact. Does anyone know why? If this is an issue can I fix it?

Any help is always appreciated.

Thanks,

Bill

 

[Andrew Fergus]

Hi Bill

I had a similar situation recently myself, i.e. a bloated workbook, slow recalc, replaced the sumproducts with array 'sum(if' formula, used a UDF and I had some useful tips and links in this thread:

http://www.mrexcel.com/board2/viewtopic.php?t=247453

In particular, have a look at the link from Krishnakumar about UDF's towards the bottom of the thread. I only used the UDFs to avoid having multiple helper columns.

HTH, Andrew