检查 vba 模块中的范围是否为空
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14790662/
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
check if a range is empty in vba module
提问by Sanket
I wanted to check if an excel range in empty in a section of code in user module. I used the below code
我想检查用户模块中的一段代码中的 excel 范围是否为空。我使用了下面的代码
Worksheets(yearsheet).Range("N" & rownum & ":DI").Select
If Application.WorksheetFunction.CountA(Selection) = 0 Then
Exit Sub
End If
I'm getting runtime error 1004. Can anyone tell whats my mistake?
我收到运行时错误 1004。谁能告诉我我的错误是什么？
Thanks in advance. PS: rownum is integer variable and yearsheet is string variable. both these variables were updated properly in code prior to the above section of the code
提前致谢。PS：rownum 是整数变量，yearsheet 是字符串变量。在代码的上述部分之前，这两个变量都在代码中正确更新
采纳答案by Doug Glancy
"N" & rownum & ":DI"doesn't evaluate to a real address because it's missing the row number for the second half of the address. Also, you should avoid using Select statement whenever possible.
"N" & rownum & ":DI"不计算为真实地址，因为它缺少地址后半部分的行号。此外，您应该尽可能避免使用 Select 语句。
Assuming the whole range is in one row, this would work:
假设整个范围在一行中，这将起作用：
Sub test()
Dim yearsheet As String
Dim rownum As Integer
yearsheet = "Sheet2"
rownum = 2
If Application.WorksheetFunction.CountA(Worksheets(yearsheet) _
.Range("N" & rownum & ":DI" & rownum)) = 0 Then
Exit Sub
End If
End Sub
回答by Harm
The best way to test if a selection is (not) empty in VBA:
在 VBA 中测试选择是否为（非）空的最佳方法：
' Tests if a selection of cells exists.
' @return true or false
Function isCellSelection() As Boolean
Dim r As range
isCellSelection = False
Set r = Selection.Cells
If IsEmpty(r) Then
isCellSelection = True
End If
End Function ' isCellSelection
