We use cookies to give you the best experience on our website. If you continue to browse, then you agree to our privacy policy and cookie policy.
Unfortunately, activation email could not send to your email. Please try again.

how can I return a cell range from an added function and have it not surrounded with quotes

Thread ID:

Created:

Updated:

Platform:

Replies:

32729 Aug 5,2005 08:13 PM Aug 7,2005 11:44 AM Windows Forms 4
loading
Tags: Calculate
Byron Tate
Asked On August 5, 2005 08:13 PM

My function funA returns a string = "G4:G34". I call another of my functions like this: funB(funA) When I set a break in the function funB, the argument is "\"G4:G34\"", instead of being just "G4:G34" So I get the error msg "Input string was not in correct format when I try to use a built-in function like Average(funA) How can I make my function return a cell range ref that will work?

Administrator [Syncfusion]
Replied On August 6, 2005 07:48 AM

Right now, the convention is that if a function returns a non-numeric string, it is treated as a string and quoted. The quotes are necessary to tell the engine that these items should be parsed as strings when passed to other functions. Otherwise the string functions have problems handling nested function calls. I do not know of anything that you can do short of replacing the built-in functions to pre-process the arguments. Replacing 1 or 2 functions in this manner is no big deal. Doing 150+ is a little more overhead. So, what we did to accomodate this is to modify our code so if a function argument is a quoted string that is a single range (has one colon in it), then the engine will treat it as a range instead of a string. If you want to use this convention in your own functions, then you should use the engine.GetCellsFromArgs method the process the arguments as this is the method that will recognize a quoted single range in an argument as a range and not a string.

Byron Tate
Replied On August 6, 2005 07:17 PM

You said that the built in functions can treat a string like "g4:g33" as a cell range and not a string. My problem is I have an added function, funA, that returns a string, "g4:g33". But when I try to call a built in function and use the funA, like this: sum(funA(...)), sum seems to not like the argument. The formula cell has this string : ''Input string was not in a correct format''. This is the same error I get when I call my added function, funB with funA as it''s argument, like this: =funb(funA(...)), and break at the first line of funb. It''s string arg is "\"g4:g33\"". Notice the extra quotes. funA does not put in the extra quotes - it shows up in funb with the extra quotes. Engine.GetCellsFromArgs throws an exception when passed this string. So it still seems like the built-ins don''t handle a quoted range string.

Administrator [Syncfusion]
Replied On August 7, 2005 04:14 AM

>>You said that the built in functions can treat a string like "g4:g33" as a cell range and not a string. I said "Right now, the convention is that if a function returns a non-numeric string, it is treated as a string and quoted." So when your functions returns g3:g33, this is not a number. So it is treated as a string and quoted when it gets passed to another function as an argument. And this causes the problem you are seeing. We have made a change to our source code to not do this. You do not have this modified code yet. So, in the future sum(funA()) will work if funA returns the string g3:g33. Then, if you have a custom function funB, then funB(funA()) will work if you use GetCellsFromArgs to return the list as cells to be processed as we do in our functions like Sum, Average, etc. If you do not use GetCellsFromArgs in your custom function code, then your custom function code would have to explicitly change the "g3:g33" to g3:g33.

Byron Tate
Replied On August 7, 2005 11:44 AM

Ah, saved again! I''m hoping this change is in version 3.3. Gurucharan Patwal, in answering my incident 20270, gave me a key to download the internal 3.3 build. I''m going to download it for the several other issues I have been tracking, and I''ll see if this fix is also there.

CONFIRMATION

This post will be permanently deleted. Are you sure you want to continue?

Sorry, An error occured while processing your request. Please try again later.

You are using an outdated version of Internet Explorer that may not display all features of this and other websites. Upgrade to Internet Explorer 8 or newer for a better experience.

;