Is there a reason this only functions when "MsgBox (offdays)" is included?

Question

I am unsure why this code only functions properly with the inclusion of a seemingly inconsequential MsgBox line. If I try to run this code with the "MsgBox (offdays)" line commented out, I receive the error msg of

"Run-time error '13': Type mismatch".

The strings being passed into the code are in a string form, (Example: Jun 0902:00 AM), and reside in the first column of the data sheets. I am trying to determine if the 1st column of these data sheets are in sequential order from top to bottom, (Example: Cell(1,1) = Dec 31, Cell(2,1) = Jun 20, Cell(3,1) = Jan 1). For some reason the code works fine only when the MsgBox(offdays) line is included. Obviously it is unviable to close thousands of msgbox's manually and I have yet to find a different workaround and I am also unsure why this would even change how it works. Any advice is appreciated.

Current version of code:

Public Sub validSheetVerifier()

Dim sheetcounter As Integer

If VALIDSHEETORDER = True Then
    MsgBox ("Valid Sheet Order")
    For sheetcounter = 4 To Worksheets.Count
        Worksheets(sheetcounter).Select
        Call SEQUENTIALDATECHECK(ActiveSheet)
        MsgBox ("Valid Sheet")
    Next
Else
    MsgBox ("error invalidsheetorder")
End If
    
End Sub

Function VALIDSHEETORDER() As Boolean

If Worksheets(1).Name = "DATA" And Worksheets(2).Name = "TEST" And Worksheets(3).Name = "RESULTS" Then
    VALIDSHEETORDER = True
Else
    VALIDSHEETORDER = False
End If

End Function

Function SEQUENTIALDATECHECK(currentsheet As Worksheet) As Boolean

Dim rowcounter As Integer
Dim nextrow As Integer
Dim offdays As Integer

    For rowcounter = 2 To TOLASTROW(currentsheet)
        nextrow = rowcounter + 1
        offdays = WorksheetFunction.Days(TOMATCHDAY(rowcounter), TOMATCHDAY(rowcounter + 1))
        ' MsgBox (offdays)
        If offdays < 0 Then
            MsgBox ("error " & ActiveSheet.Name & " row: " & rowcounter)
        Else
        End If
    Next

End Function

Function TOLASTROW(currentsheet As Worksheet) As Integer
    
    TOLASTROW = currentsheet.Cells(Rows.Count, 1).End(xlUp).Row
    
End Function

Function TOMATCHSTRING(currentrow As Integer) As String

    TOMATCHSTRING = Cells(currentrow, 1)

End Function

Function TOMATCHDAY(currentrow As Integer) As Date

Dim matchday As String
    
    matchday = Left(TOMATCHSTRING(currentrow), 6)
    
    TOMATCHDAY = CDate(matchday)

End Function

Edit: Partial Sample Data Set

Jun 0902:00 AM
Jun 0806:35 AM
Jun 0806:00 AM
Jun 0803:40 AM
Jun 0801:55 AM
Jun 0706:35 AM
Jun 0706:00 AM
Jun 0703:40 AM
Jun 0701:55 AM
Jun 0407:00 AM
Jun 0405:40 AM
Jun 0405:05 AM
Jun 0404:00 AM
Jun 0402:30 AM
Jun 0402:00 AM
Jun 0306:30 AM
Jun 0306:05 AM
Jun 0305:00 AM
Jun 0303:35 AM
Jun 0303:05 AM
Jun 0302:05 AM
Jun 0102:35 PM
Jun 0101:20 PM
Jun 0111:45 AM
Jun 0111:15 AM
Jun 0109:55 AM
Jun 0109:20 AM
Jun 0107:45 AM
May 3006:55 AM
May 3006:00 AM
May 3005:10 AM
May 3003:40 AM

When the MsgBox (offdays) line is not included it points to "TOMATCHDAY = CDATE(matchday)", however I have tried a few different variations of this same code including using DateDiff and I encounter similar issues. — Frenzii, Jun 09 '21 at 15:49
That means that `matchday` is not a date or text-that-looks-like-a-date. Consider using [`IsDate`](https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/isdate-function) to test. — BigBen, Jun 09 '21 at 15:49
I changed the TOMATCHDAY function to Function TOMATCHDAY(currentrow As Integer) As Date Dim matchday As String matchday = Left(TOMATCHSTRING(currentrow), 6) If IsDate(matchday) = True Then TOMATCHDAY = CDate(matchday) Else MsgBox (matchday) End If End Function however it still works if i include the random msgbox line and not without it, i apologize for the poor formatting — Frenzii, Jun 09 '21 at 15:56
Some other suggestions: [avoid using `Select`](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba), instead of using a row number as a function parameter, use an actual `Range`. Really `TOMATCHSTRING = Cells(currentrow, 1)` is problematic because the `Cells` is implicitly referring to the `ActiveSheet`. — BigBen, Jun 09 '21 at 16:03
Also [use `Long` instead of `Integer`](https://stackoverflow.com/questions/26409117/why-use-integer-instead-of-long). — BigBen, Jun 09 '21 at 16:05
Not sure how `matchday = Left(TOMATCHSTRING(currentrow), 6)` will be a date which you are making a date out of by doing `TOMATCHDAY = CDate(matchday)`. Is there something inconsistent there? — shahkalpesh, Jun 09 '21 at 16:05
@shahkalpesh the raw datestrings are in the format of Jun 0902:00 AM and since the time isnt relevant for this part at least, I wanted to cut them to the format of "Jun 09" for this function but since I had to pass Date datatypes for the Days function i tried using CDATE although this did not solve my problem and was probably incorrect — Frenzii, Jun 09 '21 at 16:15
@BigBen should i still use long even if the maximum possible row will be ~1500 due to a max of 5 matches per day per player for 365 days if they play every single day — Frenzii, Jun 09 '21 at 16:17
Yes, as the linked thread explains, there's no benefit to using `Integer`. — BigBen, Jun 09 '21 at 16:23
Would help to post some sample data (just a list of the date values enough to replicate the issue) — Tim Williams, Jun 09 '21 at 16:27
I think it only "works" with the messagebox because you didn't run through the whole sheet to the point where you run off the end of your data. Because you are using `rowcounter + 1` in your date comparison, the very last "date" cell will be empty when `rowCounter` = `TOLASTROW(currentsheet)`. That would give you the Type Mismatch error. Change your loop to `For rowcounter = 2 To TOLASTROW(currentsheet) - 1` — Tim Williams, Jun 09 '21 at 16:39
@TimWilliams yep that was the issue lol thanks alot, it works with TOLASTROW - 1 instead — Frenzii, Jun 09 '21 at 16:42

score 2 · Answer 1 · answered Jun 09 '21 at 16:44

My guess is it only "works" with the messagebox because you didn't run through the whole sheet to the point where you run off the end of your data. Because you are using rowcounter + 1 in your date comparison, the very last "date" cell will be empty when rowCounter = TOLASTROW(currentsheet).

That would give you the Type Mismatch error.

Change your loop to

For rowcounter = 2 To TOLASTROW(currentsheet) - 1

Is there a reason this only functions when "MsgBox (offdays)" is included?

1 Answers1