ARRAY of Dates in VBA

[akasmia]

My array of dates contains blanks or zeros, pending on the user. I have only 4 dates DATE1, DATE2, DATE3 and DATE4 to sort and obtain the smallest date. Example:

DATE1: 12/3/02
DATE2: blank
DATE3: 1/12/03
DATE4: 4/14/04

I WOULD LIKE TO SORT THE ARRAY AND PICK 12/3/02. I AM RUNNING INTO SOME DIFFICULTY. I HAVE MANAGED TO WRITE A LONG ROUTINE WITH MANY "IF... END IF" STATEMENTS. IS THERE AND EASIER WAY IN MICROSOFT ACCESS VBA ?.

HELP APPRECIATED!.

 

[ttmac]

Paste the following function into a new Excel module and save.

Function Date_Sorter(Date1 As Date, Date2 As Date, Date3 As Date, Date4 As Date)

Dim myarray()
Dim temp As Date
Dim x As Integer
Dim y As Integer

myarray = Array(Date1, Date2, Date3, Date4)

For x = LBound(myarray) To UBound(myarray)
For y = (x + 1) To UBound(myarray)
If myarray(x) > myarray Then
temp = myarray
myarray = myarray(x)
myarray(x) = temp
End If
Next y
Next x
For x = LBound(myarray) To UBound(myarray)
If Not (IsNull(myarray(x)) Or IsEmpty(myarray(x)) Or myarray(x) = "00:00:00&quot Then
Date_Sorter = DateValue(myarray(x))
Exit For
End If
Next x

End Function

Usage =Date_Sorter(date1 or cellreference,date2 or cellreference,date3 or cellreference,date4 or cellreference)

eg.

=Date_Sorter(a1,b1,c1,d1)
All input and output cells should be formated as dates.

Tom

 

[akasmia]

Thanks TOM that was great!. After posting my question I was able to write a small function called MAX(DATES...) which seems to have solved the problem. Of cource now I have your function just in case. There is also s small function called IsNothing in John Viedcas CD which seems to work for several variable types.

Appreciate it!

AK