Sorx Sorx - 4 months ago 15
Vb.net Question

VB.Net FormulaR1C1 doesn't work

The FormulaR1C1 Method doesn't work as it's supposed to.

objExcel = CreateObject("Excel.Application")
objWorkbook = objExcel.Workbooks.Add
Dim Formula As String
Formula = "=SUMME(Z1S1;Z2S1)"
For i = 1 To 5 Step 1
objWorkbook.Worksheets("Tabelle1").Cells(i, 1).FormulaR1C1 = 5
objWorkbook.Worksheets("Tabelle1").Cells(i, 5).FormulaR1C1 = "Text"
objWorkbook.Worksheets("Tabelle1").Cells(i, 3).FormulaR1C1 = Formula
Next


This should give me 10 in the third column 5 times, but it doesn't. I even tried it with:

Formula = "=SUMME(Z[0]S[-2];Z[1]S[-2])"


but that doesn't work either. The Loop just breaks when coming to the assigment line :( . If I try it with

Formula = "=SUMME(Z1S1,Z2S1)"


It executes completely but it doesn't work for excel because then it says =SUMME('Z1S1';'Z2S1') in the Excel Field

Answer

You have to use the Range.FormulaLocal property or Range.FormulaR1C1Local property if you plan to use a string that contains non-EN-US function. VB.Net expects a EN-US formula and will translate that to the regional language of the worksheet.

Formula = "=SUM(Z1S1, Z2S1)"
'or depending on your requiements,
Formula = "=SUM(Z1S1:Z2S1)"
.Formula = Formula

Formula = "=SUMME(Z1S1;Z2S1))"
'or depending on your requiements,
Formula = "=SUMME(Z1S1:Z2S1)"
.FormulaLocal = Formula

Formula = "=SUMME(R1C26;R2C26))"
'or depending on your requiements,
Formula = "=SUMME(R1C26;R2C26)"
.FormulaLocalR1C1 = Formula

Don't confuse xlA1 cell references with xlR1C1 references. R23 would mean Cells(23, 18) in xlA1 and 23:23 in xlR1C1.

Note that using the Range.Formula property or Range.FormulaR1C1 property enforces the use of a comma (EN-US standard) instead of a semicolon as a system list separator.

Comments