LibXL/Excel formula with reference to another worksheet

General
1

When I open an Excel spreadsheet in Excel that my Xojo app has created I get this alert:

We found a problem with some content in ‘My Ret Plan.xlsx’. Do you want us to try to recover as much as we can?

After opening the spreadsheet the repair log says

****Removed Records: Formula from /xl/worksheets/sheet1.xml

I have found that the problem is writing a formula with a reference to another worksheet such as this

theStr="=("+prevCol+theRow+"-1000)*(1 + Factors!"+thisCol+theRow+")"
call gSheet0.WriteFormula(row,i,theStr)

On the other hand this code causes no error

call gSheet0.WriteFormula(99,i,"=Transfers!"+col+"24")

I am out of ideas.

2

Could you please log the actual formula text you pass here?
I alias suggest you put it in a local variable for review in debugger.
e.g. is column a letter combination? and theRow a number?

3

Here is the value of theStr copied when I added a break

=(b5-Transfers!c5)*(1+Factors!c5)
4

And these are the names of the sheets at the time when you add the formula to the sheet?
This calculation works in Excel when you paste it there?
I wonder if Transfers or Factors may be keywords…

5

Christian,

Thanks for the thoughts. Yes, those are the names of the worksheets and the formula works when I paste it directly into the worksheet. Also, checked that the two worksheet names are not keywords.

6

I’ll ask Dmytro about this.
Which LibXL version maybe?

7

I am using MBS Xojo XL Plugin version 23.4

8

Found my error. A formula of this type was written in 60 columns. Of course I didn’t check all 60 columns. To translate between LibXL column numbers 0 to 60 and Excel column letter designations I created a comma separated string kColumns (a,b,c…ax,ay,az). Then I selected the column for the formula by

kColumn.NthField(",",i)

I only put 56 elements in the CSV so the formulas in the last few columns were missing a column letter.