UDF Question

[Koye Li]

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?

 

[Bob Phillips]

I don't think so. Why would you want to know?

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Niek Otten]

I don't think so. Of course you could keep static variables holding last time's values of both.
But Calculation might have been set to manual and both cells changed and then which cell originated the calculation is undefined.

Just curiosity, why do you need this?

--
Kind regards,

Niek Otten

| 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?
|
|

 

[Guest]

Lookup the topic/example Application.Caller in the help files

 

[Koye Li]

Thanks for the help. But Application.Caller wouldn't help in this case.
Since Application.Caller only gives the cell which the UDF call is
originated. But what I am interested is which cell in the UDF argument list
that triggers the UDF call.

 

[Guest]

Try this function:

Function abc(ByVal Cell1 As Range, cell2 As Range)
abc = Cell1.Value + cell2.Value
MsgBox Cell1.Address & " " & cell2.Address
End Function

Since there is no way of determining which cell changed to cause the
function to be called, the MsgBox returns the address of both cells.

 

[Dave Peterson]

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.

 

[Niek Otten]

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

 

[Dave Peterson]

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>)

 

[Dave Peterson]

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.