Making Processing Faster

[Custos]

I have a problem in that i have a series of Vlookup formulas that can be automatically expand to 60,000 lines depending on how many reference cells there are. My problem though is that when Excel calculates all these cells the workbook hangs for quite a while, while processing this data.

Is there anyway to speed up the processing of formulas in excel using VBA? (i.e. like taking out a safety measure then reapplying that safety measure, like Application.ScreenUpdating function.)

Thanks in advance.

[ALe]

this article explains some hints to make vlookup faster. hope it helps
http://www.decisionmodels.com/optspeede.htm

[Custos]

Thank you Ale but the version i am using does not have this function availavle yet. Is there no other way of making calculations run faster?

[ALe]

which function?

[Ken Puls]

CUriuos, what version of Excel are you on?

For reference, the only formula that is referred to there that you won't have (don't care what version) is AVLOOKUP, as that is a function of the FastExcel product. If you scroll down the list, there are several helpful tips there, as ALe mentions. Just a couple are:


Avoid using exact match lookup if possible.
If speed is critical keep Lookups and Data on the same sheet.
SORT your data and use approximate Match.
The smaller the Range the better.
etc...