Speeding up a UDF

[JenniferMurphy]

I have a complicated UDF that makes hundreds of updates to the data on the screen. It takes 10-15 seconds to complete, depending on what else the machine is doing.

I've seen websites that recommend adding code to such UDFs to speed them up, such as

<code>Application.ScreenUpdating = False
<code>ActiveSheet.EnableCalculation = False</code></code>

with the opposite toggle at the end of the code.

Are these helpful, harmful, or negligible?

Are there other global settings that might help?

 

[footoo]

[COLOR=#333333]Are these helpful, harmful, or negligible?[/COLOR]

Helpful? - Maybe
Harmful? - No
Negligible? - Why not try and see whether it makes any noticeable difference

Do you have any event procedures that get triggered?

If you post your code, someone may be able to suggest ways to speed it up.

 

[Rick Rothstein]

I have a complicated UDF that makes hundreds of updates to the data on the screen. It takes 10-15 seconds to complete, depending on what else the machine is doing.

I've seen websites that recommend adding code to such UDFs to speed them up, such as

<code>Application.ScreenUpdating = False
<code>ActiveSheet.EnableCalculation = False</code></code>

with the opposite toggle at the end of the code.

Are these helpful, harmful, or negligible?

I don't know the answer for sure, but my gut tells me that they will not speed up your UDF. As far as I know, each occurrence of a UDF runs independently from the other occurrences, so I would think each cell with the UDF will update as soon as those switches are enabled again which I believe happens each time the UDF is executed, so it would seem like each updates things the same whether you turn those switches off or not. Now those switches are useful on a macro where the macro is processing multiple cells as all the cell processing will be done while the switches are off.

 

[JenniferMurphy]

[COLOR=#333333]Are these helpful, harmful, or negligible?[/COLOR]

Helpful? - Maybe
Harmful? - No
Negligible? - Why not try and see whether it makes any noticeable difference

Because I have had bad experiences in the past just trying something on a random website. I posted it here, because I was hoping someone with more experience than I would comment. And I was hoping that the comments would also suggest the correct syntax and whether the settings need to be undone on exit.

Do you have any event procedures that get triggered?

Nope

If you post your code, someone may be able to suggest ways to speed it up.

I am looking for general settings that will work for any code. The websites where I saw those suggestions said that these are things that would slow down any code. If the code it just a few lines without any lengthy loops, then it won't matter.

Anyway, my code is lengthy -- several pages.

 

[footoo]

Anyway, my code is lengthy -- several pages.

Probably why it takes a long time.
UDF's generally are quite slow.

 

[JenniferMurphy]

Probably why it takes a long time.
UDF's generally are quite slow.

(sigh) Yes, I know. And I'll live with it. But these websites were saying that some things that UDFs do, like updating the screen and recalculating after each instruction can increase the time by a lot. So I was asking (a) is that true and (b) which commands will help and what is the correct syntax?

Am I not speaking clearly?

 

[Rick Rothstein]

(sigh) Yes, I know. And I'll live with it. But these websites were saying that some things that UDFs do, like updating the screen and recalculating after each instruction can increase the time by a lot. So I was asking (a) is that true and (b) which commands will help and what is the correct syntax?

Am I not speaking clearly?

Did you see what I posted in Message #3 yet?

 

[footoo]

See post #2 : Why not try and see whether it makes any noticeable difference

 

[JenniferMurphy]

Did you see what I posted in Message #3 yet?

Ooops. I missed it. Sorry.

 

[JenniferMurphy]

I don't know the answer for sure, but my gut tells me that they will not speed up your UDF. As far as I know, each occurrence of a UDF runs independently from the other occurrences, so I would think each cell with the UDF will update as soon as those switches are enabled again which I believe happens each time the UDF is executed, so it would seem like each updates things the same whether you turn those switches off or not. Now those switches are useful on a macro where the macro is processing multiple cells as all the cell processing will be done while the switches are off.

OK, thanks. That's what I wanted to know. Thanks.