Excel VBA Recalculate Selection

excel xla vba
Scott picture Scott · Dec 16, 2009 · Viewed 19.2k times · Source

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?

Answer

Chris Spicer picture Chris Spicer · Dec 17, 2009

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.

Related questions

How to stop Excel storing the absolute path to an XLA?

I have an XLA file that is to be deployed to a number of users in the organisation as an Excel add-in. My intention is to deploy it to a directory in the user's "documents and settings" folder in "Application …

Read More
Excel 2003 - How to build my own XLA?

How can you make the .xla file if you want to create your own xla? I have the code, classes, shapes, etc....what is the process to making an xla file to point to? I know that I have to …

Read More
Is there a way to crack the password on an Excel VBA Project?

I've been asked to update some Excel 2003 macros, but the VBA projects are password protected, and it seems there's a lack of documentation... no-one knows the passwords. Is there a way of removing or cracking the password on a VBA …

Read More