• Home
  • Other Stuff
  • Contact
Black Tor

VBA: A Few Tips

IF

Excel VBA

One of the most commonly used processes within VBA is IF. In its most basic form it is similar in use and interpretation to the formula performing a simple check to find if a value is or is not equal to a value and taking the appropriate steps.
Here is the code...

Sub SimpleIF ()
' This checks if a value is greater than 100
Dim iValueA As Integer
Dim iValueB As Integer
' Assign value to integers A & B
iValueA = 150
iValueB = 100
If iValueA > iiValueB Then
Exit Sub
Else
MsgBox "It is less than 100"
End if

End Sub

If Alone

It is a common misconception that when using IF that two differing options should exist in the nature of TRUE and FALSE or black and white. Within VBA this is not the case, an IF check can exist on a single line if it completes itself. Using the same parameters as above this routine produces exactly the same result for the user with less code. Although the routine below is not significantly shorter the IF section has been reduced from 5 lines to 1.
Here is the code...

Sub SingleIF ()
' This uses one line to perform an IF check
Dim iValueA As Integer
Dim iValueB As Integer
' Assign value to integers A & B
iValueA = 150
iValueB = 100
If iValueA > iiValueB Then Exit Sub
MsgBox "It is less than 100"

End Sub

.

Return to the  TOP Up Arrow

More on IF: Elseif

This addition to the IF check allows the process to check for multiple results instead of a simple TRUE or FALSE comparison. The above IF checks have made no allowance for both values being equal, this is now rectified.
Here is the code...

Sub ElseIf ()
' This allows multiple checks within an IF process
Dim iValueA As Integer
Dim iValueB As Integer
' Assign value to integers A & B
iValueA = 150
iValueB = 100
If iValueA = iiValueB Then
MsgBox "A & B are the same"
ElseIf iValueA > iValueB Then MsgBox "A is greater than B"
Else MsgBox "B is greater than A"
End If

End Sub

Note that although the above example uses only a single iteration of ElseIf it can be used multiple times if you require. However it may be better to consider using SELECT CASE.

Return to the  TOP Up Arrow

Select Case

.

Sub SingleIF () ' This uses one line to perform an IF check
Dim iValueA As Integer
Dim iValueB As Integer
' Assign value to integers A & B
iValueA = 150
iValueB = 100
If iValueA > iiValueB thenExit Sub
msgbox "It is less than 100"

End Sub

Return to the  TOP Up Arrow

Updating

Working on it!

Currently this site is under re-construction and this page amongst many is constantly being updated with information. Please revisit the site over the next few days and weeks as we add more content.