VBA to Extract All Excel Formulas in a Workbook

Summary

Some visual-basic for applications code to extract all the formulas from every sheet in an Excel Workbook and save them in a text file.

Background

Avoiding a long explanation, we’ve been writing a reporting tool that combines a Microsoft Access front-end to a SQL Server database. It generates and updates some Excel reports/data visualizations in a sort of dashboard.

As part of managing multiple worksheets and sheets, at times sheets get copied into another workbook. When that occurs, Excel modifies the copied formula to refer back to data in the original sheets. This is a problem when we are copying both the underlying data table and the sheet. We want the formulas to reference the new (copied) data table in the new workbook.

This function writes the formulas, with their cell-references, to a text file. We then have a function that reads that text file and re-writes the formulas cell by cell in the new sheet and workbook.

Regular Formulas and Array Formulas

Array formulas must be written (CSE – control-shift-enter) into a cell differently than standard formulas. This is true in code as well. So the function identifies whether a function is an array function or not and provides an identifier.

The output

The text file created includes the following data:

[Cell Reference]:@@[formula]@@

The colon “:” and the “@@” surround the formula.

Array formulas include a “{“ squiggly bracket prior to the formula.

Regular formula text example

$A$1:@@[=SUM(F1:F100)]@@

Array formula text example

$G$20:@@[{=SUM((SHEETNAME!$E$3:$E$96=$B11)*(SHEETNAME!$AB$2:$AV$2=”HEADING”)*HCBudgetFY2017!$AB$3:$AV$96)/12*(DATEDIF($D$4,$G$4,”m”)+1)]@@

Most important to note is that the two formulas differ in the “@@[“ and “@@[{ – with the second prefix used to identify an array formula.

The Code

The following function writes a single sheet out to a text file. I’ll add the expanded code next week and include code that writes the same formulas into a worksheet.



Sub ReadFormulas()
Dim lformulas As String, lrow As Integer, lcol As Integer, strAflag
'strAflag is empty or adds specifier if formula is an array formula

Dim strWksName As String
Dim wks As Worksheet
Set wks = ActiveSheet
strWksName = wks.Name
lformulas = "WKS:" & strWksName & vbCrLf
lrow = Range("A1").SpecialCells(xlCellTypeLastCell).Row
lcol = Range("A1").SpecialCells(xlCellTypeLastCell).Column
For c = 1 To lcol
   For r = 1 To lrow
     strForm = wks.Cells(r, c).Formula
     strRef = wks.Cells(r, c).Address
     strAflag = IIf(wks.Cells(r, c).HasArray = True, "{", "")
     If Len(strForm) > 0 And Trim(Mid(strForm, 1, 1)) = "=" Then 'not just values but actual formula
         lformulas = lformulas & strRef & ":@@[" & strAflag & strForm & "]@@" & vbCrLf
     End If

   Next
Next

blnFile = MakeFile(lformulas, "c:\data\form" & strWksName & ".txt")

End Sub

Function MakeFile(inFText, inFileName)
' inFText = File text
Set oFS = CreateObject("Scripting.FileSystemObject")
Set nRF = oFS.CreateTextFile(inFileName, True)
nRF.Write inFText
Set nRF = Nothing
Set oFS = Nothing
MakeFile = True
End Function

Function ReadFileStr(inFile)
Dim strFile As String
'Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFile = objFSO.OpenTextFile(inFile)
strFile = objFile.ReadAll
objFile.Close
ReadFileStr = strFile
End Function

Conclusion

I provided a couple helper functions – one to write the file out, the other to read the file into memory. The second function would be used later to iterate through the file to write the data back to a sheet of your choice.

This is a simple but useful routine. We included it as part of a larger sheet management solution. Sheets and entire workbook data is stored in SQL tables and key lists of data that we report on are synchronized across various reporting sheets and summary dashboards.

More on this later.

Posted in Consulting, Microsoft Access, SQL Server and tagged , .

Leave a Reply

Your email address will not be published. Required fields are marked *