none
UDF will be affected by automatic calculate or calculate now under formula tab RRS feed

  • Question

  • Hi ,

    I have a work sheet with User difned function, and if calculation is set to manual, it's fine. But if I set it to automatic calculate or click calculate now, excel will change value of my UDF.

    set to manual

    Image

    set to automatic or click calculate now

    Image


    Tuesday, December 13, 2016 2:33 AM

All replies

  • The file(dtest.xlsm) is uploaded to https://onedrive.live.com/?id=B887C15481596A3%21126&cid=0B887C15481596A3 , and my excel version is 1609 build 7369.2308
    Tuesday, December 13, 2016 2:49 AM
  • Hi,

    Calculate Now is used to calculate when you set manually calculate. If you get the same results when you setting automatic calculate or click Calculate Now when it is set into Manually, it indicates the UDF is calculated and you get the result.

    According to your UDF, do you check if the add-ins LsAgXLB.xla/LsVisXLB.xla/Vision5.xla are loaded in your Excel application? 

    The add-ins are in the Developer tab -> Add-ins groups -> Excel Add-ins.


    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, December 14, 2016 5:34 AM
    Moderator
  • I don't have the add-in in my excel,  so my expected result is my UDF won't be affected by click calculate now when it is set into Manually or automatic calculate. As I mihgt have other excel formula need to be calculated but UDF should not be calculated without the addin.

    But actual result is my UDF is affected.



    it works as expected in excel 2010, but not in excel 2013 & 2016
    • Edited by rdchuang Wednesday, December 14, 2016 5:59 AM
    Wednesday, December 14, 2016 5:42 AM
  • I test in Excel 2010/2013/2016, I get the same result:  when clicking calculate now or setting into automatic calculate, all the results are -585217308.33. The values are the calculated result for your UDF.

     

    >>my expected result is my UDF won't be affected by click calculate now when it is set into Manually or automatic calculate

     

    Calculate Now would take action when it is set Manually. Users click it, all the formula are recalculate.

    Automatic Calculation would recalculate all dependent formulas every time you make a change to them.

     

    Do you want to keep the value what we see when opening the file and keep the formula, but you don’t want users to changing them when they use Automatic Calculation or click Calculate Now? I think it is impossible to let Excel skips one function when calculating.

    >>it works as expected in excel 2010, but not in excel 2013 & 2016

    Do you check if there are these add-ins in your Excel2010, which might cause the difference?


    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, December 14, 2016 6:38 AM
    Moderator
  • Why you return -585217308.33 for all but not other value? My UDF won't work without my Addin. What's your logic to return the same result?

    • Edited by rdchuang Wednesday, December 14, 2016 7:49 AM
    Wednesday, December 14, 2016 7:48 AM
  • Hi Celeste,

    I uploaded another file dtest_New.xlsm in onedrive, try this one. It will be different between Excel 2010 and 2016

    Wednesday, December 14, 2016 8:49 AM
  • Hi,

    The value is calculated result from your UDF. After debugging, I found -585217308.33 comes from GetCellValueFromText function.

     For your new file, since I don’t have your add-in, all the values would not be changed. The values are from gLsLastTargetValue. I think that should be your expected result.


    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, December 14, 2016 9:08 AM
    Moderator
  • Hi Celeste,

    What's you Excel2016 vestion? It seems a bug in version 16.0.7426.1015 that will change the value(for file dtest_New.xlsm). It works as expected  in version is 16.0.6925.1041.

    Wednesday, December 14, 2016 9:19 AM
  • Hi,

    After searching, it seems 16.0.7426.1015 is an version of Office Android.

    Testing in Version1612(Build 7668.2016)  and Version1609(Build 7369.2095), I get the same result for your old or new files.

    What is your problematic Excel version shown in Control Panel? I could try to convert to the version and check if I could reproduce your issue.

    To confirm the issue causes from Excel or your UDF, I suggest you test built-in function to check if the result would be different under different calculation modes.


    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, December 14, 2016 9:59 AM
    Moderator
  • Hi Celeste,

    16.0.7426.1015 is a version of Office 365. And lots of our customer are using this version of office365.




    • Edited by rdchuang Friday, December 16, 2016 2:21 AM
    Thursday, December 15, 2016 2:03 AM
  • Hi,

    Sorry that I failed to revert using this version number.

    Please visit Version and build numbers of update channel releases for Office 365 clients.

    Please share the version listed under the Office Updates section. Or go to Control Panel > Programs > Programs and Features, and then view the information in the Version column.

    If you run built-in function in this version, would it be different result?

    If you update to the newest version or revert to an earlier version in the unsuccessful computer, would it be different?


    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.

    Thursday, December 15, 2016 7:23 AM
    Moderator
  • I upload another screenshot, it's version of 1609 Build 7369.2038(version 16.0.7426.1015, office365) that will have problem. Another version 16.0.6965.2105 works as expected.

    • Edited by rdchuang Friday, December 16, 2016 2:48 AM
    Friday, December 16, 2016 2:22 AM
  • Hi,

    Sorry that I failed to find the exact same version like yours.

    I test with your new file in the version shown as pictures. I get the same result: none of the values would be changed when automatic calculate or clicking Calculate Now.

    Due to the limitation of the community support channel, I will suggest you contacting the Microsoft professional support so that our engineers can work closely with you to troubleshoot this issue.

    If the support engineer determines that the issue is the result of a bug, the service request will be a no-charge case and you won't be charged.

    Please visit the link below to see the various paid support options that are available to better meet your needs.  

    http://support.microsoft.com/default.aspx?id=fh;en-us;offerprophone

    Thanks for your understanding.

    Regards,

    Celeste


    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, December 16, 2016 9:57 AM
    Moderator
  • Hi Celeste,

    Check "Microsoft Support.html" in my onedrive. If you think it's a bug of Excel,  give an answer here. So that I can let my customer refer to this link.


    • Edited by rdchuang Wednesday, December 21, 2016 1:28 AM
    Tuesday, December 20, 2016 2:23 AM
  • Hi,

    Due to the limitation of the community support channel, we are unable to reproduce your issue with the same version, please wait for Microsoft engineer's respond if you have opened a support ticket.

    Regards,

    Celeste


    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.

    Thursday, December 22, 2016 10:19 AM
    Moderator
  • Hi Celeste,

    You said wait for Microsoft engineer's respond , can you let me know the engineer's name and how to contact him?
    Friday, December 23, 2016 3:24 AM
  • Hi,

    If you want to contact Microsoft professional support, please visit the link below to see the various paid support options.

    http://support.microsoft.com/default.aspx?id=fh;en-us;offerprophone

    Regards,

    Celeste


    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, December 23, 2016 8:52 AM
    Moderator
  • Hi Celeste,

    Check "Microsoft Support.html" in my onedrive. There're some answer at the bottom of the file  from MS support as below

    "
     If you have UDF and set it to automatic, supposedly that won't reset any data
     that won't affect a date
                                          ok, so you mean there'a bug on automatic calculation in version 7426, right?
     Yes, a bug with the 16.0.7426.1015
    "

    So that's the official answer from MS, am I right?



    • Edited by rdchuang Monday, December 26, 2016 3:09 AM
    Monday, December 26, 2016 2:53 AM
  • Hi,

    If Microsoft Support Engineer and Engineering Team confirm it is a bug, that's official answer from Microsoft.


    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.

    Tuesday, December 27, 2016 8:39 AM
    Moderator
  • Hi Celeste,

    We note that latest version Excel prevent our codes to amend the Error Mssage under Data Valdation. Which is editable in earlier version, can you verified that MS make that changes? And if it's bug of Excel?

    Friday, January 6, 2017 7:33 AM
  • Hi,

    I suggest you uninstall the update or revert to an earlier version to check if update causes the issue.

    If the issue could be reproduced on different computers and only occurs in a specific version, I suggest you revert to an earlier version to work around and submit your feedback on user voice site.

    Besides, I suggest you post a new thread for a different issue.

    Regards,

    Celeste


    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.


    Monday, January 9, 2017 4:32 AM
    Moderator
  • Hi Celeste,

    You said that you can't confirm it with MS. So you're not a staff of MS? And here's not a MS website? If I can't get answer here, where can I get the answer. I'm suggested to ask question here by another guy from MS. It's frustrated to post my quesiton again and again in different forums under MS site.

    Tuesday, January 10, 2017 2:37 AM
  • Hi rdchuang,

    Thanks for letting us know your comments and we are sorry for the confusion. Yes, we are Microsoft staff. Also, MSDN forum is an open and multiple: multiple community platform for technical issue discussion, and we are always doing our best to provide quick troubleshooting steps & suggestion for non-complex scenarios. For this issue, we noticed that you contacted us via different channels (such as community and chat support). Due to the complexity of this issue, I would like to suggest that you continue to follow up this issue in the previous chat case, which is 1:1 assisted support channel, so that we could drive the request in a more efficient way.

    We also host a triage meeting for this case, and we also agree with Junil that it may be a bug. While waiting for further update, a workaround could be temporarily rolling back Office to a previous version before the new update is released. Thanks for your understanding and do let us know if anything we could help.

    Regards,
    Jeffrey


    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.


    Friday, February 17, 2017 3:06 AM
    Moderator