How to Extract Excel Formulas

Sep 23rd, 2013

How to Extract Excel Formulas

Share

I had a spreadsheet in Excel 2007. (I suspect the approach used here would also work in other versions of Excel, but I have not tried it.) I wanted to print out the formulas used in each cell. I did a couple of searches and wound up in a thread where they have advised me to create a module for the same. To add a new module to your excel file press Alt + F11. Following is the code that I used to get the list of all the formulas used in the excel file.

Sub ListFormulas()

‘ constants
Const ksDot = “.”
Const ksDoc = “_formula_doc”
Const ksXLSX = “xlsx”

‘ declarations
Dim I As Integer, J As Integer, K As Integer
Dim L As Integer
Dim iRows As Integer, iColumns As Integer
Dim sName As String, sFileName As String
Dim sNewName As String, sNewFileName As String
Dim sCellName As String

‘ start
‘ source workbook
With ActiveWorkbook
sName = .Name
sFileName = .FullName
I = InStr(StrReverse(sName), ksDot)
sNewFileName = Left$(sName, _
Len(sName) – I – 1) & ksDoc &
ksDot & ksXLSX
I = Application.SheetsInNewWorkbook
Application.SheetsInNewWorkbook = .Worksheets.Count
End With

‘ delete
If Dir(sNewFileName, vbNormal) <> “”
Then Kill sNewFileName

‘ add
Workbooks.Add
Application.SheetsInNewWorkbook = I
With ActiveWorkbook
.SaveAs sNewFileName
sNewName = .Name
End With
Workbooks(sName).Activate

‘ process
With ActiveWorkbook
For I = 1 To .Worksheets.Count
‘ format
Workbooks(sNewName).Worksheets(I).Cells.NumberFormat
= “@”
‘ name
Workbooks(sNewName).Worksheets(I).Name =
.Worksheets(I).Name
‘ formulas
With .Worksheets(I)
With .UsedRange
iRows = .Rows.Count
iColumns = .Columns.Count
End With
For K = 1 To iRows
For J = 1 To iColumns
If .Cells(K, J).HasFormula Then
sCellName = .Cells(K, J).Address
Else
sCellName = “”
End If
Workbooks(sNewName).Worksheets(I).Cells(K, J).Value =
sCellName + .Cells(K, J).Formula

Next J
Next K
End With
Next I
End With
‘ end

End Sub

Comments are closed.

Let's Discuss Your Project

Get free consultation and let us know your project idea to turn
it into an amazing digital product.

Let’s talk

NEWS & BLOG

Related Blogs

Why CFOs Are Moving from Hyperion to OneStream: A Complete Hyperion to OneStream Migration Guide

Hire Developer Jun 23rd, 2025

Why CFOs Are Moving from Hyperion to OneStream: A Compl...

Read more
AI Use Cases Across Industries: Examples in the Real World

Hire Developer Jun 2nd, 2025

AI Use Cases Across Industries: Examples in the Real Wo...

Read more
AI Development in 2025: Trends That Will Define the Next Generation of Business

Hire Developer May 14th, 2025

AI Development in 2025: Trends That Will Define the Nex...

Read more

INQUIRY

Let's get in touch

UNITED STATES

31236 Meadowview Square,
Delmar,
DE 19940, USA

Sales: +1 (415) 230 0051

UNITED KINGDOM

13 Layton Road,
Hounslow,
London, TW3 1YJ

Sales: +44 7404 607567

INDIA

101, Kalasagar Shopping Hub, Sattadhar, Gujarat 380061

+91 999-894-5667

For Project Inquiries

Please enable JavaScript in your browser to complete this form.
emailsales@solutionanalysts.com emailinfo@solutionanalysts.com emailcareer@solutionanalysts.com skypebiz.solutionanalysts