excel - Excel VBA：从 UDF 的范围中选择子范围的各种方法

Question

我在这个网站上看到了从一个范围中选择一个子范围的各种方法，我正在努力理解这些差异。

对于以下设置：

我有以下代码：

Option Explicit


Function TestX(rg As Range) As Range

    Dim subrg1 As Range, subrg2 As Range, subrg3 As Range

    Set subrg1 = rg.Parent.Range(rg.Cells(4, 4), rg.Cells(9, 10))
    Set subrg2 = Range(Cells(4, 4), Cells(9, 10))
    Set subrg3 = rg.Range(Cells(4, 4), Cells(9, 10))
        
    Debug.Print subrg1.Cells(1).Value, subrg1.Count
    Debug.Print subrg2.Cells(1).Value, subrg2.Count
    Debug.Print subrg3.Cells(1).Value, subrg3.Count
    
End Function

日志如下：

 16            42 
 1             42 
 16            42 

这三种方式有什么区别？

我只能理解 subrg2，这似乎是一种直接、绝对的选择范围的方式D4:J9。

标签： excelvba

Answer 1

定义子范围的几种方法

• 该getSubRange功能应该满足您的要求。
• 如果绿色范围的大小可能发生变化，该getSubRange2功能会派上用场。

编码

Option Explicit

Sub getSubRangeTEST()
    
    Dim rg As Range: Set rg = Range("B3:K10")
    Dim srg As Range: Set srg = getSubRange(rg)
    Debug.Print rg.Address, srg.Address
    
    Set srg = getSubRange2(rg)
    If Not srg Is Nothing Then
        Debug.Print rg.Address, srg.Address
    End If

End Sub

Function getSubRange(rg As Range) As Range
    
    Const rRows As Long = 6
    Const rCols As Long = 7
    Const oRows As Long = 1
    Const oCols As Long = 2
    
    If Not rg Is Nothing Then
        With rg
            Set getSubRange = .Offset(oRows, oCols).Resize(rRows, rCols)
            'Set getSubRange = .Offset(1, 2).Resize(6, 7) ' without the constants
            'Set getSubRange = .Cells(oRows + 1, oCols + 1).Resize(rRows, rCols)
            'Set getSubRange = .Cells(2, 3).Resize(6, 7) ' without the constants
        End With
    End If

End Function

Function getSubRange2(rg As Range) As Range
    
    Const rRows As Long = 2
    Const rCols As Long = 3
    Const oRows As Long = 1
    Const oCols As Long = 2
    
    If Not rg Is Nothing Then
        With rg
            On Error Resume Next
            Set getSubRange2 = .Resize(.Rows.Count - rRows, _
                .Columns.Count - rCols).Offset(oRows, oCols)
            'Set getSubRange2 = .Resize(.Rows.Count - 2, _
                .Columns.Count - 3).Offset(1, 2) ' without the constants
            On Error GoTo 0
        End With
    End If

End Function