CODEWITHSUNDEEP
excelvbaruntime-error
Lorenzo Castagno
Lorenzo Castagno

Reputation: 572

Invalid procedure call or argument (Run time error 5) using VBA with Excel

I have the following issue with this VBA:

Column A (FirstDate), Column B (EndDate), Column C (Number) are input:

Sub DateTest()
    Dim FirstDate As Date    ' Declare variables.
    Dim IntervalType As String
    Dim Number As Integer
    Dim EndDate As Date
    Dim TempDate As Date
    Dim i As Integer

    IntervalType = "m" ' "m" specifies MONTHS as interval.

    With ActiveWorkbook.Worksheets(1)
        lLastRow = .UsedRange.Rows.Count

        For lRow = 1 To lLastRow
            FirstDate = Format(.Cells(lRow, 1).Value, "YYYY-MM-DD")
            EndDate = Format(.Cells(lRow, 2).Value, "YYYY-MM-DD")
            Number = .Cells(lRow, 3).Value

            ' If the number is not greater than zero an infinite loop will happen.
            If Number <= 0 Then
                MsgBox "Number needs to be greater than 0", vbCritical
                Exit Sub
            End If

            i = 1
            Do Until TempDate = EndDate
                If i <= 1 Then
                    TempDate = DateAdd(IntervalType, Number, FirstDate)
                Else
                    TempDate = DateAdd(IntervalType, Number, TempDate)
                End If
                i = i + 1
                Debug.Print i
            Loop
            Cells(lRow, 4).Value = i - 1
        Next
    End With
End Sub

If I run the above for 9 rows I got this, the output is the highlighted column:

Output_No_Issues

All good so far, but if I try to run the code for more than 9 rows: I got this:

Warning

OutputWrong

I have searched for the answer on here I read in some posts that I'm not "calling the function in the right way" but I don't understand what do I need to change also I read that I need to check the permitted ranges for arguments to make sure no arrangement exceeds the permitted values.

Upvotes: 0

Views: 2058

Answers (2)

Justyna MK
Justyna MK

Reputation: 3563

Change Debug.Print i to Debug.Print i & " - " & TempDate and see your Immediate Window. You will notice that for row 11 (31/08/2010 - 31/08/2020) the code is shifting the day from 31st (31st of August) to 30th (30th of November) and then defaults to 28th (28th of February). Once it reaches this stage, it will always take 28th day into account, making it impossible for the loop to finish the calculation (infinite loop).

The result will look like that:

2 - 30/11/2010
3 - 28/02/2011
4 - 28/05/2011
...
39 - 28/02/2020
40 - 28/05/2020
41 - 28/08/2020
42 - 28/11/2020
...
89 - 28/08/2032
90 - 28/11/2032
91 - 28/02/2033
...

I hope it clarifies the issue well enough and it gives you a hint on how to proceed.

Upvotes: 3

Xabier
Xabier

Reputation: 7735

How about the following using DateDiff:

Sub DateTest()
Dim ws As Worksheet: Set ws = ThisWorkbook.Worksheets(1)
Dim IntervalType As String
Dim lLastRow As Long, lRow As Long

IntervalType = "m" ' "m" specifies MONTHS as interval.

lLastRow = ws.UsedRange.Rows.Count

For lRow = 1 To lLastRow
   ' If the number is not greater than zero an infinite loop will happen.
    If ws.Cells(lRow, 3).Value <= 0 Then
        MsgBox "Number needs to be greater than 0", vbCritical
        Exit Sub
    End If

    ws.Cells(lRow, 4).Value = DateDiff(IntervalType, ws.Cells(lRow, 1).Value, ws.Cells(lRow, 2).Value) / ws.Cells(lRow, 3).Value
Next lRow
End Sub

Upvotes: 3

Related Questions