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

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

 
 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

A simple decision-making structure in a subroutine

 
=     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

Block If Statements

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

Combine several If structures using ElseIf.

 
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

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

 
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

If statement ladder

 
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

If/Then/Else: guess a number

 
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

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

 
  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

If...Then...ElseIf Statement

 
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

Nest an if statement with Do While

 
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

Nest if statement in a Do Loop

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

One-Line If Statements

 
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

Use and in if statement

 

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

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

 
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

Using the If...Then Statement

 
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

Using the Multi-Line If...Then Statement

 
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

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

 
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

Write If Then statement in one line

 
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