How to use <> in an if statement in VBA (Input box)

Question

in an input box I am trying to allow the user to only input certain text, in this case weekly, monthly, quarterly, semiannually, or annually. It seems fairly easily but I just don't know where I am going wrong.

Do
        frequency = InputBox("Please enter your savings frequency.")
        If frequency <> "weekly" or "monthly" or "quarterly" or "semiannually" or "annually" Then
        MsgBox ("Must be weekly, monthly, quarterly, semiannually, or annually .")
        End If
    Loop While frequency <> weekly" or "monthly" or "quarterly" or "semiannually" or "annually"

Answer 1

You need to test every condition separately. You can use a variable to check the loop condition.

Try this code:

Do
    Retry = False
    Saving_frequency = InputBox("Please enter your savings frequency.")
    If Saving_frequency <> "weekly" and Saving_frequency <> "monthly" and Saving_frequency <> "quarterly" and Saving_frequency <> "semiannually" and Saving_frequency <> "annually" Then
        MsgBox ("Must be weekly, monthly, quarterly, semiannually, or annually .")
        Retry = True
    End If
Loop While Retry

Answer 2

This line:

If Saving_frequency <> "weekly" or "monthly" or "quarterly" or "semiannually" or "annually" Then
'code
End If

should be:

If Saving_frequency <> "weekly" _ 
And Saving_frequency <> "monthly" _
And Saving_frequency <> "quarterly"_
And Saving_frequency <> "semiannually" _
And Saving_frequency <> "annually" Then _
'code
End If

Each clause needs to be handled individually, and not as a group.

Your version would read as:

If "Saving_frequency" is not a member of Array("weekly", "monthly", "quarterly", "semiannually", "annually") Then

which is the same as my replacement version which uses the And operator instead.

Use the same condition to test for the loop.

Alternatively, multiple answers like this are better handled by a droplist or radio buttons in a UserForm.

Answer 3

InputBox vs Application.InputBox

• InputBox Function

• Application.InputBox Method

• Note the key difference: there are three choices in the second solution against the two in the first.

The Code

Option Explicit

' InputBox Function
Sub DoLoopQuickFix()
    
    Dim Saving_frequency As String
    Do
        Saving_frequency = _
          InputBox(Prompt:="Please enter your savings frequency.", _
                   Title:="Savings Frequency")
        Select Case Saving_frequency
            Case "weekly", "monthly", "quarterly", "semiannually", "annually"
                MsgBox "You chose '" & Saving_frequency & "'.", _
                       vbInformation, "Success"
                Exit Do
            Case Else
                MsgBox "Must be weekly, monthly, quarterly, semiannually, " _
                     & "or annually .", vbExclamation, "Fail"
        End Select
    Loop

End Sub

' Application.InputBox Method
Sub DoLoop()
    
    Dim Saving_frequency As Variant
    Do
        Saving_frequency = Application _
          .InputBox(Prompt:="Please enter your savings frequency.", _
                    Title:="Savings Frequency", _
                    Type:=2)
        Select Case Saving_frequency
            Case "weekly", "monthly", "quarterly", "semiannually", "annually"
                MsgBox "You chose '" & Saving_frequency & "'.", _
                       vbInformation, "Success"
                Exit Do
            Case False
                MsgBox "You cancelled.", vbCritical, "Cancel"
                Exit Sub
            Case Else
                MsgBox "Must be weekly, monthly, quarterly, semiannually, " _
                     & "or annually .", vbExclamation, "Fail"
        End Select
    Loop

End Sub

Answer 4

You have a number of predefined items and you need to check if your input is one of those items. This is a classic case for a Dictionary.

e.g.

Private SavingsFrequency as Scripting.Dictionary

' Later in your Module

If SavingsFrequency Is Nothing Then PopulateSavingsFrequency


Do

    Dim mySavingFrequency as String
    mySavingFrequency = InputBox("Please enter your savings frequency.")

    If SavingFrequency.Exists(LCase$(mySavingFrequency)) Then Exit Do

    MsgBox ("Must be one of: " & GetSavingsFrequencyList())
       
Loop 

' Remainder of code

Public Sub PopulateSavingsFrequency()

    Set SavingsFrequency = New Scripting.DIctionary
    With SavingsFrequency

        .Add .Count, "weekly"
        .Add .Count, "monthly"
        .Add .Count, "quarterly"
        .Add .Count, "semiannually"
        .add .Count, "annually"

    end with

End Function

Private Function GetSavingFrequencyList()

    Dim myKey as Variant
    Dim myList As String
    myString=vbNullString
    For Each myKey in SavingFrequency

         myList = myList & "," & SavingsFrequency.Item(myKey)

    Next

    GetSavingFrequencyList=myList

End Sub

You could use an alternative Dictionary such as Kvp (Short for Key Value Pairs (which I wrote in C# for my own edification) which would allow you to greatly simplify the code above.

Private SavingsFrequency as Kvp

' Later in your Module

If SavingsFrequency Is Nothing Then PopulateSavingsFrequency


Do

    Dim mySavingFrequency as String
    mySavingFrequency = InputBox("Please enter your savings frequency.")

    If SavingFrequency.HoldsValue(LCase$(mySavingFrequency)) Then Exit Do

    MsgBox ("Must be one of: " & SavingFrequency.GetValuesAsString)
       
Loop 


' Remainder of code

Public Sub PopulateSavingsFrequency()

    Set SavingsFrequency = New Kvp
    SavingsFrequency.AddByIndexFromArray split("weekly,monthly,quarterly,semiannually,annually")

End Sub