In Excel programming IIF statement is one of the most effective function to use. It is normally like the logical function in Excel
IF(Logical_test,[value_if_true],[value_if_false])
where logical test is testing the conditions and if condition is satisfied or matching then return the TRUE value else look for alternate value or return the FALSE Value.
Likewise IIF Statement is used in Excel VBA, where
IIF Statement evaluates the supplied Expression (logical test) and returns the TRUE value else returns FALSE Value.
Syntax:
IIF(Expression, TRUEPART, FALSEPART)
Expression: Is nothing but the condition to evaluate in our formula.
TRUEPART: If condition is satisfied then return with TRUE Value.
FALSEPART: If condition is not met or satisfied then return with FALSE Value.
With example let us learn how to write VBA IIF statement.
In above example we can see the criteria or condition that we have to evaluate the recognize the Sales Rep.
The Criteria is if Rank of business done by rep is >= 8 then recognize the rep as Good Performer, >= 5 then as Average Performer else Low Performer.
Incentive criteria is if Rep Rank is
>=8 then Incentive = 10% of Total business
>=5 then Incentive = 5% of Total Business
Else 2% of Total Business
Using IIF Statement is as simple as Logical Function IF(logical_test, value_if_true, value_if_false)
where Incentive and Recognition criteria are expression and returns the values on condition matches.
Start writing VBA Code in Module
First declare variable to hold values
Write the statement to determine the last used row in the table.
Activesheet.UsedRange.Rows.count
This will give you the last row number where you can use in loop as shown below
now write the vba statement to evaluate the Recognition based on Ranks and return the value in cells(i, 5)
Now calculate the incentive basis of evaluation if is TRUE and if is FALSE. Finally return the calculated values in cells(i,6)
Finally your code should look like this.
Hope you like this simple method of using IIF Statement.
You can Download << IIF Statement >> practice workbook
Continue to subscribe my blog for latest updates on VBA Coding and tactical automation.
IIF has one rather important drawback: independent of the test result, both the TRUE part of the expression and the false part are always executed. It is therefore a rather inefficient function to use. An If-Then-Else construct is more efficient.
ReplyDelete