Excel - 使用VBA插入公式

Question

大家好，

我正在为想要通过VBA插入的公式苦苦挣扎了一个小时：

Formula = "=IFERROR(VLOOKUP(Q" & j & ";Table1[#All];2;FALSE);"""")"
ThisWorkbook.Worksheets("Sheet1").Cells(j, "AE").FormulaArray = Formula

我收到了以下的错误信息：

运行时错误 '1004' - 应用程序定义或对象定义错误

方括号或双引号有问题吗？

谢谢！

- user1688175

Answer 1

用逗号代替分号：

Formula = "=IFERROR(VLOOKUP(Q" & j & ",Table1[#All],2,FALSE),"""")"

在函数参数之间，OpenOffice 使用分号进行分隔，Excel 通常使用逗号，而在上述方式中设置公式时，必须始终使用逗号。

- Bathsheba

---

错误的，这取决于您的区域设置。我来自比利时，使用;来分隔参数。 - CustomX

不过我仍然会首先检查这个；否则这个公式看起来没问题；引号都是正确的，Table1[#All] 是另一个可能的罪魁祸首，但我们需要查看工作簿才能确定。 - Bathsheba

嗯，没错，但说 Excel 只使用逗号是不准确的 ;) 如果您编辑您的答案，我可以取消我的踩，我看到 TS 已经接受了它作为答案 ;) - CustomX

我像一只猫一样接受了你的负评，我的朋友；-) 实际上，我不知道你可以在Excel中更改函数分隔符。为了严谨起见，我建议您需要一种访问正在使用的分隔符的方法，以便上面的公式分配在Excel用户之间是可移植的。 - Bathsheba

在VBA中，.Formula、.FormulaArray和.FormulaR1C1使用国际分隔符（即,），而.FormulaLocal和.FormulaR1C1Local使用用户的语言设置（因此如果您的语言设置为;，则可以使用;）。因此，在将值赋给.FormulaArray时，正确的做法是始终使用,。 - chris neilsen

@chrisneilsen，感谢您的评论，我之前不知道这个。现在感觉自己像个白痴:$ 我认为我严重混淆了它们。 - CustomX

Answer 2

在任何语言中编程，包括VBA，最好不要限制用户使用特定的区域设置或特定版本的Excel。 因此，应该避免以下情况：

Formula = "=IFERROR(VLOOKUP(Q" & j & ";Table1[#All];2;FALSE);"""")"
ThisWorkbook.Worksheets("Sheet1").Cells(j, "AE").FormulaArray = Formula

当您确定确切的用户环境时，最好使用此方法：

s = Application.International(xlListSeparator)
Formula = "=IFERROR(VLOOKUP(Q" & j & s +"Table1[#All]" + s + "2" + s + "FALSE)" + s + """"")"
ThisWorkbook.Worksheets("Sheet1").Cells(j, "AE").FormulaArray = Formula

顺便提一下，我并没有检查括号等公式的正确性，但只是指出了列表分隔符的正确用法，以及如何以正确的方式在单元格中使用VBA代码插入公式。

此外，正如之前的帖子所说，当你打开Excel时，它可能会自动更改公式。然而，Excel不会自动更改VBA代码，因此请注意并关注VBA中正确的代码。

- Silvestr

Answer 3

根据不同地区的设置，列表分隔符（也用于在函数中分隔参数）可以是分号或逗号。这适用于将公式键入单元格时。

Excel在打开文件时，会根据当前计算机的区域设置动态调整列表分隔符（和函数名称）。

例如，如果具有使用列表分隔符;的德国区域设置的用户保存文件，然后使用具有列表分隔符,的美国区域设置的用户打开相同的文件，则Excel会自动调整公式中的德国列表分隔符。

但是，在编写VBA时，您始终需要使用美式英语约定的列表分隔符，即逗号。

- teylyn