• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 234
  • Last Modified:

Testing Name Resolution

Is there any inbuilt way to test the resolution of all in all formulas in a workbook?

For example, if a cell has this formula...

=IF(Month(Now())=1, MyJanuaryValue, "It's not January!")

...I would have no way of knowing if the name "MyJanuaryValue" actually resolved to anything unless it actually were January now, in which case the formula would yield "#NAME?".  In fact, without looking into the formula I wouldn't even know if it contained any names at all.  The point is, I want to audit my workbook for invalid names.  Parsing every formula is a pain.  Is there a better way?

Mike


0
shacho
Asked:
shacho
  • 13
  • 9
  • 7
  • +2
4 Solutions
 
SiddharthRoutCommented:
This will give you the names of all Invalid Names in a workbook.

Option Explicit
Sub Sample()
    Dim TempRange As Range, strName As Name

    For Each strName In ActiveWorkbook.Names
        Set TempRange = Nothing
        On Error Resume Next
        Set TempRange = strName.RefersToRange
        On Error GoTo 0

        If TempRange Is Nothing Then
            MsgBox strName.Name & " is an invalid Name"
        End If
    Next strName
End Sub

Open in new window


Sid
0
 
shachoAuthor Commented:
Please reread the question.
0
 
shachoAuthor Commented:
One typo in the question:
Is there any inbuilt way to test the resolution of all [names] in all formulas in a workbook?
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
SiddharthRoutCommented:
>> The point is, I want to audit my workbook for invalid names.  Parsing every formula is a pain.  Is there a better way?

If I understood your query, you want to parse all invalid names in all formulas?

Sid
0
 
shachoAuthor Commented:
I want to know if there are any names referred to in any formulas that will not resolve on evaluation.  In the example I provided the formula conditionally will try to resolve the name "MyJanuaryValue".  I need to know if "MyJanuaryValue" is in fact a known name in the Workbook's Names collection.  That's the easy part.  The hard part is finding these references in the first place.  Unless and until a formula throws an error, it may have references to non-existent names and you would never know about it.
0
 
shachoAuthor Commented:
For the sake of clarity, your code is trying to yield names that exist but resolve to errors.  I'm searching for reference to names, not the names themselves.  By the way, your code has an important problem; it assumes that all of the names in the workbook resolve to ranges.  That's not required and often is not the case.  For example you could have a name that resolves to a value, like "=1".  It doesn't point to a range, but your code will tell you it's invalid.  Something to keep in mind.


0
 
SiddharthRoutCommented:
There are 2 scenarios here.

1) Invalid names in formulas (Possible a typo for example)
2) Invalid names in formulas (Valid Names but refers to non existent range)

The code that I gave above is for scenario 2. Once you get the name of those "names" simply use .Find() to check all formulas in the workbook if they contain that.

For scenario 1

We will loop through all formulas in a workbook and test it and check if it equals to #NAME?

For example

Sub Sample()
    Dim ws As Worksheet
    Dim cl As Range
    Dim rngFormulas As Range
    
    For Each ws In ActiveWorkbook.Sheets
        On Error Resume Next
        Set rngFormulas = ws.UsedRange.SpecialCells(xlCellTypeFormulas)
        
        If Not rngFormulas Is Nothing Then
            For Each cl In rngFormulas
                If cl.Text = "#NAME?" Then _
                Debug.Print "Error in formula in Sheet " & ws.Name & " , Cell " & cl.Address
            Next
        End If
        On Error GoTo 0
    Next
End Sub

Open in new window


Sid
0
 
SiddharthRoutCommented:
>> By the way, your code has an important problem; it assumes that all of the names in the workbook resolve to ranges.  That's not required and often is not the case.  For example you could have a name that resolves to a value, like "=1".  It doesn't point to a range, but your code will tell you it's invalid.

You are right on that part. :)

I think the code that I gave you in post ID: 35003868 is what you are after?

Sid
0
 
shachoAuthor Commented:
The new code assumes the failed resolution is already manifest.  That's easy.  The problem is with conditionals.  If the formula's internal conditions are not met, the bad name will not throw an error.  Try my sample formula with your code.  It won't catch it.

0
 
SiddharthRoutCommented:
I see what you mean...

Give me few moments.

Sid
0
 
dlmilleCommented:
It might be overkill, but for future reference, there's a MappIt addin that audits your workbook you might find useful.

dave

http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/A_2613-Mappit-a-free-Excel-model-auditing-addin.html?sfQueryTermInfo=1+30+audit
0
 
dlmilleCommented:
Also, just pull up the name manager, and you can sort by column to see the #VALUE, #REF, etc., errors...

Dave
0
 
SiddharthRoutCommented:
shacho: It is an question. I still don't have an answer to it. I never thought of it this way. I am still wracking my brains over it :)

Sid
0
 
SiddharthRoutCommented:
Please read

>>>It is an question.

as

It is an Excellent question

in the above post.

Sid
0
 
shachoAuthor Commented:
>just pull up the name manager
If you're referring to the "Name Manager" add-in, that's not gonna do it either.  Same as above.  It looks at names, not formulas.

0
 
dlmilleCommented:
its not the addin - its off the Ribbon and it will show you formula resolution so you can see errors...

I assume you're using 2007+?
0
 
dlmilleCommented:
Ribbon->Formulas-Names-> Name Manager

:)

0
 
dlmilleCommented:
what is your formula, if I may ask (while Sid works this out programmatically, I'd like to see if there's a manual approach, if you're willing.

Dave
0
 
dlmilleCommented:
Never mind.  I created a garbage formula and stuffed it in a name.  I see what you mean as well.  Mappit provided no glory, nor did name manager....

Tricky.

Dave
0
 
shachoAuthor Commented:
2003.  If 2007+ supports deeper interaction with names, that's great news.  Long overdue.  I'm dubious that it can do what I'm trying to do, though.  Please test it and let me know.  As for the manual approach, just try using my sample formula above.  My manual approach currently is to parse the entire formula and visually inspect the contents for things that look like names, then test them one by one. Excel clearly has some method for parsing them without evaluation as it can correctly replace standard references in formulas with names (i.e. Insert -> Names -> Apply).  But I can't find any interface to this functionality through the GUI or VB-API.





0
 
dlmilleCommented:
lol - i created a name called garbagename with the formula:

=offset(garbage,1,1).  There were no apparent errors - until you try to actually use the name.  No error showed up in the name manager as its a volatile formula all I get is {...} as the result.  So, this isn't working as a solution.
0
 
shachoAuthor Commented:
This is pretty impressive:
http://ewbi.blogs.com/develops/2004/12/excel_formula_p.html

Harder than it sounds, eh?  This makes changing names really thorny.  
0
 
shachoAuthor Commented:
So thorny, in fact, that you have to jump through a lot of hoops to make sure you don't damage your worksheet whenever you want to change one.  I wrote this behemoth of a routine to accomplish just that some time ago.

Public Sub ChangeMyName()
    Dim TargetBook As Workbook, TargetRange As Range, FoundNames As Collection, NameToChange As Name
    Dim NewName As String, DefaultChoice As String
    Dim NME As Name, CTR As Integer, STG As String, IGR As Integer, LNG As Long, SHT As Worksheet, RNG As Range
    Dim ValidationCells As Range, ConditionCells As Range

    'BEGIN BY SELECTING THE RANGE OR MERGED RANGE OVER THE RANGE REFERENCE NAME YOU WANT TO CHANGE

    'THIS CANNOT WORK FOR NON-CONTIGUOUS SELECTION RANGES
    If Selection.Areas.Count <> 1 Then Exit Sub

    'REFERENCE THE TARGET WORKBOOK
    Set TargetBook = ActiveCell.Worksheet.Parent

    'MAKE SURE THERE ARE NO PROTECTED SHEETS
    For Each SHT In ActiveCell.Worksheet.Parent.Worksheets
        If SHT.ProtectContents Then
            MsgBox SHT.Name & " is protected."
            Set SHT = Nothing
            GoTo DONE
        End If
    Next SHT

    'SPECIFY PRECISELY WHAT THE SELECTION RANGE IS IF THERE ARE MERGED CELLS
    If Selection.Address = ActiveCell.MergeArea.Address Then
        DefaultChoice = 1 'TOP LEFT CELL
    Else
        If Selection.Rows.Count > Selection.Columns.Count Then DefaultChoice = 3 'JUST THE FIRST COLUMN
        If Selection.Rows.Count < Selection.Columns.Count Then DefaultChoice = 2 'JUST THE FIRST ROW
    End If
    Select Case InputBox( _
            "1: Top Left Only" & vbCr & _
            "2: Row 1 Only" & vbCr & _
            "3: Col 1 Only" & vbCr & _
            "4: As Is", _
            "Modify Selection", DefaultChoice)
        Case 1: Set TargetRange = Selection.Cells(1)
        Case 2: Set TargetRange = Selection.Rows(1)
        Case 3: Set TargetRange = Selection.Columns(1)
        Case 4: Set TargetRange = Selection
        Case Else: GoTo DONE
    End Select

    'FIND EVERY KNOWN NAME THAT POINT TO THE SELECTION RANGE
    Set FoundNames = New Collection
        For Each NME In TargetBook.Names
            If NME.RefersTo = "=" & TargetRange.Worksheet.Name & "!" & TargetRange.Address Then FoundNames.Add NME.Name
        Next NME
        If FoundNames.Count = 0 Then
            'NO NAME CORRRESPONDS TO THE SELECTION
            MsgBox "No names specified for " & TargetRange.Address
            GoTo DONE

        ElseIf FoundNames.Count = 1 Then
            'ONE NAME FOUND, NO NEED TO ASK WHICH ONE
            Set NameToChange = ActiveWorkbook.Names.Item(FoundNames(1))

        Else
            'ASK WHICH NAME TO CHANGE
            For CTR = 1 To FoundNames.Count
                Set NME = ActiveWorkbook.Names.Item(FoundNames(CTR))
                    STG = STG & CTR & ": " & NME.Name & " " & NME.RefersTo & vbCr
                Set NME = Nothing
            Next CTR
            On Error Resume Next
                IGR = 0
                IGR = CInt(InputBox(STG, "Which Name?"))
                If Err.Number <> 0 Then IGR = 0
            On Error GoTo 0
            If IGR = 0 Then GoTo DONE 'CANCELLED
            If IGR < 1 Or IGR > FoundNames.Count Then GoTo DONE 'ILLEGAL CHOICE
            Set NameToChange = FoundNames(IGR)
        End If
    Set FoundNames = Nothing

    'GET THE NEW NAME TO USE
    NewName = InputBox("Change to", , NameToChange.Name)
    If Trim(NewName) = "" Then GoTo DONE

    'TRY TO ADD THE NAME
    On Error Resume Next
        ActiveWorkbook.Names.Add NewName, NameToChange.RefersTo
        If Err.Number <> 0 Then
            'ILLEGAL NAME
            MsgBox "Could not add the name """ & NewName & """" '
            GoTo DONE
        End If
    On Error GoTo 0

    'REPLACE ALL REFERENCES IN FORUMALS
    For Each SHT In ActiveWorkbook.Worksheets
        Application.DisplayAlerts = False
            SHT.Cells.Replace _
                What:=NameToChange.Name, _
                Replacement:=NewName, _
                LookAt:=xlPart, _
                SearchOrder:=xlByRows, _
                MatchCase:=False, _
                MatchByte:=False, _
                SearchFormat:=False
        Application.DisplayAlerts = True
    Next SHT

    'REPLACE ALL REFERENCES IN OTHER NAMES
    For Each NME In ActiveWorkbook.Names
        If InStr(1, NME.RefersTo, NME.Name) <> 0 Then
            ActiveWorkbook.Names.Add NME.Name, Replace(NME.RefersTo, NME.Name, NewName)
        End If
    Next NME

    'REPLACE ALL REFERENCES IN CUSTOM VALIDATIONS
    For Each SHT In ActiveWorkbook.Worksheets
        On Error Resume Next
            Set ValidationCells = SHT.Cells.SpecialCells(xlCellTypeAllValidation)
        On Error GoTo 0
        If Not ValidationCells Is Nothing Then
            For Each RNG In ValidationCells.Cells
                On Error Resume Next
                    LNG = 0
                    LNG = RNG.Validation.Type
                    If Err.Number <> 0 Then LNG = 0
                On Error GoTo 0
                If LNG = xlValidateCustom Then
                    With RNG.Validation
                        If InStr(1, .Formula1, NameToChange.Name) <> 0 Then
                            .Modify Formula1:=Replace(.Formula1, NameToChange.Name, NewName)
                        End If
                    End With
                End If
            Next RNG
        End If
        Set ValidationCells = Nothing
    Next SHT

    'REPLACE ALL REFERENCES IN CONDITIONAL FORMATS
    For Each SHT In ActiveWorkbook.Worksheets
        On Error Resume Next
            Set ConditionCells = SHT.Cells.SpecialCells(xlCellTypeAllFormatConditions)
        On Error GoTo 0
        If Not ConditionCells Is Nothing Then
            For Each RNG In ConditionCells.Cells
                If RNG.FormatConditions.Count > 0 Then
                    For CTR = 1 To RNG.FormatConditions.Count
                        With RNG.FormatConditions
                            If .Item(CTR).Type = xlExpression Then
                                If InStr(1, .Item(CTR).Formula1, NameToChange.Name) > 0 Then
                                    .Item(CTR).Modify _
                                        Type:=xlExpression, _
                                        Formula1:=Replace(.Item(CTR).Formula1, _
                                        Find:=NameToChange.Name, _
                                        Replace:=NewName)
                                End If
                            End If
                        End With
                    Next CTR
                End If
            Next RNG
        End If
        Set ConditionCells = Nothing
    Next SHT

    'DELETE THE ORIGINAL NAME
    NameToChange.Delete

    MsgBox "Done!"

DONE:
    On Error GoTo 0
    Set TargetBook = Nothing
    Set FoundNames = Nothing
    Set TargetRange = Nothing
    Set NameToChange = Nothing

End Sub

Open in new window

0
 
dlmilleCommented:
Yes very, and I get a headache going through the hoop jumping (yours impressive as well).

It was probably a design intent to add =IF statements to Excel formula, allowing us to anticipate errors, and circumvent them.

=if(divisor = 0, don't divide by zero, divide by zero)

I'm not sure you need a formula parser to ensure name resolution doesn't have errors, but it is starting to FEEL that way...

Dave
0
 
Rory ArchibaldCommented:
I think you would have to parse the formulas and you would have to work out what is a defined name and what is a function name. Getting round the IF I don't think is that hard per se (change it to AND, then evaluate the formula) but you need to be able to ignore any other errors that might occur in the parts of the formula you are evaluating. You also need to parse because formulas like COUNTIF and SUMIF will happily take an invalid name as criteria and simply return 0, not an error.
0
 
shachoAuthor Commented:
Is there still no name auditing feature in 2010?  That would definitely be an improvement if not.
0
 
dlmilleCommented:
I've just got 2010 but not installed, give me a few days and I'll look (unless Sid/rorya want to look :)

Dave
0
 
Rory ArchibaldCommented:
It's no different in 2010, I'm afraid.

Regards,
Rory
0
 
shachoAuthor Commented:
Well I was starting to take a crack at a basic parser, but my priorities have suddenly been reoriented by a 9.0 earthquake, tsunami, and nuclear meltdown in my backyard....  Thanks all for contributing.  I'm just going to close and distribute.
0
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
shacho, our thoughts are with the Japanese people. Let's hope this meltdown can be avoided.

greetings, teylyn
0
 
shachoAuthor Commented:
Thanks for your encouragement.  The latest news is good.  Let's hope things continue to improve.
0
 
shachoAuthor Commented:
Not supported by Excel
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 13
  • 9
  • 7
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now