Influence of IsEmpty function

[Wamme]

Hi,

When updating the code of an old (working) UDF I tried to make it more
user-friendly by changing a part of the code with the "IsEmpty( ...)"
function.
But after this modification the UDF would only return a "0" when referenced
by a cell containing a formula.

I changed the UDF back to its original code but can't figure out tried what
happens with the code when using "IsEmpty(...)". The problem of returning 0
arrises as soon as I add "IsEmpty(...)" in the code. Even when it's only used
in a Debug.Print statement (ex. "Debug.Print isEmpty(UDFargument)")

Does the IsEmpty function do anything more dan returning "True" for a blank
cell or "False" ?


Original code:
If TypeName(Argument) = "Range" Then
Argument = Cell
.....
ElseIf ...
Argument = number
End If

When argument is a cell containing a formula: "=A1^2+3*A1+1"
=> returns expected value

Changed to:
If IsEmpty(Argument) Then
Argument is cell with not evaluated formula
....
End If

or

Debug.Print IsEmpty(Argument)
If TypeName(Argument) = "Range" Then
Argument = Cell
.....
ElseIf ...
Argument = number
End If


When argument is a cell containing a formula: "=A1^2+3*A1+1"
=> returns 0

 

[Niek Otten]

Please post all code

 

[Wamme]

Posting the relevant original code (all the code is impossible, it's part of
an extended add in)

Arguments: PressCell / TempCell
calling the function by
=> FlowProperty( A1;A2;velocity )
or
=> FlowProperty( 1 ; 20.5 ; velocity )

Public Function FlowProperty(PressCell As Variant, TempCell As Variant,
Property As String) As Double

Application.Volatile True
On Error GoTo catch
ExcelListSeparator = Application.International(xlListSeparator)
Dim PropertyData As New Collection
Application.Calculation = xlManual


If TypeName(TempCell) = "Range" Then
Temp = Application.ActiveSheet.Evaluate(TempCell.Formula)
ElseIf IsNumeric(TempCell) Then
Temp = TempCell
Else
MsgBox "Enter valid temperature range"
Exit Function
End If


If TypeName(PressCell) = "Range" Then
Press = Application.ActiveSheet.Evaluate(PressCell.Formula)
ElseIf IsNumeric(PressCell) Then
Press = PressCell
Else
MsgBox "Enter valid pressure range"
Exit Function
End If

 

[Charles Williams]

Application.Calculation = xlManual inside a UDF is ignored.

You don't need to use evaluate, just use
Temp=TempCell.Value

This will work both when the cell contains a formula and the cell contains a
value.

if you want to avoid your UDF processing uncalculated cells put at the top
of your function
If IsEmpty(PressCell) or Isempty(Tempcell) then exit function


Charles
___________________________________
The Excel Calculation Site
http://www.decisionmodels.com

 

[Niek Otten]

<Application.Calculation = xlManual inside a UDF is ignored>

at least when called from a worksheet formula, directly or indirectly. Same
for setting separators.
In fact you can't change anything from within a UDF, you can just return a
value that takes the place of the call in the worksheet formula. Again, when
called from a worksheet formula.

 

[Wamme]

When using TempCell.Value, the UDF returns the same wrong value as with the
IsEmpty function...

I think those functions modify the calculationflow of the excel sheet?

Many thanks
Wim

 

[Niek Otten]

In your code snippets, we do not see which value is assigned to return from
the function