Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
IsEmpty function in VSTO | Excel Programming | |||
IsEmpty | Excel Programming | |||
Help with IsEmpty | Excel Programming | |||
vba: isempty | Excel Programming | |||
Problem with IsEmpty Function | Excel Programming |