Using a collection to check if Names exist

Question

I am trying to create a subroutine that will take a collection of a bunch of strings, step through it, and check for the existence of a named range or formula that has that string as it's name. Trying it with just one item first:

Dim colCritNames As New Collection
colCritNames.Add "Version" 'the name of a named formula

For i = 1 To colCritNames.Count
    nm = CStr(colCritNames(i).Name)
    nmchk = Check_UW_For_Name(nm)
    If Not nmchk Then Call Fail("Critical Name")  'prints a msgbox with the error type so I know what happened
Next i
'...code for if all the names are there...

Function Check_UW_For_Name(find_name As String) As Boolean
Dim wb As Workbook
Set wb = UserFileBook  'global ref to the workbook to check

On Error Goto Fail
Check_UW_For_Name = CBool(Len(wb.Names(find_name).Name) <> 0)
On Error GoTo 0
End Function

Thats edited from the full thing. Check_UW_For_Name was working fine when I just called it with "Version" as the argument Check_UW_For_Name("Version"); it found it in USerFIleBook, and when I called it with "Nope", since there is no Nope name it went to my error handler. But when I try to use a collection to store the names I want to look for I keep getting 'ByRef argument mismatch'. I tried just nm = colCritNames(i) and nm=colCritNames(i).Name, I tried having find_name be Variant and adding a ByVal, and I originally tried having nm be a Name, having Check_UW_For_Name(find_name as Name) and using a for each (for each nm in colCritNames...) and none of it has worked.

How could I set a collection of names and step through it to see if there's a named range/formula that matches in the relevant workbook? Or is there a better way to do this? (I need the collection in other places too)

Answer 1

This works for me:

Sub Tester()
    Dim colCritNames As New Collection, nm, wb As Workbook, msg As String
    
    colCritNames.Add "Version"
    colCritNames.Add "NotThere"
    colCritNames.Add "AlsoNotThere"
    
    Set wb = ThisWorkbook 'for example
    For Each nm In colCritNames
        If Not Check_UW_For_Name(wb, CStr(nm)) Then
            msg = msg & vbLf & " - " & nm
        End If
    Next nm
    
    If Len(msg) > 0 Then
        MsgBox "One or more required names are missing:" & msg, _
                vbExclamation, "Oops"
        Exit Sub
    End If
    
    'proceed if OK...
    
End Sub

'check for a defined Name `find_name` in workbook `wb`
' prefer wb as parameter over using a Global....
Function Check_UW_For_Name(wb As Workbook, find_name As String) As Boolean
    On Error Resume Next
    Check_UW_For_Name = (wb.Names(find_name).Name = find_name)
End Function

Answer 2

I don't quite understand what your plan is with a collection, but this will add any cell with the specified string in, as well as any ranges. What you're doing once they've been identified (added to collection) is not clear to me, but hopefully this makes sense and gets you going.

Sub RunForEachString()
   Const yourStrings = "foo,bar,hope,this,works"
   Dim stringsAsArray() As String
   stringsAsArray = Split(yourStrings, ",")
   
   Dim i As Long
   
   For i = LBound(stringsAsArray) To UBound(stringsAsArray)
     Call findAllNamesFormulas(stringsAsArray(i), ThisWorkbook)
   Next i
   
End Sub


Private Sub findAllNamesFormulas(theText As String, theWorkbook As Workbook)
   Dim ws As Worksheet, n As Name, aCell As Range
   Dim aCollection As New Collection
   
   For Each ws In ThisWorkbook.Worksheets
      For Each aCell In ws.UsedRange.Cells
         If InStr(1, aCell.Formula, theText, vbTextCompare) > 0 Then
            aCollection.Add (aCell)
         End If
         
      Next aCell
   Next ws

   For Each n In ThisWorkbook.Names
      If InStr(1, n.Name, theText, vbTextCompare) > 0 Then
         aCollection.Add (n)
      End If
   Next n
   
   'not sure what you plan to do after collection?
   Debug.Print aCollection.Count
End Sub

Answer 3

You could create a collection of all named ranges in the workbook like this:

Private Sub NamedRangesDemo()

    Dim NamedRanges As New Collection, NamedRange As Variant
    
    For Each NamedRange In ThisWorkbook.Names
    
        NamedRanges.Add NamedRange.Name
        
    Next NamedRange

End Sub

And then compare the whatever strings you want to the NamedRanges collection.

By the way, this question is somewhat similar to yours.