无法访问Range对象的Range方法; COM的局限性? [英] Can't access Range method of Range object; COM limitations?
问题描述
在Excel VBA编辑器的立即窗口"中,我可以执行以下操作:
In the Excel VBA Editor's Immediate Window, I can do the following:
?ActiveSheet.Range("C3:D4").Range("C3:D4").Address
$E$5:$F$6
根据一些简单的测试,这在Matlab中似乎没有相同的反应.这是为测试设置COM接口的代码:
According to some simple tests, this doesn't seem to respond the same in Matlab. Here is the code to set up the COM interface for the tests:
excel = actxserver('Excel.Application');
excel.Visible=1;
wbks = excel.Workbooks;
wbks.Add
sht = wbks.Item(1).Sheets.Item(1)
%
% Run some range tests
%
try
excel.DisplayAlerts = 0; % Forgo save prompt on Close
end; try
wbk.Close
end; try
excel.Quit % Excel process still present
end; try
delete(excel) % Excel process disappears
end % try
现在,以sht
为工作表对象,我得到了以下错误:
Now, with sht
being a Worksheet object, I get the following error instead:
K>> o=sht.Range('C3:D4')
o = Interface.Microsoft_Excel_14.0_Object_Library.Range
K>> o.Range('C3:D4').Address
Cannot find an exact (case-sensitive) match for 'Range'
The closest match is: range in C:\Program Files\MATLAB\Single_R2015b\toolbox\stats\stats\range.m
Did you mean: K>> o.range('C3:D4').Address
这是错误的功能,因为range
的大写r
是内部Matab函数.因此,我按了Ctrl-C组合键(否则,它将抱怨参数不兼容).
This is the wrong function, since range
with an uncapitalized r
is the internal Matab function. Hence, I pressed Ctrl-C to break out (otherwise, it complains about the incompatible argument).
要解决为什么无法识别Range
的问题,请检查is是方法还是属性.通过COM访问时,Range
是方法而不是属性:
To troubleshoot why Range
is not recognized, check whether is is a method or property. When accessed via COM, Range
is a method rather than a property:
K>> methods(o)
Methods for class Interface.Microsoft_Excel_14.0_Object_Library.Range:
<...snip...>
PrintPreview Table
Range TextToColumns
RemoveDuplicates UnMerge
<...snip...>
这进一步表明Range
不是属性(即使它在VBA中):
This further shows that Range
is not a property (even though it is in VBA):
K>> get(o)
<...snip...>
QueryTable: 'Error: Object returned error code: 0x800A03EC'
Resize: [1x1 Interface.Microsoft_Excel_14.0_Object_Library.Range]
Row: 3
<...snip...>
由于属性是按字母顺序列出的,因此如果将Range
识别为属性,它将出现在QueryTable之后.但是,上面的结果中没有列出它.
Since properties are listed alphabetically, Range
would show up after QueryTable if it was recognized as a property. However, it isn't listed in the above results.
作为另一种诊断步骤,我尝试使用点符号(o.Range
)访问Range
.不幸的是,Matlab似乎有自己的本机函数range
,它与Excel Range
无关.
As an alternative diagnostic step, I tried accessing Range
using the dot notation (o.Range
). Unfortunately, Matlab seems to got for its own native function range
, which has nothing to do with an Excel Range
.
因此,在完成所有诊断工作之后......
So after all that diagnostic work....
问题
对于给定的Range
对象,如何访问Range
方法(由COM识别)或 Range
属性(如VBA中所述)文档)?
For a given Range
object, how does one access the Range
method (as recognized by COM) or the Range
property (as it is described in the VBA documentation)?
AFTERNOTE
通过COM接口访问Range属性和方法似乎存在许多差异.在即时窗口"中,可以使用Offset
属性(将其描述为属性):
There seem to be many discrepancies with in accessing Range properties and methods via the COM interface. In the Immediate Window, one can use the Offset
property (it is desribed as a property):
? ActiveSheet.Range("C3:D4").Offset(2,3).Address
$F$5:$G$6
在COM上,即使get(o)
显示Offset
是可以返回范围的有效属性,也没什么大作用.
Over COM, not so much, even though get(o)
shows Offset
to be a valid property that returns a range:
K>> o=sht.Range('C3:D4')
o = Interface.Microsoft_Excel_14.0_Object_Library.Range
K>> o=o.Offset(2,2)
Index exceeds matrix dimensions.
推荐答案
以下是我通过反复试验得出的解决方案,并在Matlab技术支持的一些帮助下进行了完善:
Here is a solution I've reached by trial and error, refined with some help with Matlab technical support:
调用get
作为方法,然后提供所需的属性名称.它适用于属性Offset
.
Invoke get
as a method, then supply the desired property name. It works for property Offset
.
K>> o = sht.Range('C3:D4')
o = Interface.Microsoft_Excel_14.0_Object_Library.Range
K>> o.get('Offset',2,3).Address
ans = $F$5:$G$6
它也适用于Range
.
K>> o = sht.Range('C3:E5')
o = Interface.Microsoft_Excel_14.0_Object_Library.Range
K>> o.get('Range','B2:C3').Address
ans = $D$4:$E$5
奇怪的是,Range
是COM的一种方法(但根据Excel VBA文档是一种属性).尽管通过COM访问时被视为一种方法,但仍需要使用get
而不是invoke
来调用它.后者会产生错误:
The strange thing is that Range
is a method according to COM (but a property according to the Excel VBA documentation). Despite being seen as a method when accessed via COM, it needs to be invoked using get
rather than invoke
. The latter yields an error:
K>> o.invoke('Range','B2:C3').Address
Error using Interface.Microsoft_Excel_14.0_Object_Library.Range/invoke.
Cannot find an exact (case-sensitive) match for 'Range'.
The closest match is: range in C:\Program Files\MATLAB\Single_R2015b\toolbox\stats\stats\range.m
这篇关于无法访问Range对象的Range方法; COM的局限性?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!