如何为整数区间编写选择大小写条件?

Question

我正在尝试读取某个列的Excel工作表中每一行的值，并根据数值在另一列中设置文本。

我在使用该功能时遇到溢出错误CInt。我想确定我得到的值用作整数。我在该列中的最大值是43803，所以整数应该足够了。但是，我读到我需要使用 Long 来引用代码中的列/行，所以我CInt更改为CLng.现在溢出错误消失了，但所有行都被视为类别 B。

我在列中的最小数值是329。

Sub insertColumnCategory()
Dim cellValue As Long
Dim lastColumn As Long
Dim lastRow As Long
Dim i As Long
lastColumn = ActiveSheet.Range("A1").CurrentRegion.Columns.Count + 1
Columns(lastColumn).Insert
ActiveSheet.Columns(lastColumn).NumberFormat = "General"
With ActiveSheet
lastRow = .Cells(.Rows.Count, 1).End(xlUp).Row
End With
For i = 2 To lastRow
cellValue = CLng(Cells(i, 21).Value)
Select Case cellValue
Case Is <= 0
Cells(i, 22).Value = "Category A"
Case Is >= 1, Is <= 30
Cells(i, 22).Value = "Category B"
Case Is >= 31, Is <= 60
Cells(i, 22).Value = "Category C"
Case Is >= 61, Is <= 90
Cells(i, 22).Value = "Category D"
Case Is >= 91, Is <= 180
Cells(i, 22).Value = "Category E"
Case Is >= 181, Is <= 365
Cells(i, 22).Value = "Category F"
Case Is > 365
Cells(i, 22).Value = "Category G"
End Select
Next i
End Sub

Answer 1

Case评估在第一次测试通过后立即结束。

例如：

Sub Tester()
Dim a As Long
a = 20
Select Case a
Case Is <= 0: Debug.Print "zero or less"
Case Is >= v1, Is <= v15: Debug.Print "1-15"
Case Is >= 16, Is <= 30: Debug.Print "16-30"
End Select
End Sub
Function v1()
Debug.Print "Called v1"
v1 = 1
End Function
Function v15()
Debug.Print "Called v15"
v15 = 15
End Function

输出：

Called v1
1-15

因此，永远不会调用 v15，因为跳过了该测试。