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.<\/p>\n
‘ constants ‘ declarations ‘ start ‘ delete ‘ add ‘ process Next J End Sub<\/p>\n","protected":false},"excerpt":{"rendered":" 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 […]<\/p>\n","protected":false},"author":1,"featured_media":20133,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[],"class_list":["post-20132","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-hire-developer"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.solutionanalysts.com\/blog\/wp-json\/wp\/v2\/posts\/20132","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.solutionanalysts.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.solutionanalysts.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.solutionanalysts.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.solutionanalysts.com\/blog\/wp-json\/wp\/v2\/comments?post=20132"}],"version-history":[{"count":1,"href":"https:\/\/www.solutionanalysts.com\/blog\/wp-json\/wp\/v2\/posts\/20132\/revisions"}],"predecessor-version":[{"id":38610,"href":"https:\/\/www.solutionanalysts.com\/blog\/wp-json\/wp\/v2\/posts\/20132\/revisions\/38610"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.solutionanalysts.com\/blog\/wp-json\/wp\/v2\/media\/20133"}],"wp:attachment":[{"href":"https:\/\/www.solutionanalysts.com\/blog\/wp-json\/wp\/v2\/media?parent=20132"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.solutionanalysts.com\/blog\/wp-json\/wp\/v2\/categories?post=20132"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.solutionanalysts.com\/blog\/wp-json\/wp\/v2\/tags?post=20132"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}Sub ListFormulas()<\/code><\/p>\n
\nConst ksDot = “.”
\nConst ksDoc = “_formula_doc”
\nConst ksXLSX = “xlsx”<\/p>\n
\nDim I As Integer, J As Integer, K As Integer
\nDim L As Integer
\nDim iRows As Integer, iColumns As Integer
\nDim sName As String, sFileName As String
\nDim sNewName As String, sNewFileName As String
\nDim sCellName As String<\/p>\n
\n‘ source workbook
\nWith ActiveWorkbook
\nsName = .Name
\nsFileName = .FullName
\nI = InStr(StrReverse(sName), ksDot)
\nsNewFileName = Left$(sName, _
\nLen(sName) – I – 1) & ksDoc &
\nksDot & ksXLSX
\nI = Application.SheetsInNewWorkbook
\nApplication.SheetsInNewWorkbook = .Worksheets.Count
\nEnd With<\/p>\n
\nIf Dir(sNewFileName, vbNormal) <> “”
\nThen Kill sNewFileName<\/p>\n
\nWorkbooks.Add
\nApplication.SheetsInNewWorkbook = I
\nWith ActiveWorkbook
\n.SaveAs sNewFileName
\nsNewName = .Name
\nEnd With
\nWorkbooks(sName).Activate<\/p>\n
\nWith ActiveWorkbook
\nFor I = 1 To .Worksheets.Count
\n‘ format
\nWorkbooks(sNewName).Worksheets(I).Cells.NumberFormat
\n= “@”
\n‘ name
\nWorkbooks(sNewName).Worksheets(I).Name =
\n.Worksheets(I).Name
\n‘ formulas
\nWith .Worksheets(I)
\nWith .UsedRange
\niRows = .Rows.Count
\niColumns = .Columns.Count
\nEnd With
\nFor K = 1 To iRows
\nFor J = 1 To iColumns
\nIf .Cells(K, J).HasFormula Then
\nsCellName = .Cells(K, J).Address
\nElse
\nsCellName = “”
\nEnd If
\nWorkbooks(sNewName).Worksheets(I).Cells(K, J).Value =
\nsCellName + .Cells(K, J).Formula<\/p>\n
\nNext K
\nEnd With
\nNext I
\nEnd With
\n‘ end<\/p>\n