23 Sep, 2013
Shardhha
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