Blog

Latest technology news, trends and developers guide for business application. Visit this space for more tech updates.

Let’s work on your exciting new project together!

How to Extract Excel Formulas

how to extract excel formulas, microsoft excel

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

We are always happy to hear from you - Share your business requirements. Our business experts will reach you shortly

 

Select Your Requirement

Secure : We'll ensure, all the information provided by you will not be shared with anyone and your details are completely safe.

We respect your privacy. *Privacy Policy

Select Your Requirement

Secure : We'll ensure, all the information provided by you will not be shared with anyone and your details are completely safe.

We respect your privacy. *Privacy Policy