使用VBA在Excel计算单元中编写公式

I'm trying to use VBA to write a formula into a cell in Excel. My problem is that when I use a semicolon (;) in my formula, I get an error:

我正在尝试使用VBA在Excel中将公式写到单元格中。我的问题是，当我在公式中使用分号(;)时，我得到一个错误:

Error 1004

错误1004

My macro is the following :

我的宏如下:

Sub Jours_ouvres()
    Dim Feuille_Document As String
    Feuille_Document = "DOCUMENT"        
    Application.Worksheets(Feuille_Document).Range("F2").Formula = "=SUM(D2;E2)"    
End Sub

5 个解决方案

#1

You can try using FormulaLocal property instead of Formula. Then the semicolon should work.

你可以尝试使用公式性质而不是公式。然后分号就可以了。

#2

The correct character to use in this case is a full colon (:), not a semicolon (;).

在这种情况下，正确的字符是一个完整的冒号(:)，而不是分号(;)。

#3

The correct character (comma or colon) depends on the purpose.

正确的字符(逗号或冒号)取决于目的。

Comma (,) will sum only the two cells in question.

逗号(，)将只对所讨论的两个单元格求和。

Colon (:) will sum all the cells within the range with corners defined by those two cells.

结肠(:)将所有的细胞与这两个细胞所定义的角相结合。

#4

Treb, Matthieu's problem was caused by using Excel in a non-English language. In many language versions ";" is the correct separator. Even functions are translated (SUM can be SOMMA, SUMME or whatever depending on what language you work in). Excel will generally understand these differences and if a French-created workbook is opened by a Brazilian they will normally not have any problem. But VBA speaks only US English so for those of us working in one (or more) foreign langauges, this can be a headache. You and CharlesB both gave answers that would have been OK for a US user but Mikko understod the REAL problem and gave the correct answer (which was also the correct one for me too - I'm a Brit working in Italy for a German-speaking company).

Treb, Matthieu的问题是在非英语语言中使用Excel造成的。在许多语言版本中，“;”是正确的分隔符。甚至函数也可以被翻译(SUM可以是SOMMA, SUMME或其他，这取决于您使用的语言)。Excel一般会理解这些差异，如果一个巴西人打开了一个法国创建的工作簿，他们通常不会有任何问题。但是VBA只会说我们英语，所以对于那些在一个(或多个)外国语言中工作的人来说，这可能会让人头疼。你和查尔斯布都给出了对美国用户来说应该没问题的答案，但米科发现了真正的问题，并给出了正确的答案(对我来说也是正确的答案——我是在意大利一家讲德语的公司工作的英国人)。

#5

-1

I don't know why, but if you use

我不知道为什么，如果你用的话

(...)Formula = "=SUM(D2,E2)"

(',' instead of ';'), it works.

('，'而不是';')

If you step through your sub in the VB script editor (F8), you can add Range("F2").Formula to the watch window and see what the formular looks like from a VB point of view. It seems that the formular shown in Excel itself is sometimes different from the formular that VB sees...

如果您在VB脚本编辑器(F8)中遍历子文件，您可以添加范围(“F2”)。公式到手表窗口，看看形式是什么样子从VB的观点。似乎Excel中显示的表单有时与VB看到的表单有所不同……

#1