AsFormula cells do not open correctly in Excel 2007

[mwhiting]

When using AsFormula to create a cell, the formula cell created will have a result of 0 when opened in Excel 2007. When opening in 2010 and 2013 the cell shows the result of the formula as expected.

This appears to only be the case when the formula is not a plain arithmetic formula.

For Instance:

• Correctly shows result: =H88
• Correctly shows result: =H88 * H87
• Shows 0: =MyNamedCell
• Shows 0 however the hyperlink actually works: =HYPERLINK("#'MytabName'!C5","My Pretty Link Name")

If the cell is put in edit mode and exited (F2 then enter) then Excel treats it as a formula cell and shows the result as it should.

Is there something I need to specify in order to have these work correctly in 2007 or did this expose a bug?

I've checked my TXLSReadWriteII5 version property after creation and before writing and in both cases it is xvExcel2007.

[mwhiting]

Are these being written out as Text cells instead of Formula cells?

[mwhiting]

When opening in Excel 2007 and then saving, the formula tag for the cell does not contain the aca nor ca attribute. When I edit the cell and save it, the ca attribute is added.

Code: Select all

<c r="C6" s="6">
  <f ref="C6:R6" t="shared" si="0">InputCell</f>
  <v>0</v>
</c>

Without an edit E6 shows as: 
<c r="E6" s="6">
  <f t="shared" si="0"/>
  <v>0</v>
</c>

With an edit E6 shows as: 
<c r="E6" s="6">
  <f ca="1">InputCell</f>
  <v>1000</v>
</c>

On https://msdn.microsoft.com/en-us/librar ... ellformula
I found that ca designates "Calculate Cell" - "Indicates that this formula needs to be recalculated the next time calculation is performed."

aca is "Always Calculate Cell"

With this discovery I need to know how to set this attribute. I found the following in the WriteFormula procedure of TXLSWriteSheetData.OnWriteCell but I haven't been able to see how to set these.

Code: Select all

  Cell.F.Aca := (FH.Options and Xc12FormulaOpt_ACA) <> 0;
  Cell.F.CA  := (FH.Options and Xc12FormulaOpt_CA) <> 0;

For the hyperlinks it appears to be the absence of the t="str" attribute of the cell that is the problem.

Code: Select all

<c r="A10" t="str" s="25">
  <f>HYPERLINK("#'Numerics'!A1","Simple Numerics")</f>
  <v>Simple Numerics</v>
</c>

[mwhiting]

Issue resolved

I verified that if the ca attribute is added to the formulas both the hyperlinks and the formulas with names will calculate in 2007.

Verified by setting this in WriteFormula procedure of TXLSWriteSheetData.OnWriteCell

Code: Select all

  Cell.F.CA  := true;

I then played around until I found the following solution.

Assuming variables are within scope, the following will set the ca (Calculate Cell) formula option for the cell created.

Code: Select all

procedure SetFormulaOptionCalcCell(aColumn, aRow: integer);
var
  cellToAlter : TXLSCellMMU;
  formulaHelper : TXLSFormulaHelper;
begin
  cellToAlter := myXLS[mySheet].Cell[aColumn,aRow].Cells;
  formulaHelper := cellToAlter.FormulaHelper;
  formulaHelper.Options := Xc12FormulaOpt_CA;
end;

myXLS[mySheet].AsFormula[myColumn,myRow] := myText;
SetFormulaOptionCalcCell(myColumn,myRow);

[larsa]

Hello

Your suggested fix will not work as expected. It's not possible to set the CA flag for individual formulas. There is no easy fix for this so it will not be implemented. The next update will have an option, XLS.FormulasUncalced, where you can set the CA flag for all formulas when the file is written.