vba - 为什么在比较日期时 31 >= 20 会返回 False？

Question

我正在调试这段代码，但我不确定为什么它返回 false 而不是 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

Answer 1

因为您正在比较两个具有不同类型的Variant(正如我们讨论后的结果......谢谢@MatsMug)。比较不同类型(一个数字和一个字符串)的 Variants 时，比较结果是未定义的行为。

这是 Variant anomalies再次..考虑这个 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 不会将数字隐式转换为字符串，反之亦然。它比较了两个不同类型的变体并返回了垃圾结果。

有关变体诅咒的更多信息，请阅读 For v=1 to v and For each v in v -- different behavior with different types

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

<小时/>

进一步考虑这三个简单的例子:

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

正如您所看到的，当数字和字符串这两个变量都被声明为变体时，比较是垃圾。当至少其中一个是显式的时，比较成功!