Why does 31> = 20 return False here when comparing day?
I've debugged this code, but I'm not sure why this returns 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
source to share
Because you are comparing two Variant
with different types (as it turned out after our discussions ... thanks @MatsMug). The result of the comparison is undefined behavior when comparing Variants
different types, one numeric and one string.
Repeat Anomaly Variant .. 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 that String Variant
, explicitly converting it to String
with CStr
solved the problem. However, without this transformation, the comparison failed. VBA did not indirectly convert a number to a string or vice versa. It compared two variants of different types and returned the garbage result.
For more about curse Variant read For v = 1 to v and for each v in v - different behavior with different types
As it turns out, using CLng
either Val
to force the comparison of numbers is a safe way, or CStr
to force the comparison of text.
Consider these three simple examples further:
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, number and string, were declared as variants, the comparison is garbage. When at least one of them is explicit, the comparison succeeds!
source to share
Dim stDate, enDate
This statement declares two variables Variant
. They are assigned here:
stDate = STG.Range("B2"): enDate = STG.Range("B4")
Assuming [B2]
and [B4]
contain the actual date values, at this point the variables contain Variant/Date
. This is because the implicit code here looks like this:
stDate = STG.Range("B2").Value: enDate = STG.Range("B4").Value
But you probably already know that. Moving on.
salDay = Split(STG.Range("B6"), "-")
salDay
is also implicit Variant
. However, this instruction is quite loaded. Here's the implicit code:
salDay = Split(CStr(STG.Range("B6").Value), "-")
This makes an salDay
array of strings. So here we are:
?Day(i)
31
?salday(0)
20
The leading space in front 31
is that a spot of negative sign always remains in the immediate area. salDay(0)
is String
, there is no leading space. That was your clue right there.
?Day(i)>salday(0)
False
If salDay(0)
is String
, here we do a string comparison as mentioned . Except that there is no leading seat in front of 31; the implicit code is this because the type Day(i)
is Integer
:
?CStr(Day(i)) > salDay(0)
False
The solution is to get rid of it entirely salDay
: you don't need to. Assuming it [B6]
also contains the actual date, you can immediately get the day at Integer
:
?Day(STG.Range("B6").Value)
As a bonus, you'll separate your code from the string representation of the underlying date value that is in your sheet, so the change NumberFormat
won't break your code. Always treat dates as such!
source to share