Solved

Testing Name Resolution

Posted on 2011-02-28
32
218 Views
Last Modified: 2012-06-21
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
Comment
Question by:shacho
  • 13
  • 9
  • 7
  • +2
32 Comments
 
LVL 30

Expert Comment

by:SiddharthRout
Comment Utility
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
 

Author Comment

by:shacho
Comment Utility
Please reread the question.
0
 

Author Comment

by:shacho
Comment Utility
One typo in the question:
Is there any inbuilt way to test the resolution of all [names] in all formulas in a workbook?
0
 
LVL 30

Expert Comment

by:SiddharthRout
Comment Utility
>> 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
 

Author Comment

by:shacho
Comment Utility
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
 

Author Comment

by:shacho
Comment Utility
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
 
LVL 30

Expert Comment

by:SiddharthRout
Comment Utility
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
 
LVL 30

Expert Comment

by:SiddharthRout
Comment Utility
>> 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
 

Author Comment

by:shacho
Comment Utility
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
 
LVL 30

Expert Comment

by:SiddharthRout
Comment Utility
I see what you mean...

Give me few moments.

Sid
0
 
LVL 41

Expert Comment

by:dlmille
Comment Utility
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
 
LVL 41

Expert Comment

by:dlmille
Comment Utility
Also, just pull up the name manager, and you can sort by column to see the #VALUE, #REF, etc., errors...

Dave
0
 
LVL 30

Assisted Solution

by:SiddharthRout
SiddharthRout earned 166 total points
Comment Utility
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
 
LVL 30

Expert Comment

by:SiddharthRout
Comment Utility
Please read

>>>It is an question.

as

It is an Excellent question

in the above post.

Sid
0
 

Author Comment

by:shacho
Comment Utility
>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
 
LVL 41

Expert Comment

by:dlmille
Comment Utility
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
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
LVL 41

Expert Comment

by:dlmille
Comment Utility
Ribbon->Formulas-Names-> Name Manager

:)

0
 
LVL 41

Expert Comment

by:dlmille
Comment Utility
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
 
LVL 41

Expert Comment

by:dlmille
Comment Utility
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
 

Author Comment

by:shacho
Comment Utility
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
 
LVL 41

Expert Comment

by:dlmille
Comment Utility
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
 

Author Comment

by:shacho
Comment Utility
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
 

Author Comment

by:shacho
Comment Utility
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
 
LVL 41

Assisted Solution

by:dlmille
dlmille earned 167 total points
Comment Utility
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
 
LVL 85

Assisted Solution

by:Rory Archibald
Rory Archibald earned 167 total points
Comment Utility
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
 

Author Comment

by:shacho
Comment Utility
Is there still no name auditing feature in 2010?  That would definitely be an improvement if not.
0
 
LVL 41

Expert Comment

by:dlmille
Comment Utility
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
 
LVL 85

Expert Comment

by:Rory Archibald
Comment Utility
It's no different in 2010, I'm afraid.

Regards,
Rory
0
 

Accepted Solution

by:
shacho earned 0 total points
Comment Utility
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
 
LVL 50

Expert Comment

by:teylyn
Comment Utility
shacho, our thoughts are with the Japanese people. Let's hope this meltdown can be avoided.

greetings, teylyn
0
 

Author Comment

by:shacho
Comment Utility
Thanks for your encouragement.  The latest news is good.  Let's hope things continue to improve.
0
 

Author Closing Comment

by:shacho
Comment Utility
Not supported by Excel
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Drop Down List with Unique/Distinct Values (enhancing the Combo-Box with a few steps and a little code) David miller (dlmille) Intro Have you ever created a data validation list from a database field or spreadsheet column (e.g., Zip Codes or Co…
Dealing with unintended Excel Active-X resizing quirks (VBA code simulates "self correction") David Miller (dlmille) Intro Not everyone is a fan of Active-X controls in spreadsheets (as opposed to the UserForm approach, the older Form controls …
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now