How to Use the Microsoft Excel IIF VBA Function

Excel IIF Function

IIF function is used to evaluate an expression and perform one of two actions based on the outcome of the evaluation. For example:

The Basics:

IIF (Value > 10, Perform this action if Value is <= 10, Perform this action is Value is > 10)

This function is available within VBA code and also as an Excel function. Usually the IIF function is used to perform quick logical assessments and can be nested to perform more complicated evaluations. It is however important to remember that nested IF statements can become very complicated and difficult to support and maintain.

Now let’s look at an example. Let’s assume that we want to calculate the length of the string only if it contains the value Excel Help and Excel.

It is important to note that we could have used the IIF statement in one of our For Next loops to run through all the rows on a worksheet.

Code

Dim StringToProcess As String’Variable to hold the string to be processed
StringToProcess = ActiveSheet.Cells(2, 1).Value
ActiveSheet.Cells(6, 1).Value = IIf(InStr(StringToProcess, “ExcelHelp”) > 0, IIf(InStr(StringToProcess, ” Excel “) > 0,
Len(StringToProcess), 0), 0)

Output

Do you want to learn more about Excel and become a PRO in VBA at your own pace? Our friends over at Excel Campus will teach you everything you need to know!

If you need Microsoft Excel help today, contact us now!