在一个输入框中，我试图允许用户只输入某些文本，在这种情况下是每周、每月、每季度、每半年或每年。这看起来相当容易，但我只是不知道哪里出了问题。
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"
您需要分别测试每个条件。您可以使用变量来检查循环条件。
试试这个代码：
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
InputBox与Application.InputBox
-
InputBox函数
-
应用程序输入框方法
-
请注意关键的区别：第二种解决方案中有三种选择，而第一种方案中有两种。
代码
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
此行：
If Saving_frequency <> "weekly" or "monthly" or "quarterly" or "semiannually" or "annually" Then
'code
End If
应该是：
If Saving_frequency <> "weekly" _
And Saving_frequency <> "monthly" _
And Saving_frequency <> "quarterly"_
And Saving_frequency <> "semiannually" _
And Saving_frequency <> "annually" Then _
'code
End If
每个子句都需要单独处理，而不是作为一个组处理。
您的版本将显示为：
如果"Saving_ frequency"；是不是Array的成员("每周"、"每月"、"每季度"、"半年"、"每年"(然后
这与我的替换版本相同，后者使用And运算符。
使用相同的条件测试循环。
或者，像这样的多个答案可以通过下拉列表或UserForm中的单选按钮更好地处理。
您有许多预定义的项，您需要检查您的输入是否是其中之一。这是字典的经典案例。
例如
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
您可以使用另一个Dictionary，如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