Excel-VBA Solutions

Want to become an expert in VBA? So this is the right place for you. This blog mainly focus on teaching how to apply Visual Basic for Microsoft Excel. So improve the functionality of your excel workbooks with the aid of this blog. Also ask any questions you have regarding MS Excel and applying VBA. We are happy to assist you.

Pages

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.

Name

Email *

Message *