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 same as 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 same as If else statement.
Difference between If-Else and IIF
- In If-else, else part is not mandatory. You can write If statement even without any else block whereas in IIF both – true and false values are mandatory to provide. You can skip neither of them.
- 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.
2nd difference mentioned above is one of the main difference between If Else and IIF statement. This is also one of the main reason – I do not prefer to use this more often.
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 meeting, then I prefer using 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 If Else part.
3# Colon in VBA as – End of Statement
As you know in VBA new line itself is considered as end of statement. Therefore, if you want to type multiple statements in a single line then you can use colon as end of 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 statements written in a new line. Using multiple statements in a single line using colon reduces the code readability drastically.
4# Using immediate window and Debug statement
This is one of my favorite feature 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
Above code will result in following message box with Text – “Excel is magical”
Integer Length specified in Mid function should be 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, expression will not throw any error. It will simply takes the lowest of both and does its job.
Following will result – Excel is magical
inputStr = "Excel is logical" Mid(inputStr, 10, 3) = "ma"
Following will result – Excel is magical
inputStr = "Excel is logical" Mid(inputStr, 10, 2) = "man"