我正在嘗試創建一個子例程，它將獲取一堆字串的集合，單步執行它，并檢查是否存在以該字串為名稱的命名范圍或公式。先嘗試一個專案：
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
那是從完整的東西中編輯的。當我用“版本”作為引數呼叫它時，Check_UW_For_Name 作業正常Check_UW_For_Name("Version")；它在 USerFIleBook 中找到它，當我用“Nope”呼叫它時，由于沒有 Nope 名稱，它進入了我的錯誤處理程式。但是，當我嘗試使用集合來存盤我想要查找的名稱時，我不斷收到“ByRef 引數不匹配”。我試過了nm = colCritNames(i)，nm=colCritNames(i).Name我嘗試讓 find_name 成為 Variant 并添加一個 ByVal，我最初嘗試讓 nm 成為一個名稱，使用 Check_UW_For_Name(find_name as Name) 并為每個使用一個（對于 colCritNames 中的每個 nm ...）并且沒有它的作業。
我如何設定名稱集合并逐步查看它以查看相關作業簿中是否存在匹配的命名范圍/公式？還是有更好的方法來做到這一點？（我也需要其他地方的收藏）
uj5u.com熱心網友回復：
這對我有用：
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
uj5u.com熱心網友回復：
我不太明白您對集合的計劃是什么，但這將添加任何具有指定字串的單元格以及任何范圍。一旦他們被識別（添加到收藏中）你在做什么我不清楚，但希望這是有道理的，讓你繼續前進。
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
uj5u.com熱心網友回復：
您可以像這樣在作業簿中創建所有命名范圍的集合：
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
然后將您想要的任何字串與NamedRanges集合進行比較。
順便說一句，這個問題和你的有點相似。
轉載請註明出處，本文鏈接：https://www.uj5u.com/caozuo/473437.html