Hi Dave,
I was rather surprised that this worked. I really thought nothing at all in a worksheet could be changed from within a function.
Are you aware of any more exceptions?
--
Kind regards,
Niek Otten
| 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