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 said:
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
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.
[quoted text clipped - 11 lines]
Is it possible to recalculate my function when any cell in entire
workbook
has been changed?
