VBA为单元格设置公式

Question

我试图使用一个(动态创建的)工作表名称和固定单元格地址为单元格设置公式。我使用了以下代码行，但似乎无法使其工作：

"=" & strProjectName & "!" & Cells(2, 7).Address

任何关于为什么这不起作用的建议或引导都将不胜感激。

- Ryuu

---

我没有尝试过你的公式，但作为替代方案，你总是可以在工作表模块中创建一个变化事件，并通过编程进行计算。 - html_programmer

“strProjectName”的示例值是什么？您是否在尝试引用不存在的工作表或工作簿？ - Stewbob

@Ryuu，那是你的问题。当你放置那个公式时，Excel会立即尝试引用它。当它找不到工作簿时，它会打开文件浏览器，以便你可以选择一个有效的工作簿。 - Stewbob

@Stewbob，我希望那不是问题所在。我现在会尝试并回复你。 - Ryuu

@Ryuu，已经创建了一个。 - Stewbob

Answer 1

我不确定你的情况下哪里出了问题，但是以下代码将在单元格A1中放入一个公式，该公式将检索单元格G2中的值。

strProjectName = "Sheet1"
Cells(1, 1).Formula = "=" & strProjectName & "!" & Cells(2, 7).Address

strProjectName所引用的工作簿和工作表必须在公式放置时存在。Excel将立即尝试计算该公式。您可能可以通过关闭自动重算直到工作簿存在来阻止这种情况发生。

- Stewbob

---

这就是我一直在做的，但它并没有解决公式 - 只是显示#REF！如果有帮助的话，每当我运行宏时，在代码通过那个点之后，它会打开一个文件浏览对话框。 - Ryuu

@Ryuu，如果它要求输入文件名，则strProjectName指向的工作表名称无效。公式的完整引用格式为**[文件名]工作表名称！地址**。您可以省略[文件名]以使用当前文件，也可以省略[文件名]工作表名称以使用当前工作表，但这些是单元格引用中唯一允许的组合。 - SeanC

如果你的项目名称中有空格，你需要在项目名称前后加上单引号。 - pashute

Answer 2

尝试：

.Formula = "='" & strProjectName & "'!" & Cells(2, 7).Address

如果您的工作表名称（strProjectName）中有空格，则需要在公式字符串中包含单引号。

如果这样做没有解决问题，请提供更多关于具体错误或失败的信息。

更新

在评论中，您表示正在用下划线替换空格。也许您正在执行类似以下操作：

strProjectName = Replace(strProjectName," ", "_")

但是如果您没有将更改推送到 Worksheet.Name 属性，那么您可以期望发生以下情况：

1. 文件浏览对话框出现
2. 公式返回 #REF 错误

这两种情况的原因是您正在传递一个不存在的工作表的引用，这就是为什么会出现 #REF 错误的原因。文件对话框是希望让您更正该引用，指向一个包含该工作表名称的文件。当您取消时，预计会出现 #REF 错误。

因此，您需要执行以下操作：

Worksheets(strProjectName).Name = Replace(strProjectName," ", "_")
strProjectName = Replace(strProjectName," ", "_")

那么，你的公式应该能够正常工作。

- David Zemens

---

我通过将空格替换为下划线来处理空格问题。没有实际上的错误，只是无法解析公式（显示#REF!）如果有帮助的话，每当我运行宏时，在代码中通过该点后，它会打开一个文件浏览对话框。 - Ryuu

如果您没有将“REPLACE”函数推送到“Worksheet.Name”属性，那么这正是我所期望的。请参见上面的修订。 - David Zemens

@Stewbob上面的评论指出了错误的根本原因。在创建表格之后放置公式。然后你就可以顺利完成了。 - David Zemens

谢谢您的帮助。只是订购问题。新手错误，但我只用宏做了两天的东西。有点预料到这会是答案，但还是希望不是这样... - Ryuu

Answer 3

如果Cells(1, 1).Formula引发了1004错误，就像在我的情况下一样，请将其更改为：

Cells(1, 1).FormulaLocal

- Eneas Gesing

Answer 4

如果您想直接进行地址操作，则必须存在工作表。

关闭自动重新计算会有所帮助 :)

但是...您仍然可以间接获取值...

.FormulaR1C1 = "=INDIRECT(ADDRESS(2,7,1,0,""" & strProjectName & """),FALSE)"

在插入公式时，由于strProjectName工作表不存在，它将返回#REF错误。

但在此工作表出现后，Excel将重新计算公式，并显示正确的值。
缺点是没有追踪功能，因此如果移动单元格或更改工作表名称，公式将不会根据变化进行调整，就像直接寻址一样。

- Carnifex