Excel VBA：运行时错误“438”对象不支持此属性或方法
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/25572021/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Excel VBA: Run-time error '438' Object doesn't support this property or method
提问by WonderWoman
Please help debug: Run-time error '438' Object doesn't support this property or method
请帮助调试：运行时错误“438”对象不支持此属性或方法
I'm not sure why my the Function ConvertToStdDateFormat(InputRange As Range) is not accepting the range 'ThisRange'
我不确定为什么我的函数 ConvertToStdDateFormat(InputRange As 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
回答by DerVeganer
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).
并且代码将起作用（范围所在的工作表存储在范围对象本身中，可以通过 引用ThisRange.Worksheet）。
To make debugging easier it may be useful to start all modules with the line Option Explicit. This enforces the explicitdeclaration of all variables used (i.e. the Dim x as Integerlines).
为了使调试更容易，以行开头所有模块可能很有用Option Explicit。这强制显式声明所有使用的变量（即Dim x as Integer行）。
