none
Excel OLE automation in XLL and setting number format for a range RRS feed

  • Question

  • Hello,

    in my XLL I have declared a recalc handler.

    In this recalc handler I try to set a specific number format for a specifc range.

    extern const wchar_t pEmptyFormat[] = L";;;\" - \"";
    extern const size_t EmptyFormatLen = sizeof(pEmptyFormat) / sizeof(wchar_t) - 1;
    
    const _variant_t EmptyFormat = pEmptyFormat;
    
    Excel::RangePtr xRange = ...
    
    
    xRange->PutNumberFormat(EmptyFormat);
    _variant_t vformat = this->xRange->NumberFormat;

    In my testcase the range consists of C8 and D7:D8.

    D7,C8 and D8 have a formula.

    C8 and D7 already have the numberformat ;;;" - " and D8 has  a number Format without numbers after decimal Point.

    And the calculation is set to Manual.

    I also have a keyhandler for CTRL-DEL.

    I select D8 and press CTRL-DEL.

    This triggers a calculation of D8 (in fact a calculation of all loaded workbooks).

    This result in D8 having value "".

    The recalc handler is executed, escpecially the putformat line and although vformat has the value of ;;;" - " I see an empty D8.

    I have to press F9 to get the desired - in D8.

    I already tried to call select and activate on XRange (and the other way round) or just one of them.

    But still no luck.

    What does I miss ?

      Hendrik

    Wednesday, July 13, 2016 12:34 PM

Answers

  • Very sorry for the noise.

    It turrns out that due to an unexpected code path the numberformat of D8 was reset to the numberformat of the style of D8 afterwards.

    So it looks like PutNumberFormat() does works as expected.


    • Marked as answer by h_schmieder Thursday, July 14, 2016 1:10 PM
    Thursday, July 14, 2016 1:09 PM

All replies

  • Hi h_schmieder,

    According to your description, could you reproduce this issue if you use VBA?

    'These examples set the number format for cell A17, row one, and column C (respectively) on Sheet1.
    Worksheets("Sheet1").Range("A17").NumberFormat = "General" 
    Worksheets("Sheet1").Rows(1).NumberFormat = "hh:mm:ss" 
    Worksheets("Sheet1").Columns("C"). _ 
     NumberFormat = "$#,##0.00_);[Red]($#,##0.00)"

    For more information, click here to refer about Range.NumberFormat Property (Excel)

    Thursday, July 14, 2016 7:35 AM
  • Very sorry for the noise.

    It turrns out that due to an unexpected code path the numberformat of D8 was reset to the numberformat of the style of D8 afterwards.

    So it looks like PutNumberFormat() does works as expected.


    • Marked as answer by h_schmieder Thursday, July 14, 2016 1:10 PM
    Thursday, July 14, 2016 1:09 PM