Main Content

MLMissingDataAsNaN

Set empty cells to NaN or 0

Description

example

= 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.

Examples

collapse all

Cancel the use of the value NaN for empty cells.

= MLMissingDataAsNaN("no")

Input Arguments

collapse all

Empty cell indicator, specified as "no" or "yes".

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

Output Arguments

collapse all

Success indicator, returned as 0 if the command succeeded. Otherwise, a string containing error code.

Tips

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

  • To work with VBA code in Excel with Spreadsheet Link, you must enable Spreadsheet Link as a reference in the Microsoft Visual Basic® Editor. For details, see Installation.

Version History

Introduced in R2007a

See Also