UDF Macro crashing excel while UDF is recalculating

Hi,

I'm using excel 2010 (32 bit), on a windows 7 64 bit computer. I recently installed 16gb of ram on this computer but excel still crashes when I use a UDF created using excel VBA (previously i had windows XP and excel 2007 and had the same issue). I have attached a spreadsheet that will illustrate my issue. When cell AI18 is changed to "y", the value in H2 is changed and as a result the UDFs in H5:AA75 are recalculated. Similarly, changing AJ18 to "y" changes the value in H79 which subsequently results in the UDFs in H82:AA152 to be recalculated...and so on. The problem is that whenever a group of cells containing the UDFs are recalculated (as is the case when AI18:AN18 are changed to "y"), the spreadsheet and excel freezes/crashes. The code for the UDFs is below as well as in the VBA editor of the attached spreadsheet. The only loop I have is a while loop and it seems to be written correctly because if you change the value in cell H2 directly by entering 5.25 for example, the cells that are dependent on the value of cell H2 are recalculated without excel freezing/crashing. I'm not sure whats causing excel to crash. I'd appreciate any thoughts and solutions.

exampleissue.xlsm

Thanks

Please Login or Register  to view this content.

could the lookup functions be causing the problems?