VBA/Excel/Access/Word/Language Basics/IF

An If... Then... ElseIf Statement without an Else Clause

   <source lang="vb">

Sub Check_Password()
    Dim strPassword As String
BadPassword:
    strPassword = "Password"
    If Len(strPassword) = 0 Then
        End
    ElseIf Len(strPassword) < 6 Then
        Debug.Print "too short."
        GoTo BadPassword
    ElseIf Len(strPassword) > 15 Then
         Debug.Print "too long."
         GoTo BadPassword
    End If
End Sub
</source>
   
  

A simple decision-making structure in a subroutine

   <source lang="vb">

= Is equal to <> Is not equal to > Is greater than < Is less than >= Is greater than or equal to <= Is less than or equal to

Sub ifTest()

   Dim intNum As Integer
   Dim strMessage As String
   intNum = 12
   If intNum > 10 Then
       strMessage = "The number is " & intNum
   End If
   Debug.Print strMessage

End Sub

</source>
   
  

Block If Statements

   <source lang="vb">

Sub blockIf()

   Dim bytAge As Byte
   If bytAge < 21 Then
       MsgBox "You may not purchase alcohol.", , "Underage"
       End
   End If

End Sub

</source>
   
  

Combine several If structures using ElseIf.

   <source lang="vb">

Sub ifTest4()

     Dim intNum As Integer
     intNum = 12
     If intNum = 1 Then
           Debug.Print "This is the lowest number"
     ElseIf intNum = 15 Then
           Debug.Print "This is the highest number"
     Else
           Debug.Print "The number is between 1 and 15"
     End If

End Sub

</source>
   
  

Decisions Based on More Than One Condition: Using the If..Then...AND Conditional Statement

   <source lang="vb">

Sub IfThenAnd()

   Dim price As Single
   Dim units As Integer
   Dim rebate As Single
   Const strMsg1 = "To get a rebate you must buy an additional "
   Const strMsg2 = "Price must equal $7.00"
   units = 234
   price = 7
   If price = 7 And units >= 50 Then
       rebate = (price * units) * 0.1
        MsgBox "The rebate is: $" & rebate
   End If
   If price = 7 And units < 50 Then
       MsgBox strMsg1 & "50 - units."
   End If
   If price <> 7 And units >= 50 Then
       MsgBox strMsg2
   End If
   If price <> 7 And units < 50 Then
       MsgBox "You didn"t meet the criteria."
   End If

End Sub

</source>
   
  

If statement ladder

   <source lang="vb">

Function Price(Product As String) As Variant

 If Product = "Apples" Then
   Price = 12.5
 ElseIf Product = "Oranges" Then
   Price = 15
 ElseIf Product = "Pears" Then
   Price = 18
 ElseIf Product = "Mangoes" Then
   Price = 25
 Else
   Price = CVErr(xlErrNA)
 End If

End Function

Sub test()

   MsgBox CStr(Price("Oranges"))

End Sub

</source>
   
  

If/Then/Else: guess a number

   <source lang="vb">

Sub NumberGuess()

   Dim ranNumber As Integer
   Dim userGuess As Integer
   Randomize
   ranNumber = Int(Rnd * 10) + 1
   userGuess = InputBox("Guess a number between 1 and 10", "Number Guess")
   If ranNumber = userGuess Then
       Debug.Print ("You got it!")
   Else
       Debug.Print ("No, the correct answers was " & ranNumber)
   End If

End Sub Sub NumberGuess1()

   Dim ranNumber As Integer
   Dim userGuess As Integer
   Randomize
   ranNumber = Int(Rnd * 10) + 1
   userGuess = InputBox("Guess a number between 1 and 10", "Number Guess")
   If ranNumber = userGuess Then
       Debug.Print ("You got it!")
   Else
       If ranNumber < userGuess Then
           userGuess = InputBox("Too high, guess again ", "Number Guess")
           If ranNumber = userGuess Then
               Debug.Print ("You got it!")
           Else
               Debug.Print ("No, the correct answer was " & ranNumber)
           End If
       Else
           userGuess = InputBox("Too low, guess again ", "Number Guess")
           If ranNumber = userGuess Then
               Debug.Print ("You got it!")
           Else
               Debug.Print ("No, the correct answer was " & ranNumber)
           End If
       End If
   End If

End Sub

</source>
   
  

If... Then... ElseIf... Else Statements

   <source lang="vb">

 Sub Creating_a_Document()
     Dim lngButton As Long
     Dim strMessage As String
     strMessage = "Question."
     lngButton = MsgBox(strMessage, vbYesNoCancel + vbQuestion, "Create New Document")
     If lngButton = vbYes Then
         Documents.Add Template:="C:\t.dot"
     ElseIf lngButton = vbNo Then
         Documents.Add
     Else    "lngButton is vbCancel
         End
     End If
 End Sub
</source>
   
  

If...Then...ElseIf Statement

   <source lang="vb">

Sub elseIfDemo()

   Dim myNumber As Integer
   myNumber = 5
   If myNumber = 0 Then
       MsgBox "You entered zero."
   ElseIf myNumber > 0 Then
       MsgBox "You entered a positive number."
   ElseIf myNumber < 0 Then
       MsgBox "You entered a negative number."
   End If

End Sub

</source>
   
  

Nest an if statement with Do While

   <source lang="vb">

Sub doTest()

   Dim intCounter As Integer
   Dim intTest As Integer
   intTest = 1
   intCounter = 1
   Do While intTest = 1
       Debug.Print "This is loop number " & intCounter
       If intCounter >= 5 Then
           intTest = 0
       End If
       intCounter = intCounter + 1
   Loop

End Sub

</source>
   
  

Nest if statement in a Do Loop

   <source lang="vb">

Sub ShadeEverySecondRow()

 i = 0
 Do
   i = i + 2
   If IsEmpty(Cells(i, 1)) Then Exit Do
   Rows(i).Interior.ColorIndex = 15
 Loop

End Sub

</source>
   
  

One-Line If Statements

   <source lang="vb">

Sub ifDemo()

   Dim bytAge As Byte
   bytAge = InputBox("Enter your age.", "Age")
   If bytAge < 21 Then MsgBox "You may not purchase alcohol.", , "Underage"

End Sub

</source>
   
  

Use and in if statement

   <source lang="vb">

Sub ifTest3()

   Dim intNum As Integer
   Dim strMessage As String
   intNum = 4
   If intNum >= 1 And intNum <= 15 Then
       msgBox "the number is between 1 and 15"
   Else
       msgBox "Sorry, the number must be between 1 and 15"
   End If

End Sub

</source>
   
  

Using If...Then...Else Conditional Statement

   <source lang="vb">

Sub WhatTypeOfDay()

   Dim response As String
   Dim question As String
   Dim strMsg1 As String, strMsg2 As String
   Dim myDate As Date
   question = "Enter any date in the format mm/dd/yyyy:" _
           & Chr(13) & " (e.g., 11/22/1999)"
   strMsg1 = "weekday"
   strMsg2 = "weekend"
   response = InputBox(question)
   myDate = Weekday(CDate(response))
   If myDate >= 2 And myDate <= 6 Then
       MsgBox strMsg1
   Else
       MsgBox strMsg2
   End If

End Sub

</source>
   
  

Using the If...Then Statement

   <source lang="vb">

Sub SimpleIfThen()

   Dim weeks As String
   
   weeks = InputBox("How many weeks are in a year:", "Quiz")
   If weeks <> 52 Then MsgBox "Try Again"

End Sub

</source>
   
  

Using the Multi-Line If...Then Statement

   <source lang="vb">

Sub SimpleIfThen3()

   Dim weeks As String
   Dim response As String
   weeks = InputBox("How many weeks are in a year?", "Quiz")
   If weeks <> 52 Then
       MsgBox "The correct answer is 52.", response = MsgBox("Would you like to try again?", _
       vbYesNo + vbInformation + vbDefaultButton1, "Continue Quiz?")
       If response = vbYes Then
           Call SimpleIfThen3
       End If
   End If

End Sub

</source>
   
  

Within an If structure, you can have an alternative path by using an Else statement.

   <source lang="vb">

Sub ifTest2()

   Dim intNum As Integer
   Dim strMessage As String
   intNum = 9
   If intNum > 10 Then
       strMessage = "The number is greater than 10"
   Else
       strMessage = "The number is less than 10"
   End If
   Debug.Print strMessage

End Sub

</source>
   
  

Write If Then statement in one line

   <source lang="vb">

Sub SimpleIfThen2()

   Dim weeks As String
   On Error GoTo VeryEnd
   weeks = InputBox("How many weeks are in a year:", "Quiz")
   If weeks <> 52 Then MsgBox "Try Again": SimpleIfThen2
   If weeks = 52 Then MsgBox "Congratulations!"

VeryEnd: End Sub

</source>