none
C-api and actual range value RRS feed

  • Question

  • Hello,

    we have an Xll written in C/C++ wich uses the C-Api.

    Is there a way for a normal worksheet function to ask for the current value of the range.

    Example : In A1 theres the formula =Myfunc(...) and you see 27.

    Is there a way for myfunc to get the value 27.

    tia

      Hendrik Schmieder

    Tuesday, February 6, 2018 3:48 PM

All replies

  • Hello h_schmieder,

    Myfunc is an UDF function, right? 27 is the calculated result of the function and it should be the return value of the Myfunc. What do you mean a way for myfunc to get the value?

    If you need get the value 27, you could get the value from A1 directly no matter the value is written directly or returned by a formula.

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, February 7, 2018 6:54 AM
  • Hello,

    the use case is something like an "offline" mode.

    In this mode the UDF should just return the last value instead of calculating the value again.

    So I tried

    int rc;
    XLOPER12 xRes, xRes2;
    rc = Excel12(xlfCaller, &xRes, 0);
    rc = Excel12(xlCoerce, &xRes2, 1, &xRes);
    
    But the second rc is 64 which means uncalced cell

    Thursday, February 8, 2018 3:03 PM
  • Hello h_schmieder,

    To be honestly, I'm not familiar with programming with the C API in Excel. So I am still wondering what you want to do according to the code. 

    I'm trying to involve some senior engineers into this issue and it will take some time. Your patience will be greatly appreciated.

    Sorry for any inconvenience and have a nice day! 

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, February 9, 2018 9:03 AM
  • Hi h_schmiederJedox AG,

    Based on the description, it seems that you want to the function will not be recaculated. Actually we don't need to return the last value for the custom calculation in the code. The custom function in XLL for Excel is non-volatile functions by default. It means that the function will not be called unless Excel decide to recalculate its value or first calculate. 

    In the custom function we only need to focus on the custom business logic. Excel will help to take care of when to calculate. 

    More detail info about Excel Recalculation please refer link below:

    Excel Recalculation

    If you have any special scenario please feel free to let me know.

    Regards & Fei 


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Monday, February 12, 2018 10:13 AM
    Moderator
  • Hello,

    I'm back again.

    Until yesterday I thought that I found the solution to my problem,
    but them found out that the drawbacks still exists.

    Before I want to go in Detail I want first speak about the use case,

    We have several UDFs implemented in a XLL using the c-api and XLOPER12.

    These UDF get their Data from a database backend.

    In some cases this take long or isn't possible (for instance in a train if the server is not on the same laptop)

    So the idea was to gave some sort of an "offline" mode.

    Foe instance if you have in B3 the formula = MyUdf1() then MyUdf1() would just return the actual value of B3 if the workbook is in "offline" mode.

    The standard way is to use following code

    XLOPER12 xCaller, xRetval;
    
    Excel12(xlfCaller, &xCaller, 0);
    Excel12(xlCoerce, &xRetval, 1, &xCaller);

    But for xlCoerce to succeed in this case the function MyUdf1 has to be registered as macro sheet functions.

    And now we have the following Drawback:

    unfortunately Excel doesn't still detect the correct calculation order without help.

    I have a workbook with
    B2 = MyUdf1() and B3 = MyUdf2(B2), so that B2 should be calculated first and then B3.

    But if the workbook is opened the Excel tries to calculate B3 first.

    To help Excel to get the right calculation chain we return a null pointer.

    If the UDf is registered as normal function then Excel retry to calculate B3 a later time and rebuild its calculation tree.
    And if you then Press F9 Excel uses the new calculation tree.

    But if the UDF is registered as macro sheet functions then Excel doesn't try to recalculate the cell, but shows #NUM!.

    So

    a) is there another way for normal worksheet functions to get the actual value of the  current calculated cell ?

    b) Is there' s another return value for macro sheet functions that forces Excel to try to recalculate the cell in the same way as in the normal function case

    TIA

      Hendrik Schmieder


    Friday, August 3, 2018 1:06 PM
  • Hello Mr. Fei Xue,

    did you read my note from August 3, 2018.

    Even if you have no proposal yet, it would be nice if you give a note, that you have read my note

    and try to understand my problem.

    with best regards

      Hendrik Schmieder

    Tuesday, August 14, 2018 1:10 PM