默认范围属性产生意外结果 [英] Default Range property produces unexpected results
问题描述
作为回答的后续问题,我发布了,如果我们没有指定属性,它也可以工作:
Debug.Print WorksheetFunction.Match(Range(C3),Range(A1:A14),0)
所以如果 .Value
不起作用,这是默认属性的范围
对象 - 为什么在上面的例子中工作?
这可能是一个错误吗?还是有一定程度的评估发生抵消这一点?
我不认为这是一个错误。如果您运行下面的宏:
Debug.Print Range(C3),Range(C3)。 (C3)Value2
第二个将返回相同的结果。
3/2/2015 3/2/2015 42065
哪个确认默认属性为值
for 范围对象
。
值得注意的是,如果您明确地使用 Value
,它将会同样适用。
Debug.Print WorksheetFunction.Match(Range(C3)。Value,Range(A1:A14)。Value,0)
所以我认为这是问题的另一个表现,一旦描述 here 。
如果您没有明确定义 Value
属性,Excel是足够聪明的,假定您匹配值
。但是,如果你明确地暗示一个属性,但是离开另一个属性,那么它将不起作用。
As a follow-up question to an answer I posted here I would like to know why the following doesn't error, is this a possible bug in VBA?
Take the following data:
If we use the following VBA code, we will receive an error because we need to use the numeric value of the date to match:
'//Produces error
Debug.Print WorksheetFunction.Match(Range("C3").Value, Range("A1:A14"), 0)
and so either of these statements will work:
'// Cast to Long
Debug.Print WorksheetFunction.Match(CLng(Range("C3").Value), Range("A1:A14"), 0)
'// Access .Value2 property directly
Debug.Print WorksheetFunction.Match(Range("C3").Value2, Range("A1:A14"), 0)
However as pointed out by Jean-François Corbett, if we don't specify a property it also works:
Debug.Print WorksheetFunction.Match(Range("C3"), Range("A1:A14"), 0)
So if .Value
doesn't work, and this is the default property of the Range
object - why does it work in the above example?
Could this be a bug? Or is there some level of evaluation taking place that counteracts this?
I don't think it is a bug. If you run below macro:
Debug.Print Range("C3"), Range("C3").Value, Range("C3").Value2
1st two will return returns identical results.
3/2/2015 3/2/2015 42065
Which confirms the default property as Value
for Range Object
.
It is worth noting though that if you explicitly use Value
all throughout, it will work as well.
Debug.Print WorksheetFunction.Match(Range("C3").Value, Range("A1:A14").Value, 0)
So I think that it is another manifestation of the issue once described here.
If you don't explicitly define Value
property for both, Excel is smart enough to assume that you're matching Values
. However, if you explicitly imply one property but leave Excel guessing for the other, it will not work.
这篇关于默认范围属性产生意外结果的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!