测试 VBA 中是否存在范围
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12611900/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Test if range exists in VBA
提问by Colten J Nye
I have a dynamically defined named range in my excel ss that grabs data out of a table based on a start date and an end date like this
我的 excel ss 中有一个动态定义的命名范围，它根据这样的开始日期和结束日期从表中提取数据
=OFFSET(Time!$A,IFERROR(MATCH(Date_Range_Start,AllDates,0)-1,MATCH(Date_Range_Start,AllDates)),1,MATCH(Date_Range_End,AllDates)-IFERROR(MATCH(Date_Range_Start,AllDates,0)-1,MATCH(Date_Range_Start,AllDates)),4)
But if the date range has no data in the table, the range doesn't exists (or something, idk). How can I write code in VBA to test if this range exists or not?
但是，如果日期范围在表中没有数据，则该范围不存在（或诸如 idk 之类的东西）。如何在 VBA 中编写代码来测试此范围是否存在？
I have tried something like
我试过类似的东西
If Not Range("DateRangeData") Is Nothing Then
but I get "Runtime error 1004, method 'Range' of object '_Global' failed."
但我收到“运行时错误 1004，对象 '_Global' 的方法 'Range' 失败。”
回答by Steztric
Here is a function I knocked up to return whether a named range exists. It might help you out.
这是我调用的一个函数，用于返回命名范围是否存在。它可能会帮助你。
Function RangeExists(R As String) As Boolean
Dim Test As Range
On Error Resume Next
Set Test = ActiveSheet.Range(R)
RangeExists = Err.Number = 0
End Function
回答by nutsch
You can replicate the match in your VBA to count before using the range how many rows you would have, or you can use error handling:
您可以在 VBA 中复制匹配项以在使用范围之前计算您将拥有的行数，或者您可以使用错误处理：
On Error Resume Next
Debug.Print range("DateRangeData").Rows.Count
If Err = 1004 Then
MsgBox "Range Empty"
Exit Sub
Else
MsgBox "Range full"
End If
Err.Clear
On Error GoTo 0
回答by codea
This is another approach. It has the advantage to take the container and the name you want to test. That means you can test either Sheets Names or Workbook Names for example.
这是另一种方法。使用您要测试的容器和名称具有优势。这意味着您可以测试例如工作表名称或工作簿名称。
Like this:
像这样：
If NamedRangeExists(ActiveSheet.Names, "Date") Then
...
Else
...
End If
or
或者
If NamedRangeExists(ActiveWorkbook.Names, "Date") Then
...
Else
...
End If
Public Function NamedRangeExists(ByRef Container As Object, item As String) As Boolean
Dim obj As Object
Dim value As Variant
On Error GoTo NamedRangeExistsError:
value = Container(item)
If Not InStr(1, CStr(value), "#REF!") > 0 Then
NamedRangeExists = True
End If
Exit Function
Exit Function
NamedRangeExistsError:
NamedRangeExists = False
End Function
回答by FCastro
Depending on the application you're doing, it's good to consider using a Dictionary. They're especially useful when you wanna check whether something exists. Take this example:
根据您正在执行的应用程序，最好考虑使用字典。当您想检查某些东西是否存在时，它们特别有用。拿这个例子：
Dim dictNames as Scripting.Dictionary
Sub CheckRangeWithDictionary()
Dim nm As Name
'Initially, check whether names dictionary has already been created
If Not dictNames Is Nothing Then
'if so, dictNames is set to nothing
Set dictNames = Nothing
End If
'Set to new dictionary and set compare mode to text
Set dictNames = New Scripting.Dictionary
dictNames.CompareMode = TextCompare
'For each Named Range
For Each nm In ThisWorkbook.Names
'Check if it refers to an existing cell (bad references point to "#REF!" errors)
If Not (Strings.Right(nm.RefersTo, 5) = "#REF!") Then
'Only in that case, create a Dictionary entry
'The key will be the name of the range and the item will be the address, worksheet included
dictNames(nm.Name) = nm.RefersTo
End If
Next
'You now have a dictionary of valid named ranges that can be checked
End Sub
Within your main procedure, all you need to do is do an existence check before using the range
在您的主要程序中，您需要做的就是在使用范围之前进行存在检查
Sub CopyRange_MyRange()
CheckRangeWithDictionary
If dictNames.exists("MyRange") then
Sheets(1).Range("MyRange").Copy
end if
End Sub
While loading the dictionary may look a little longer, it's extremely fast to process and search. It also becomes much simpler to check whether any named range referring to a valid address exists, without using error handlers in this simple application.
虽然加载字典可能看起来有点长，但处理和搜索的速度非常快。在这个简单的应用程序中无需使用错误处理程序，检查是否存在任何引用有效地址的命名范围也变得更加简单。
Please note that when using names at sheet level rather than workbook level, it is necessary to use more elaborate keys to guarantee uniqueness. From the way the dictionary was created, if a key is repeated, the item value is overwritten. That can be avoided by using the same Existsmethod as a check in the key creation statement. If you need a good reference on how to use dictionaries, use this one.
请注意，在工作表级别而不是工作簿级别使用名称时，有必要使用更精细的键来保证唯一性。从字典的创建方式来看，如果某个键重复，则项目值将被覆盖。这可以通过使用与密钥创建语句中的检查相同的Exists方法来避免。如果您需要关于如何使用字典很好的参考，使用此一个。
Good luck!
祝你好运！
