Ps. I could set the .id property of a range, too:

Option Explicit
Function aaa(rng As Range)
Dim myCell As Range
For Each myCell In rng.Cells
myCell.ID = "hi"
Next myCell
End Function
Sub testme2()
Dim myCell As Range
For Each myCell In Range("a1:c1")
MsgBox myCell.ID
Next myCell
End Sub

I used
=aaa(a1:c1)
In cell d8 first, then used the sub to retrieve the .id.

IIRC, .id was added in xl2002???? And it's one of those properties that isn't
saved if the workbook is saved as a normal workbook. IIRC (again), it is saved
if the workbook is saved as .htm.



Dave Peterson wrote:

John Walkenbach has at least a partial list:
http://j-walk.com/ss/excel/odd/odd06.htm

And this is a nice place to see other oddities:
http://j-walk.com/ss/excel/odd/index.htm

(well, not counting the NewsGroup regulars <gd&r)

Niek Otten wrote:

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

"Dave Peterson" wrote in message ...
| 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

--

Dave Peterson

--

Dave Peterson