刷新 Excel VBA 函数结果
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11045/
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
Refresh Excel VBA Function Results
提问by Brian Sullivan
How can I get a user-defined function to re-evaluate itself based on changed data in the spreadsheet?
如何让用户定义的函数根据电子表格中更改的数据重新评估自身？
I tried F9and Shift+F9.
我试过F9和Shift+F9。
The only thing that seems to work is editing the cell with the function call and then pressing Enter.
唯一似乎有效的是使用函数调用编辑单元格，然后按 Enter。
回答by vzczc
You should use Application.Volatilein the top of your function:
您应该Application.Volatile在函数的顶部使用：
Function doubleMe(d)
Application.Volatile
doubleMe = d * 2
End Function
It will then reevaluate whenever the workbook changes (if your calculation is set to automatic).
然后它会在工作簿更改时重新评估（如果您的计算设置为自动）。
回答by Robert Mearns
Some more information on the F9 keyboard shortcuts for calculation in Excel
有关在 Excel 中进行计算的 F9 键盘快捷键的更多信息
- F9Recalculates all worksheets in all open workbooks
- Shift+ F9Recalculates the active worksheet
- Ctrl+Alt+ F9Recalculates all worksheets in all open workbooks (Full recalculation)
- Shift+ Ctrl+Alt+ F9Rebuilds the dependency tree and does a full recalculation
- F9重新计算所有打开的工作簿中的所有工作表
- Shift+F9重新计算活动工作表
- Ctrl+ Alt+F9重新计算所有打开的工作簿中的所有工作表（完全重新计算）
- Shift+ Ctrl+ Alt+F9重建依赖树并进行完整的重新计算
回答by Brian Sullivan
Okay, found this one myself. You can use Ctrl+Alt+F9to accomplish this.
好吧，我自己找到了这个。您可以使用Ctrl+ Alt+F9做到这一点。
回答by Brian Sullivan
If you include ALL references to the spreadsheet data in the UDF parameter list, Excel will recalculate your function whenever the referenced data changes:
如果您在 UDF 参数列表中包含对电子表格数据的所有引用，则只要引用的数据发生变化，Excel 就会重新计算您的函数：
Public Function doubleMe(d As Variant)
doubleMe = d * 2
End Function
You can also use Application.Volatile, but this has the disadvantage of making your UDF always recalculate - even when it does not need to because the referenced data has not changed.
您也可以使用Application.Volatile，但这有一个缺点，就是让您的 UDF 总是重新计算——即使它不需要，因为引用的数据没有改变。
Public Function doubleMe()
Application.Volatile
doubleMe = Worksheets("Fred").Range("A1") * 2
End Function
回答by ayman
To switch to Automatic:
切换到自动：
Application.Calculation = xlCalculationAutomatic
To switch to Manual:
切换到手动：
Application.Calculation = xlCalculationManual
回答by MfJ
This refreshes the calculation better than Range(A:B).Calculate:
这比Range(A:B).Calculate以下更能刷新计算：
Public Sub UpdateMyFunctions()
Dim myRange As Range
Dim rng As Range
' Assume the functions are in this range A1:B10.
Set myRange = ActiveSheet.Range("A1:B10")
For Each rng In myRange
rng.Formula = rng.Formula
Next
End Sub
回答by alex303411
The Application.Volatiledoesn't work for recalculating a formula with my own function inside. I use the following function:
Application.CalculateFull
在Application.Volatile不重新计算公式以我自己的函数里面工作。我使用以下功能：
Application.CalculateFull
回答by Gene Sorg
I found it best to only update the calculation when a specific cell is changed. Here is an example VBA code to place in the "Worksheet" "Change" event:
我发现最好只在更改特定单元格时更新计算。这是放置在“工作表”“更改”事件中的示例 VBA 代码：
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("F3")) Is Nothing Then
Application.CalculateFull
End If
End Sub
回答by Prashanth
Public Sub UpdateMyFunctions()
Dim myRange As Range
Dim rng As Range
'Considering The Functions are in Range A1:B10
Set myRange = ActiveSheet.Range("A1:B10")
For Each rng In myRange
rng.Formula = rng.Formula
Next
End Sub
