Note that there are some explanatory texts on larger screens.

plurals
  1. POExcel UDF calculation should return 'original' value
    text
    copied!<p>I've been struggling with a VBA problem for a while now and I'll try to explain it as thoroughly as possible.</p> <p>I have created a VSTO plugin with my own RTD implementation that I am calling from my Excel sheets. To avoid having to use the full-fledged RTD syntax in the cells, I have created a UDF that hides that API from the sheet. The RTD server I created can be enabled and disabled through a button in a custom Ribbon component. </p> <p>The behavior I want to achieve is as follows:</p> <ul> <li>If the server is <strong>disabled</strong> and a reference to my function is entered in a cell, I want the cell to display <code>Disabled</code></li> <li>If the server is <strong>disabled</strong>, but the function had been entered in a cell when it was enabled (and the cell thus displays a value), I want the cell to keep displaying that value</li> <li>If the server is <strong>enabled</strong>, I want the cell to display <code>Loading</code></li> </ul> <p>Sounds easy enough. Here is an example of the - non functional - code:</p> <pre><code>Public Function RetrieveData(id as Long) Dim result as String // This returns either 'Disabled' or 'Loading' result = Application.Worksheet.Function.RTD("SERVERNAME", "", id) RetrieveData = result If(result = "Disabled") Then // Obviously, this recurses (and fails), so that's not an option If(Not IsEmpty(Application.Caller.Value2)) Then // So does this RetrieveData = Application.Caller.Value2 End If End If End Function </code></pre> <p>The function will be called in thousands of cells, so storing the 'original' values in another data structure would be a major overhead and I would like to avoid it. Also, the RTD server does not know the values, since it also does not keep a history of it, more or less for the same reason.</p> <p>I was thinking that there might be some way to exit the function which would force it to not change the displayed value, but so far I have been unable to find anything like that.</p> <p>Any ideas on how to solve this are greatly appreciated!</p> <p>Thanks, Che</p> <p><strong>EDIT:</strong><br> Due to popular demand, some additional info on why I want to do all this: As I said, the function will be called in thousands of cells and the RTD server needs to retrieve quite a bit of information. This can be quite hard on both network and CPU. To allow the user to decide for himself whether he wants this load on his machine, he or she can disable the updates from the server. In that case, he or she should still be able to calculate the sheets with the values currently in the fields, yet no updates are pushed into them. Once new data is required, the server can be enabled and the fields will be updated.</p> <p>Again, since we are talking about quite a bit of data here, I would rather not store it somewhere in the sheet. Plus, the data should be usable even if the workbook is closed and loaded again.</p>
 

Querying!

 
Guidance

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload