If with Comparisons example codes

If Comparisons

When making comparisons, we will usually use one of the comparison operators:

Comparison Operator	Explanation
=	Equal to
<>	Not Equal to
>	Greater than
>=	Greater than or Equal to
<	Less than
<=	Less than

However, we can also use any expression or function that results in TRUE or FALSE

If – Boolean Function

When build expressions for If Statements, you can also use any function that generates TRUE or False.  VBA has a few of these functions:

Function	Description
IsDate	Returns TRUE if expression is a valid date
IsEmpty	Check for blank cells or undefined variables
IsError	Check for error values
IsNull	Check for NULL Value
IsNumeric	Check for numeric value

Comparing Text

VBA is case sensitive language, which mean if you type "a" and compare with "A", it will both character as different, we never know how would user give us an input , in such cases comparing the string gets difficult. to resolve this issue we can use the keyword " Option Compare Text " at the top of the our module so that it will consider both of the character as same

lets see with simple example 

Option Compare Text  Sub If_Text()    MsgBox "a" = "A" End Sub

 Result

 

here message box is returning True when we are comparing  "a" with "A"

VBA If Like

The VBA Like Operator allows you to make inexact comparisons of text.

Sub IF_Like() Dim strName As String strName = "Hello Mona" 'check if our string starts with Hel  If strName Like "Hel*" Then     MsgBox "True" Else     MsgBox "False" End If End Sub

 Here we’re using an asterisk “*” wildcard. The * stands for any number of any characters.  So if our string starts with Hel and followed by any other character our condition will turn true.

If Loops

VBA Loops allow you to repeat actions. Combining IF-ELSEs with Loops is a great way to quickly resolve many conditions

Sub If_Loop() Dim Cell as Range   For Each Cell In Range("A2:A6")     If Cell.Value > 0 Then       Cell.Offset(0, 1).Value = "Positive"     ElseIf Cell.Value < 0 Then       Cell.Offset(0, 1).Value = "Negative"     Else       Cell.Offset(0, 1).Value = "Zero"      End If   Next Cell  End Sub

If with Instr Function

The Instr Function tests if a string of text is found in another string.

syntax 

Instr(string, text)

lets see with an example 

Sub If_instr() Dim sti As String  sti = " hello there is lots of text " If InStr(sti, "text") > 0 Then  debug.print "Text Found" else debug.print "Text not Found" End If  End Sub

we have a string called "sti" and checking with the help of instr function if "text" is include in the string or not, if it is include then it will print text found, otherwise it will print text not found

If Goto

we can use the result of an If statement to “Go to” another section of code. 

Sub IfGoTo ()     If IsError(Cell.value) Then         Goto Skip   'here skip is label which is define at the end of the code     End If     'Some Code Skip: End Sub

Delete Row if Cell is Blank

Using Ifs and loops you can test if a cell is blank and if so delete the entire row.

Sub DeleteRowIfCellBlank() Dim Cell As Range For Each Cell In Range("A2:A10")     If Cell.Value = "" Then Cell.EntireRow.Delete Next Cell End Sub

If Message Box Result

With VBA Message Boxes we can ask to the user to select from several options. The Yes/No Message Box asks the user to select Yes or No.  You can add a Yes / No Message Box to a procedure to ask the user if they would like to continue running the procedure or not. You handle the user’s input using an If statement. 

there are multiple result of Message box that we can use as per out requirements, thees results are caller enumerator, which mean they have there fix value which can be used as result of the message box. they also have numerical value assign to them which can also be used, to view those value you can check the object browser window

Enumerator of Message Box results

 

example to use thees result

Sub MsgBoxVariable() Dim answer answer = MsgBox("Do you want to Continue?", vbQuestion + vbYesNo)   If answer = vbYes Then     MsgBox "Yes"   Else     MsgBox "No"     End If  End Sub