Displaying formula errors

FLX TMS FlexCel for VCL & FMX
1

Hi Adrian


I'm using the TMS FMX grid in virtual mode and Flexcel GetCellValue in the OnGetCellData handler. If I type an unknown formula into a cell I want to have #NAME? displayed in the cell so I set the xls cell value to this in the exception handler. However, when I select the cell I also want to view the formula text that was typed in a status bar. This is easy when the formula is valid but I can't see anywhere obvious to pick up the original formula if I overwrite the value with #NAME?. Is the original formula text stored anywhere else?

Thanks, Bob
2
If you set the value to #Name? then the formula will be lost.

Is this only for non existing functions like "=potato()" or also for syntax errors  like "= 1 + * 2" ?
Note that Excel will let you enter "=potato()" in a cell, but no "= 1 + * 2"

If this is only about not existing functions then I see 2 cases:
1)You know in advance the non existing functions the user might enter.
If this is the case, you can add those by using something like:
  xls.AddUserDefinedFunction(TUserDefinedFunctionScope.Local, TUserDefinedFunctionLocation.Internal, TPotato.Create);

for every known function. Then the user will be able to enter those names but no other.

2)You don't know in advance which functions the user might enter. There is no built in method to do this, but we could add one. Let me know if this would be helpful to you.

If you care not only about unknown functions but also about syntax errors, you'll probably have to store the formula in some external Dictionary<>
3

Bob,

Just to let you know we've added a new property: xls.AllowEnteringUnknownFunctions that will allow you to enter stuff like "=Potato(4, 3) + Lettuce()". Formulas will have a #NAME result, but the formula text will be there.

This property is false by default, as to avoid people writing misspelled functions.
Would this fix your problem?
Note that it won't still let you enter syntax errors, like "=1 + * 2", same as Excel won't let you. 

What is happening under the hood is that the original formula text isn't stored anywhere, inside an xls file we store a parsed Reverse-Polish-Notation representation of it. So if the formula is invalid, we can't enter it.It makes sense if you think that the formulas can be localized: For example I might write =SUMA(A1:A10) in Excel here, send you the file, and you would see "=SUM(A1:A10)" if you open the file in an English Excel. In order to be able to do this, the exact text can't be saved: We save an ID (6 for SUM), and different Excels will show SUM, SUMA, SUMME, whatever when they see the 6.

If this property would solve your problem, email me at adrian@tmssoftware.com and I'll send you an updated copy with this change.