I have a UDF defined in C# that returns the result of a SQL query to a dynamic array. This formula works perfectly and can return values. You can edit the formula in the formula bar but when editing the formula with the Insert Formula dialog box (Fx button) it crash excel. I believe it is because the formula editor is trying to evaluate the parameters as you change it. This will be problematic as it will need to execute the SQL query every time a parameter is changed while editing the formula.
There don't seem to be a way of turning off the "preview" of the result in the dialog box. Is there a way around this as I do not want the users to crash excel if they try to edit the formula using the insert formula editor?
I experimented with a simple VBA UDF function that create a dynamic array and that also give a preview of the array created as you edit the formula