Loop Not Working in UDF Function Excel VBA?

Question

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

When you declare variables, you need to declare the type of each one so: "Dim varDateFrom As Variant, varDateTo As Variant, varDateDuration As Variant" Dim x,y,z As Variant only sets 'z' to variant type. I don't think this is the problem, but it can't help. — OpiesDad
– OpiesDad, Commented Apr 8, 2015 at 19:32
@OpiesDad thank you for the information, really this is the first time I got it, I change my variable declaration as you suggested, but unfortunately still the same problem. — Mohammad Awni Ali
– Mohammad Awni Ali, Commented Apr 8, 2015 at 19:34

OpiesDad · Accepted Answer · 2015-04-08 19:46:16Z

1

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

answered Apr 8, 2015 at 19:46

OpiesDad

3,4352 gold badges19 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Mohammad Awni Ali Over a year ago

thank you I tried it and didn't work also, but I tried some strange issue, the code is working on excel 2007, but not on excel 2010!

OpiesDad Over a year ago

What happens when you step through the code? Does it cause an error anywhere?

Mohammad Awni Ali Over a year ago

I tried, it will enter the loop only once, now its working very will on office 2007, but will enter the loop only once on office 2010! wonder why!

OpiesDad Over a year ago

What happens when it enters the loop? If you step through the code, does it get to the "Next i" line, or does something else happen before then?

OpiesDad Over a year ago

I just noticed another area where you have the "CInt(CDate...." combination. You need to remove "CInt" from all of these cases....I think you are getting the same overflow exception I mentioned in my answer.

Collectives™ on Stack Overflow

Loop Not Working in UDF Function Excel VBA?

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related