specifying default for optional argument 2

[elizabeth]

If I make an argument optional, how do I specify the default for that argument?<br><br>Example:<br><br>Function PatientAge(Birthdate As Date, Optional EndDate As Date) As Integer<br>' calc patient age in months, if &gt; 23 months old, recalc in years<br>' also use today's date if EndDate not specified<br><br>If AgeInMonths(Birthdate, EndDate) &gt; 23 Then<br>&nbsp;&nbsp;&nbsp;&nbsp;PatientAge = AgeinYears(Birthdate, Date)<br>Else<br>&nbsp;&nbsp;&nbsp;&nbsp;PatientAge = AgeInMonths(Birthdate, EndDate)<br>End If<br><br>End Function

 

[Lightning]

What about<br>&nbsp;If IsNull(EndDate) then EndDate = Date()<br><br>

 

[samara]

I think you can use IsMissing() function,<br><br>&nbsp;IF IsMissing(EndDate) Then <br>&nbsp;&nbsp;&nbsp;&quot;Put what you want to do here&quot;<br><br>&nbsp;End If<br><br> <p>Mohamed Aly<br><a href=mailto:samara_79@hotmail.com>samara_79@hotmail.com</a><br><a href= > </a><br>

 

[rhicks]

If you are wanting to specify the default in the declaration statement use this as an example:<br><br>Function PatientAge(Birthdate As Date, Optional EndDate As Date = Date()) As Integer<br><br>This example will insert the current date as the argument if the arugument is not specified.<br><br>HTH<br>RDH<br><br> <p>Ricky Hicks<br><a href=mailto: rdhicks@mindspring.com> rdhicks@mindspring.com</a><br><a href= > </a><br>

 

[elizabeth]

Ricky, when I try to type:<br><i>Function PatientAge(Birthdate As Date, Optional EndDate As Date = Date<font color=red>()</font>) As Integer</i><br>the VB Editor &quot;fixes&quot; it to become <br><i>Function PatientAge(Birthdate As Date, Optional EndDate As Date = Date) As Integer</i><br>Then when I try to compile this function I get:<br><b>Compile Error: Constant expression required</b><br><br>Lightning & Samara,<br>When I try your solutions, my answer is off by -100 years. It works if I change the birthdate to 100 years earlier, but I wonder if this has to do with some year2000 related thing...<br><br>They still work OK, BTW if I include date() as the EndDate when I call the function. I'm just trying to get fancy!<br><br>

 

[harryrich]

The IsMissing function is designed for parameter of type variant, and returns true if a varient parameter is missing.&nbsp;&nbsp;Unfortunately, givent the type structure it is syntactically incorrect to pass it a parameter of some other type, even though these parameter types are never missing.&nbsp;&nbsp;They will always default to something of the correct type even if omitted from the call.<br><br>Declare your parameter as Variant instead of Data and you code should work correctly.<br><br>Of course, you may wantto be a little smarter than the implementor of IsMissing and check the parameter type at run time using the Vartype function.

 

[samara]

Guys,<br>i returned to my books and found out that any optional argument has to be of type Variant...<br><br>is that RIGHT ??? <p>Mohamed Aly<br><a href=mailto:samara_79@hotmail.com>samara_79@hotmail.com</a><br><a href= > </a><br>

 

[elizabeth]

Thank you! FYI, here's my now-working functions:<br><br>Function AgeInMonths(Birthdate As Date, Optional EndDate As Variant) As Integer<br>Dim NewEndDate As Date<br><br>If IsMissing(EndDate) Then<br>&nbsp;&nbsp;&nbsp;&nbsp;NewEndDate = Date<br>Else<br>&nbsp;&nbsp;&nbsp;&nbsp;NewEndDate = CDate(EndDate)<br>End If<br>AgeInMonths = DateDiff(&quot;m&quot;, Birthdate, NewEndDate) + _<br>(NewEndDate &lt; DateSerial(Year(NewEndDate), Month(Birthdate), Day(Birthdate)))<br>End Function<br><br>Function AgeinYears(Birthdate As Date, Optional EndDate As Variant) As Integer<br>Dim NewEndDate As Date<br><br>If IsMissing(EndDate) Then<br>&nbsp;&nbsp;&nbsp;&nbsp;NewEndDate = Date<br>Else<br>&nbsp;&nbsp;&nbsp;&nbsp;NewEndDate = CDate(EndDate)<br>End If<br>AgeinYears = DateDiff(&quot;yyyy&quot;, Birthdate, NewEndDate) + _<br>(NewEndDate &lt; DateSerial(Year(NewEndDate), Month(Birthdate), Day(Birthdate)))<br>End Function<br><br>Public Function PatientAge(Birthdate As Date, Optional EndDate As Variant) As String<br>' calc patient age in months, if &gt; 24 months old, recalc in years<br>Dim NewEndDate As Date<br><br>If AgeInMonths(Birthdate) &gt; 23 Then<br>&nbsp;&nbsp;&nbsp;&nbsp;PatientAge = AgeinYears(Birthdate) & &quot; years&quot;<br>Else<br>&nbsp;&nbsp;&nbsp;&nbsp;PatientAge = AgeInMonths(Birthdate) & &quot; months&quot;<br>End If<br><br>End Function