excel utility

romithdas used Ask the Experts™
I need an excel utility which can create a report for all named ranges in a workbook by worksheet and lists all the dependencies of these name ranges in a workbook
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2006
try this it wil do some more

title : Excel macro list all named ranges and formulas
source : http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&selm=OEozKCwRDHA.1688%40TK2MSFTNGP11.phx.gbl&rnum=2

Try running this code on a blank worksheet with in the workbook you want
the formulae listing for. It will also give you a list of all the named
ranges. Enjoy.

Steven Revell
MS Applications Development & Support


Public Sub doFormulae()
    Dim bSaved As Boolean
    Dim sWS As String
    'Displays a list of formulae and named ranges used in the workbook
    Dim wks As Worksheet
    Dim bHasFormula As Boolean
    Dim i As Integer
    Dim cell As Range
    'Switch off screen updating to improve performance
    Application.ScreenUpdating = False
    With ActiveSheet
        'Unprotect and clear the worksheet
        .UsedRange.Delete xlShiftUp
        bHasFormula = False
        Range("A1").Value = "Worksheet"
        Range("B1").Value = "Formula"
        Range("C1").Value = "Range"
        Range("D1").Value = "Protected"
        Range("A1:D1").Font.Bold = True
        'Check if the workbook contains any formulae
        For Each wks In ThisWorkbook.Worksheets
            If wks.UsedRange.HasFormula = False Then
                bHasFormula = True
                Exit For
            End If
        Next wks
        If bHasFormula = False Then
            'If false display message in cell C1
            .Range("B2").Value = "This Workbook contains no formulae."
            i = 2
            'If true build the formulae list
            i = 2
            'Check each cell on each sheet for formulae
            For Each wks In ThisWorkbook.Worksheets
                For Each cell In wks.UsedRange.Cells
                    If cell.HasFormula = True Then
                        'If the cell has a formula record the worksheet name, cell address and cell formula
                        .Cells(i, 1).Value = wks.name
                        .Cells(i, 2).Value = "'" & cell.Formula
                        .Cells(i, 3).Value = cell.Address(RowAbsolute:=False, ColumnAbsolute:=False)
                        .Cells(i, 4).Value = cell.Locked                        
                        'Increment the row counter
                        i = i + 1
                    End If
                Next cell
            Next wks
        End If
        i = i + 2
        'Check if the workbook contains Names
        .Cells(i, 1).Value = "Name"
        .Cells(i, 1).Font.Bold = True
        .Cells(i, 2).Value = "Refers to"
        .Cells(i, 2).Font.Bold = True
        If ThisWorkbook.Names.Count > 0 Then
            'If true display a list of Names
            .Cells(i + 1, 1).ListNames
            .Cells(i + 1, 2).Value = "This Workbook contains no named ranges."
        End If
        'Format the list
        With .Columns("A:A")
            .HorizontalAlignment = xlGeneral
            .VerticalAlignment = xlTop
            .WrapText = True
        End With
        With .Columns("B:B")
            .HorizontalAlignment = xlGeneral
            .VerticalAlignment = xlTop
            .ColumnWidth = 70
            .WrapText = True
        End With

    End With
    'Switch screen updating back on
    Application.ScreenUpdating = True
    Exit Sub

end sub

hope this helps
Author of the Year 2009

Moderator, my recommended disposition is:

    Accept bruintje's comment(s) as an answer.

DanRollins -- EE database cleanup volunteer

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial