UDF not updating

[Guest]

I have some UDF's in a workbook and they are not updating properly. What are
the procedures to get a UDF to update?

Thanks,
Barb Reinhardt

 

[Guest]

I've already got a Worksheet_Activate event set up as follows

myRange.formulaR1C1 = myRange.formulaR1c1

Where myRange is the range containing the UDFs

 

[Chip Pearson]

Barb,

A UDF will recalculate only when one of its precedent (input) cells is
changed. For this reason, you should always pass in any cell reference and
never address cells directly from the VBA code. For example,

' Do This
Function XYZ(Rng1 As Range, Rng2 As Range) As Double
' your code
XYZ = Rng1.Value + Rng2.Value
End Function

' Do NOT Do This
Function XYZ(Rng1 As Range)
Dim Rng2 As Range
Set Rng2 = Range("A1")
XYZ = Rng1.Value + Rng2.Value
End Function

In the second example, Excel can't know that XYZ uses cell A1, and thus will
not recalculate when A1 is changed.

You can include Application.Volatile True to cause VBA to calculate the
function whenever any calculation is performed, not just when a precedent is
changed. E.g,

Function XYZ(Rng1 As Range)
Application.Volatile True
' your code
End Function

Note, though, that Volatile may cause sluggish performance.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting
www.cpearson.com
(email on the web site)

 

[Guest]

Chip, how would you change this function to get it to work

Function PlanLessActual(myPARange As Range, myPlanorActualRange As Range,
ProcessArea As String) As Long
Dim myRange As Range
Dim myCell As Range
Dim PlanValue As Variant
Dim ActValue As Variant

'Debug.Print PARange
'Debug.Print PlanorActualRange

'Debug.Print myPARange.Address, myPlanorActualRange.Address

PlanValue = 0
ActValue = 0
For Each myRange In myPARange
If LCase(myRange.Value) = LCase(ProcessArea) Then
Set myCell = myRange.Offset(0, myPlanorActualRange.Column -
myRange.Column)
'If myCell.Row >= 62 And Not LCase(myCell.FormulaR1C1) Like
LCase("PlanLessActual") And _
myCell.FormulaR1C1 Like "=*" Then
'Debug.Print myCell.Address, myCell.FormulaR1C1
'End If
If LCase(myCell.Value) = "planned" Then
PlanValue = Cells(myCell.Row, ActiveCell.Column)
ElseIf LCase(myCell.Value) = "actual" Then
ActValue = Cells(myCell.Row, ActiveCell.Column)
End If
End If
Next myRange
'Debug.Print ActiveCell.Address, ProcessArea, PlanValue, ActValue
PlanLessActual = PlanValue - ActValue


End Function

 

[Chip Pearson]

Barb,

The problem in your code, I believe, is due to the following line (and other
similar lines):

ActValue = Cells(myCell.Row, ActiveCell.Column)

In this and similar lines of code, ActiveCell is NOT the cell that contains
the call to the UDF. ActiveCell is whatever cell happens to be selected when
Excel decides to do a calculation. It could be anything. If you need to
reference the cell containing the function call, use Application.Caller.
This will return a Range object that points to the cell containing the
formula.

ActValue = Cells(myCell.Row, Application.Caller.Column)


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting
www.cpearson.com
(email on the web site)

 

[Chip Pearson]

I should have added that using "Cells" as you do is troublesome also.
"Cells" will point to whatever worksheet happens to be active at calculation
time, not the worksheet containing the function call. Instead of an
unqualified Cells reference, point it to the Parent of Application.Caller.

ActValue = Cells(myCell.Row, Application.Caller.Column)
' should have been
ActValue = Application.Caller.Parent.Cells(myCell.Row,
Application.Caller.Column)



--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting
www.cpearson.com
(email on the web site)

 

[Guest]

Thanks Chip. I had identified the worksheet in another function, but not this
one. I also didn't know of the application.caller.