VB.NET生成Excel时设置SI函数遇COM异常及公式不生效问题求助

Let's break down why you're hitting these problems and how to fix them cleanly:

Why the first approach throws a COM exception #

Excel's .Formula property requires American English syntax regardless of your regional settings. That means:

• Use IF instead of SI (SI is the localized version of the function)
• Use commas (,) as parameter separators instead of semicolons (;)
• Use dots (.) for decimal points instead of commas (,)

If you want to use your region's native syntax (like SI, ;, and , for decimals), you need to use the .FormulaLocal property instead of .Formula.

Solution 1: Use .FormulaLocal (matches your regional settings) #

This is the easiest fix since you can keep your original formula syntax:

Private Const quote As String = Chr(34)
With .Range("H15")
    .Characters.Font.Size = 10
    .HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter
    .FormulaLocal = "=SI(F15=" & quote & quote & ";" & quote & quote & ";G15-F15-0,5)"
End With

.FormulaLocal tells Excel to parse the formula using your current regional settings, so the semicolons and decimal commas will work correctly without throwing errors.

Solution 2: Use .Formula with American English syntax #

If you prefer to stick with .Formula (which works across all regional settings), adjust the formula to use American conventions:

Private Const quote As String = Chr(34)
With .Range("H15")
    .Characters.Font.Size = 10
    .HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter
    .Formula = "=IF(F15=" & quote & quote & "," & quote & quote & ",G15-F15-0.5)"
End With

Why the second approach doesn't work #

When you modify the cell's text via .Characters, Excel treats the content as plain text instead of a formula. SendKeys also won't work when Excel runs in the background because it requires the window to be active and focused. Instead of trying to hack around this, using .Formula or .FormulaLocal directly is the reliable way to set formulas programmatically.

Bonus: Force calculation if needed #

If you ever need to trigger a recalculation for the cell (though it shouldn't be necessary with the above fixes), you can use:

.Range("H15").Calculate()

Or recalculate the entire workbook:

.Application.CalculateFull()

内容的提问来源于stack exchange，提问作者Benoit Bouckaert