Unable to assign formula to cell range in Excel

Question

Someone else's code in the project, that I am trying to fix up.

listO.Range(i, j).FormulaR1C1 = FormulaMatrix(i, j)

where FormulaMatrix(i, j) is always a String value. Whatever random/test value, I try with, is being assigned successfully, except when it is a formula, eg.

=IF(LENGTH([@Units])>0;[@SalesAmount]-[@DiscountAmount]0)

If I remove the = sign in the beginning of the formula, it gets assigned correctly, but then it's useless, because it's not a formula.

@Units, @SalesAmount, @DiscountAmount are references/names of columns.

So, when assigning a formula, I get an exception HRESULT: 0x800A03EC. I looked up in this answer in order to get explanation and followed some of the instructions there. I determined that my problem is the following: the problem happens due to a function entered in a cell and it is trying to update another cell.

Checked out also this post. I tried quite different (like putting just the formulas without = and then run over again and put the equal signs), but same problem.

I am clueless of how to approach this.

Where is this other function? What is it doing, and what cell is it trying to update? — nwhaught, May 15 '15 at 13:28
Is that Excel Interop? try directly pass the formula as string with .Value or .FormulaLocal instead of .FormulaR1C1. If I recall correctly that R1C1 thing never worked for me. — Esselans, May 15 '15 at 13:31
Also, the formula in your question is not valid...do you mean `=IF(LEN([@Units])>0,[@SalesAmount]-[@DiscountAmount],0)`? — nwhaught, May 15 '15 at 13:32
@nwhaught , the formula is fine. If, when I'm already in Excel, select the certain cell and manually insert this formula - it works. :) — Syspect, May 18 '15 at 06:42
@nwhaught , I tried with your suggested formula, as well, and I still get the exception... — Syspect, May 18 '15 at 07:31
_If, when I'm already in Excel, select the certain cell and manually insert this formula - it works. :)_ It doesn't mean, that the same formula text will work in VBA code. For example field separator might be different in cell and in VBA. The easiest way to check it is to use macro recorder: record entering the formula manually and check the resulting VBA code. — BrakNicku, May 18 '15 at 07:55
@ZygD I don't know, but I guess it is possible only in Excel. `FormulaLocal` accepts language/regional settings specific version of the formula, but ths code will fail in different language version, so it's probably more safe to use `FormulaR1C1` (or `Formula`) — BrakNicku, May 18 '15 at 11:47

score 4 · Answer 1 · edited May 23 '17 at 11:51

.formulalocalworks! (While .formula, .value and .formular1c1 don't.)

I've just started working with VB.NET and came into a very similar issue. This was my simplified data at first (Table1 in Sheet1):

before

Then after applying the code below I had this:

after

The whole code for the form:

Imports Excel = Microsoft.Office.Interop.Excel

Public Class Form1
    '~~> Define your Excel Objects
    Dim xlApp As New Excel.Application
    Dim xlWorkBook As Excel.Workbook
    Dim xlWorkSheet As Excel.Worksheet
    Dim strAddress As String = "C:\Temp\SampleNew.xlsx"
    Dim list1 As Object


    Private Sub btnOpen_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnOpen.Click
        '~~> Add a New Workbook (IGNORING THE TWO DOT RULE)
        xlWorkBook = xlApp.Workbooks.Open(strAddress)

        '~~> Display Excel
        xlApp.Visible = True

        '~~> Set the relevant sheet that we want to work with
        xlWorkSheet = xlWorkBook.Sheets("Sheet1")

        With xlWorkSheet

            '~~> Change the range into a tabular format
            list1 = .ListObjects("Table1")

        End With

        list1.range(2, 4).formulalocal = "=IF(LEN([@Month])>5;[@Income]-[@MoneySpent];0)"

        '~~> Save the file
        xlApp.DisplayAlerts = False
        xlWorkBook.SaveAs(Filename:=strAddress, FileFormat:=51)
        xlApp.DisplayAlerts = True

        '~~> Close the File
        xlWorkBook.Close()

        '~~> Quit the Excel Application
        xlApp.Quit()

        '~~> Clean Up
        releaseObject(xlApp)
        releaseObject(xlWorkBook)
    End Sub


    '~~> Release the objects
    Private Sub releaseObject(ByVal obj As Object)
        Try
            System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
            obj = Nothing
        Catch ex As Exception
            obj = Nothing
        Finally
            GC.Collect()
        End Try
    End Sub
End Class

@Siddharth Rout helped a lot to build this code, as he owns this awesome site: http://www.siddharthrout.com/

Haha yup. FormulaLocal is what I used on a project w/ Interop. After that I stop using Excel Interop because it needs to Excel be installed and if you change office version/language you code might stop working!. I know `infragistics` is paid but it Excel library is just awesome and you don't even need office. — Esselans, May 17 '15 at 23:30
Nope, I tried all `FormulaLocal`, `Value`, and `Formula` and neither works. I still get the same exception... — Syspect, May 18 '15 at 07:30
Can you compare my code to yours and come back with differences? Even better - **maybe you can create a demo**, so that everyone could recreate the error? This specific error is very very broad, as I've searched a while on the issue. I think one of the likely options would be that you have a loop somewhere in your code which starts counting from 0, while Excel would need to start it from 1. Again, please investigate your code, it would be a great help if you could paste a [Minimal, Complete, and Verifiable example](http://stackoverflow.com/help/mcve). — ZygD, May 18 '15 at 11:15

score 1 · Accepted Answer · answered May 19 '15 at 08:49

The error might be coming from your current data, respectively, the layout of the sheet. I would suggest you to check what is inside the listO.Range(i, j).FormulaR1C1 before you assign the formula.

I have had a case where the range has already got wrong data inside, and then strangely, I don't know why, I cannot assign the new formula.

If that is the case - try clearing the value of the range and then assigning the formula:

listO.Range(i, j).FormulaR1C1 = ""
listO.Range(i, j).FormulaR1C1 = FormulaMatrix(i, j)

Simple as that... The range had wrong values for each formula. There was some additional text put before each column reference, for some reason... That's what happens when you fix/upgrade someone else's code. — Syspect, May 19 '15 at 09:57

score -1 · Answer 3 · answered May 15 '15 at 17:47

The problem might be with your formula. Try this-

=IF(LEN([@Units])>0,[@SalesAmount]-[@DiscountAmount],0)

If this doesn't work, I would try using the .formula method instead of .formulaR1C1.. Is there any reason in particular you are using R1C1 references?

Unable to assign formula to cell range in Excel

3 Answers3