vba 如何确定从字符串创建的变体是否为整数？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1795423/
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
How do I determine if a variant created from a string is a whole number?
提问by René Nyffenegger
I am looking to determine if a variant created from a string is a whole number.
我正在寻找确定从字符串创建的变体是否是整数。
Here's a test script:
这是一个测试脚本：
dim v as variant
v = "42"
if v <> round(v) then
msgBox("<>")
end if
The msgBox pops up, probably because the variant was created from a string, although I would have expected v to be = round(v).
msgBox 弹出，可能是因为该变体是从字符串创建的，尽管我希望 v 为 = round(v)。
回答by Philippe Grondier
You should write something like:
你应该这样写：
if cDbl(v) <> round(cDbl(v)) Then
Where cDbl is a function converting any data to a double-type number. You might have to treat cases where v cannot be converted to a number with the isNumeric() function before calling the cDbl function. You can even use the cInt function for your comparisons:
其中 cDbl 是将任何数据转换为双精度数的函数。在调用 cDbl 函数之前，您可能必须处理无法使用 isNumeric() 函数将 v 转换为数字的情况。您甚至可以使用 cInt 函数进行比较：
if isnumeric(v) then
if cDbl(v) - cInt(v) <> 0 Then
....
endif
else
debug.print "data cannot be converted to a number"
endif
回答by Dick Kusleika
Sub test()
Dim v As Variant
v = "42"
If Val(v) <> Int(Val(v)) Then
MsgBox ("<>")
End If
End Sub
If you use Val(), it will try its best to convert to a number. If it can't, it will return zero and Val(v) will always equal Int(Val(v)) in that case.
如果使用 Val()，它会尽量转换为数字。如果不能，它将返回零，并且在这种情况下 Val(v) 将始终等于 Int(Val(v))。
回答by Brian Babo
what about checking that the floor function matches the ceiling function?
检查地板函数是否与天花板函数匹配呢？
Private Function isWhole(value As Variant) As Boolean
If WorksheetFunction.Ceiling_Math(value) = WorksheetFunction.Floor_Math(value) Then
isWhole = True
Else: isWhole = False
End If
End Function
I had a similar issue and this code is working for me.
我有一个类似的问题，这段代码对我有用。
回答by Czeskleba
I like to use the simple +0 trick if I suspect numbers might come in as a string. For example for index/match things. Like so:
如果我怀疑数字可能以字符串形式出现，我喜欢使用简单的 +0 技巧。例如索引/匹配的东西。像这样：
Dim v As Variant
v = "42"
If IsNumeric(v) Then
If v + 0 <> Round(v + 0) Then
MsgBox ("<>")
End If
End If
This should work for dates, text, numbers, numbers as text. Not sure if or when this breaks, should be fine.
这应该适用于日期、文本、数字、数字作为文本。不确定这是否或何时中断，应该没问题。
