基于单元格值的Excel VBA switch语句
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16960352/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Excel VBA switch statement based on the value of a cell
提问by Mike Barnes
So I have a cell(A2) with an Integer value in it(5). I would like a switch statement that looks at the cell and then if the value of that cell is greater than 50000 do something, if the value is less than 50000 do something.
所以我有一个带有整数值的单元格（A2）（5）。我想要一个 switch 语句来查看单元格，然后如果该单元格的值大于 50000 做某事，如果该值小于 50000 做某事。
Here is my code that does not work, it says "overflow" for the line of code threshold = 50000:
这是我的代码不起作用，它说代码行“溢出” threshold = 50000：
Private Sub Worksheet_Change(ByVal Target As Range)
Application.ScreenUpdating = False
Dim state As Integer
Dim threshold As Integer
threshold = 50000
state = Sheet1.Range("A2").Value
Select Case state
Case state < threshold
'Do something
Debug.Print (state)
Case Is >= threshold
'Do something
Debug.Print (state)
End Select
End Sub
How can I fix this?
我怎样才能解决这个问题？
回答by
The problem is here:Sheet1.Range.Value("$A$2")
问题在这里：Sheet1.Range.Value("$A$2")
using Select Case
使用 Select Case
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim state As Integer
state = CInt(Sheet1.Range("$A"))
Select Case state
Case Is > Target.Value
Debug.Print "less than " & state
Case Is < Target.Value
Debug.Print "greater than " & state
Case Else
Debug.Print "Equals"
End Select
End Sub
Using ifstatement
使用if语句
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim state As Integer
state = CInt(Sheet1.Range("$A"))
If Target.Value < state Then
Debug.Print "less than " & state
ElseIf Target.Value > state Then
Debug.Print "greater than " & state
Else
Debug.Print "Equals"
End If
End Sub
POST_EDIT:
50000 is too big for an Integerdata type, so dimension it as a Longdata type to avoid the Overflow
POST_EDIT:
50000 对于一个Integer数据类型来说太大了，所以将它作为一个Long数据类型来衡量以避免溢出
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim threshold As Long
threshold = 50000
Select Case Target.Value
Case Is >= threshold
Debug.Print "greater or equal " & threshold
Case Is < Target.Value
Debug.Print "smaller than " & threshold
Case Else
Debug.Print "Can't calculate! Error"
End Select
End Sub
回答by JosieP
Your Range statement is wrong
你的 Range 语句是错误的
Private Sub Worksheet_Change(ByVal Target As Range)
Application.ScreenUpdating = False
Dim state As Integer
state = Sheet1.Range("A2").Value
Select Case state
Case Is < 5
'Do something
Debug.Print (state)
Case Is > 5
'Do something
End Select
End Sub
you didn't mention what to do if state = 5?
你没有提到如果 state = 5 该怎么办？
