In Excel, formulas are the backbone of data analysis and calculations. Extracting these formulas using VBA can be a powerful tool for various purposes. In this guide, we'll explore step by step how to use VBA to extract formulas from cells.

Here are a few scenarios where we need to extract formulas from cells using vba

- To help in keeping track of changes made to formulas over time.
- Analyze formulas to identify potential errors or inconsistencies.
- Extracting formulas can help you analyze dependencies and relationships between different cells.
- To partially automate the documentation process, providing insights of the logic behind calculations.

Now let’s see how we can develop a macro to extract a formula from a cell using VBA. First let’s see how we can get the formula from a particular cell we want. Let’s consider this example sheet.

This worksheet has some values from B1 cell to B9 cell. Then I have put the following formula in cell B11.

=SUM(B1:B9)

Assume the name of the worksheet is “Sheet1”. Then the following subroutine will show the formula of cell B11 in a message box.

Dim WS As Worksheet

Set WS = Worksheets("Sheet1")

MsgBox WS.Range("B11").Formula

End Sub

Now we learnt how to get a formula from a particular cell using VBA. Next let’s look at how to get a list of all the formulas in a sheet with cell addresses. Let’s consider this example Excel sheet.

In this worksheet, Total sales in column D is calculated using formulas. For an example D2 cell has following formula

=B2*C2

Then Sales Ranking in column E is also calculated using formulas. For an example E2 cell has the following formula.

=RANK.EQ(D2,$D$2:$D$11,0)

Also % of Total Sales in column F is calculated using formulas. F2 cell has following formula

=D2/SUM($D$2:$D$11)

Then 3 fields(Grand Total Sales, Average Quantity Sold, Maximum Unit Price) in cells B13, B14 and B15 are calculated using following formulas.

Grand Total Sales | =SUM(D2:D11) |

Average Quantity Sold | =AVERAGE(B2:B11) |

Maximum Unit Price | =MAX(C2:C11) |

Then here is the macro to print all the formulas and their cell addresses in the immediate window

Dim WS As Worksheet

Dim Rng As Range

Set WS = Worksheets("Sheet2")

For Each Rng In WS.UsedRange

If Rng.HasFormula = True Then

Debug.Print "Formula in " & Rng.Address & ": " & Rng.Formula

End If

Next Rng

End Sub

In the above subroutine, the statement “If Rng.HasFormula = True Then” is used to identify the cells having formulas.

Also Rng.Address statement outputs the cell address of the cell and Rng.Formula statement output formula of that cell.

**Also read**

How to Add a Formula to a Cell Using VBA

Access Formula Bar Using Keyboard