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

[Byron Tate]

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]

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]

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]

>>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]

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.