List All Formulas and Addresses in Entire File

[Excel4444]

I have a file with 1M+ formulas and 30K named ranges. I am trying to aggregate and simplify both areas. In order to accomplish this, I ideally need to know either the number of times a named range is used through out the workbook and it's location (sheet/address) OR a list of all formulas in the workbook and then i just do a search off that list (albeit a large list). the issue that's coming up is that even with a macro like this http://www.vbaexpress.com/kb/getarticle.php?kb_id=409 it's taking entirely too long. now I concede that in order to do this analysis it may literally just take hours/days if a macro is sorting through that many cells, HOWEVER i was hoping there was a fast way. Is there a file contained in the folder when it's converted to a zip file that (whilst needs parsing) contains file meta to the tune of what formulas are being used and where. also a forum contributor even solved an issue related to this https://www.excelforum.com/excel-pro...ml#post5736452 but even this method once applied to the large workbook takes so long it locks up.

Goal (1 or 2):
1. count number of times a named range is used and where
2. list all formulas or get a file where this is located

thanks in advance!

[FireDept]

Hi Excel,
Maybe this will help for option 2

Option Explicit
Sub Excel4444Req()

Dim SourceWb As Workbook
Dim SourceWs As Worksheet, DestWs As Worksheet
Dim c As Range, FRng As Range
Dim DestWsNom As String, AddPrefix As String
Dim LastRow As Long

Set SourceWb = ActiveWorkbook
AddPrefix = "F_"                                                                                                'New Ws is named after the original, with the prefix ?F_?.

Application.DisplayAlerts = False                                                                           'Stop flickering

For Each SourceWs In SourceWb.Worksheets
  LastRow = 2                                                                                               'DLR
  
  If Left(SourceWs.Name, Len(AddPrefix)) <> AddPrefix Then
    Set FRng = Nothing
    On Error Resume Next
    Set FRng = SourceWs.Cells.SpecialCells(xlCellTypeFormulas, 23)                                           'Numbers, Texts, Logicals and Errors
    If Not FRng Is Nothing Then
      DestWsNom = Left(AddPrefix & SourceWs.Name, 30)                                                            'Check
      Worksheets(DestWsNom).Delete                                                                           'Delete if needed
      
      Set DestWs = Worksheets.Add                                                                            'Create new ws
        
      With DestWs
      
        .Name = DestWsNom                                                                                    'Ws Name
        .Columns("A:D").NumberFormat = "@"                                                                   'Text Format
        .Range(.Cells(1, 1), .Cells(1, 4)).Value = Array("ID", "Sheet", "Cell", "Formula")                   'Headers
            For Each c In FRng
              .Range(.Cells(LastRow, 1), .Cells(LastRow, 4)).Value = Array(LastRow - 1, SourceWs.Name, c.Address(0, 0), c.Formula)
              LastRow = LastRow + 1                                                                          'Counter
            Next c
      End With

      Set DestWs = Nothing                                                                                    '0
      
    End If
  End If
  
Next SourceWs

Application.DisplayAlerts = True                                                                              'Turn back on

MsgBox "Procedure Done!"

End Sub

[Excel4444]

thanks FireDept!

i made a tweak to place everything on one sheet. thanks!

Sub Excel4444Req()

Dim SourceWb As Workbook
Dim SourceWs As Worksheet, DestWs As Worksheet
Dim c As Range, FRng As Range
Dim DestWsNom As String, AddPrefix As String
Dim LastRow As Long

Set SourceWb = ActiveWorkbook
AddPrefix = "F_"                                                                                                'New Ws is named after the original, with the prefix ?F_?.

Application.DisplayAlerts = False                                                                           'Stop flickering
Set DestWs = Worksheets.Add
For Each SourceWs In SourceWb.Worksheets
  LastRow = Cells(Rows.Count, 1).End(xlUp).Row                                                                                              'DLR
  
  
  If Left(SourceWs.Name, Len(AddPrefix)) <> AddPrefix Then
    Set FRng = Nothing
    On Error Resume Next
    Set FRng = SourceWs.Cells.SpecialCells(xlCellTypeFormulas, 23)                                           'Numbers, Texts, Logicals and Errors
    If Not FRng Is Nothing Then
      DestWsNom = Left(AddPrefix & SourceWs.Name, 30)                                                            'Check
      Worksheets(DestWsNom).Delete                                                                           'Delete if needed
      
                                                                                 'Create new ws
        
      With DestWs
      
        .Name = DestWsNom                                                                                    'Ws Name
        .Columns("A:D").NumberFormat = "@"                                                                   'Text Format
        .Range(.Cells(1, 1), .Cells(1, 4)).Value = Array("ID", "Sheet", "Cell", "Formula")                   'Headers
            For Each c In FRng
              .Range(.Cells(LastRow, 1), .Cells(LastRow, 4)).Value = Array(LastRow - 1, SourceWs.Name, c.Address(0, 0), c.Formula)
              LastRow = LastRow + 1                                                                          'Counter
            Next c
      End With

      'Set DestWs = Nothing                                                                                    '0
      
    End If
  End If
  
Next SourceWs

Application.DisplayAlerts = True                                                                              'Turn back on

MsgBox "Procedure Done!"

End Sub

[FireDept]

You're welcome!!
If this helped you can mark the thread as solved. Also, you can add reputation using the star button :-)