无法访问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屋！