UDF Recalculation

[showard1]

Hi all

Hopefully a simple problem. I have a UDF in a spreadsheet where all the inputs are on 1 worksheet as is the result. In fact the function is used several times on the worksheet to show a range of scenarios.

The function is fairly simple math, but was easier to do as a UDF that using what would be quite long formula's.

However how do I force the worksheet automatically re-calculate if I change one or more of the statis input values (there are only 5)

Hopefully somone can help

 

[VoG]

Try this near the top of your UDF

Application.Volatile

 

[showard1]

Sorry, no that doesnt work. Perhaps I have not explained well enough

here a simplified version of the UDF code I used to debug the concept. If anyone changes the contents of cells D2, or D7 to D9, I need the UDF to recalculate.

Function pool(ack)
 
Q2 = Worksheets(4).Range("D7").Value * 1000
Q2P = 0
Q3 = Worksheets(4).Range("D8").Value * 1000
Q3P = 0
Q4 = Worksheets(4).Range("D9").Value * 1000
Q4P = 0
ppc = Worksheets(4).Range("D2").Value
ackq = ack / 3
If ackq >= Q2 Then
Q2P = ackq * ppc
Else: End If
If ackq >= Q3 Then
Q3P = ackq * ppc
Else: End If
If ackq >= Q4 Then
Q4P = ackq * ppc
Else: End If
 
pool = Q2P + Q3P + Q4P
 
End Function

 

[VoG]

I tried a simpler UDF like this and it recalculated

Function pool(ack)
Application.Volatile
'
'rest of code

 

[showard1]

OK found the problem. If I refreshed the UDF cells first then it worked.

Clearly the change to the UDF had not "transferred"

Many thanks for your help