|
|
dear all, i'd like to return a xloper with type 'xltypeMulti'. Lots of
elements are just zeros and I'd like to make excel show them as empty
cell but behavior like double zero. Setting element as xltypeNil
doesnot work because excel will show 0. xltypeMissing does not work as
excel shows "#Value!". xltypeError(xlerrNA) behaves correctly but
excel will show "#NA". Any tricks/tips for a clean spreadsheet?
Thanks, Louis
|
|
louis wrote:
[Quoted Text] > dear all, i'd like to return a xloper with type 'xltypeMulti'. Lots of
> elements are just zeros and I'd like to make excel show them as empty
> cell but behavior like double zero. Setting element as xltypeNil doesnot
> work because excel will show 0. xltypeMissing does not work as excel
> shows "#Value!". xltypeError(xlerrNA) behaves correctly but excel will
> show "#NA". Any tricks/tips for a clean spreadsheet? Thanks, Louis
I use an empty (length 0) string for this purpose. I have been unable to
find a way of returning an empty cell in an xltypeMulti array, and the
empty string is as close as I can get. At least the difference is not
visible to the user :-)
--
Jesper Lund
|
|
Hi Jesper
A cell that contains a formula is not, by definition, empty. Therefore
Excel has to evaluate the contained formula the returns the xltypeNil to
something, and defaults to numeric zero. This is of course true for cells
that are part of an array formula as well as single-cell formulae.
If you want to clear a cell's contents using the C API you can use xlSet
with xltypeNil, but you can only do this from command functions (macros).
Worksheet formulae cannot call this function as they are not permitted to
alter the values of cells in a workbook (other than the celling cell(s) via
the returned data).
Regards
Steve Dalton
"Jesper Lund" <usenet[ at ]jesperlund.com> wrote in message
news:eABIPLyKJHA.5904[ at ]TK2MSFTNGP02.phx.gbl...
[Quoted Text] > louis wrote:
>
>> dear all, i'd like to return a xloper with type 'xltypeMulti'. Lots of
>> elements are just zeros and I'd like to make excel show them as empty
>> cell but behavior like double zero. Setting element as xltypeNil doesnot
>> work because excel will show 0. xltypeMissing does not work as excel
>> shows "#Value!". xltypeError(xlerrNA) behaves correctly but excel will
>> show "#NA". Any tricks/tips for a clean spreadsheet? Thanks, Louis
>
> I use an empty (length 0) string for this purpose. I have been unable to
> find a way of returning an empty cell in an xltypeMulti array, and the
> empty string is as close as I can get. At least the difference is not
> visible to the user :-)
>
> --
> Jesper Lund
|
|
Somewhat of a hack, but still an option: You can return actual zeros instead
of empty cells, then on the sheet users can add a column that shows an empty
string on zero, and hide the actual result column. It's even better in some
respect, because the user of your function (in case it's not you) is now
made aware of the zeros, and makes an explicit decision to show empty cells.
--
Danny Khen
Excel Program Manager, Microsoft Corp.
[To use my email address, replace (dot) and (at) with respective chars, and
(MSFT) with microsoft.com.]
** This posting is provided "AS IS", with no warranties, and confers no
rights. **
"Steve Dalton" <NOsteveSPAM(at)NOeigensysSPAM(dot)com> wrote in message
news:OADQn6VLJHA.4324[ at ]TK2MSFTNGP05.phx.gbl...
[Quoted Text] > Hi Jesper
>
> A cell that contains a formula is not, by definition, empty. Therefore
> Excel has to evaluate the contained formula the returns the xltypeNil to
> something, and defaults to numeric zero. This is of course true for cells
> that are part of an array formula as well as single-cell formulae.
>
> If you want to clear a cell's contents using the C API you can use xlSet
> with xltypeNil, but you can only do this from command functions (macros).
> Worksheet formulae cannot call this function as they are not permitted to
> alter the values of cells in a workbook (other than the celling cell(s)
> via the returned data).
>
> Regards
>
> Steve Dalton
>
>
>
> "Jesper Lund" <usenet[ at ]jesperlund.com> wrote in message
> news:eABIPLyKJHA.5904[ at ]TK2MSFTNGP02.phx.gbl...
>> louis wrote:
>>
>>> dear all, i'd like to return a xloper with type 'xltypeMulti'. Lots of
>>> elements are just zeros and I'd like to make excel show them as empty
>>> cell but behavior like double zero. Setting element as xltypeNil doesnot
>>> work because excel will show 0. xltypeMissing does not work as excel
>>> shows "#Value!". xltypeError(xlerrNA) behaves correctly but excel will
>>> show "#NA". Any tricks/tips for a clean spreadsheet? Thanks, Louis
>>
>> I use an empty (length 0) string for this purpose. I have been unable to
>> find a way of returning an empty cell in an xltypeMulti array, and the
>> empty string is as close as I can get. At least the difference is not
>> visible to the user :-)
>>
>> --
>> Jesper Lund
>
>
|
|
Or return the zero and set the sheet to not display zeros in
Tools>>Options>>view.
Bit of a potential auditing pain, but meets the 'clean' requirement.
Cheers
Simon
Blog: www.smurfonspreadsheets.net
Danny Khen [MSFT] wrote:
Somewhat of a hack, but still an option: You can return actual zeros
instead of empty cells, then on the sheet users can add a column that
shows an empty string on zero, and hide the actual result column. It's
even better in some respect, because the user of your function (in case
it's not you) is now made aware of the zeros, and makes an explicit
decision to show empty cells.
--
Danny Khen
Excel Program Manager, Microsoft Corp.
[To use my email address, replace (dot) and (at) with respective chars,
and (MSFT) with microsoft.com.]
** This posting is provided "AS IS", with no warranties, and confers no
rights. **
"Steve Dalton" <NOsteveSPAM(at)NOeigensysSPAM(dot)com> wrote in message
news:OADQn6VLJHA.4324[ at ]TK2MSFTNGP05.phx.gbl...
> Hi Jesper
>
> A cell that contains a formula is not, by definition, empty.
Therefore Excel has to evaluate the contained formula the returns the
xltypeNil to something, and defaults to numeric zero. This is of course
true for cells that are part of an array formula as well as single-cell
formulae.
>
> If you want to clear a cell's contents using the C API you can use
xlSet with xltypeNil, but you can only do this from command functions
(macros). Worksheet formulae cannot call this function as they are not
permitted to alter the values of cells in a workbook (other than the
celling cell(s) via the returned data).
>
> Regards
>
> Steve Dalton
>
>
>
> "Jesper Lund" <usenet[ at ]jesperlund.com> wrote in message
news:eABIPLyKJHA.5904[ at ]TK2MSFTNGP02.phx.gbl...
>> louis wrote:
>>
>>> dear all, i'd like to return a xloper with type 'xltypeMulti'. Lots of
>>> elements are just zeros and I'd like to make excel show them as empty
>>> cell but behavior like double zero. Setting element as xltypeNil
doesnot
>>> work because excel will show 0. xltypeMissing does not work as excel
>>> shows "#Value!". xltypeError(xlerrNA) behaves correctly but excel will
>>> show "#NA". Any tricks/tips for a clean spreadsheet? Thanks, Louis
>>
>> I use an empty (length 0) string for this purpose. I have been unable to
>> find a way of returning an empty cell in an xltypeMulti array, and the
>> empty string is as close as I can get. At least the difference is not
>> visible to the user :-)
>>
>> --
>> Jesper Lund
|
|
What you do will depend on what processing of the returned data you want or
need to do. If the non-empty values are typically numbers and you want to,
say, calculate the average, then having a bunch of zeros makes live
difficult, as zero may also be a valid return value as well as the default
xltypeNil converted value.
I have had that issue and this is how I got round it: I returned "-" to
represent an 'empty' cell, so that users could see that there was no numeric
value. Many of Excel's built-in functions (AVERGE and SUM for example)
ignore text inputs and only operate on the numeric values. If you need to
do something these functions can't, for example calculate the average of the
cosine of the returned numbers, then you can use array formulae such as
{=SUM(IF(MyRange="-",0,COS(MyRange)))/COUNT(MyRange)}.
"Simon Murphy" <tobinsm[ at ]codematic.net> wrote in message
news:u2m87YjNJHA.4116[ at ]TK2MSFTNGP02.phx.gbl...
[Quoted Text] > Or return the zero and set the sheet to not display zeros in
> Tools>>Options>>view.
>
> Bit of a potential auditing pain, but meets the 'clean' requirement.
>
> Cheers
> Simon
> Blog: www.smurfonspreadsheets.net
>
>
> Danny Khen [MSFT] wrote:
> Somewhat of a hack, but still an option: You can return actual zeros
> instead of empty cells, then on the sheet users can add a column that
> shows an empty string on zero, and hide the actual result column. It's
> even better in some respect, because the user of your function (in case
> it's not you) is now made aware of the zeros, and makes an explicit
> decision to show empty cells.
>
> --
> Danny Khen
> Excel Program Manager, Microsoft Corp.
> [To use my email address, replace (dot) and (at) with respective chars,
> and (MSFT) with microsoft.com.]
>
> ** This posting is provided "AS IS", with no warranties, and confers no
> rights. **
> "Steve Dalton" <NOsteveSPAM(at)NOeigensysSPAM(dot)com> wrote in message
> news:OADQn6VLJHA.4324[ at ]TK2MSFTNGP05.phx.gbl...
> > Hi Jesper
> >
> > A cell that contains a formula is not, by definition, empty.
> Therefore Excel has to evaluate the contained formula the returns the
> xltypeNil to something, and defaults to numeric zero. This is of course
> true for cells that are part of an array formula as well as single-cell
> formulae.
> >
> > If you want to clear a cell's contents using the C API you can use
> xlSet with xltypeNil, but you can only do this from command functions
> (macros). Worksheet formulae cannot call this function as they are not
> permitted to alter the values of cells in a workbook (other than the
> celling cell(s) via the returned data).
> >
> > Regards
> >
> > Steve Dalton
> >
> >
> >
> > "Jesper Lund" <usenet[ at ]jesperlund.com> wrote in message
> news:eABIPLyKJHA.5904[ at ]TK2MSFTNGP02.phx.gbl...
> >> louis wrote:
> >>
> >>> dear all, i'd like to return a xloper with type 'xltypeMulti'. Lots of
> >>> elements are just zeros and I'd like to make excel show them as empty
> >>> cell but behavior like double zero. Setting element as xltypeNil
> doesnot
> >>> work because excel will show 0. xltypeMissing does not work as excel
> >>> shows "#Value!". xltypeError(xlerrNA) behaves correctly but excel will
> >>> show "#NA". Any tricks/tips for a clean spreadsheet? Thanks, Louis
> >>
> >> I use an empty (length 0) string for this purpose. I have been unable
> >> to
> >> find a way of returning an empty cell in an xltypeMulti array, and the
> >> empty string is as close as I can get. At least the difference is not
> >> visible to the user :-)
> >>
> >> --
> >> Jesper Lund
|
|
|