Extract Formulas From Cells Using VBA

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.

Sub ExtractOneFormula()

   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

Sub ExtractFormulas()

   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