Can I increase the speed up without change the code?

[VB_Lover]

Hello :)

I wrote a VBA function. It is a lot of calculations (nested loops)
Anyway..

When I change certains cells on the sheet, the cell of my own function is recalcualted auto. (as planned)

My work was on a small size of data but when I increase the data (rows & cols), my function is still working well but slowing

With a change on a certain cell, I should wait more than a minute to see the recalculation result. I need to speed up this code..

However, I don't want to make "much" modification to my code since it contains a lot of "dependent" statments

i.e., any change here needs another change there and so on.. it may result in a lot of maintenance later..

I don't care about the readability of the code..


Many thanks,
VB_Lover

[georgiboy]

Perhaps you could turn your function back into a sub and fire it from a button, allowing you to populate many cells and calculate at the end or calculate on a deactivate sheet event. Might be a good idea to post the function for all to see.

[Aflatoon]

I am not really certain what you are hoping for. You want your code to run faster but without altering it? How - magic?

[VB_Lover]

Altering the code is Ok if it is little

I heared there are some properties in Excel or in ExcelVBA need to set/reset (active/deactivate) in the begining of the function code to increase the speed up..

or something like this..

Playing on such parameters can imrove the speed without a lot of change

Thanks again

[Aflatoon]

For subroutines yes, but not for UDFs since they either do not apply, or you cannot change them anyway.

[Kenneth Hobs]

http://vbaexpress.com/kb/getarticle.php?kb_id=1035

[Paul_Hossler]

With a change on a certain cell, I should wait more than a minute to see the recalculation result. I need to speed up this code..

However, I don't want to make "much" modification to my code since it contains a lot of "dependent" statments i.e., any change here needs another change there and so on.. it may result in a lot of maintenance later..

I don't care about the readability of the code..


1. Buy a faster computer
2. Post the code so people can see it
3. Bite the bullet and redesign what you're doing, not just the VBA, to improve performance
4. Volatile in a UDF is sometimes necessary, but will slow things down
5. Use Ken's code

and


6. You really should worry about the readability of the code since it does make debugging for you and for others easier

Paul