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)

If you were to use an array of strings then you could just use the VBA [Filter](https://learn.microsoft.com/en-gb/office/vba/language/reference/user-interface-help/filter-function) function to determine inclusion — Spectral Instance, May 13 '22 at 00:01
You could also use the Evaluate() method - unless the return value was "Error 2029" you'd know that the name existed in the workbook — Spectral Instance, May 13 '22 at 00:08
Im still pretty iffy on Evaluate..(well, on all of this!) how would I use that to check if there's a Name in a workbook that matches the collection item? — J_Nyiri, May 13 '22 at 00:28
Both my suggestions were in lieu of a Collection, as they both avoid the need for loops: if `Application.Evaluate(queriedName)` returns "Error 2029" then you know that the name doesn't exist anywhere in the workbook. You could also, with error trapping use `Application.Goto queriedName` - if there's an error then you know the name doesn't exist — Spectral Instance, May 13 '22 at 00:35
It should also be borne in mind that the [`Names` object](https://learn.microsoft.com/en-gb/office/vba/api/excel.names) is a built-in collection of all the names in a workbook — Spectral Instance, May 13 '22 at 01:01
And then how do I pass the collection item to the evaluate function? Thats where my problem is; if I can get a string to any of a variety of functions I can check if that string is a name...but I can't figure out how to get the items in my collection to pass to the checking functions as a string. Check_UW_For_Name works fine when it gets a string; I just cant get my collection items to be passed to it as strings, I just keep getting ByRef argument type mismatch no matter how I try to send the collection items (Even if I set Check_UW_For_Name(ByVal find_name as string) — J_Nyiri, May 13 '22 at 01:05
Basically the error in your code is, that an item of a collection does not have the property "name". Maybe it would help you to read the documentation for collections. In this scenario an array with all expected "Names" would be sufficient - in my eyes — Ike, May 13 '22 at 07:56

score 1 · Answer 1 · answered May 13 '22 at 00:12

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

Hi, thanks! This is hard for me to understand, but I have no problem adding the strings I need to the collection. I'm not looking for Names in the workbook to add to the collection (which I ?think? is what this does?), I'm using the collection to see if those strings already are Names are in the workbook. Not all of them refer to ranges; some are named formulas. I.e. my collection has two items, "UserFormula" and "UserRange", and I need to check UserWorkbook to see if it has a named formula UserFormula and a named range UserRange. I will create the collection of things to search for myself. — J_Nyiri, May 13 '22 at 00:27

score 0 · Accepted Answer · answered May 13 '22 at 01:02

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

Trying this method, but same problem; I get a type mismatch. ```Dim colCritNames As New Collection colCritNames.Add "Version" Dim nm For Each nm In colCritNames If Not (Check_Name(CStr(nm))) Then Call StartFail("Critical Name") 'error handler Next nm Function Check_Name(find_name As String) As Boolean On Error Resume Next Check_Name = (UserFileBook.Names(find_name).Name = find_name) End Function``` — J_Nyiri, May 13 '22 at 01:18
what's the code for `StartFail`? Add any code to your question - it's not readable as a comment. — Tim Williams, May 13 '22 at 03:13

score 0 · Answer 3 · answered May 13 '22 at 07:43

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.

Using a collection to check if Names exist

3 Answers3