为什么在比较日期时31> = 20在这里返回False? [英] Why is 31 >= 20 returning False here when comparing day?

问题描述

我正在调试此代码，但是我不确定为什么它返回false而不是true.

I was debugging this code but I am not sure why this is returning false instead of true.

?Day(i)>salday(0)
False
?Day(i)
 31 
?salday(0)
20
?isnumeric(day(i))
True
?isnumeric(salday(0))
True

Option Explicit
Option Compare Text

Sub genOP()

Dim wO As Worksheet
Dim i As Long, j As Long
Dim stDate, enDate, intVal, entR As Long, salDay, salAmt, stTime, enTime, dbMin, dbMax
Dim stRow As Long
Dim cet, curMn


'On Error Resume Next
Application.ScreenUpdating = False

stDate = STG.Range("B2"): enDate = STG.Range("B4")
intVal = Split(STG.Range("B3"), ","): entR = STG.Range("B5")
salDay = Split(STG.Range("B6"), "-")
salAmt = STG.Range("B7"): stTime = STG.Range("B8"): enTime = STG.Range("B9"): dbMin = STG.Range("B10"): dbMax = STG.Range("B11")

Set wO = ThisWorkbook.Sheets.Add
TEMP.Cells.Copy wO.Range("A1")

stRow = 19
curMn = Month(stDate)

For i = CLng(stDate) To CLng(enDate)

    If stRow > 19 Then
        wO.Rows(stRow & ":" & stRow).Copy
        wO.Rows(stRow + 1 & ":" & stRow + 1).Insert Shift:=xlDown
        Application.CutCopyMode = False
    End If

    cet = Trim(DESC.Range("A" & WorksheetFunction.RandBetween(2, DESC.UsedRange.Rows.Count)))

    If STG.Range("B14") = "ON" Then
       cet = cet & "Transaction amount " & Chr(34) & "&TEXT(H" & stRow & "," & Chr(34) & "#,##0.00" & Chr(34) & ")&" & Chr(34) & " GEL,"
    End If
    If STG.Range("B13") = "ON" Then
       cet = cet & Chr(34) & "&TEXT(B" & stRow & "-1," & Chr(34) & "dd mmm yyyy" & Chr(34) & ")&" & Chr(34)
    End If
    If STG.Range("B12") = "ON" Then
       cet = cet & " " & Format(stTime + Rnd * (enTime - stTime), "HH:MM AM/PM")
    End If

    If curMn = Month(i) And (Day(i) >= salDay(0) And Day(i) <= salDay(1)) Then  'Salary Day
        cet = Trim(DESC.Range("A" & WorksheetFunction.RandBetween(2, DESC.UsedRange.Rows.Count)))
        wO.Range("B" & stRow) = Format(i, "DD-MM-YYYY")
        wO.Range("I" & stRow) = salAmt
        wO.Range("L" & stRow) = MonthName(Month(i)) & "- Salome Baazov - " & "Geo" & " Ltd "
        curMn = WorksheetFunction.EDate(i, 1)
    Else
        wO.Range("B" & stRow) = Format(i, "DD-MM-YYYY")
        wO.Range("H" & stRow) = WorksheetFunction.RandBetween(dbMin, dbMax) + (WorksheetFunction.RandBetween(0, 1) * 0.5)
        wO.Range("L" & stRow) = "=" & Chr(34) & cet & Chr(34)
    End If

    stRow = stRow + 1
    i = i + intVal(WorksheetFunction.RandBetween(LBound(intVal), UBound(intVal))) - 1
Next i

wO.Rows(stRow).EntireRow.Delete
wO.Range("I" & stRow).Formula = "=SUM(I19:I" & stRow - 1 & ")"
wO.Range("H" & stRow).Formula = "=SUM(H19:H" & stRow - 1 & ")"

wO.Activate
Application.ScreenUpdating = True
STG.Range("B5") = stRow - 1
MsgBox "Process Completed"

End Sub

推荐答案

因为您正在比较两个具有不同类型的Variant(在我们的讨论之后才发现……@MatsMug).比较不同类型的Variants，一个数字和一个String时，比较结果是未定义的行为.

Because you are comparing two Variants with different types (As it turned out after our discussions... thx @MatsMug). The comparison result is undefined behavior when comparing Variants of different types, one numeric and one String.

这是

It's the Variant anomalies once again.. Consider this MCVE:

Sub Test1()
  Dim i, salday
  i = CDate("5/30/2017")
  salday = Split("20-20-20", "-")

  Debug.Print Day(i), salday(0)                  '  30    20
  Debug.Print Day(i) > salday(0)                 '  False
  Debug.Print Day(i) > CStr(salday(0))           '  True
  '                    ^^^^
  Debug.Print Val(Day(i)) > salday(0)            '  True
  '           ^^^^
End Sub

尽管salday(0)是String Variant，但使用CStr将其显式转换为String可以解决此问题.但是，如果没有该转换，则比较将失败. VBA并未将数字隐式转换为字符串，反之亦然.它比较了两个不同类型的变体，并返回了垃圾结果.

Although salday(0) is a String Variant, explicitly converting it to String with CStr solved the issue. However, without that conversion, the comparison failed. VBA did not implicitly convert the number to a string or vice-versa. It compared two Variants of different types and returned a rubbish result.

有关变种诅咒的更多信息，请阅读

For more about the Variant curse, read For v=1 to v and For each v in v -- different behavior with different types

事实证明，使用CLng或Val强制进行数字比较是比较安全的方法，或者使用CStr强制进行文本比较.

As it turns out, using CLng or Val to force number comparison is the safe way to go, or CStr to force text comparison.

请进一步考虑以下三个简单示例:

Consider further these three simple examples:

Sub Test1()
  Dim x, y: x = 30: y = "20"
  Debug.Print x > y               ' False !!
End Sub

Sub Test2()
  Dim x As Long, y: x = 30: y = "20"
  '       ^^^^^^
  Debug.Print x > y             ' True
End Sub

Sub Test3()
  Dim x, y As String:  x = 30: y = "20"
  '           ^^^^^^
  Debug.Print x > y             ' True
End Sub

如您所见，当两个变量(数字和字符串)都被声明为变量时，比较是垃圾.如果其中至少有一个明确，则比较成功！

As you can see, when both variables, the number and the string, were declared variants, the comparison is rubbish. When at least one of them is explicit, the comparison succeeds!

这篇关于为什么在比较日期时31> = 20在这里返回False?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！