Search code examples
excelvbaexcel-2007

Find highest date from an array in excel vba

i am trying to find out the maximum value from the array ---> FinishDateArray()
However, the MaxDate value is coming out to be 0 each time. Any idea what could be wrong in this? The array contains dates and I wish to find highest of them.

The below for loop shows that the array does contain all values. But running the inbuilt Max command doesnt give the maximum date. Please help.

Below is the code:

For i = 0 To UBound(FinishDateArray)  
    MsgBox i & " Date: " & FinishDateArray(i)  
Next i  
MaxDate = WorksheetFunction.Max(FinishDateArray)

MsgBox "Max Date: " & MaxDate
Solution

  • I also had the same problem. So I searched the web for the answer, but I haven't found it yet. Maybe it's MS's mistake, I think. Instead, I figured out a solution. Here it is.

    ReDim CLngArray(UBound(FinishDateArray))

For i = 0 To UBound(FinishDateArray)  
CLngArray(i) = CLng(FinishDateArray(i))  
Next i  
MaxDate = CDate(WorksheetFunction.Max(CLngArray))
MsgBox "Max Date: " & MaxDate