by Allen Wyatt
(last updated December 30, 2017)
Tim has a workbook that is rather large, and he wonders if there is a way to do an "inventory" of which functions are used in the various formulas in the workbook, along with the cells in which those functions are used.
This task is a bit harder that it may at first seem. You can make some rules as to what constitutes an Excel function, but those rules can get a bit hinky. For instance, you might think that a function is defined by an uppercase word followed by an opening parenthesis. This hasn't been true for a while, however, as function names can now contain periods.
The easiest way that I've found to get a function inventory is to develop a macro that will step through each formula in each worksheet in the workbook and check that formula against every possible worksheet function. The following macro will do that just fine:
Sub FormulaInventory() Dim EFunc(500) As String Dim iEFCnt As Integer Dim sFile As String Dim sTemp As String Dim SourceBook As Workbook Dim TargetBook As Workbook Dim TargetSheet As Worksheet Dim w As Worksheet Dim c As Range Dim iRow As Integer Dim J As Integer Dim K As Integer Dim L As Integer ' Read functions from text file sFile = ActiveWorkbook.Path & "\ExcelFunctions.txt" iEFCnt = 0 Open sFile For Input As #1 While Not EOF(1) Line Input #1, sTemp sTemp = Trim(sTemp) If sTemp > "" Then iEFCnt = iEFCnt + 1 EFunc(iEFCnt) = sTemp & "(" End If Wend Close #1 ' Sort functions; longest to shortest For J = 1 To iEFCnt - 1 L = J For K = J + 1 To iEFCnt If Len(EFunc(L)) < Len(EFunc(K)) Then L = K Next K If L <> J Then sTemp = EFunc(J) EFunc(J) = EFunc(L) EFunc(L) = sTemp End If Next J ' Create and setup new workbook Set SourceBook = ActiveWorkbook Set TargetBook = Workbooks.Add Set TargetSheet = TargetBook.Worksheets.Add TargetSheet.Name = "Inventory" TargetSheet.Cells(1, 1) = "Function Inventory for " & SourceBook.Name TargetSheet.Cells(3, 1) = "Function" TargetSheet.Cells(3, 2) = "Worksheet" TargetSheet.Cells(3, 3) = "Cell" TargetSheet.Range("A1").Font.Bold = True TargetSheet.Range("A3:C3").Font.Bold = True With TargetSheet.Range("A3:C3").Cells.Borders(xlEdgeBottom) .LineStyle = xlContinuous .Weight = xlThin End With ' Perform actual inventory iRow = 4 For Each w In SourceBook.Worksheets For Each c In w.Cells.SpecialCells(xlCellTypeFormulas) sTemp = c.Formula For J = 1 To iEFCnt If InStr(sTemp, EFunc(J)) Then TargetSheet.Cells(iRow, 1) = Left(EFunc(J), Len(EFunc(J)) - 1) TargetSheet.Cells(iRow, 2) = w.Name TargetSheet.Cells(iRow, 3) = Replace(c.Address, "$", "") iRow = iRow + 1 sTemp = Replace(sTemp, EFunc(J), "") End If Next J Next c Next w End Sub
Careful examination of the macro indicates that it reads in a text file (ExcelFunctions.txt) that includes a listing of all of Excel's worksheet functions. This file can be downloaded by using this link, and it should be stored in the same folder as the workbook that you are analyzing. (This means that the workbook you are analyzing must have already been saved in a folder.)
When the worksheet function names are loaded from the text file, the macro appends an opening parenthesis to each name. These names are stored in an array, and then this array is sorted according to the length of the function name, with the longest function names at the beginning of the array. This is done because of some ambiguity in function names. For instance, when the function-name array is read in you'll have one function named LEFT( and another named T(. When looking at a formula, if the LEFT( name is found then the T( name will also be found. By looking at the longest function names first and then removing those names from the formula string, then you eliminate the possibility of "false positives."
It should be noted that this function will only work with those worksheet functions listed in the ExcelFunctions.txt file. You can modify the file, as desired, to adapt what is located. It currently includes all standard worksheet functions for Excel 2016, but you may want to modify it to include functions made available by your add-ins, or you may want to delete functions that you find too esoteric. (Hint: You could also simply add an apostrophe in front of a function name in the text file and it will then never be included in an inventory.) There are just below 500 function names in the file now; if you add too many more, you'll need to change the number elements declared in the macro for the EFunc array.
When the macro is completed, you'll find your inventory in the new workbook that the macro creates. You should note that the time it takes the macro to complete can vary from quite fast to amazingly slow. The larger your workbook—the more worksheets it contains and the more formulas on those worksheets—the longer it will take to finish its work. Remember it has to check every formula in the workbook against almost 500 worksheet function names.
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (5136) applies to Microsoft Excel 2007, 2010, 2013, and 2016.
Program Successfully in Excel! John Walkenbach's name is synonymous with excellence in deciphering complex technical topics. With this comprehensive guide, "Mr. Spreadsheet" shows how to maximize your Excel experience using professional spreadsheet application development tips from his own personal bookshelf. Check out Excel 2013 Power Programming with VBA today!
Knowing if a workbook is already open can be a prerequisite to your macro working correctly. Here's how to check it out.Discover More
Macros are great when it comes to automating how you work with your workbooks. What if you want to fundamentally change ...Discover More
When creating your own user interfaces within Excel, the various developer controls can add a nice touch. One such ...Discover More
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.