I'm not sure how much good this will do--since anytime you force a recalculation
(alt-F9 or variants of that), then the values would not have changed.

I wouldn't use this, but it may give you an idea:

Option Explicit
Function foo(cell1 As Range, cell2 As Range) As Variant

Dim myStr As Variant
Dim myMsg1 As String
Dim myMsg2 As String

If Application.Caller.Comment Is Nothing Then
'who knows what happened?
'do nothing
Else
myStr = Application.Caller.Comment.Text
myStr = Split(myStr, "|")

If CStr(cell1.Value) = myStr(LBound(myStr)) Then
myMsg1 = ""
Else
myMsg1 = vbLf & cell1.Address(0, 0) _
& " Changed from: " & myStr(LBound(myStr))
End If

If CStr(cell2.Value) = myStr(UBound(myStr)) Then
myMsg2 = ""
Else
myMsg2 = vbLf & cell2.Address(0, 0) _
& " Changed from: " & myStr(UBound(myStr))
End If
End If

foo = cell1.Value + cell2.Value & myMsg1 & myMsg2

On Error Resume Next
Application.Caller.Comment.Delete
On Error GoTo 0

Application.Caller.AddComment Text:=cell1.Value & "|" & cell2.Value

End Function

=============

I'm not sure what you're doing, but if you want, you could create a log that
tracks each time one of those formulas recalculates. Maybe you can inspect that
when you need to.

Function foo2(cell1 As Range, cell2 As Range) As Double

Dim MyFileName As String
Dim myStr As String
Dim FileNum As Long

MyFileName = ThisWorkbook.FullName & ".log"

myStr = cell1.Address(external:=True) & vbTab & cell1.Value _
& vbTab & cell2.Address(external:=True) & vbTab & cell2.Value _
& vbTab & Format(Now, "mm/dd/yyyy hh:mm:ss")

FileNum = FreeFile
Close FileNum
Open MyFileName For Append As FileNum
Print #FileNum, myStr
Close FileNum

foo2 = cell1.Value + cell2.Value

End Function

I bet if you're industrious, you could open that log file in the function and
inspect the previous values--but it would still suffer from you hitting the
calculate now problem.



Koye Li wrote:

Let say we have defined an UDF fuction foo(a,b) that takes 2 cell references
as its arguments :

public function foo(byval a, byval b)

and on the worksheet, we have

=foo(B1,C1)

Whenever B1 or C1 changes, foo() gets triggered and is called by Excel. Is
there anyway within foo() to tell which argument originated the call?

--

Dave Peterson