Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
IIf Function
I just ran across the IIf function and am trying to clarify its usage.
I would normally write: If Len(Range("A1")) 0 then Var1 = "Yes" Else Var1 = "No" End If It looks like I could write: Var1 = IIf(Len(Range("A1")) 0, "Yes" , "No") Is my logic correct? Would this be the principle use of that function, to shorten the coding required in my example? Or is this function used in some other types of cases? -- Ken Hudson |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
IIf Function
Hi,
In your simple example both snippets evaluate the same but consider this equally simplistic example Var1 = IIf(Len(Range("A1")) 0, "Yes", 22 / 0) Even if this evaluates as TRUE (Len(A1)0) you will still get an error (Div/zero) because in IIF both parts are evaluated unlike an If/then statement where evaluation stops as soon as a condition satisfies the statement. If Len(Range("A1")) 0 Then Var1 = "Yes" Else Var1 = 22 / 0 End If Would work if A10 Mike "Ken Hudson" wrote: I just ran across the IIf function and am trying to clarify its usage. I would normally write: It looks like I could write: Var1 = IIf(Len(Range("A1")) 0, "Yes" , "No") Is my logic correct? Would this be the principle use of that function, to shorten the coding required in my example? Or is this function used in some other types of cases? -- Ken Hudson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
IIf Function
Thanks Mike.
I think that I read that somewhere. Is there a clear example then of when one would need the IIf function? -- Ken Hudson "Mike H" wrote: Hi, In your simple example both snippets evaluate the same but consider this equally simplistic example Var1 = IIf(Len(Range("A1")) 0, "Yes", 22 / 0) Even if this evaluates as TRUE (Len(A1)0) you will still get an error (Div/zero) because in IIF both parts are evaluated unlike an If/then statement where evaluation stops as soon as a condition satisfies the statement. If Len(Range("A1")) 0 Then Var1 = "Yes" Else Var1 = 22 / 0 End If Would work if A10 Mike "Ken Hudson" wrote: I just ran across the IIf function and am trying to clarify its usage. I would normally write: It looks like I could write: Var1 = IIf(Len(Range("A1")) 0, "Yes" , "No") Is my logic correct? Would this be the principle use of that function, to shorten the coding required in my example? Or is this function used in some other types of cases? -- Ken Hudson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
IIf Function
Hi,
For the reason given I avoid it's use and have never found any compelling reason to change that view. In addition the extra call in evaluating the false part (assuming it has evaluated as true) could be an issue if speed and preformance are a factor in your code. Mike "Ken Hudson" wrote: Thanks Mike. I think that I read that somewhere. Is there a clear example then of when one would need the IIf function? -- Ken Hudson "Mike H" wrote: Hi, In your simple example both snippets evaluate the same but consider this equally simplistic example Var1 = IIf(Len(Range("A1")) 0, "Yes", 22 / 0) Even if this evaluates as TRUE (Len(A1)0) you will still get an error (Div/zero) because in IIF both parts are evaluated unlike an If/then statement where evaluation stops as soon as a condition satisfies the statement. If Len(Range("A1")) 0 Then Var1 = "Yes" Else Var1 = 22 / 0 End If Would work if A10 Mike "Ken Hudson" wrote: I just ran across the IIf function and am trying to clarify its usage. I would normally write: It looks like I could write: Var1 = IIf(Len(Range("A1")) 0, "Yes" , "No") Is my logic correct? Would this be the principle use of that function, to shorten the coding required in my example? Or is this function used in some other types of cases? -- Ken Hudson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
IIf Function
To add to Mike's thoughts, there is never a situation where you would need
Iif because If ... Else ... Endif can always do the same job. I use it very occasionally where I think it makes the code read more clearly. -- __________________________________ HTH Bob "Mike H" wrote in message ... Hi, For the reason given I avoid it's use and have never found any compelling reason to change that view. In addition the extra call in evaluating the false part (assuming it has evaluated as true) could be an issue if speed and preformance are a factor in your code. Mike "Ken Hudson" wrote: Thanks Mike. I think that I read that somewhere. Is there a clear example then of when one would need the IIf function? -- Ken Hudson "Mike H" wrote: Hi, In your simple example both snippets evaluate the same but consider this equally simplistic example Var1 = IIf(Len(Range("A1")) 0, "Yes", 22 / 0) Even if this evaluates as TRUE (Len(A1)0) you will still get an error (Div/zero) because in IIF both parts are evaluated unlike an If/then statement where evaluation stops as soon as a condition satisfies the statement. If Len(Range("A1")) 0 Then Var1 = "Yes" Else Var1 = 22 / 0 End If Would work if A10 Mike "Ken Hudson" wrote: I just ran across the IIf function and am trying to clarify its usage. I would normally write: It looks like I could write: Var1 = IIf(Len(Range("A1")) 0, "Yes" , "No") Is my logic correct? Would this be the principle use of that function, to shorten the coding required in my example? Or is this function used in some other types of cases? -- Ken Hudson |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
IIf Function
The IIF function is much slower than using an If..Else..Then construction,
so it is probably a good rule to never use it in a large loop. The only time I tend to use it is where the true and false arguments are constants. For example, Cell.Interior.IndexColor = IIF(<<logical expression, 3, 5) Restricting yourself to constants only avoids the time wasted evaluating an expression that won't be used (one of the two expressions is guaranteed not to be used) and restricting yourself to single statements or, at worse, very small loops minimizes the speed disadvantage as compared to using If..Else..Then. By the way, more than likely, I would code the above line like this... Cell.Interior.IndexColor = 5 - 2 * (<<logical expression) which would match the speed of the If..Else..Then construction while giving me the advantage of an inline function call (especially useful in a chained calculation). Rick "Ken Hudson" wrote in message ... Thanks Mike. I think that I read that somewhere. Is there a clear example then of when one would need the IIf function? -- Ken Hudson "Mike H" wrote: Hi, In your simple example both snippets evaluate the same but consider this equally simplistic example Var1 = IIf(Len(Range("A1")) 0, "Yes", 22 / 0) Even if this evaluates as TRUE (Len(A1)0) you will still get an error (Div/zero) because in IIF both parts are evaluated unlike an If/then statement where evaluation stops as soon as a condition satisfies the statement. If Len(Range("A1")) 0 Then Var1 = "Yes" Else Var1 = 22 / 0 End If Would work if A10 Mike "Ken Hudson" wrote: I just ran across the IIf function and am trying to clarify its usage. I would normally write: It looks like I could write: Var1 = IIf(Len(Range("A1")) 0, "Yes" , "No") Is my logic correct? Would this be the principle use of that function, to shorten the coding required in my example? Or is this function used in some other types of cases? -- Ken Hudson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel Data Validation/Lookup function does function correcty | Excel Worksheet Functions | |||
User Function Question: Collect Condition in Dialog Box - But How toInsert into Function Equation? | Excel Programming | |||
LINKEDRANGE function - a complement to the PULL function (for getting values from a closed workbook) | Excel Worksheet Functions | |||
Excel - User Defined Function Error: This function takes no argume | Excel Programming | |||
Offset function with nested match function not finding host ss. | Excel Worksheet Functions |