Adding formula to Excel with C# - making the formula shown

Question

I wanted to add formulas to an Excel workSheet.
I managed to do so with the Formula property.

The problem is that when I open the worksheet in Excel, I can see that the formula works - but I can only see the result in the cell. I can't see the formula that was calculated in the Formula Bar at the top of Excel.

Obviously if I enter a formula in Excel itself I can see the result in the cell and the formula in the Formula Bar.

Some of my code:

for (int i = 0; i < nOfColumns / 3; i++)
{
    Range cells = workSheet.Range[workSheet.Cells[2, i * 3 + 3], workSheet.Cells[lastRowNumber, i * 3 + 3]];
    cells.FormulaR1C1 = "=IF(EXACT(RC[-2],RC[-1]),TRUE,ABS(RC[-2]/RC[-1]-1))";
}

below is a test code. even after I save the workbook - the FormulaHidden is false and I can successfully retrieve the formula insterted. really frustrated

        Microsoft.Office.Interop.Excel.Application excelApp = null;
        Workbooks workBooks = null;
        Workbook workBook = null;
        Worksheet workSheet;

        try
        {

            excelApp = new Microsoft.Office.Interop.Excel.Application();
            excelApp.DisplayAlerts = false;

            workBooks = excelApp.Workbooks;
            workBook = workBooks.Open(filePath, AddToMru: false);
            workSheet = workBook.Worksheets.get_Item(1);

            int nOfColumns = workSheet.UsedRange.Columns.Count;
            int lastRowNumber = workSheet.UsedRange.Rows.Count;

            Range rng = workSheet.Range["C1"];
            rng.Formula = "=SUM(B2:B4)";
            String formula = rng.Formula; //retrieve the formula successfully

            rng.FormulaHidden = false;
            workSheet.Unprotect();

            workBook.SaveAs(filePath, AccessMode: XlSaveAsAccessMode.xlExclusive);

            formula = rng.Formula;  //retrieve the formula successfully
            bool hidden = rng.FormulaHidden;

        }
        catch (Exception e)
        {
            throw;
        }
        finally
        {
            if (workBook != null)
            {
                workBook.Close();
                workBook = null;
            }
            if (workBooks != null)
            {
                workBooks.Close();
                workBooks = null;
            }
            if (excelApp != null)
            {
                excelApp.Quit();
                excelApp = null;
            }
        }
    }

Anyone know how to make the formula shown, when adding the formulas programatically ?

Please post some relevant code. Like the bit where you set the formula on the cell — musefan, Jan 16 '12 at 10:49
Try making your code retrieve the formula from the cells immediately after you have written it. If thats OK then something later on in your code is overwriting it, probably with a pastespecial values — Charles Williams, Jan 16 '12 at 13:38
Charles, as you can see in the updated POST, i mange to retrieve the formula successfully. its a simple code - nothing to do with pasteSpecial — Amico, Jan 19 '12 at 09:23

score 3 · Accepted Answer · answered Jan 19 '12 at 09:44

finally !!! figured it out. this behavior is caused by the SaveAs flags. changed

workBook.SaveAs(filePath, AccessMode: XlSaveAsAccessMode.xlExclusive);

to

workBook.SaveAs(filePath, AccessMode: XlSaveAsAccessMode.xlShared);

now the only thing left is to understand what exactly is the different between the two flags. :)

shahkalpesh · Answer 2 · 2012-01-16T13:29:15.713

1

Hiding the formula (by checking Hidden checkbox on Format Cells dialog) & protecting the worksheet (thereafter) will cause the formula to not show in the formula bar.

Example VBA code

Range("C1").FormulaHidden = True    'set this property to false to make formula visible.
Sheet1.Protect

EDIT: In order to see the formula in the formula bar

Range("C1").FormulaHidden = False
Sheet1.Unprotect

edited Jan 16 '12 at 13:29

answered Jan 16 '12 at 11:13

shahkalpesh

33,172
3
63
88

hey shahkalpesh, I tried your suggested. I updated the original post with a complete test code that not working. – Amico Jan 19 '12 at 08:56

score 0 · Answer 3 · answered Jan 16 '12 at 11:03

0

Go to the Formula tab on the tool bar, and click "Show Formulas".

enter image description here

answered Jan 16 '12 at 11:03

BG100

4,481
2
37
64

no - Show Formulas shows the formula in the cell. does nothing with the formula bar. in my case, pressing the Show Formula doesn't do anything. I think the problem is that by "adding" a formula - its create the formula, calculates it and actually set the cell's value the result of the formula. it doesnt save the formula that was generated. still stuck – Amico Jan 16 '12 at 11:09
1

Ok... just as a test, try typing "=1+2" into a cell... it should show "3" as the value in the cell, but what does the formula bar say? – BG100 Jan 16 '12 at 11:14
the cell shows 3. the formula bar shows =1+2. enabling Show Formulas makes the value of the cell =1+2 as well – Amico Jan 16 '12 at 11:15
In that case it sounds like it's working as it should, not sure what your issue is. – BG100 Jan 16 '12 at 11:17
its working when i insert formulas manually within Excel. when i'm trying to programatically insert a formula in C#, the formula is not showing - only the result – Amico Jan 16 '12 at 11:51
Ok, that wasn't clear from your original question. Please can you update your question and post the code you are using to do this? – BG100 Jan 16 '12 at 11:52

score 0 · Answer 4 · answered Jan 19 '12 at 09:34

I think localization could be involved in this weird behaviour.

Some time ago, working in Excel, I had the impression that formulas got stored in localized language (I was using italian), then undergo a conversion when compiled. This could make sense, because localized constants are an essential part of the spreadsheet data.

I'm sorry I haven't now Excel available, so I can't be more precise, but I think you could try to localize to english your spreadsheet, or set the formula text in your local language.

Adding formula to Excel with C# - making the formula shown

4 Answers4