excel - 如何在VBA的if语句中使用<>（输入框）

Question

在输入框中，我试图让用户只输入某些文本，在这种情况下，每周、每月、每季度、每半年或每年。这似乎很容易，但我只是不知道我哪里出错了。

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"

标签： excelvbaif-statementuser-inputinputbox

Answer 1

您有许多预定义的项目，您需要检查您的输入是否是这些项目之一。这是字典的经典案例。

例如

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

您可以使用替代字典，例如Kvp（键值对的缩写（我用 C# 编写的用于我自己的教化），它可以让您大大简化上面的代码。

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