User Defined Function not recalculating (1 Viewer)

S

standrd

Guest
Hi,

I'd be grateful if anyone can help me with a User Defined Function problem, a simple example of which is shown in the attached file.

The problem is that my function ("cof_pricer") works perfectly well, but when the data (C3:C5) is changed, the function doesn't automatically recalculate In each case, I have to go into the respective cells (E3:E5), 'F2' to edit the cell, press enter, & the correct calculation takes place.

This is obviously less than satisfactory, and I need to get the function to recalculate automatically every time the data changes. I have included a second (very simple function) called Two_mth_Ave (d4:d5), which DOES recalculate on changing the data, to illustrate that it isn't Excel that's at fault!

There is presumably something in my VBA coding that needs tweaking.

Does anyone have any ideas?

Many thanks,

David Standring.
 

Attachments

  • excelproblem.zip
    13.1 KB · Views: 343

Rod D

Registered User.
Local time
Yesterday, 23:50
Joined
Feb 9, 2006
Messages
87
Hi David

The problem
Your function is not recalculating because it is not "dependant" on the cells being changed. i.e. Your function is looking for a change in the Month Number column as a trigger to re-calc - which is not happening. It does not have the "intelligence" to see that it needs to recalc because the % has changed in another column. That is why when you F2 it, it forces a recalculation.

The solution:
Append this line immediately below the DIM statements.

Application.Volatile

This will force the re-calc of the function on ANY cell change.

or
Include (somehow!) the % value in the function definition, so that it "sees" the change and initiates a re-calc.
The Warning:
If you use the Application.Volatile in many UDF's, or the same UDF many times in a worksheet, you can SERIOUSLY downgrade the performance of your spreadsheet as all these functions will re-calculate on every cell change...

HTH
Rgds
Rod
 

Users who are viewing this thread

Top Bottom