Excel VBA: Run-time error '438' Object doesn't support this property or method

Question

Please help debug: Run-time error '438' Object doesn't support this property or method

I'm not sure why my the Function ConvertToStdDateFormat(InputRange As Range) is not accepting the range 'ThisRange'

Here's what my input looks like

201301  201401      201301  201401
201302  201402      201302  201402
201303  201403      201303  201403
201304  201404      201304  201404
201305  201405      201305  201405

Below is the code

Sub trythis()
Dim ThisRange As Range
Dim MonthYear_array As Variant
start_date_row = 1
end_date_row = 12

With ActiveSheet
    Set ThisRange = .Range(Cells(start_date_row, 1), Cells(end_date_row, 2))
    MonthYear_array = .Range(Cells(start_date_row, 4), Cells(end_date_row, 5)).Value
End With

Call ConvertToStdDateFormat(ActiveSheet.Range(Cells(start_date_row,1), Cells(end_date_row, 2)))
Call ConvertToStdDateFormat(ActiveSheet.ThisRange)
End Sub


Public Function GetMonthYearFormatted(InputDate)
'InputDate should be in the format "201401" i.e. year(2014)month(01)
    IPString = CStr(InputDate)
    monthval = CInt(Right(IPString, 2))
    yearval = CInt(Left(IPString, 4))
    opDate = DateSerial(yearval, monthval, 1)
    OPFormatDate = Month(opDate) & "-" & Year(opDate)
    GetMonthYearFormatted = OPFormatDate
End Function

Function ConvertToStdDateFormat(InputRange As Range)
    Dim temp_array As Variant
    temp_array = InputRange
    For colsC = 1 To UBound(temp_array, 2)
        For rowsC = 1 To UBound(temp_array, 1)
            temp_array(rowsC, colsC) = GetMonthYearFormatted(temp_array(rowsC, colsC))
        Next rowsC
    Next colsC
    InputRange.Resize(UBound(temp_array, 1), UBound(temp_array, 2)) = temp_array
    ConvertToStdDateFormat = Null
End Function

Solution

Just replace the line

Call ConvertToStdDateFormat(ActiveSheet.ThisRange)

by

Call ConvertToStdDateFormat(ThisRange)

and the code will work (the worksheet where the range is located is stored in the range object itself and can be referenced by ThisRange.Worksheet).

To make debugging easier it may be useful to start all modules with the line Option Explicit. This enforces the explicit declaration of all variables used (i.e. the Dim x as Integer lines).