Influence of IsEmpty function
 

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

Influence of IsEmpty function
 

Please post all code


--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Wamme" wrote in message
...
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

Influence of IsEmpty function
 

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

"Niek Otten" wrote:

Please post all code


--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Wamme" wrote in message
...
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

Influence of IsEmpty function
 

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

"Wamme" wrote in message
...
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

"Niek Otten" wrote:

Please post all code


--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Wamme" wrote in message
...
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

Influence of IsEmpty function
 

<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.

--
Kind regards,

Niek Otten
Microsoft MVP - Excel


"Charles Williams" wrote in message
...
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

"Wamme" wrote in message
...
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

"Niek Otten" wrote:

Please post all code


--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Wamme" wrote in message
...
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

Influence of IsEmpty function
 

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

"Charles Williams" wrote:

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

"Wamme" wrote in message
...
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

"Niek Otten" wrote:

Please post all code


--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Wamme" wrote in message
...
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

Influence of IsEmpty function
 

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

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Wamme" wrote in message
...
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

"Charles Williams" wrote:

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

"Wamme" wrote in message
...
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

"Niek Otten" wrote:

Please post all code


--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Wamme" wrote in message
...
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