Do Whie Loop to Add Days to Date

[BrotherDude]

Hello all,

I am trying to create a function that would allow me to select a date cell and add a certain amount of days to it until it is larger than the current date. I keep crashing excle with the function below any suggestions? Seems like I am declaring the variables incorrectly.

Public Function NextDueDate(StartDate As Range, NumberDays As Integer) As Date


On Error GoTo Err_Handler


Dim DueDate As Date
Dim TodaysDate As Date
TodaysDate = Date


If IsNull(StartDate) Or IsNull(NumberDays) < 0 Then
NextDueDate = Null
GoTo Exit_Proc
End If


Do Until DueDate > TodaysDate
DueDate = StartDate + NumberDays
Loop


NextDueDate = DueDate




Exit_Proc:
On Error Resume Next
Exit Function


Err_Handler:
MsgBox Err.Number & " " & Err.Description, vbCritical, _
"NextDueDate()"
NextDueDate = Null
Resume Exit_Proc




End Function


Any help is much appreciated

-Jeff

 

[Joe4]

A few things I see:

Public Function NextDueDate(StartDate As Range, NumberDays As Integer) As Date

If you are trying to pass in a date (which I think you are), you need to declare StartDate as Date, Number,or Variant, not "Range". I am guessing that you are trying to do is pass in the date from range variable, which is fine, but you wouldn't declare that as "Range", as you actually want the date in that range.

If IsNull(StartDate) Or IsNull(NumberDays) < 0 Then
The "<0" part is unnecessary. IsNull returns boolean values (True/False). See: http://www.techonthenet.com/excel/formulas/isnull.php

I think it is the first issue which is really causing your issues. The other one is really just informational.

 

[BrotherDude]

Thanks for the reply. So I had already tried using Variant and Date but this crashed my excel. I cant find any info online regarding user defined functions and date inputs. Also do I even need to declare the function type? I have tried switching the function to Variant as well since that's usually more flexible.

Any idea how I can track variables in VBA functions/Debug or stop excel from crashing? I was going to try any switch the function to a sub with an input box to define the startdate cell so I could track the errors.

 

[Joe4]

OK, I tested out your code and see the problem. Your calculation in your loop never changes, since your StartDate and NumberDays never changes. So you are getting caught in an endless loop, as the value in DueDate is always the same (never changes as you iterate through your loop).

Try changing this part:

[COLOR=#333333]Do Until DueDate > TodaysDate[/COLOR]
[COLOR=#333333]    DueDate = StartDate + NumberDays[/COLOR]
[COLOR=#333333]Loop[/COLOR]

to

DueDate = StartDate

Do Until DueDate > TodaysDate
    DueDate = DueDate + NumberDays
Loop

 

[BrotherDude]

Ahhh very nice, I really appreciate the help.

That was quick, if you don't mind what steps do you take to test?

Is the best way to dump code into a Sub module, set values to variables and run code while variables are in watch window?

 

[Joe4]

Is the best way to dump code into a Sub module, set values to variables and run code while variables are in watch window?

That's pretty close to what I did. I changed it to a Sub Procedure, and then called it from another Sub Procedure passing in the variables.
I used F8 to step through the code one line at a time, but you can accomplish the same thing using Watch Windows too.