excel utility

romithdas
romithdas used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2006
Commented:
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


description
------------
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
   
    UnprotectWS
    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
            Else
                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
        Else
           
            '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
        Else
            .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
            .Cells.EntireRow.AutoFit
        End With

        Range("A1").Select
    End With
       
    'Switch screen updating back on
    Application.ScreenUpdating = True
    Exit Sub

end sub
------------

hope this helps
Author of the Year 2009

Commented:
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