Creating a Function Inventory for a Workbook

Here's an alternate version of the Tip's macro that uses worksheet function WEBSERVICE to download LF delimited function names from the latest list at https://excelribbon.tips.net/ExcelFunctions.txt. More functions can be added in a VBA.Array(...) statement. All function names are treated as case sensitive.
This version creates a new unsaved workbook/worksheet with 3 columns: Sheet!Cell, Function, and Formula. The first column is hyperlinked to its source.

Sub FormulaInventory2()
    Dim EFunc() As String, AFunc() As Variant, v As Variant
    Dim SourceBook As Workbook, w As Worksheet
    Dim FormulaCells As Range, c As Range
    Dim sTemp As String, sAddr As String
    Dim iEFCnt As Integer, J As Integer, K As Integer, L As Integer
    Const sFile = "https://excelribbon.tips.net/ExcelFunctions.txt"
    'Get LF delimited list of functions; max is 32767 characters
    EFunc = Split(WorksheetFunction.WebService(sFile), vbLf)
    iEFCnt = UBound(EFunc)
    If Len(EFunc(iEFCnt)) = 0 Then iEFCnt = iEFCnt - 1
    'Add functions to EFunc; don't worry about possible duplicates
    For Each v In VBA.Array("TEXTSPLIT")
        iEFCnt = iEFCnt + 1
        ReDim Preserve EFunc(iEFCnt)
        EFunc(iEFCnt) = v
    Next v
    'Sort functions longest to shortest
    For J = 0 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
    Set SourceBook = ActiveWorkbook
    'Create and setup new active workbook/worksheet
    Workbooks.Add
    With ActiveSheet
        .Name = "FormulaInventory"
        .Range("A2") = "Sheet!Cell"
        .Range("B2") = "Function"
        .Range("C2") = "Formula"
        .Range("A1:C2").Font.Bold = True
        .Range("A3").Select
        ActiveWindow.FreezePanes = True
        'Perform inventory
        L = 3
        For Each w In SourceBook.Worksheets
            Set FormulaCells = Nothing
            On Error Resume Next
                Set FormulaCells = w.UsedRange.SpecialCells(xlCellTypeFormulas)
            On Error GoTo 0
            If Not (FormulaCells Is Nothing) Then
                For Each c In FormulaCells
                    sTemp = c.Formula
                    For J = 0 To iEFCnt
                        'Perform case sensitive comparison
                        If InStr(1, sTemp, EFunc(J) & "(", vbBinaryCompare) Then
                            sAddr = Replace(c.Address(External:=True), _
                                "[" & SourceBook.Name & "]", "", 1, 1)
                            .Cells(L, 1) = "'" & sAddr
                            .Cells(L, 1).Hyperlinks.Add .Cells(L, 1), _
                                SourceBook.FullName, sAddr
                            .Cells(L, 2) = "'" & EFunc(J)
                            .Cells(L, 3) = "'" & c.Formula
                            L = L + 1
                            sTemp = Replace(sTemp, EFunc(J) & "(", "", 1, -1, _
                                vbBinaryCompare)
                        End If
                    Next J
                Next c
            End If
        Next w
        .Columns("A:C").AutoFit
        .Range("A1") = SourceBook.FullName
    End With
End Sub

@James: The primary place from which I garnered the information is here:

https://support.microsoft.com/en-us/office/excel-functions-alphabetical-b3944572-255d-4efb-bb96-c6d90033e188

That doesn't tell the whole story, though, as there are some functions that are not listed there. These are deprecated, but still work in Excel for compatibility with past versions.

As to TEXTSPLIT, I'm not sure how I missed that one. I've now updated the file to include it. If you find any others, let me know and I'll get them added.

-Allen

@Allen
I'm curious how you derived the ExcelFunctions.txt list of functions. While testing the macro I noticed the Excel 2024 TEXTSPLIT function is missing. For example, the following formula
=TEXTSPLIT("Hello World"," ")
adds function T to the inventory instead of TEXTSPLIT. It's difficult to determine if any other functions are missing.

@Allen
Perhaps my question was not clearly stated. Do you intend to update https://excelribbon.tips.net/ExcelFunctions.txt whenever new worksheet functions are added to Excel?
As you suggested, here is another way to save ExcelFunctions.txt after downloading in a web browser:
1. Press Ctrl+A to copy all of the values from ExcelFunctions.txt
2. Press Ctrl+V to paste them into a blank Notepad window or tab
3. Press Ctrl+S (or Ctrl+Shift+S) to Save as type: Text documents (*.txt)
4. Specify that file in Sub FormulaInventory

@James

I updated the list just this past week, so it is as up to date as I know how to make it right now.

As to saving the functions from a web browser page, you should just be able to copy it and save it into a text file in Notepad.

-Allen

SpecialCells(xlCellTypeFormulas) will produce an error if there are no formulas; therefore, replace the following statements in Sub FormulaInventory
    ' Perform actual inventory
    iRow = 4
    For Each w In SourceBook.Worksheets
        If w.Cells.SpecialCells(xlCellTypeFormulas).Count > 0 Then
            For Each c In w.Cells.SpecialCells(xlCellTypeFormulas)
with these statements
    ' Perform actual inventory
    Dim FormulaCells As Range
    iRow = 4
    For Each w In SourceBook.Worksheets
        Set FormulaCells = Nothing
        On Error Resume Next
            Set FormulaCells = w.Cells.SpecialCells(xlCellTypeFormulas)
        On Error GoTo 0
        If Not (FormulaCells Is Nothing) Then
            For Each c In FormulaCells
Saving text like TRUE or FALSE in a cell results in a logical value instead of text; therefore, after this statement
            TargetSheet.Cells(iRow, 1) = Left(EFunc(J), Len(EFunc(J)) - 1)
add the following statements
            If EFunc(J) = "TRUE(" Then
                TargetSheet.Cells(iRow, 1) = "'TRUE"
            ElseIf EFunc(J) = "FALSE(" Then
                TargetSheet.Cells(iRow, 1) = "'FALSE"
            End If

When I use Chrome to save https://excelribbon.tips.net/ExcelFunctions.txt as a text file, each line is terminated by LF instead of CR or CR-LF; therefore, VBA's Line Input statement does not separate lines as expected. To remedy this:
1. Press Ctrl+A to copy all of the values from ExcelFunctions.txt
2. Press Ctrl+V to paste them into cell A1 of a blank worksheet
3. Press F12 and pick Save as type: Text (MS-DOS) (*.txt)
4. Specify that file in Sub FormulaInventory

@Allen
Do you intend to regularly update the list of Excel's worksheet functions?https://excelribbon.tips.net/ExcelFunctions.txt