Why do I receive the error message #VALUE! in the Excel worksheet when returning a cell array using MATLAB Builder for Excel?

I have a function which retrieves data from a database. The data is returned in a cell array format. I have compiled this function using MATLAB Builder for Excel. I am calling the VBA macro which was automatically generated by MATLAB in an Excel array function so that all values of the cell array will be displayed.

If my query returns only one row, the data displays properly, however if my query returns multiple rows, none of the data is displayed and instead all the cells are filled with the error message

 #VALUE!

The data is of the form:

x={[1.232;3.232;8.343],['01-JAN-2006';'05-MAR-2006';'23-FEB-2006']}

Answer 1

[MathWorks Support Team]

This may occur if your database returns results as a nested cell array; Excel can not display nested arrays using the array function syntax.

Some database query functions return data in a 1-by-m cell array, where "m" is the number of columns of data returned by the query. Each cell contains a n-by-1 matrix of values where "n" is the number of rows returned by the query (except in the case of character data). Each cell can contain a matrix of a different data type. MATLAB Builder for Excel translates this data structure into a nested array of Variants in Visual BASIC for Applications (VBA).

Excel worksheets can not display a nested array returned from a VBA function. You will need to either modify the data structure returned by MATLAB to be a regular 2D cell array or matrix, or modify the VBA macro to manipulate the data into a 2D array.

In MATLAB there are several functions which may be useful to transform your nested cell array structure into a 2D cell array. These functions are:

CAT - concatenates two cell arrays or matrices into a single matrix

CELLSTR - converts a matrix of characters in a cell array of strings

MAT2CELL - converts a matrix into a cell array

For more information on these commands execute the appropriate MATLAB command from the list below:

doc cellstr

doc cat

doc mat2cell

As an example, the data above could be transformed into a 2D cell array as follows.

s=size(x{1})

cat(2,mat2cell(x{1},ones(1,s(1)),ones(s(2),1)),cellstr(x{2}))

For more information on using the array syntax in Excel to return a range of values, see the related solution below.