On the development of Excel (vi) asynchronous custom functions for Excel

Related articles:

On Excel Development (1) Overview of Excel Development

On Excel Development (II.) Excel menu system

On the development of Excel (III.) Excel object model

The Custom Function (UDF) in Excel, described above, greatly expands the functionality of the Excel plug-in, allowing us to represent business logic in the form of Excel functions and to build complex analytic reports based on these fine-grained custom functions.

The basic execution logic of the ordinary UDF custom function is that Excel accepts the function expression entered by the user and then processes it through the processing logic of the UDF function, in which the UI interface of Excel waits until the function body finishes executing to update the cell data. As with most synchronous applications, synchronized UDF functions block the Excel UI thread and are not convenient for dynamically expanding computing power, resulting in a poor user experience when processing logic is more complex and time-consuming. So we need to develop an asynchronous UDF function.

A question of the proposed

Typically, when users enter custom functions in Excel, we want to perform the following:

Open another thread or process the function calculation logic (different from the Excel UI thread) in the thread pool.

Return immediately to "calculating", "fetching" or "Loading" equivalent prompt the user is calculating.

Notify Excel to recalculate the cell after the calculation is complete.

Returns the true result of the calculation.

In the above steps, the difficulty lies in the third step, after the general function returns the value in the second step, the whole process ends. Because the function calculation is not in the Excel main thread, refreshing Excel cell recalculation is done in the Excel main thread, and it needs to be specified to refresh which cell, and it is cumbersome to handle exception handling.

In Excel 2010, the ability to write asynchronous UDF directly is provided, but it is only available in the XLL addin that is developed with C + + and is not backward compatible. To write the most version of Excel (version 03 and above), you must use the programming mechanism that Excel has already provided. We can use the Excel RTD function described above to solve the above problem, the Excel RTD mechanism is introduced in Excel 2002, so the upward compatibility is good. The rationale for using Excel RTD to implement asynchronous UDF functions is when a user enters a function in a cell:

Request the function with the RTD function, record the TOPICID, and the requested expression and parameters, and return the "calculating" prompt the user is processing

Open another thread to handle the topicid corresponding to the Excel request, and calculate the result and save it.

Call the UpdateNotify method, request Excel to recalculate the cells, return to excel in the recalculated cells, based on topicid, and the calculated result values

Excel refreshes the cells based on the value of the calculated results returned.

Based on the above analysis, the available solutions for writing asynchronous UDF functions for Excel are:

In Excel 2010 and above, you can develop XLL type plug-in implementations by invoking the Excel API by using C + +.

In Excel2002 and above, you can use the Excel RTD function to implement

Through the third party class library, such as Excel-dna introduced in the reactiveextension to achieve.

Given the maximum compatibility of Excel versions and the focus of this series of articles on the development of Excel Plug-ins under. NET, the 2nd approach is highlighted.

Two how to implement an asynchronous UDF using RTD

As mentioned earlier, the RTD function is used primarily as a real-time data update, but we can exploit the special push-pull mechanism of the RTD function to develop an asynchronous UDF function. The main implementation framework for asynchronous UDF functions is as follows:

The implementation of an asynchronous UDF function based on RTD approximate process as above:

The user enters the UDF function, and at the VBA function level, the function name of the UDF function, and the parameter as an array of parameters of the RTD function, initiates the RTD function call internally.

In the ConnectData method of the RTD function, the topicid that the request Excel assigns, and the requested parameter, including the function name, is spliced into the formula style, as the key is saved to a global dictionary, the value is an entity class, The entity class records the TopicID, and the value result (object type) returned by the key's request, as well as some other fields.

ConnectData returns loading, or another prompt, displayed in a cell, prompting a background operation.

At the same time, open a thread to the global request dictionary inside the request processing, this process involves synchronous processing, because it is possible to process the request, there are new requests to add in.

Remove key, parse method name and parameters, use reflection or some efficient method to get the corresponding value of the request, stored in the result field of the entity of value corresponding to the key.

Call the UpdateNotify method of the RTD at the same time to notify Excel that there is data to update.

Excel invokes the RefreshData method of the RTD and then places data items and topicid with the resulting values in the dictionary of the global request into a two-dimensional array and returns.

The Excel cell displays the result value that is actually returned for the request.