时间戳

首页 > 解决方案 > IF weeknumber <= "53" And weeknumber >= "1" 在 6, 7, 8, 9 下不起作用

excel - IF weeknumber <= "53" And weeknumber >= "1" 在 6, 7, 8, 9 下不起作用

问题描述

Private Function wknumber(weeknumber)
Do
weeknumber = InputBox("Week number:", "Week Selection")
If weeknumber = vbNullString Then
    MsgBox "User Canceled!!!"
    ThisWorkbook.Close
    End
Else
    If weeknumber <= "53" And weeknumber >= "1" Then
    Else
        Call MsgBox("Your week is not among 1-52, retry with a valid week and use only numeric entries!!!", vbOKOnly, "Week Validation")
      ThisWorkbook.Close
        End
    End If
End If
Loop Until IsNumeric(weeknumber)
End Function

这是一个弹出框，输入周必须小于53并且大于1，但是数字6、7、8、9不起作用，不知道为什么。你们还有什么看到的吗？

标签： excelvba

解决方案

问题是您正在比较字符串，而不是数字。VBA 可以做到这一点，但我相信只是比较第一个字符的 ascii 值，所以 "500 < "60" 将返回 true，因为 "5" 的 ascii 值小于 "6" 的 ascii 值。这就是为什么你的 6- 9 次测试失败。

您应该做的是尝试将字符串输入转换为 aLong并将其与您的限制进行比较。

如果你想保持Do ... Loop结构，那么代码看起来有点像下面。注意：例如，如果您想消除输入的小数或其他类型的数字，则需要进行额外的检查：

Dim weekString As String
Dim weekNumber As Long

Do
    weekString = InputBox("Week number:", "Week Selection")
    
    'Check for an empty string.
    If weekString = vbNullString Then
        MsgBox "User cancelled."
        'do tasks.
        Exit Sub
    End If

    'Check for a numeric
    weekNumber = 0
    On Error Resume Next
    weekNumber = CLng(weekString)
    On Error Goto 0
    If weekNumber = 0 Then
        MsgBox "Week number must be a number."
    'Check within range.
    ElseIf weekNumber < 1 Or weekNumber > 52 Then
        MsgBox "Week number must be between 1 and 52."
    End If
    
Loop While weekNumber < 1 Or weekNumber > 52

MsgBox "Week " & weekNumber & " selected."

推荐阅读