As promised last week, in this article, I am going to talk about 5 rarely used features of Excel VBA. [I am not saying that all of these features are awesome to use… I have provided my personal choices for each of them]

1# Using IIF in Excel vba

IIF is a type of IF ELSE END IF. Of course, it is not exactly the same as a normal if-else statement.

Syntax of IIF Statement

IIF(Expression to check, value if true, value if false )

As you can see this, it pretty much looks like If, do this, else do this. But this is not the same as the If else statement.

Difference between If-Else and IIF

1. In If-else, else part is not mandatory. You can write an If statement even without any else blocks whereas in IIF both – true and false values are mandatory to provide. You can skip either of them.
2. In If-else, If condition expression is true then – Code inside IF block is evaluated and executed and Else Block is completely ignored. Whereas in IIF, both the expressions [value if true part] and [value if false part] are evaluated even though only one is executed at a time. Therefore you should be careful while using this statement to evaluate some conditions.

Above explanation will be more clear with the following example.

Sub IIF_Demo()

Dim x As Integer
Dim y As Integer
Dim div As Integer
x = 0
y = 2
IIf x <> 0, div = y / x, div = 0
Debug.Print div
End Sub

Note: Above program will throw a run time error divide by zero error. As mentioned above, even though condition x is not equal to zero – true and code should execute div = 0, it will also evaluate div = y/x expression as well and therefore this error.

Same logic is written using If Else in the below code and it will run successfully without any error.

Sub IFELSE_Demo()
Dim x As Integer
Dim y As Integer
Dim div As Integer
x = 0
y = 2
If (x <> 0) Then
    div = y / x
Else
    div = 0
End If
Debug.Print "If Else executed and div value is calculated... div = "; div
End Sub

2# End If is not always mandatory for If statement

If you can type your IF and/or Else statement(s) in single line as shown below then you do not need to type End If.

 Sub IfWithoutElseDemo()
    Dim a As Integer
    Dim b As Integer
    a = 1
    b = 2
    If (a > b) Then MsgBox a Else MsgBox b
End Sub

Remarks: Personally I prefer this only when
1. There is no Else part in If Statement
2. Single statement is executed as part of If

If any of the above criteria is not met, then I prefer using the If and End If – Complete set. This is my personal preference</>. This is also because it makes the code more readable else it will be difficult to read the If Else part.

3# Colon in VBA as – End of Statement

As you know in VBA new line itself is considered the end of the statement. Therefore, if you want to type multiple statements in a single line then you can use a colon as the end of a statement in VBA.

Sub ColonAsEndOfStatement()
    Dim a As Integer
    Dim b As Integer
    a = 1
    b = 2
    If (a > b) Then MsgBox "Line 1": MsgBox "Line 2": MsgBox "Line 3" Else MsgBox "Else part 1": MsgBox "Else part 2"
End Sub

Note: Personally I do not like this at all. I prefer each statement written in a new line. Using multiple statements in a single line using a colon reduces the code readability drastically.

4# Using immediate window and Debug statement

This is one of my favorite features of Excel VBA. To know more about this, I have written a detailed article about this. Read this article to know more about:
What is immediate window?
What is Debug command and how to use it?

5# Replace text in a String using mid function

Using Mid function, you can replace text in a string.

Sub replaceTextUsingMid()
    Dim inputStr As String
    inputStr = "Excel is logical"
    Mid(inputStr, 10, 2) = "ma"
    MsgBox inputStr
End Sub

Result:

The above code will result in the following message box with Text – “Excel is magical”

Note:

Integer Length specified in the Mid function should be the same as the length of the string which you want to replace it with. For example, in the above code, I have used 2 in mid function and length of string ma is also 2.

In case these two are not equal, the expression will not throw any error. It will simply take the lowest of both and does its job.

Example 1:
Following will result – Excel is magical

    inputStr = "Excel is logical"
    Mid(inputStr, 10, 3) = "ma"

Example 1:
Following will result – Excel is magical

    inputStr = "Excel is logical"
    Mid(inputStr, 10, 2) = "man"

[content_boxes layout=”icon-on-side” columns=”1″ backgroundcolor=”#F4EB94″ ][content_box title=”Important Info” icon=”” backgroundcolor=””]This method can be used effectively when you want to replace some fixed number of characters located at some specific location in a given string irrespective of what those strings are.
In this case VBA.Replace() is not useful because to use this Replace() function – you need to know what exactly the string is at a given location in a string to replace.

So this is a useful feature that can be used effectively in a particular situation.[/content_box][/content_boxes]