Excel Macro to Find and Calculate Precedents

Today’s Guest Author

Kintaar is the editor of Math for Mere Mortals, a blog about numerical calculations and programming for those of us who are not godlike experts. He writes about a variety of topics in the areas of financial modelling, data analysis, math, and science, and often includes Excel VBA scripts to demonstrate the topics. This post is about locating and recalculating precedent cells in Excel VBA. This macro is an essential part of Minimize() and MinimizeNonnegative(), which are Excel spreadsheet functions that automatically optimize cells without using the Solver.

Excel Macro to Find and Calculate Precedents

One way to speed up an Excel macro is to disable automatic calculation via the Application.Calculation property, but that faster speed comes at a price. If you want to recalculate, you have to recalculate everything by calling Application.Calculate(). But what if that takes too long, and all you really need is to recalculate the values of just a few cells while your macro is running?

Excel doesn’t yet offer a convenient way to find the precedents of a given cell. There is a Range.Precedents property, but with the caveat that it only works when the precedents are all on the same sheet. Most spreadsheet calculations span more than one sheet, so Range.Precedents is a no-go!

This VBA Excel macro solves the problem of Range.Precedents. Give the macro a range of cells, and it determines the precedents and recalculates them in the correct order. At that point you can recalculate your cells and be confident that their precedent cells’ values are up to date, with the added bonus that it only calculated what was necessary – no wasted time!

The only limitation is that it can’t recalculate precedents in closed workbooks, on hidden sheets, on protected sheets, or with circular references. In most situations these limitations shouldn’t be a problem.

Example Spreadsheet and Dependency Tree

A sample workbook with the code can be downloaded from here.

The diagram below is an example dependency tree. It shows how cells are dependent on each other and that they have to be calculated in a certain order, which is from the bottom to the top of the tree. In other words, the final results in F3 and G3 depend on everything below them being calculated first. The goal of this macro is essentially to reproduce this dependency tree to identify precedent cells and to recalculate those cells efficiently.

Checking for a Deeper Level

This part of the code is responsible for updating the level of any cell it encounters that is already in the dependency tree. As the algorithm traces precedent cells, it may have to update their levels more than once. B3 is one of the cells whose level is updated in this example.

' If this is already in the list of precedents, its level (and its precedents' levels) may need to be updated.
If dicAllPrecedents.Exists(strPrecedentAddress) Then
    
    ' Does the dictionary list a shallower level?  (If so, update it.  If not, leave it alone.)
    If dicAllPrecedents.Item(strPrecedentAddress) < lngLevel Then
        
        ' Replace the existing level with the updated, deeper level.
        dicAllPrecedents.Item(strPrecedentAddress) = lngLevel
        
        ' The precedent cell's own precedent cells also need to be updated.
        Call GetPrecedents(rngPrecedentRange, dicAllPrecedents, lngLevel + 1)
        
    End If
Else
    ' This item must not be in the dictionary.
    ' Add this item and its precedents as usual.
    Call dicAllPrecedents.Add(strPrecedentAddress, lngLevel)
    Call GetPrecedents(rngPrecedentRange, dicAllPrecedents, lngLevel + 1)
    
End If

Calculation Order and ArrangePrecedents()

Precedent cells have to be recalculated starting with the deepest level and working up. Cells at the same level have to be calculated together as a group, but not necessarily in any particular order. We are ok as long as Level-4 cells are calculated first, then the Level-3 cells, then Level 2, and so on. The function ArrangePrecedents() looks at the list of precedents (which are listed in the order in which they were discovered) and arranges them in order of decreasing level number. RecalculateRanges() does the actual calculating, and it is just a few lines of code because it knows that the precedent cells were sorted by their levels in the dependency tree.

Recursive functions have a penchant for gathering information out of order, so it is common to have to sort the results like we are doing here.

How to Use the Excel Macro to Recalculate Precedent Cells

Sub TestArrangePrecedents() demonstrates how to find and recalculate precedent cells.

Here are the general steps:

• Suppose your output cell is G3. Call GetAllPrecedents() to locate its precedents. This produces a Dictionary object that contains the addresses of G3’s precedent cells.
• Call ArrangePrecedents() to convert the Dictionary into an array of Range objects in calculation order.

Repeat the following steps as needed:

• Change one or more values in a worksheet, such as the value of A3.
• Call RecalculateRanges() to recalculate the precedent cells.
• Recalculate the output cell, such as G3, using its Calculate() method. G3 then contains the correct answer in light of any changes that occurred anywhere in the workbook.

Just one more thing…

When you use GetAllPrecedents(), be mindful of the size of the dependency tree you are navigating.

The NavigateArrows() method is just a graphical toy that was designed for drawing little arrows on the screen when you click a button, but we are using it to catalog a potentially exhaustive list of precedent cells. If your spreadsheet has formulas like SUM(A1:A10), that’s not a big deal. However, a formula like SUM(A1:ZZ999) has 701,298 precedents, not to mention that we have to call NavigateArrows() at least once on each of them. If each NavigateArrows() call takes one ten-thousandth of a second, then the process will take more than 140 seconds to finish. Until Microsoft updates the Range object to reference cells on more than one worksheet, this is the fastest way to navigate a dependency tree in the Excel object model. Happy hunting!

Advertisement