奇怪的方法比较发生 [英] Strange ways comparisons occur

问题描述

这是一件奇怪的事情，我在这里看到这个问题：问题与VBA Excel数字格式我以为我可以扩展它，看看有没有人可以解释。

说我有一个Excel文档，列有5个单元格 A 全部为 1234

• 第一个格式为格式字符串000000000


• 第二个格式使用Excel的Number格式


• 第三个格式为Excel的常规格式


• 第四个格式为文本


• 第五个格式为文本，但实际上是字符串000001234

因此，excel表看起来像这样

  A | 
 ---------- +  -  
 000001234 | 
 1234.00 | 
 1234 | 
 1234 | 
 000001234 | 
  

现在我运行以下代码，基于上面提到的问题。

  Sub test3（）
 Dim rng As Range 
 Dim cl As Range 
 Set rng = Range（A1： A& Range（A& Rows.Count）.End（xlUp）.Row）
 
对于每个cl在rng.Cells 
如果1234 =000001234然后
 cl.Offset（0，1）.Value =A
 End If 
如果1234 = cl.Value然后
 cl.Offset（0，2）.Value = B
 End If 
如果000001234= cl.Value然后
 cl.Offset（0，3）.Value =C
结束If 
下一步cl 
 
 End Sub 
  

输出如下

  A | B | C | D | 
 ---------- + --- + --- + --- +  -  
 000001234 | A | B | | 
 1234.00 | A | B | | 
 1234 | A | B | | 
 1234 | A | B | | 
 000001234 | A | B | C | 
  

列 B 是有道理的。 VBA可以比较字符串，就像它们是数字一样，扩展列 C 也是有道理的，在每种情况下，我们要么将数字与数字进行比较前3行，或在最后2行进行类似的字符串比较。

但是列 D 得到我基本上是我的问题。

如果 1234 =000001234和 1234 =那么为什么不在所有情况下000001234= cl.Value

解决方案

类型 cl.Value 是 vbDouble （前3个样本行）或 vbString （其他两个），请参阅 VarType（）函数。但是在比较中，有类型的投放。

因此





• 将值与数字 1234 将始终成功，因为在比较之前将 cl.Value 中的字符串转换为数字（整数）。


• 将值与字符串000001234进行比较，只有在字符串<$ c的情况下才能成功$ c> 000001234 因为 c> cl.Value 在比较之前转换为字符串，因此您的第一个样本行000001234< ;> 1234（请参阅 cl.Value 发生的情况： 000001234 → 1234 →1234）。对于其他行，它类似： 1234.00 → 1234 →1234，这再次不等于000001234等。

This is something strange that I've come across looking at this question here: Issue with VBA Excel number formatting I thought I might expand on it and see if anyone can explain.

Lets say I have an Excel document with 5 cells in column A all with the value 1234

• The first is formatted with the format string "000000000"
• The second is formatted using Excel's "Number" format
• The third is formatted as Excel's "General" format
• The fourth is formatted as "Text"
• The fifth is formatted as "Text" but is actually the string "000001234"

As such, the excel table looks like this

     A    |
----------+-
 000001234|
   1234.00|
      1234|
1234      |
000001234 |

Now I run the following code, base slightly on the question mentioned above.

Sub test3()
    Dim rng As Range
    Dim cl As Range
    Set rng = Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row)

    For Each cl In rng.Cells
        If 1234 = "000001234" Then
            cl.Offset(0, 1).Value = "A"
        End If
        If 1234 = cl.Value Then
            cl.Offset(0, 2).Value = "B"
        End If
        If "000001234" = cl.Value Then
            cl.Offset(0, 3).Value = "C"
        End If
    Next cl

End Sub

And the output is as follows

     A    | B | C | D |
----------+---+---+---+-
 000001234| A | B |   |
   1234.00| A | B |   |
      1234| A | B |   |
1234      | A | B |   |
000001234 | A | B | C |

The column B makes sense. VBA can compare Strings as if they're numbers, and by extensions the column C makes sense as well, in each case, we're either comparing numbers to numbers as in the first 3 rows, or doing a similar string comparison in the last 2 rows.

However the column D gets me and is basically my question.

If 1234 = "000001234" and 1234 = cl.Value then why doesn't "000001234" = cl.Value in all cases?

解决方案

Type of cl.Value is either vbDouble (first 3 sample rows) or vbString (other two), see VarType() function. But during the comparison, there is type casting.
Therefore

• comparing values to number 1234 will always succeed, becaue doubles and strings in cl.Value are converted to number (integer) before comparison. Leading zeros get insignificant during conversion to number.
• comparing values to string "000001234" will succeed only in case of string 000001234 because cl.Value is converted to string before comparison so for your first sample row "000001234" <> "1234" (see what happens to cl.Value: 000001234 → 1234 → "1234"). For other rows, it is similar: 1234.00 → 1234 → "1234" and this is again not equal to "000001234" etc.

这篇关于奇怪的方法比较发生的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！