Chapter 23: Calculating a Range

Overview

If you have a complex spreadsheet, it often takes time to calculate, particularly if you have complex formulas and a number of worksheets. Of course, you can set calculation to manual using Tools Options, but this can sometimes take time to calculate when you only want one sheet or a range of cells to calculate. Microsoft has built a range.calculate method into the Excel object model, but strangely enough, it is not available from the spreadsheet menu. To use it, you need the following code in a module:

 Sub range_calculate() 
 
 For Each window In Windows 
 
 For Each Worksheet In window.SelectedSheets 
 
 For Each cell In Application.Selection 
 
 addr = Worksheet.Name & "!" & cell.Address 
 
 Range(addr).Calculate 
 
 Next cell 
 
 Next worksheet 
 
 Next window 
 
 End Sub 

The user selects a range by dragging the cursor across it. Multiple sheets may be used.

The code cycles through each window in the Windows object and through each worksheet in the window.selectedsheets object to find the sheets the user has selected.

Each cell within the selection is then gone through. The variable addr holds the selected worksheet name and is concatenated with the cell address using the ! character.

The cell is then calculated. Basically, only the cells within the selected range and on the selected sheets are calculated, which saves a large amount of time.

Try putting some data onto a spreadsheet, including formulas. Set the calculation to manual by using Tools Options and selecting the Calculation tab, and then set the radio button to Manual.

Change the data and then select the range. Leave some cells with formulas outside the range. Run the code and check the results. The formulas within the range will recalculate; the ones outside will not.