Application.Match给出类型不匹配 [英] Application.Match gives type mismatch
问题描述
我试图使用 Application.Match
,但是它返回一个类型不匹配错误:13
错误。为什么?
Dim mySrs as Series
Dim ws as Worksheet
set ws Activesheet
For每个mySrs在ActiveChart.SeriesCollection
tempvar = mySrs.Name
y = Application.Match(tempvar,ws.Range(P37:P71),0)
MsgBox y
很可能没有找到匹配项。在这种情况下, Application.Match
返回Excel错误代码,即变量/错误,值为错误2042
(这对应于在Excel中获取#N / A
)。
这样的错误值不能隐式强制到一个String(这是 MsgBox
期望的),因此你得到类型不匹配。请注意,使用 WorksheetFunction.Match $可以调用相同的
Match
函数。 C $ C>。唯一的区别是如何处理错误:
-
使用
WorksheetFunction
,错误被视为VBA错误,可以使用On Error
语法来捕获。 -
使用
应用程序
,他们返回一个包含在Variant中的Excel错误代码。您可以使用IsError
来查看返回的变量是否是错误类型变体。
I am trying to use Application.Match
however it is returning a type mismatch error:13
error. Why?
Dim mySrs as Series
Dim ws as Worksheet
set ws Activesheet
For Each mySrs in ActiveChart.SeriesCollection
tempvar = mySrs.Name
y = Application.Match(tempvar, ws.Range("P37:P71"), 0)
MsgBox y
In all likelihood, no match is found. In such a case, Application.Match
returns an Excel error code i.e. a Variant/Error whose value is Error 2042
(this corresponds to getting #N/A
in Excel).
Such an Error value cannot be implicitly coerced to a String (which is what MsgBox
expects) and thus you get the type mismatch.
Note that the same Match
function can be called using WorksheetFunction.Match
. The only difference is how errors are to be handled:
With
WorksheetFunction
, errors are treated as VBA errors, trappable using theOn Error
syntax.With
Application
, they return an Excel error code wrapped in a Variant. You can useIsError
to see if the returned variable is an Error type variant.
这篇关于Application.Match给出类型不匹配的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!