How to Extract Excel Formulas
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.
Const ksDot = "."
Const ksDoc = "_formula_doc"
Const ksXLSX = "xlsx"
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
' source workbook
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
If Dir(sNewFileName, vbNormal) <> ""
Then Kill sNewFileName
Application.SheetsInNewWorkbook = I
sNewName = .Name
For I = 1 To .Worksheets.Count
iRows = .Rows.Count
iColumns = .Columns.Count
For K = 1 To iRows
For J = 1 To iColumns
If .Cells(K, J).HasFormula Then
sCellName = .Cells(K, J).Address
sCellName = ""
Workbooks(sNewName).Worksheets(I).Cells(K, J).Value =
sCellName + .Cells(K, J).Formula