vba 当公式重新计算中仅特定 Excel 单元格更改时调用函数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12631679/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Call a function when only a specific Excel cell changes on Formula Recalculation
提问by Shorn Jacob
As far as i know, Worksheet_Calculateis called when the value of any cell in a worksheet changes on Formula recalculation.
据我所知，Worksheet_Calculate当公式重新计算时工作表中任何单元格的值发生变化时调用。
Is there a way so that i need a function to be called only when a specific cell changes on Formula Recalculation
有没有办法让我只需要在公式重新计算中特定单元格更改时才调用函数
采纳答案by Robert Co
Let me see if I interpret your question correctly. You want to know if it is possible to only kickoff a macro if a particular cell (or group of cells) is changed.
让我看看我是否正确解释了您的问题。您想知道是否可以仅在更改特定单元格（或单元格组）时启动宏。
The answer is Yes. To tweak Ed's code a little.
答案是肯定的。稍微调整 Ed 的代码。
Private Sub Worksheet_Change(byval target as range)
If Not Intersect(target.address, Range("YourCells")) is Nothing Then
MyMacro()
End If
End Sub
I think your use of "function" is throwing people off. That's why Ed's answer is so elaborate.
我认为您对“功能”的使用使人们望而却步。这就是为什么 Ed 的回答如此详尽的原因。
Ok. It is possible that you stated your question correctly and you just want to gain efficiency. In that case, Ed's answer solves your immediate problem, but will cause the spreadsheet NOT to calculate automatically when you change other cells.
好的。您可能正确地陈述了您的问题，而您只是想提高效率。在这种情况下，Ed 的回答解决了您当前的问题，但会导致电子表格在您更改其他单元格时不会自动计算。
回答by Ed Bolton
To make something happen when a specific cell is changed, you need to embed the relevant selection change event within the file Worksheet_Change(byval target as Range). We can re-calculate a worksheet when your cell changes as follows:
要在更改特定单元格时发生某些事情，您需要在文件中嵌入相关的选择更改事件Worksheet_Change(byval target as Range)。当您的单元格更改时，我们可以重新计算工作表，如下所示：
Private Sub Worksheet_Change(byval target as range)
If target.address = Range("YourCell").Address Then Application.Calculate
End Sub
Now what you want to do is switch off calculations the rest of the time. If you only want to switch off calculations on the single sheet (and not your whole file), you will need to turn calculations off when it is activated, and on when deactivated e.g.
现在您要做的是在其余时间关闭计算。如果您只想关闭单个工作表（而不是整个文件）上的计算，则需要在激活时关闭计算，并在停用时打开计算，例如
Private Sub Worksheet_Activate
Application.Calculation = xlCalculationManual
End Sub
Private Sub Worksheet_Deactivate
Application.Calculation = xlCalculationAutomatic
End Sub
Of course, your requirements to re-calculate may be considerably more complex than the example above. Firstly, you may open the file whilst on the sheet in question in which case you should use the Workbook_Open event to detect your sheet, and set calculations accordingly
当然，您重新计算的要求可能比上面的示例复杂得多。首先，您可以在有问题的工作表上打开文件，在这种情况下，您应该使用 Workbook_Open 事件来检测您的工作表，并相应地设置计算
Then you may have several cells that may require some sort of calculation. Presumably the reason you want to switch off calculations is that the file is running too slowly. If so, and you are identifying all the input cells, you could enter the outputs using code. One method would be to enter the formulas using this guide to entering formulas in Excel Visual Basic. You could then replace the formula with the calculated value e.g. Range("YourCell") = Range("YourCell").Value...so stopping the number of formulas in the sheet constantly growing
然后您可能有几个可能需要某种计算的单元格。据推测，您要关闭计算的原因是文件运行速度太慢。如果是这样，并且您正在识别所有输入单元格，则可以使用代码输入输出。一种方法是使用本指南输入公式以在 Excel Visual Basic 中输入公式。然后，您可以用计算值替换公式，例如Range("YourCell") = Range("YourCell").Value......所以停止工作表中公式的数量不断增长
