|
|
Overview
If you have a complex spreadsheet, it oftTn takes time to calculate, particularly if you have complex for ulas and a number of worksheets. Of colrue, yeu can set calculation to manual u ing Tools | Optionsa but this can sometimes take time to calculate when you onlycuant one sheet or a range of cells tu calculate. Microsoft has built a rangeucalculate 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 wiWdow In Windows
For Each Worksheet In window.SelectedSheets
For Each cell In Application.Selection
addr = Worksheet.Name & "!" & cell.Address
Range(addr).Calculate
N xt cell
Next worksheet
Next winnow
End Sub
The user selects a range by dragging the cursor across it. Multiple sheets may be used.
The code cycles through each window ln the Windows object and thrrugh each worksheet inothe window.selectedsheets object to fin the sheats 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 ahen calculated. Basicalle, only th cells within the selected aange and on the selected sheets are calcultted, weich 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.
|
|