PDA

View Full Version : User Defined Function and calculate



mikke3141
12-06-2010, 12:04 PM
Hello,

I have a user defined function that I have copied to one worksheet over 100 times. The problem is that when I change a cell that the formula is referring to, the formula is not recalculated. I would like to formula to recalculate only for the UDF on the same row that the formula is located on. I of course need to use the Worksheet_Change event, but I have not been able to have it so that only the one instance of UDF is calculate.

CalculateFull and Application.Volatile cannot be used as all the UDF's will be recalculated. Should I send a F2 + Enter to the cell or something else, like evalulate.

Thank you for your help,

Mikke

mikerickson
12-06-2010, 12:33 PM
What is the formula that you want to re-calculate? If you want a UDF to recalculate when a cell is changed, that cell should be an argument of the function.

For example, if your UDF uses a value from a table, the range of that table should be passed as an argument, even if you only use the UDF to refer to one table.
'BAD UDF
Function interVal(rowNum as Long, colNum as Long) as Variant
interVal = Range("MyTable").Cells(rowNum, colNum)
End Function

'Good UDF
Function interVal2(rowNum as Long, colNum as Long, tableRange as Range) as Variant
interVal2 = tableRange.Cells(rowNum, colNum)
End Function
While =interVal(A1, B1) would (initialy) return the same value as =interVal2(A1, B1, MyTable)
and both would update automaicaly when A1 or B1 are changed, only interVal2 would update if an entry in MyTable was changed.

If this information is useless in your situation, could you post the UDF and a typical formula in which it is used?

mikke3141
12-06-2010, 01:42 PM
Hello,

The formula gathers date from different sheets

Function cost_e(Product As Variant, Material As String, Supplier As String, Optional Cost1 As String, _
Optional Cost2 As String, Optional Cost3 As String, Optional Cost4 As String, Optional Cost5 As String, Optional Cost6 As String)
I = Array(Cost1, Cost2, Cost3, Cost4, Cost5, Cost6)
'-----------
cost_e = (((Sup_Price_pcs + (Sea_freight / Weight_in_Con * Sup_Weight)) / _
Currenci * (customs) + Sea_freight / Weight_in_Con * Sup_Weight + (cus_pic / Prod_per_pal)) _
* (Capit) + Cost_kg * Sup_Weight) * (margina)
End Function


I would use it like

=cost_e ($B3;$E3;$D3;"cust_group1";"USD";"Sea_freight";"Hamburg";"London";"Cust_pickup")

mikke3141
12-06-2010, 01:50 PM
Here is my try that works, but certainly is not beautiful

Sub Worksheet_Change(ByVal Target As Range)

If Cells(2, Target.Column) = "xxx" Then
rivi = Target.Row
For loopp = 7 To 50
If Cells(1, Target.Column) = Cells(1, loopp) And _
Cells(2, loopp) = "Price" Then
Cells(rivi, loopp).Select
SendKeys ("{F2}")
SendKeys ("{ENTER}")
End If
Next
End If

End Sub

macropod
12-06-2010, 10:08 PM
Hi Mike,

If the UDF returns a text string, you can append:
&IF(NOW()=0,"","")
Alternatively, if the UDF returns a numeric value, you can append:
*(NOW()<>0)

Thus, since it seems your UDF returns a numeric value, you could use:
=cost_e ($B3;$E3;$D3;"cust_group1";"USD";"Sea_freight";"Hamburg";"London";"Cust_pic kup")*(NOW()<>0)
With this approach, you don't need the 'Worksheet_Change' procedure.

Note, though, that because NOW() is itself volatile, Excel will prompt you to save the workbook even if no changes have been made.

mikerickson
12-06-2010, 11:22 PM
Where do the values of Sup_Price_pcs, Sea_freight, Weight_in_Con and Sup_Weight
come from. Are those public variables or other udfs?