我对之前的两个答案都有困难。
我同意验证是必不可少的;如果用户没有认真考虑提示,他们可能会输入“2011-4”。检查其格式是否为“Q# ####”绝对是朝着正确方向迈出的一步。然而:
我会指出这种程度的检查是不够的。例如,“Q5 1234”将匹配此格式。 “Q5 1234”表明用户试图破坏系统,但“Q4 2101”是一个很容易犯的错误。
Like 运算符是 Excel 2003 中唯一的选择,但对于更高版本,我建议考虑正则表达式。我一直在 VB 2010 中尝试它们。我不否认它们很难理解,但它们为您做了很多事情。也许目前重武器已经有足够的学习内容,但我仍然建议看看最近关于其使用的一些问题。
正如前面的答案中所使用的,InputBox 没有实现重武器的目标。如果我输入“Q4 2101”而不是“Q4 2011”,并且宏被增强以检查不可能的日期,我将不知道我的简单错误,除非错误消息包含我输入的值。另外,我无法将“Q4 2101”编辑为我想要输入的值。 InputBox 的语法为 vReply = InputBox(Prompt, Title, Default, ...)。因此,如果我要推荐使用 Like 运算符,我会建议:
Sub updatesheet()
Dim vReply As String
Dim Prompt As String
Dim Title As String
Dim UpdateQuarter As Integer
Dim UpdateYear As Integer
' I have found users respond better to something like "Qn ccyy"
Prompt = "Enter period (format: Qn ccyy) to update, or hit enter to escape"
' I find a title that gives context can be helpful.
Title = "Update sheet"
vReply = InputBox(Prompt, Title)
Do While True
' I have had too many users add a space at the end of beginning of a string
' or an extra space in the middle not to fix these errors for them.
' Particularly as spotting extra spaces can be very difficult.
vReply = UCase(Trim(VReply))
vReply = Replace(vReply, " ", " ") ' Does not cater for three spaces
If Len(vReply) = 0 Then Exit Sub
If vReply Like "Q# ####" Then
' I assume your macro will need these value so get them now
' so you can check them.
UpdateQuarter = Mid(vReply, 2, 1)
UpdateYear = Mid(vReply, 4)
' The check here is still not as full as I would include in a macro
' released for general use. I assume "Q4-2011" is not valid because
' the quarter is not finished yet. Is "Q3-2011" available yet? I
' would use today's date to calculate the latest possible quarter.
' I know "You cannot make software foolproof because fools are so
' ingenious" but I have learnt the hard way that you must try.
If UpdateQuarter >= 1 And UpdateQuarter <= 4 And _
UpdateYear >= 2009 And UpdateYear <= 2012 Then
Exit Do
Else
' Use MsgBox to output error message or include it in Prompt
End If
Else
' Use MsgBox to output error message or include it in Prompt
End If
vReply = InputBox(Prompt, Title, vReply)
Loop
End Sub
最后,我很少使用 InputBox,因为表单一旦掌握,就很容易创建并提供更多的控制。