CODEWITHSUNDEEP
excelvbafunctionloops
Mohammad Awni Ali
Mohammad Awni Ali

Reputation: 115

Loop Not Working in UDF Function Excel VBA?

I am having a problem running the code below, the code is to calculate the difference between two array of dates, values is separted by line carriage (CHR(10)), for example in cell A1 I have the following dates

A1
12/12/2012
11/12/2021
7/8/2015
9/4/2014

B1
12/12/2012
11/12/2021
7/8/2015
9/4/2014

C1
2D
1D
4D
10D

in D1 I call the function from which is inside module 1 as following

=calcSumDurations(A1,B1,C1)

it will always return 0

and when I try to trace the code, it will enter the for loop only once, even than intmax = 3, or 4 or 40 in some cases, I tried while, for, foreach, none working.

    Function calcSumDurations(dateFrom, dateTo, dateDuration As String)
    Dim intmax, intSum, i, intError As Integer
    Dim varDateFrom, varDateTo, varDateDuration As Variant

    intSum = 0
    intmax = -1
    i = 0
    intError = 0
    varDateFrom = Split(dateFrom, Chr(10))
    varDateTo = Split(dateTo, Chr(10))
    varDateDuration = Split(dateDuration, Chr(10))
    intmax = UBound(varDateFrom)


    If UBound(varDateFrom) = UBound(varDateTo) Then ' both are same lenght
        If intmax >= 0 Then ' more than one line
            For i = 0 To intmax
            'While i < intmax
                MsgBox (i)
                If CInt(CDate(varDateTo(i))) >= CDate(varDateFrom(i)) Then                         'check dates are more
            If testDate(CStr(varDateTo(i))) And                 testDate(CStr(varDateFrom(i))) Then
                        intDuration = Abs(CInt(CDate(varDateTo(i)) - CDate(varDateFrom(i)))) + 1
                        intSum = intSum + intDuration
                        'strRes = strRes & CStr(intDuration) & Chr(10)
                    Else
                        intError = 1
                        'Exit For
                    End If
                Else
                    intError = 2
                End If
            Next i
        End If
    Else
        intError = 3
    End If
    calcSumDurations = intSum
            End Function

Upvotes: 0

Views: 254

Answers (1)

OpiesDad
OpiesDad

Reputation: 3435

The problem is in this line of code:

If CInt(CDate(varDateTo(i))) >= CDate(varDateFrom(i)) Then

an integer is too small to hold the date value and is causing an overflow exception. I'm not sure why you're trying to convert it into an integer anyways as the comparison won't work if you do that.

Try this:

If CDate(varDateTo(i)) >= CDate(varDateFrom(i)) Then

It'll at least start getting through the loop.

I'd also define what you want the function to return

Function calcSumDurations(dateFrom As String, dateTo As String, dateDuration As String) As Long

Upvotes: 1

Related Questions