Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
UDF Question
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
UDF Question
I don't think so. Why would you want to know?
-- HTH Bob Phillips (remove nothere from email address if mailing direct) "Koye Li" wrote in message ... 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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
UDF Question
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 "Koye Li" wrote in message ... | 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? | | |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
UDF Question
Lookup the topic/example Application.Caller in the help files
"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? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
UDF Question
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. "AA2e72E" wrote in message ... Lookup the topic/example Application.Caller in the help files "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? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
UDF Question
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. "Koye Li" wrote: 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. "AA2e72E" wrote in message ... Lookup the topic/example Application.Caller in the help files "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? |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
UDF Question
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
UDF Question
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
UDF Question
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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
UDF Question
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|