Update Cells after changing UDF

**strokebow** · 11-03-2011, 08:31 AM

Hi,

I have a UDF in action in a spreadsheet and its used in many places.

I have amended the code/calculations within my UDF.

However, the cells dont update automatically.

In order to update them, I need to select them one by one and press enter on the formula again to get them to update.
This is rather cumbersome. Is there anyway to get them to update with out me having to select them one by one??

Thanks

**Kyle123** · 11-03-2011, 08:34 AM

Press F9

**StephenR** · 11-03-2011, 08:36 AM

You can add

Please Login or Register  to view this content.

at the start of your UDF, but it will recalculate whenever Excel calculates which can slow things down.

Also, make sure you specify all your arguments.

**romperstomper** · 11-03-2011, 08:44 AM

Do a Find/Replace and replace = with =

**strokebow** · 11-03-2011, 09:42 AM

Originally Posted by romperstomper

Do a Find/Replace and replace = with =

Thanks for all replies

@romper

What do you mean??

How can I do this since, my function is the same but the cell values will be different.

e.g.)
For my UDF the cell refs change.
=MYUDF($A$1:$A$22, B1:B22)
=MYUDF($E$1:$E$22, E1:E22)

etc...

**romperstomper** · 11-03-2011, 09:44 AM

Ctrl+H
in the Find box: =
in the Replace box: =
press Replace All. It will effectively recalculate all the formulas for you.

**MrShorty** · 11-03-2011, 10:15 AM

One additional thought. Make sure everything the UDF needs from the spreadsheet is passed to the UDF through the argument list. This is what Excel uses to figure out the "dependency tree" (is that what it is called?) so it knows what cells need to be calculated when a given cell is changed.