Custom Excel VBA Function with return type as Date

Question

I have a workbook with multiple sheets. Except for the Summary Sheet, all other sheets have a dynamic range of consecutive cells (within a column) that stores some Date formatted values. I need to select that range dynamically and choose the Largest Date Value. The Function should return that value.

Here's what I was trying:

Function GetMSDate(Counter As Integer, DesignCtrs() As String, NumOfVendors() As Long, 
          MSDesc() As String, PlanOrActual As String) As Date
    GetMSDate = Application.Max(ThisWorkbook.Worksheets(Counter + 1).Range("A:A")
            .Find(PlanOrActual, SearchOrder:=xlByColumns, searchDirection:=xlNext)
            .EntireRow
            .Find(MSDesc(1), SearchOrder:=xlByColumns, searchDirection:=xlNext)
            .Offset(1, 0).Address & ":" & _
            ThisWorkbook.Worksheets(Counter + 1).Range("A:A")
            .Find(PlanOrActual, SearchOrder:=xlByColumns, searchDirection:=xlNext)
            .EntireRow
            .Find(MSDesc(1), SearchOrder:=xlByColumns, searchDirection:=xlNext)
            .Offset(NumOfVendors(Counter) + 1, 0).Address)
    MsgBox GetMSDate
End Function

My intent was that I would define the dynamically constructed Range for the Application.Max() argument. In doing so, finding the range anchor, I choose the correct sheet (Counter+1 allows that correctly); then find a specific "predefined" string in the first column; select the entire row of the found cell and find within that range another predefined string; the range is then defined from the cell below that anchor cell to an offset defined by: NumOfVendors(Counter)+1

In running the above, it gives me:

Type Mismatch error at the "GetMSDate = " statement.

Dan Metheus Dan Metheus · Accepted Answer · 2013-03-08T18:23:53

To debug, assign your value to a variant variable and then output the typename() of that variable after the assignment. It should tell you why it is not accepting date.

Function GetMSDate(Counter As Integer, DesignCtrs() As String, NumOfVendors() As Long, MSDesc() As String, PlanOrActual As String) As Date
    Dim v as Variant

    v = Application.Max(ThisWorkbook.Worksheets(Counter +
        1).Range("A:A").Find(PlanOrActual, SearchOrder:=xlByColumns,
        searchDirection:=xlNext).EntireRow.Find(MSDesc(1), SearchOrder:=xlByColumns,
        searchDirection:=xlNext).Offset(1, 0).Address & ":" & _
    ThisWorkbook.Worksheets(Counter + 1).Range("A:A").Find(PlanOrActual,
        SearchOrder:=xlByColumns, searchDirection:=xlNext).EntireRow.Find(MSDesc(1),
        SearchOrder:=xlByColumns, searchDirection:=xlNext).Offset(NumOfVendors(Counter)
        + 1, 0).Address)
    debug.print v
    debug.print TypeName(v)
    stop
    MsgBox GetMSDate
End Function

Custom Excel VBA Function with return type as Date

1 Answers