Excel VBA动态命名范围:如何检查范围是否为空白 [英] Excel VBA Dynamic Named Ranges: How to check if range is blank

问题描述

我正在尝试编写一个excel子程序，该子程序将从命名范围"中的条目填充数组.我具有该范围的代码是:

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:

=OFFSET(Home!$B$5,1,0,COUNTA(Home!$B:$B)-1,1)

我正在尝试编写一个vba脚本，该脚本将在调用函数之前检查命名范围是否已填充，但看不到任何可用的东西.正在尝试:

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:

Debug.Print ActiveSheet.Range("NamedRange")

返回1004错误.并尝试:

returns a 1004 error. And trying:

If (IsError(ActiveSheet.Names("NamedRange")) Then

返回相同的错误.谁能指出我正确的方向?

returns the same error. Can anyone point me in the right direction?

推荐答案

这是您要尝试的吗?

Sub Sample()
    Dim rng As Range        
    Set rng = [NamedRange]        
    If Application.WorksheetFunction.CountA(rng) = 0 Then _
    MsgBox "Range is blank"
End Sub

在VBA中，您可以使用Range属性引用Excel的range对象.可以使用方括号([])将其缩短.例如，如果您要引用单元格A1，请使用"[A1]". Book1.xlsm的Sheet1中单元格A1的完全合格的引用将是Application.Workbooks("Book1.xlsm").Worksheets("Sheet1").[A1]

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]

这篇关于Excel VBA动态命名范围:如何检查范围是否为空白的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！