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 should you hire React Native developers for your project?

Hire Developer Jun 19th, 2023

Why should you hire React Native developers for your pr...

Read more
Seven Wearable App Technology Trends to Watch Out for in 2022

Hire Developer Dec 28th, 2020

Seven Wearable App Technology Trends to Watch Out for i...

Read more
How On-demand Grocery Delivery App Benefits Your Store and Expands Business

Hire Developer Jun 9th, 2020

How On-demand Grocery Delivery App Benefits Your Store ...

Read more

INQUIRY

Let's get in touch

UNITED STATES

31236 Meadowview Square,
Delmar, DE 19940, USA

Sales: +1 667 771 6758

UNITED KINGDOM

13 Layton Road, Hounslow,
London, TW3 1YJ

Sales: +44 7404 607567

INDIA

2nd Floor, Sun Avenue One, Bhudarpura, Ayojan Nagar, Nr. Shyamal Cross Road, Ahmedabad, Gujarat-380006

Sales: +91 635-261-6164

For Project Inquiries

biolah

hariantulis

depo 25 bonus 25

depo 25 bonus 25

mndrmndr.com

bonusdeposit.net

https://www.greentourstanzania.com/wp-includes/customize/

https://temp1.novotest.biz/id/

depo 25 bonus 25

https://sumberjo-blitar.desa.id/images

https://sumberjo-blitar.desa.id/data

depo 25 bonus 25 to 5x

depo 25 bonus 25

https://www.greentourstanzania.com/wp-includes/js/product/

https://smpabbs.sch.id/gacor/100/

https://smpabbs.sch.id/gacor/bonus/

deposit 25 bonus 25

depo 25 bonus 25

bonus new member 100

https://ppdb.smk-kosgoro.sch.id/data/depo 25 bonus 25https://jesus.nouvellevie.com/wp-includes/images/Getoko.iddepo 25 bonus 25https://bonus-baru.s3.ap-southeast-1.amazonaws.com/link-daftar-slot-gacor.htmlhttps://bonus-baru.s3.ap-southeast-1.amazonaws.com/scatter-pink-paling-gacor.htmlhttps://worldlisteningproject.org/wp-includes/depo25bonus25/bonus new member 100