Excel VBA 动态命名范围：如何检查范围是否为空
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20293904/
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
Excel VBA Dynamic Named Ranges: How to check if range is blank
提问by Socii
I'm trying to write an excel sub that will populate an array from entries in a Named Range. The code I have for the range is:
我正在尝试编写一个 excel 子程序，它将从命名范围中的条目填充一个数组。我的范围代码是：
=OFFSET(Home!$B,1,0,COUNTA(Home!$B:$B)-1,1)
I'm trying to write a vba script which will check to see if the Named Range is populated before calling a function, but can't see to get anything to work. Trying:
我正在尝试编写一个 vba 脚本，该脚本将检查在调用函数之前是否填充了命名范围，但看不到任何工作。试：
Debug.Print ActiveSheet.Range("NamedRange")
returns a 1004 error. And trying:
返回 1004 错误。并尝试：
If (IsError(ActiveSheet.Names("NamedRange")) Then
returns the same error. Can anyone point me in the right direction?
返回相同的错误。任何人都可以指出我正确的方向吗？
回答by Siddharth Rout
Is this what you are trying?
这是你正在尝试的吗？
Sub Sample()
Dim rng As Range
Set rng = [NamedRange]
If Application.WorksheetFunction.CountA(rng) = 0 Then _
MsgBox "Range is blank"
End Sub
In VBA, you can reference the Excel's range object using the Range property. This can be made shorter using square brackets ([ ]). For example if you're making reference to cell A1, use "[A1]". The fully qualified reference for cell A1 in Sheet1 of Book1.xlsm will be Application.Workbooks(“Book1.xlsm”).Worksheets("Sheet1").[A1]
在 VBA 中，您可以使用 Range 属性引用 Excel 的范围对象。这可以使用方括号 ([ ]) 缩短。例如，如果您要引用单元格 A1，请使用“[A1]”。Book1.xlsm 的 Sheet1 中单元格 A1 的完全限定引用将是 Application.Workbooks(“Book1.xlsm”).Worksheets(“Sheet1”).[A1]
