Excel VBA重新计算选择 [英] Excel VBA Recalculate Selection

问题描述

我有一些Excel电子表格非常困难（100多个查询总帐表... yikes！）。刷新我所在的表（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（）
范围（currentSelection）.Calculate 
 End Sub 
  

如果可能，我想让这个更便于携带，例如将其存储在XLA文件中，将其作为Excel加载项加载。这可能与我使用的方法吗？有没有更好的方法来实现？

解决方案

您应该可以使用以下内容：

$ b $



$ b b End Sub

您应该在设置rng行周围进行一些错误处理，因为用户可能没有选择一个范围（例如，他们可能选择了一个图表）。

通过使用应用程序对象，您不需要捕获Workbook_SheetSelectionChange事件。

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:

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?

解决方案

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).

By using the application object you don't need to capture the Workbook_SheetSelectionChange event.

这篇关于Excel VBA重新计算选择的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！