This is machine translation

Translated by Microsoft
Mouseover text to see original. Click the button below to return to the English version of the page.

Note: This page has been translated by MathWorks. Click here to see
To view all translated materials including this page, select Country from the country navigator on the bottom of this page.


Set empty cells to NaN or 0


= MLMissingDataAsNaN(flag)
MLMissingDataAsNaN flag
out = MLMissingDataAsNaN(flag)


= MLMissingDataAsNaN(flag) sets empty cells to NaN or 0. When the Spreadsheet Link™ software is installed, the default is "no", so empty cells are handled as 0s. If you change the value of MLMissingDataAsNaN to "yes", the change remains in effect the next time a Microsoft® Excel® session starts. Use this syntax when working directly in a worksheet.

MLMissingDataAsNaN flag sets empty cells to NaN or 0. Use this syntax in a VBA macro.

out = MLMissingDataAsNaN(flag) lets you catch errors when executing MLMissingDataAsNaN in a VBA macro. If MLMissingDataAsNaN fails, then out is a string containing error code. Otherwise, out is 0.

Input Arguments


Either "yes" or "no".

Specify "yes" to set empty cells to use NaNs. Specify "no" to set empty cells to use 0s.

Default: "no"

Output Arguments


0 if the command succeeded. Otherwise, a string containing error code.


collapse all

Cancel the use of the value NaN for empty cells:



  • A string in an Excel range always forces cell array output and empty cells as NaNs.

See Also

Introduced in R2007a