I have a UDF named Test() written below.
And I put it into Sheet1.A1 : = Test()
And I activate Sheet2 and press Ctrl + Alt + F9 (CalculateFull).
Then a msgbox is shown : 'There is a circulation reference....'
I want to turn around the msgbox.
(I tried setting Apllication.DisplayAlerts property to false in vain.)

'---------UDF -------------------------------
Function TEST()
If Not Application.Caller.Parent Is ActiveSheet Then
TEST = Application.Caller.Value
Exit Function
End If
TEST = Format(Now, "HH:MM:SS")
End Function

Zoo,
Apart from the fact that your function no return value (As String), check
the value in the Immediate window:

?typename(Application.Caller), Application.Caller
Double 468385800

The Help does not mention a return value of Double, so I would assume it
some error code
I guess your were expecting it to evaluate to the range "A1".

I do not use these properties so I can't advise you other than the above.

NickHK


"Zoo" wrote in message
...
I have a UDF named Test() written below.
And I put it into Sheet1.A1 : = Test()
And I activate Sheet2 and press Ctrl + Alt + F9 (CalculateFull).
Then a msgbox is shown : 'There is a circulation reference....'
I want to turn around the msgbox.
(I tried setting Apllication.DisplayAlerts property to false in vain.)

'---------UDF -------------------------------
Function TEST()
If Not Application.Caller.Parent Is ActiveSheet Then
TEST = Application.Caller.Value
Exit Function
End If
TEST = Format(Now, "HH:MM:SS")
End Function

Thank you for your reply , NichHK.

Application.Caller Is set to Sheet1.Range("A1") in this case.
When I type the formula '=TEST()' , Application.Caller.Parent is Sheet1.
Since Sheet1 is activated by me to enter the formula,
Application.Caller.Parent(Sheet1) is ActiveSheet, and TEST returns
Format(Now, "HH:MM:SS").

When I activate Sheet2, TEST returns the already calculated value (A1's
value itself).

The purpose of this macro is to try to make UDF being recalculated only when
the sheet is activated.

"NickHK" wrote in message
...
Zoo,
Apart from the fact that your function no return value (As String), check
the value in the Immediate window:

?typename(Application.Caller), Application.Caller
Double 468385800

The Help does not mention a return value of Double, so I would assume it
some error code
I guess your were expecting it to evaluate to the range "A1".

I do not use these properties so I can't advise you other than the above.

NickHK


"Zoo" wrote in message
...
I have a UDF named Test() written below.
And I put it into Sheet1.A1 : = Test()
And I activate Sheet2 and press Ctrl + Alt + F9 (CalculateFull).
Then a msgbox is shown : 'There is a circulation reference....'
I want to turn around the msgbox.
(I tried setting Apllication.DisplayAlerts property to false in vain.)

'---------UDF -------------------------------
Function TEST()
If Not Application.Caller.Parent Is ActiveSheet Then
TEST = Application.Caller.Value
Exit Function
End If
TEST = Format(Now, "HH:MM:SS")
End Function

I see what you want to say clearly now.
I changed my code as below.
As you've said Application.Caller.Value has no value in the msgbox.

I thougt the problem is just a message 'Circulation....'.
But it is wrong , the proble is Application.Caller.Value does not work fine
in this case.
I should think the whole idea again.

Thank you.

Function TEST()
If Not Application.Caller.Parent Is ActiveSheet Then
MsgBox Application.Caller.Value

TEST = Application.Caller.Value
Exit Function
End If
TEST = Format(Now, "HH:MM:SS")
End Function

"NickHK" wrote in message
...
Zoo,
Yes, I understand all that, but have you actually tested the value of
Application.Caller when Sheet2 is the active sheet ?

NickHK


"Zoo" wrote in message
...
Thank you for your reply , NichHK.

Application.Caller Is set to Sheet1.Range("A1") in this case.
When I type the formula '=TEST()' , Application.Caller.Parent is Sheet1.
Since Sheet1 is activated by me to enter the formula,
Application.Caller.Parent(Sheet1) is ActiveSheet, and TEST returns
Format(Now, "HH:MM:SS").

When I activate Sheet2, TEST returns the already calculated value (A1's
value itself).

The purpose of this macro is to try to make UDF being recalculated only
when
the sheet is activated.

"NickHK" wrote in message
...
Zoo,
Apart from the fact that your function no return value (As String),
check
the value in the Immediate window:

?typename(Application.Caller), Application.Caller
Double 468385800

The Help does not mention a return value of Double, so I would assume
it
some error code
I guess your were expecting it to evaluate to the range "A1".

I do not use these properties so I can't advise you other than the
above.

NickHK


"Zoo" wrote in message
...
I have a UDF named Test() written below.
And I put it into Sheet1.A1 : = Test()
And I activate Sheet2 and press Ctrl + Alt + F9 (CalculateFull).
Then a msgbox is shown : 'There is a circulation reference....'
I want to turn around the msgbox.
(I tried setting Apllication.DisplayAlerts property to false in
vain.)

'---------UDF -------------------------------
Function TEST()
If Not Application.Caller.Parent Is ActiveSheet Then
TEST = Application.Caller.Value
Exit Function
End If
TEST = Format(Now, "HH:MM:SS")
End Function

Zoo,
Yes, I understand all that, but have you actually tested the value of
Application.Caller when Sheet2 is the active sheet ?

NickHK


"Zoo" wrote in message
...
Thank you for your reply , NichHK.

Application.Caller Is set to Sheet1.Range("A1") in this case.
When I type the formula '=TEST()' , Application.Caller.Parent is Sheet1.
Since Sheet1 is activated by me to enter the formula,
Application.Caller.Parent(Sheet1) is ActiveSheet, and TEST returns
Format(Now, "HH:MM:SS").

When I activate Sheet2, TEST returns the already calculated value (A1's
value itself).

The purpose of this macro is to try to make UDF being recalculated only
when
the sheet is activated.

"NickHK" wrote in message
...
Zoo,
Apart from the fact that your function no return value (As String),
check
the value in the Immediate window:

?typename(Application.Caller), Application.Caller
Double 468385800

The Help does not mention a return value of Double, so I would assume it
some error code
I guess your were expecting it to evaluate to the range "A1".

I do not use these properties so I can't advise you other than the
above.

NickHK


"Zoo" wrote in message
...
I have a UDF named Test() written below.
And I put it into Sheet1.A1 : = Test()
And I activate Sheet2 and press Ctrl + Alt + F9 (CalculateFull).
Then a msgbox is shown : 'There is a circulation reference....'
I want to turn around the msgbox.
(I tried setting Apllication.DisplayAlerts property to false in vain.)

'---------UDF -------------------------------
Function TEST()
If Not Application.Caller.Parent Is ActiveSheet Then
TEST = Application.Caller.Value
Exit Function
End If
TEST = Format(Now, "HH:MM:SS")
End Function

Zoo,
After looking at this again, I assume it is because whilst
Application.Caller.Parent evaluates to a valid object,
Application.Caller.Value is indeterminate at the time you Get the value.
As I do not work with circular references or .Caller, I can't help much,
although these links may show you a way to change to calculation method to
something suitable:

http://www.decisionmodels.com/calcsecretsh.htm

http://www.cpearson.com/excel/sheetref.htm

NickHK

"Zoo" wrote in message
...
I see what you want to say clearly now.
I changed my code as below.
As you've said Application.Caller.Value has no value in the msgbox.

I thougt the problem is just a message 'Circulation....'.
But it is wrong , the proble is Application.Caller.Value does not work
fine
in this case.
I should think the whole idea again.

Thank you.

Function TEST()
If Not Application.Caller.Parent Is ActiveSheet Then
MsgBox Application.Caller.Value

TEST = Application.Caller.Value
Exit Function
End If
TEST = Format(Now, "HH:MM:SS")
End Function

"NickHK" wrote in message
...
Zoo,
Yes, I understand all that, but have you actually tested the value of
Application.Caller when Sheet2 is the active sheet ?

NickHK


"Zoo" wrote in message
...
Thank you for your reply , NichHK.

Application.Caller Is set to Sheet1.Range("A1") in this case.
When I type the formula '=TEST()' , Application.Caller.Parent is
Sheet1.
Since Sheet1 is activated by me to enter the formula,
Application.Caller.Parent(Sheet1) is ActiveSheet, and TEST returns
Format(Now, "HH:MM:SS").

When I activate Sheet2, TEST returns the already calculated value
(A1's
value itself).

The purpose of this macro is to try to make UDF being recalculated
only
when
the sheet is activated.

"NickHK" wrote in message
...
Zoo,
Apart from the fact that your function no return value (As String),
check
the value in the Immediate window:

?typename(Application.Caller), Application.Caller
Double 468385800

The Help does not mention a return value of Double, so I would
assume
it
some error code
I guess your were expecting it to evaluate to the range "A1".

I do not use these properties so I can't advise you other than the
above.

NickHK


"Zoo" wrote in message
...
I have a UDF named Test() written below.
And I put it into Sheet1.A1 : = Test()
And I activate Sheet2 and press Ctrl + Alt + F9 (CalculateFull).
Then a msgbox is shown : 'There is a circulation reference....'
I want to turn around the msgbox.
(I tried setting Apllication.DisplayAlerts property to false in
vain.)

'---------UDF -------------------------------
Function TEST()
If Not Application.Caller.Parent Is ActiveSheet Then
TEST = Application.Caller.Value
Exit Function
End If
TEST = Format(Now, "HH:MM:SS")
End Function

Maybe you can use .text:

Option Explicit
Function TEST()
Application.Volatile
If Not Application.Caller.Parent.Name = ActiveSheet.Name Then
TEST = Application.Caller.Text
Exit Function
End If
TEST = Format(Now, "HH:MM:SS")
End Function




Zoo wrote:

I have a UDF named Test() written below.
And I put it into Sheet1.A1 : = Test()
And I activate Sheet2 and press Ctrl + Alt + F9 (CalculateFull).
Then a msgbox is shown : 'There is a circulation reference....'
I want to turn around the msgbox.
(I tried setting Apllication.DisplayAlerts property to false in vain.)

'---------UDF -------------------------------
Function TEST()
If Not Application.Caller.Parent Is ActiveSheet Then
TEST = Application.Caller.Value
Exit Function
End If
TEST = Format(Now, "HH:MM:SS")
End Function

--

Dave Peterson