Excel VBA 重新计算选择
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1911490/
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
Excel VBA Recalculate Selection
提问by Scott
I've got some Excel spreadsheets that are hitting the database pretty hard (100+ queries against the general ledger table... yikes!). Refreshing just the sheet I'm on (SHIFT+F9) is helpful in some spreadsheets, but I wanted a way to refresh just the selected cells. I'm came up with the following code, placed in the ThisWorkbook object:
我有一些 Excel 电子表格，它们对数据库的影响非常大（对总分类帐表进行了 100 多次查询......哎呀！）。仅刷新我所在的工作表 (SHIFT+F9) 在某些电子表格中很有帮助，但我想要一种仅刷新选定单元格的方法。我想出了以下代码，放在 ThisWorkbook 对象中：
Dim currentSelection As String
Private Sub Workbook_Open()
Application.OnKey "+^{F9}", "ThisWorkbook.RecalculateSelection"
End Sub
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
currentSelection = Target.Address
End Sub
Private Sub RecalculateSelection()
Range(currentSelection).Calculate
End Sub
If possible, I'd like to make this more portable, such as storing it in an XLA file and loading it as an Excel addin. Is this possible with the method I'm using? Is there a better way to achieve this?
如果可能，我想让它更便携，例如将其存储在 XLA 文件中并将其作为 Excel 插件加载。这可能与我使用的方法有关吗？有没有更好的方法来实现这一目标？
回答by Chris Spicer
You should be able to use the following:
您应该能够使用以下内容：
Public Sub RecalculateSelection()
Dim rng As Range
Set rng = Application.Selection
rng.Calculate
End Sub
You should place some error handling around the 'Set rng' line, as the user may not have selected a range (e.g. they may have selected a chart).
您应该在“Set rng”行周围放置一些错误处理，因为用户可能没有选择一个范围（例如，他们可能选择了一个图表）。
By using the application object you don't need to capture the Workbook_SheetSelectionChange event.
通过使用应用程序对象，您不需要捕获 Workbook_SheetSelectionChange 事件。
回答by Anonymous Type
if your using the method from the accepted answer above you should first check that...
如果您使用上面接受的答案中的方法，您应该首先检查...
If Not Selection Is Nothing Then
If TypeName(Application.Selection) = "Range" Then
Dim Rng As Range
Set Rng = Application.Selection
Rng.Calculate
End If
End If
Also you might like to add it to the sheet and sheetname context menus. The name of the two commandbars that you need to do that are... "Cell" and "Ply"
此外，您可能希望将其添加到工作表和工作表名称上下文菜单中。您需要执行的两个命令栏的名称是...“Cell”和“Ply”
回答by Charles Williams
If you just want to recalculate the currently selected cells, ignoring cells that are dependent on them you can use my RangeCalc addin, downloadable from http://www.decisionmodels.com/downloads.htm
如果您只想重新计算当前选定的单元格，忽略依赖它们的单元格，您可以使用我的 RangeCalc 插件，可从http://www.decisionmodels.com/downloads.htm下载
