matlabexcel-formula

Avoid "implicit intersection" symbol addition to Excel formulas when using MATLAB Actxserver

I am using MATLAB Actxserver to generate an Excel spreadsheet. A portion of this code is used to generate a formula for determining if any values in a list are different. Below is an example MATLAB code

c = cell(1,4);
c{4} = "=IF(AND(EXACT(A1:C1,A1)),0,1)"; 
writecell(c,'test2.xlsx','UseExcel',true);

However, when I open the Excel file, it replaces the function with

=IF(AND(EXACT(@A1:C1,A1)),0,1)

The addition of the "@" symbol breaks the function and causes the cell to display "#VALUE!". If I remove the "@" symbol in Excel, the function works as expected.

How do I stop Excel from automatically adding the "implicit intersection" symbol?

I tried using a find and replace function within MATLAB as shown below, and it does not solve the issue. The "@" re-appears.

Excel = actxserver('excel.application');
WB = Excel.Workbooks.Open(oufFile,0,false);
WS = WB.Worksheets.Item(1);
WS.Range('A1:Z100').Replace("@","")
WB.Save();
WB.Close();
Excel.Quit();
Solution

  • You can choose 'UseExcel',false but then you have a different issue with the formula not evaluating unless you manually edit the cell to confirm its contents.

    It looks like this is primarily an issue in setting the Value or Formula properties of a range, which MATLAB is likely doing under the hood via the COM interface with 'UseExcel',true.

    For whatever reason, Excel is automatically adding the @ symbol for "implicit intersection" because it's trying to "cleverly" handle the fact that EXACT can return an array output.

    To get around this you might have to set the .Formula2 data instead. I'll leave the details of the COM interface calls to you, but broadly you've got something like this to get the sheet reference to an open workbook (would work on a closed workbook with different calls):

    Excel = actxGetRunningServer('Excel.Application');
Workbook = Excel.Workbooks.Item(1);
Sheet = Workbook.Sheets.Item(1);

    Then for some cell c:

    Sheet.Range('A1:D1').Formula2 = c;

    You could also write some functions to generate the 'A1:D1' string for you based on the upper-left cell and the size of c,