Wrapping an RTD server

Add-in Express™ Support Service
That's what is more important than anything else

Wrapping an RTD server
Best way to marshal results from an RTD server 
Gavin Howard




Posts: 14
Joined: 2016-12-01
I am writing an RTD server from my firm which essentially returns financial data. Obviously we want to wrap the RTD call in excel in a UDF to make it more user friendly.

Sometimes the value returned from the RTD server will be numerical data, and the clients want to be able to use this data in other formula in different cells, SUM() for example. However in a lot of the examples on the addinexpress site a string is returned from the XLL method, so the data in Excel ends up as string. If you try to then reference the output of the cell, in a calculation say, it fails with "#VALUE" as it is a string.

Some of the time string will be correct, in the event they return a stock ticker, "GOOG" for example. Other times it will be numeric. What is the best way to marshal this data back into excel so that when it is a numeric type it can be used in calculations elsewhere on the sheet, and where it is a string remains so?

I am implementing this in C#, so any example code I would appreciate in C# rather than VB.Net.

Thanks in advance.
Posted 02 Dec, 2016 06:40:12 Top
Andrei Smolin


Add-in Express team


Posts: 18787
Joined: 2006-05-11
Hello Gavin,

This is you who decides what type should be returned by this or that user-defined functions that you create. As you've admitted every type (text or double) is suitable in different scenarios. This is why you need to plan the interface that your RTD (or an XLL that wraps the RTD) provides.

Anyway, this is user who decides how to use this or that function. Although it is very often that the formula invoking your function is as simple as =MyFunction(), the user may create different constructs such as =MyFunction() + 1, etc.

If you talk about a function that may return a number or a string (depending on some condition), then I wouldn't recommend that you create such a function. As you've pointed out, such a function will break the formulas assuming that the function returns a value of a specific type.

I feel like I may misunderstand you in some way. If this is the case, please explain or provide more details for me to understand you better.


Andrei Smolin
Add-in Express Team Leader
Posted 02 Dec, 2016 08:25:44 Top
Gavin Howard




Posts: 14
Joined: 2016-12-01
Andrei - Thank you for looking into my query so quickly!

Admittedly, I am new to the framework, so I am working out what the best way to use the components are. This is a greenfield project, so I will be deciding the interfaces etc.

To clear up any misunderstanding, I was about to write a UDF that will allow a user to return numerical or string based data. In the cases where it will be numeric, I would like a user formula such as "=MyFunction(x) + 1" to work and add one to the numerical value returned.

In the case where the parameters passed in the UDF mean string data would be returned when the user uses the UDF, the string be rendered e.g where a user would write the formula: "=MyFunction(y)" would return a value "GOOG".

Question 1) Generally, are you recommending that this is a poor approach and it would be better to split these two cases out into two seperate UDF / RTD modules?

Question 2) In your sample code (https://www.add-in-express.com/creating-addins-blog/2010/03/24/addin-xll-rtd-one-assembly/) you have written:


public static string CallRTD()
            {
                if (Module.IsInFunctionWizard)
                {
                    return "This UDF calls an RTD server.";
                }
                return Module.CallWorksheetFunction(AddinExpress.MSO.ADXExcelWorksheetFunction.Rtd, new object[3] { "comaddin_xll_rtd_cs.RTDServerModule1", "", "test" }).ToString();
            }



If it is better for me to write a UDF function that only returns numerical values, should my UDF declaration in the ADXXLLModule look something like:



 public static double MyFunction(object arg, object arg1)
            {               
                return Convert.ToDouble(Module.CallWorksheetFunction(AddinExpress.MSO.ADXExcelWorksheetFunction.Rtd, new object[3] { "ExcelAddinExpress.RTDServer", "", "Randomize" }));
            }



Would it still be possible to pass error messages back in the case where the RTD function call fails, in the case where arguments fail validation or some sort of timeout? e.g AddinExpress.MSO.ADXExcelError.xlErrorValue;
Posted 02 Dec, 2016 08:54:15 Top
Andrei Smolin


Add-in Express team


Posts: 18787
Joined: 2006-05-11
Gavin,

I'd like to understand how your function is implemented:
- the function returns a value of the type which is determined by its internal logic. Say, on internally calculated condition it returns a string instead of a number every 10 seconds. In this case, the end user *may* expect that the function returns a number, then returning a text doesn't seem wise.
- the function returns a value of the type which is determined by the parameter(s) that the end user passes to the function. Say, a user passes a string or numeric identifier. In this case the user knows and expects that different parameters passed to the function returns values of different types. If this is the case then creating such a UDF is probably okay.

Which of the scenarios is yours?

I would put this as follows: The design of a UDF is poor if it isn't consistent with user expectations.

Gavin Howard writes:
If it is better for me to write a UDF function that only returns numerical values


Use System.Object as the return type: you will need to return an error.

Gavin Howard writes:
e.g AddinExpress.MSO.ADXExcelError.xlErrorValue;


Such errors are processed by Excel itself. If you don't like this way, check https://www.add-in-express.com/creating-addins-blog/2011/10/11/invoke-excel-addin-xll/.


Andrei Smolin
Add-in Express Team Leader
Posted 02 Dec, 2016 10:35:00 Top