Hi, experts

Please, help me if you can.
Every time I change cell value on other worksheet than the one where
selfwritten function is called and if this cell rules any argument
calculation I'm getting 0.0 or #value. Once I put cursor into my function
calling cell and push enter it recalculates. What should I do to force it
recalculate automaticly?
I searched for my problem here and found that I should add Application.
Volatile.
It helped but only partly. Before I had to manualy recalculate (put curcor
and push enter) every cells callin my function. Now I need to do it only in
one cell and other on the same sheet recalculate.
Is it possible to recalculate my function when any cell in entire workbook
has been changed?

Hi Alexander,

At a guess I would say you have something wrong with your UDF. My
guess would be that somewhere in the code of the function you refer to
"ActiveSheet" or something similar. Hence, when you are on a different
sheet and change something, your UDF is recalculating, but not with
the correct info. When you switch back to the correct sheet, it does
not recalculate again, because it already did when you were on the
other sheet.

Maybe post your UDF code and we could possibly help.

Cheers,
Ivan.

On Mar 27, 7:34*pm, "Alexander_Sagiyan" <u42464@uwe wrote:
Hi, experts

Please, help me if you can.
Every time I change cell value on other worksheet than the one where
selfwritten function is called and if this cell rules any argument
calculation I'm getting 0.0 or #value. Once I put cursor into my function
calling cell and push enter it recalculates. What should I do to force it
recalculate automaticly?
I searched for my problem here and found that I should add Application.
Volatile.
It helped but only partly. Before I had to manualy recalculate (put curcor
and push enter) every cells callin my function. Now I need to do it only in
one cell and other on the same sheet recalculate.
Is it possible to recalculate my function when any cell in entire workbook
has been changed?

Let's see the UDF, and it will make it simpler.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Alexander_Sagiyan" <u42464@uwe wrote in message news:81c5f9d8c589e@uwe...
Hi, experts

Please, help me if you can.
Every time I change cell value on other worksheet than the one where
selfwritten function is called and if this cell rules any argument
calculation I'm getting 0.0 or #value. Once I put cursor into my function
calling cell and push enter it recalculates. What should I do to force it
recalculate automaticly?
I searched for my problem here and found that I should add Application.
Volatile.
It helped but only partly. Before I had to manualy recalculate (put curcor
and push enter) every cells callin my function. Now I need to do it only
in
one cell and other on the same sheet recalculate.
Is it possible to recalculate my function when any cell in entire workbook
has been changed?

I think your guess is just right.
I'm not using activesheet but I'm using cells from the sheet I'm calling. As
far as I understood I can not address other sheet cells from a function. Am I
right?
So I'd better get data into my function through argument, would I?
Thank you so much for your help and here is the code


Function Interp(Param_Name As Object, X As Double, Z As Double, X_extr As
Double, Z_extr As Double)

Dim Name_Address As String
Dim b, a_row, a_column, z_above_r, z_above_c, x_above_r, x_above_c As Integer
Dim V_above, V_below As Double

ActiveWorkbook.Application.Volatile
Name_Address = Param_Name.Address(ReferenceStyle:=xlR1C1)
Name_Address = Right(Name_Address, Len(Name_Address) - 1)
b = InStr(Name_Address, "C")
a_row = Val(Name_Address)
Name_Address = Right(Name_Address, Len(Name_Address) - b)
a_column = Val(Name_Address)

z_above_r = a_row + 2
z_above_c = a_column
If Cells(z_above_r, z_above_c) < "" Then

Do Until Cells(z_above_r, z_above_c + 3) = "" Or Cells(z_above_r,
z_above_c) Z
z_above_c = z_above_c + 3
Loop
If z_above_c = a_column And Z_extr = 1 Then
z_above_c = z_above_c + 3
End If
End If
' V_above calculation
x_above_r = a_row + 5
x_above_c = z_above_c
Do Until Cells(x_above_r + 1, x_above_c) = "" Or Cells(x_above_r,
x_above_c) X
x_above_r = x_above_r + 1
Loop
If x_above_r = a_row + 5 And X_extr = 0 Then
V_above = Cells(x_above_r, x_above_c + 1)
Else
If Cells(x_above_r + 1, x_above_c) = "" And Cells
(x_above_r, x_above_c) < X And X_extr = 0 Then
V_above = Cells(x_above_r, x_above_c + 1)
Else
If x_above_r = a_row + 5 And X_extr = 1 Then
x_above_r = x_above_r + 1
End If
V_above = (X - Cells(x_above_r - 1, x_above_c)) * _
(Cells(x_above_r, x_above_c + 1) - Cells(x_above_r -
1, x_above_c + 1)) / _
(Cells(x_above_r, x_above_c) - Cells(x_above_r - 1,
x_above_c)) + _
Cells(x_above_r - 1, x_above_c + 1)
End If
End If
If Cells(z_above_r, z_above_c) = "" Then
Interp = V_above
Else
If (z_above_c = a_column And Z_extr = 0) Or (Cells(z_above_r,
z_above_c + 3) = "" And Cells(z_above_r, z_above_c) < Z And Z_extr = 0) Then
Interp = V_above
Else

' V_below calculation
x_above_r = a_row + 5
x_above_c = z_above_c - 3
Do Until Cells(x_above_r + 1, x_above_c) = "" Or Cells(x_above_r,
x_above_c) X
x_above_r = x_above_r + 1
Loop
If x_above_r = a_row + 5 And X_extr = 0 Then
V_below = Cells(x_above_r, x_above_c + 1)
Else
If Cells(x_above_r + 1, x_above_c) = "" And Cells
(x_above_r, x_above_c) < X And X_extr = 0 Then
V_below = Cells(x_above_r, x_above_c + 1)
Else
If x_above_r = a_row + 5 And X_extr = 1 Then
x_above_r = x_above_r + 1
End If
V_below = (X - Cells(x_above_r - 1, x_above_c)) * _
(Cells(x_above_r, x_above_c + 1) - Cells(x_above_r -
1, x_above_c + 1)) / _
(Cells(x_above_r, x_above_c) - Cells(x_above_r - 1,
x_above_c)) + _
Cells(x_above_r - 1, x_above_c + 1)
End If
End If
Interp = (Z - Cells(z_above_r, z_above_c - 3)) * (V_above -
V_below) / _
(Cells(z_above_r, z_above_c) - Cells(z_above_r, z_above_c - 3)) +
V_below
End If
End If

End Function



Ivyleaf wrote:
Hi Alexander,

At a guess I would say you have something wrong with your UDF. My
guess would be that somewhere in the code of the function you refer to
"ActiveSheet" or something similar. Hence, when you are on a different
sheet and change something, your UDF is recalculating, but not with
the correct info. When you switch back to the correct sheet, it does
not recalculate again, because it already did when you were on the
other sheet.

Maybe post your UDF code and we could possibly help.

Cheers,
Ivan.

Hi, experts

[quoted text clipped - 11 lines]
Is it possible to recalculate my function when any cell in entire workbook
has been changed?

No you shouldn't need to do that, there is a technique to grab the sheet the
function is on.

I hope I have caught all of the references to ranges, check it though.

One other thing, you should be aware that this line

Dim b, a_row, a_column, z_above_r, z_above_c, x_above_r, x_above_c As
Integer

may not be doing what you think it is. It is not declaring all of these
variables as type Integer. Only the last is an integer, all of the others
are variants. To make them all integer you need

Dim b As Integer, a_row As Integer, a_column As Integer, _
z_above_r As Integer, z_above_c As Integer, x_above_r,_
x_above_c As Integer

that is explicitly define them.

here is the code changed, watch the wrap-around

Function Interp(Param_Name As Object, X As Double, Z As Double, X_extr As
Double, Z_extr As Double)

Dim Name_Address As String
Dim b, a_row, a_column, z_above_r, z_above_c, x_above_r, x_above_c As
Integer
Dim V_above, V_below As Double

Application.Volatile

With Application.Caller.Parent

Name_Address = Param_Name.Address(ReferenceStyle:=xlR1C1)
Name_Address = Right(Name_Address, Len(Name_Address) - 1)
b = InStr(Name_Address, "C")
a_row = Val(Name_Address)
Name_Address = Right(Name_Address, Len(Name_Address) - b)
a_column = Val(Name_Address)

z_above_r = a_row + 2
z_above_c = a_column
If .Cells(z_above_r, z_above_c) < "" Then

Do Until .Cells(z_above_r, z_above_c + 3) = "" Or _
.Cells(z_above_r, z_above_c) Z
z_above_c = z_above_c + 3
Loop
If z_above_c = a_column And Z_extr = 1 Then
z_above_c = z_above_c + 3
End If
End If
' V_above calculation
x_above_r = a_row + 5
x_above_c = z_above_c
Do Until .Cells(x_above_r + 1, x_above_c) = "" Or _
.Cells(x_above_r, x_above_c) X
x_above_r = x_above_r + 1
Loop
If x_above_r = a_row + 5 And X_extr = 0 Then
V_above = .Cells(x_above_r, x_above_c + 1)
Else
If .Cells(x_above_r + 1, x_above_c) = "" And _
.Cells(x_above_r, x_above_c) < X And X_extr = 0
Then
V_above = .Cells(x_above_r, x_above_c + 1)
Else
If x_above_r = a_row + 5 And X_extr = 1 Then
x_above_r = x_above_r + 1
End If
V_above = (X - .Cells(x_above_r - 1, x_above_c))
* _
(.Cells(x_above_r, x_above_c + 1) -
..Cells(x_above_r - 1, x_above_c + 1)) / _
(.Cells(x_above_r, x_above_c) -
..Cells(x_above_r - 1, x_above_c)) + _
.Cells(x_above_r - 1, x_above_c + 1)
End If
End If
If .Cells(z_above_r, z_above_c) = "" Then
Interp = V_above
Else
If (z_above_c = a_column And Z_extr = 0) Or _
(.Cells(z_above_r, z_above_c + 3) = "" And _
.Cells(z_above_r, z_above_c) < Z And Z_extr = 0) Then
Interp = V_above
Else

' V_below calculation
x_above_r = a_row + 5
x_above_c = z_above_c - 3
Do Until .Cells(x_above_r + 1, x_above_c) = "" Or _
.Cells(x_above_r, x_above_c) X
x_above_r = x_above_r + 1
Loop
If x_above_r = a_row + 5 And X_extr = 0 Then
V_below = .Cells(x_above_r, x_above_c + 1)
Else
If .Cells(x_above_r + 1, x_above_c) = "" And _
.Cells(x_above_r, x_above_c) < X And X_extr = 0
Then
V_below = .Cells(x_above_r, x_above_c + 1)
Else
If x_above_r = a_row + 5 And X_extr = 1 Then
x_above_r = x_above_r + 1
End If
V_below = (X - .Cells(x_above_r - 1, x_above_c))
* _
(.Cells(x_above_r, x_above_c + 1) -
..Cells(x_above_r - 1, x_above_c + 1)) / _
(.Cells(x_above_r, x_above_c) -
..Cells(x_above_r - 1, x_above_c)) + _
.Cells(x_above_r - 1, x_above_c + 1)
End If
End If
Interp = (Z - .Cells(z_above_r, z_above_c - 3)) * (V_above -
V_below) / _
(.Cells(z_above_r, z_above_c) - .Cells(z_above_r,
z_above_c - 3)) + V_below
End If
End If
End With
End Function


--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Alexander_Sagiyan" <u42464@uwe wrote in message news:81c63188cdc50@uwe...
I think your guess is just right.
I'm not using activesheet but I'm using cells from the sheet I'm calling.
As
far as I understood I can not address other sheet cells from a function.
Am I
right?
So I'd better get data into my function through argument, would I?
Thank you so much for your help and here is the code


Function Interp(Param_Name As Object, X As Double, Z As Double, X_extr As
Double, Z_extr As Double)

Dim Name_Address As String
Dim b, a_row, a_column, z_above_r, z_above_c, x_above_r, x_above_c As
Integer
Dim V_above, V_below As Double

ActiveWorkbook.Application.Volatile
Name_Address = Param_Name.Address(ReferenceStyle:=xlR1C1)
Name_Address = Right(Name_Address, Len(Name_Address) - 1)
b = InStr(Name_Address, "C")
a_row = Val(Name_Address)
Name_Address = Right(Name_Address, Len(Name_Address) - b)
a_column = Val(Name_Address)

z_above_r = a_row + 2
z_above_c = a_column
If Cells(z_above_r, z_above_c) < "" Then

Do Until Cells(z_above_r, z_above_c + 3) = "" Or Cells(z_above_r,
z_above_c) Z
z_above_c = z_above_c + 3
Loop
If z_above_c = a_column And Z_extr = 1 Then
z_above_c = z_above_c + 3
End If
End If
' V_above calculation
x_above_r = a_row + 5
x_above_c = z_above_c
Do Until Cells(x_above_r + 1, x_above_c) = "" Or
Cells(x_above_r,
x_above_c) X
x_above_r = x_above_r + 1
Loop
If x_above_r = a_row + 5 And X_extr = 0 Then
V_above = Cells(x_above_r, x_above_c + 1)
Else
If Cells(x_above_r + 1, x_above_c) = "" And Cells
(x_above_r, x_above_c) < X And X_extr = 0 Then
V_above = Cells(x_above_r, x_above_c + 1)
Else
If x_above_r = a_row + 5 And X_extr = 1 Then
x_above_r = x_above_r + 1
End If
V_above = (X - Cells(x_above_r - 1, x_above_c)) * _
(Cells(x_above_r, x_above_c + 1) -
Cells(x_above_r -
1, x_above_c + 1)) / _
(Cells(x_above_r, x_above_c) - Cells(x_above_r - 1,
x_above_c)) + _
Cells(x_above_r - 1, x_above_c + 1)
End If
End If
If Cells(z_above_r, z_above_c) = "" Then
Interp = V_above
Else
If (z_above_c = a_column And Z_extr = 0) Or (Cells(z_above_r,
z_above_c + 3) = "" And Cells(z_above_r, z_above_c) < Z And Z_extr = 0)
Then
Interp = V_above
Else

' V_below calculation
x_above_r = a_row + 5
x_above_c = z_above_c - 3
Do Until Cells(x_above_r + 1, x_above_c) = "" Or
Cells(x_above_r,
x_above_c) X
x_above_r = x_above_r + 1
Loop
If x_above_r = a_row + 5 And X_extr = 0 Then
V_below = Cells(x_above_r, x_above_c + 1)
Else
If Cells(x_above_r + 1, x_above_c) = "" And Cells
(x_above_r, x_above_c) < X And X_extr = 0 Then
V_below = Cells(x_above_r, x_above_c + 1)
Else
If x_above_r = a_row + 5 And X_extr = 1 Then
x_above_r = x_above_r + 1
End If
V_below = (X - Cells(x_above_r - 1, x_above_c)) * _
(Cells(x_above_r, x_above_c + 1) -
Cells(x_above_r -
1, x_above_c + 1)) / _
(Cells(x_above_r, x_above_c) - Cells(x_above_r - 1,
x_above_c)) + _
Cells(x_above_r - 1, x_above_c + 1)
End If
End If
Interp = (Z - Cells(z_above_r, z_above_c - 3)) * (V_above -
V_below) / _
(Cells(z_above_r, z_above_c) - Cells(z_above_r, z_above_c - 3))
+
V_below
End If
End If

End Function



Ivyleaf wrote:
Hi Alexander,

At a guess I would say you have something wrong with your UDF. My
guess would be that somewhere in the code of the function you refer to
"ActiveSheet" or something similar. Hence, when you are on a different
sheet and change something, your UDF is recalculating, but not with
the correct info. When you switch back to the correct sheet, it does
not recalculate again, because it already did when you were on the
other sheet.

Maybe post your UDF code and we could possibly help.

Cheers,
Ivan.

Hi, experts

[quoted text clipped - 11 lines]
Is it possible to recalculate my function when any cell in entire
workbook
has been changed?

Thanks, Bob!

It works! Very useful technique.


Thank you one more time