Why is 31 >= 20 returning False here when comparing day?

Question

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

Protip: when working with dates, use the `Date` type and the functions from the `VBA.DateTime` module. — Mathieu Guindon, Jun 02 '17 at 23:13
There are a number of implicit typing issues here. `Dim a, b, c, d As Foo` declares `a`, `b`, and `c` as implicit `Variant`, and `d` as `Foo`. This leads to all kinds of implicit type conversions happening behind your back ;-) — Mathieu Guindon, Jun 02 '17 at 23:18
@Mat'sMug Yes I understand they are Variant type but then why would it would add a leading space to `Day(i)` result also to i as well ? is it only because of type declaration? — Stupid_Intern, Jun 02 '17 at 23:19
There's no extra space. Numbers output to the immediate pane simply leave a spot for a negative sign. Hmm, hold on a sec. — Mathieu Guindon, Jun 02 '17 at 23:20
@Mat'sMug well then we come to the same question again of this question's title — Stupid_Intern, Jun 02 '17 at 23:22
@A.S.H I already checked interestingly then both returned 2 the len(Day(i)) and len(salday(0)) even though there was leading space — Stupid_Intern, Jun 02 '17 at 23:39

A.S.H · Accepted Answer · 2017-06-03T08:48:20.220

6

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

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

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!

edited Jun 03 '17 at 08:48

answered Jun 02 '17 at 23:04

A.S.H

29,101
5
23
50

Ohh yes thanks I missed that I thought if isnumeric returns true then it must be a number – Stupid_Intern Jun 02 '17 at 23:08
But why would Day(i) return a number with space ? – Stupid_Intern Jun 02 '17 at 23:09
@newguy it happens :) but where does that leading space come from? a bug? it's weird indeed. – A.S.H Jun 02 '17 at 23:09
even ?i returns " 42766" with leading space and i is For i = CLng(stDate) To CLng(enDate) where stDate and enDate are dates – Stupid_Intern Jun 02 '17 at 23:12
At first I thought that you had an array called `Day`, but now that I see you're using VBA's `Day` function, I found it *very very weird*. – A.S.H Jun 02 '17 at 23:14
I added full code again just in case if it helps. Maybe because I didn't declare them as Date and as variants? – Stupid_Intern Jun 02 '17 at 23:16
I'd go with `CLng` over `Val`, but yeah that works. @newguy consider marking this answer as accepted: mine is probably useful, but misses the crux of the problem. – Mathieu Guindon Jun 03 '17 at 00:36
4

@Mat'sMug This is great. I am almost sure that RD is becoming a must for professional programming in VBA. – A.S.H Jun 05 '17 at 23:17

score 5 · Answer 2 · answered Jun 02 '17 at 23:39

5

Dim stDate, enDate

This instruction declares two Variant variables. They're assigned here:

stDate = STG.Range("B2"): enDate = STG.Range("B4")

Assuming [B2] and [B4] contain actual date values, at that point the variables contain a Variant/Date. That's because the implicit code here is as follows:

stDate = STG.Range("B2").Value: enDate = STG.Range("B4").Value

But you probably know that already. Moving on.

salDay = Split(STG.Range("B6"), "-")

salDay is also an implicit Variant. That instruction is quite loaded though. Here's the implicit code:

salDay = Split(CStr(STG.Range("B6").Value), "-")

This makes salDay an array of strings. So here we are:

?Day(i)
 31 
?salday(0)
20

The leading space in front of 31 is because the immediate pane always leaves a spot for a negative sign. salDay(0) being a String, there's no leading space. That was your clue right there.

?Day(i)>salday(0)
False

With salday(0) being a String, we're doing a string comparison here, as was already pointed out. Except there's no leading space in front of the 31; the implicit code is this, because the type of Day(i) is Integer:

?CStr(Day(i)) > salDay(0)
False

The solution is to get rid of salDay altogether: you don't need it. Assuming [B6] also contains an actual date, you can get the day into an Integer right away:

?Day(STG.Range("B6").Value)

As a bonus you decouple your code from the string representation of the underlying date value that's in your worksheet, so changing the NumberFormat won't break your code. Always treat dates as such!

answered Jun 02 '17 at 23:39

Mathieu Guindon

69,817
8
107
235

The implicit string conversion, yeah. – Mathieu Guindon Jun 02 '17 at 23:42
Good explanation. So as it turns out comparing an int to a string does not convert either type to the other for the comparison?!! Weird weird VBA. – A.S.H Jun 02 '17 at 23:44
@A.S.H it does... that's why we end up with a string comparison, because *one* of the operands is a string :-) – Mathieu Guindon Jun 02 '17 at 23:46
But `?"31" > "20"` returns True? – A.S.H Jun 02 '17 at 23:47
Why wouldn't it? By any alpha sort "3" is greater than "2"! – Mathieu Guindon Jun 02 '17 at 23:50
Of course, but what are then `CStr(Day(i))` and `salDay(0)` ? why `?Day(i) > salDay(0)` returned false? – A.S.H Jun 02 '17 at 23:51
There's no checkmark anywhere I thnk.. it's yet another vexing situation with VBA's `Variant`... Really unexpected, and the list goes on :D – A.S.H Jun 03 '17 at 00:27
Yeah! signal comparison of variants and advice explicit conversion! (y) `Val` for numeric comparison and `CStr` for string comparison.. maybe. As it turns out, at least one of the two must be explicit to be on the safe side. idk but something to investigate further here. – A.S.H Jun 03 '17 at 00:30

Why is 31 >= 20 returning False here when comparing day?

2 Answers2

Linked