Async UDF evaluation order problem

Async UDF evaluation order problem

Posted by: illia on 23 June 2026, 11:44 am EST

• 

  illia

  • Post Options:
  • Link
    Copy

  Posted 23 June 2026, 11:44 am EST

  The problem:

  Some cells reference one custom function inside another, like:

  =EXCHANGERATE("ILS", "USD", BSDATE(0))

  In this case, EXCHANGERATE and BSDATE are async functions.
  
  After calling calculate on the workbook:

          workbook.Calculate();
          workbook.WaitForCalculationToFinish();

  EXCHANGERATE received as 3rd parameter “Busy” value and failed to calculate.

  Question:
  
  Is there a supported way to work with nested async functions like in SpreadJS? If yes, how to wait for the argument result?

• 

  kartik.dua

  

  • Post Options:
  • Link
    Copy

  Posted 24 June 2026, 2:37 am EST

  Hi,

  We investigated the nested async function scenario you mentioned by utilizing two nested AsyncCustomFunction in v9.1.2 of DsExcel.

  After calling the workbook.Calculate() and workbook.WaitForCalculationToFinish() methods, the outer function successfully received the resolved final value from the inner function and the calculation completed as expected. We have attached the sample project for your reference.

  To help us determine why the EXCHANGERATE function is receiving a “Busy” value in your specific environment, could you please update our sample project to demonstrate your EXCHANGERATE and BSDATE custom functions and reproduce the issue? This will allow us to investigate the calculation sequence further.

  Attachment: NestedAsyncFunction.zip

  Best regards,
  
  Kartik

• 

  illia

  • Post Options:
  • Link
    Copy

  Posted 24 June 2026, 6:51 am EST - Updated 24 June 2026, 6:56 am EST

  Hi,
  
  Thank you for your response. I don’t know what was wrong, but after updating the library minor version, the bug is gone, and now all async functions work great.
  
  I want to report some problems with SpreadJS that I found while testing.

  1. =XMATCH(T3,A1:A22,0,0) This formula works in SpreadJS, but in DSExcel, after calculating returns Err:504
  2. =SWITCH(F25,1,End Balance,2,Start Balance,10) Also works in SpreadJS, but not in DSExcel (Err:508)
     
     It’s just a validation problem, I think, and example formulas should not work anyway, but work in Spread
     
     
     

     766×532 43.7 KB
• 

  kartik.dua

  

  • Post Options:
  • Link
    Copy

  Posted 25 June 2026, 5:18 am EST

  Hi,

  We are glad to hear that the async function issue is resolved!

  ---

  Regarding the two formulas you reported, we tested them across MS Excel, DsExcel, and SpreadJS. Here are our findings for each case:

  Formula: =XMATCH(T3,A1:A22,0,0)

  Observation: When tested with this syntax, MS Excel and DsExcel both returns #VALUE! error value. However, the formula executes without any error in SpreadJS.

  Reason for Error: This formula violates standard Excel syntax because the fourth parameter (search mode) only accepts specific defined values (1, -1, 2, -2). Passing 0 causes a validation failure in Excel and DsExcel.

  However, by-design, SpreadJS provides an additional built-in search option value of 0, which is not present in Excel. You can refer to the documentation SpreadJS-XMATCH. This is the reason, no error was thrown in SpreadJS.

  ---

  Formula: =SWITCH(F25,1,End Balance,2,Start Balance,10)

  Observation: We tested this formula in Excel, DsExcel, and SpreadJS. In our testing, all three returned a #NAME! error value. The behavior was identical across all platforms, and SpreadJS behaved as expected.

  Reason for Error: The error is caused by a syntax validation failure due to unquoted text strings. The string values End Balance and Start Balance were provided without quotation marks. Whenever there is a match case, returning an unquoted string throws an unrecognized text error. When we enclosed the strings in quotes (eg. “End Balance”), all the products showed the expected behavior.

  ---

  If you have a different observation or are still facing issues, please modify the attached Excel file to reproduce the behavior and send it back to us for further investigation.

  In case you observe any unexpected behavior specific to SpreadJS controls, you can create a case directly in the SpreadJS Forum.

  Attachment: Book1.zip

  Best Regards,
  
  Kartik