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

Question

请帮忙调试:运行时错误“438”对象不支持此属性或方法

我不确定为什么我的 Function ConvertToStdDateFormat(InputRange As Range) 不接受范围“ThisRange”

这是我的输入内容

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

下面是代码

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

Answer 1

只需更换线路即可

Call ConvertToStdDateFormat(ActiveSheet.ThisRange)

由

Call ConvertToStdDateFormat(ThisRange)

并且代码将起作用(范围所在的工作表存储在范围对象本身中，并且可以通过 ThisRange.Worksheet 引用)。

为了使调试更容易，使用 Option Explicit 行启动所有模块可能会很有用。这强制使用所有变量的显式声明(即Dim x as Integer行)。