Offset problem

FLX TMS FlexCel Studio for .NET
1

I have been given a spreadsheet that has the following formula:

 
=OFFSET(F5,,-1,1,1)
 
you will notice that the row attribute (or based on order the second value) is empty i.e. ",,"
 
Excel defines the row attribute as required, yet it still interprets a missing value as a zero and is able to return a result. Flexcel however does not interpret this in the same way i.e on calling GetCellValue, we return an ErrValue.
 
Is there a way to make flexcel behave the same as excel even though it's not how the function is defined in the help documentation?
2

just to clarify if I use the below formula, Flexcel works as expected:

 
=OFFSET(F5,0,-1,1,1)
 
However I'm looking for a programatical way to do this rather then manually going through the entire workbook and replacing all instances.
3

Apologies, the problem is the following:

 
Working:
=OFFSET(F5,,-1,1,1)
 
Not Working:
=OFFSET(F5,,-1,,)
 
The difference is the last 2 optional parameters not the row as I previously thought. Still an issue, just in a different place.
4

Hi,

Thanks for reporting this.
Indeed, when you use a missing argument for the last 2 values in the offset function, FlexCel assumes "0", while it should assume 1. (because 0 would raise an error). It correctly assumes 1 if you don't specify the last 2 parameters at all, but it won't if you use missing arguments. 

This has been internally fixed here, we will be publishing a beta very soon with it fixed.

Regards,
   Adrian.
5

Many thanks. Any idea when this beta will be available to download ?

6

..and would you have a work around in the meantime i.e. AddUserDefinedFunction ?

7

Hi,

You can't workaround it with an udf, because internal functions take priority over udfs.
We were targeting a beta for the end of this week, but if you need it urgently, just email me at adrian@tmssoftware.com and I will give you a fixed version today.