Matlab COM interface to Excel: Get range address without dollar signs

Question

In VBA, range.address(0,0) or range,address.(false,false) returns an A1 address without dollar signs ('relative referencing' in Excel VBA parlance).

Experimenting with sht as a worksheet object, I get the following:

K>> sht.Range('B2:C3').Address
    ans = $B$2:$C$3
K>> sht.Range('B2:C3').Address(false,false)
    ans = ''
K>> sht.Range('B2:C3').Address(0,0)
    Subscript indices must either be real positive integers or logicals.
K>> get( sht.Range('B2:C3'), 'Address' )
    ans = $B$2:$C$3
K>> get( sht.Range('B2:C3'), 'Address(0,0)' )
    Error using Interface.Microsoft_Excel_14.0_Object_Library.Range/get
K>> invoke( sht.Range('B2:C3') , 'Address', 0, 0 )
    Error using Interface.Microsoft_Excel_14.0_Object_Library.Range/invoke
K>> invoke( sht.Range('B2:C3') , 'Address', false, false )
    Error using Interface.Microsoft_Excel_14.0_Object_Library.Range/invoke
K>> methods( sht.Range('B2:C3') ) % Address is not a method
    Methods for class Interface.Microsoft_Excel_14.0_Object_Library.Range:
    Activate                ClearHyperlinks         <...snip...>
    AddComment              ClearNotes              <...snip...>
    <...snip...>
K>> get( sht.Range('B2:C3') )
            Application: [1x1 Interface.Microsoft_Excel_14.0_Object_Library._Application]
                Creator: 'xlCreatorCode'
                 Parent: [1x1 Interface.Microsoft_Excel_14.0_Object_Library._Worksheet]
              AddIndent: 0
                Address: '$B$2:$C$3'
           AddressLocal: '$B$2:$C$3'
                  Areas: [1x1 Interface.Microsoft_Excel_14.0_Object_Library.Areas]

                    <...snip...>

It seems that in VBA, the Address property returns an object with an implied operator(), so it can be given arguments (like false or 0) to specify local referencing. From the get results, however, the COM interface treats the Address property as a simple property with a single literal string value, which is limiting.

To avoid this limitation, I don't suppose there is a way to evaluate a VBA expression (such as what one might type into the Immediate Window of the VBA Editor) from Matlab? As can be seen above, invoke doesn't do it.

I can work around this at the tactical level by writing expressions to remove the dollar signs from the address, but I want a general way to access evalation of VBA from within Matlab. Also writing tactical formatting functions makes for noisier, more voluminous code.

Answer 1

This answer is based on the syntax to the answer to another question

% Open COM interface, create new workbook, access worksheet

excel = actxserver('Excel.Application');
excel.Visible=1;
wbks = excel.Workbooks;
wbks.Add
sht = wbks.Item(1).Sheets.Item(1)

% Syntax for range address using absolute reference
sht.Range('A3.B4').Address

% Syntax for range address using relative reference
get( sht.Range('A3.B4'), 'Address', 0, 0 )

% Shut down Excel workbook and COM app object
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