在 VBA 中指定跨工作表的 Excel 范围

Specify an Excel range across sheets in VBA

提问by OfficeAddinDev

In VBA, why does the following fail?

在 VBA 中，为什么以下失败？

Dim rng as Range rng = Range("Sheet1:Sheet3!A1")

Dim rng as Range rng = Range("Sheet1:Sheet3!A1")

It throws an HRESULT exception. Is there another way to construct this range in VBA? Note that you can enter a worksheet function like =SUM(Sheet1:Sheet3!A1) and it works fine.

它会引发 HRESULT 异常。有没有另一种方法可以在 VBA 中构建这个范围？请注意，您可以输入像 =SUM(Sheet1:Sheet3!A1) 这样的工作表函数，它工作正常。

回答by Gary's Student

A Rangeobject is limited to only one worksheet. After all, it can have only a single parent.

一个Range对象仅限于一个工作表。毕竟，它只能有一个单亲。

The =SUM() function can operate on a group of ranges. (this is true for many worksheet functions)

=SUM() 函数可以对一组范围进行操作。（这适用于许多工作表函数）

EDIT#1

编辑#1

I have been searching for a solution since Janauary:

自一月以来，我一直在寻找解决方案：

UDF Syntax

UDF 语法

.

.

I have been using an array of ranges. Not a very good solution.

我一直在使用一系列范围。不是一个很好的解决方案。

回答by feelthhis

Just developing Gary's answer (if you're going to accept an answer, accept his :):

只是开发加里的答案（如果您要接受答案，请接受他的 :)：

Using Rangevariable:

使用Range变量：

Sub SumTest1()

    Dim rSheet1     As Range
    Dim rSheet2     As Range
    Dim rSheet3     As Range
    Dim dSum        As Double

    With ThisWorkbook
        Set rSheet1 = .Sheets("Sheet1").Range("A1")
        Set rSheet2 = .Sheets("Sheet2").Range("A1")
        Set rSheet3 = .Sheets("Sheet3").Range("A1")
    End With

    dSum = WorksheetFunction.Sum(rSheet1, rSheet2, rSheet3)
    Debug.Print CStr(dSum)

End Sub

Using Variantvariable:

使用Variant变量：

Sub SumTest2()

    Dim vArray      As Variant
    Dim dSum        As Double

    With ThisWorkbook
        vArray = Array(.Sheets("Sheet1").Range("A1"), .Sheets("Sheet2").Range("A1"), .Sheets("Sheet3").Range("A1"))
    End With

    dSum = WorksheetFunction.Sum(vArray)
    Debug.Print CStr(dSum)

End Sub

Using no variable:

不使用变量：

Sub SumTest3()

    Dim dSum        As Double

    With ThisWorkbook
        dSum = WorksheetFunction.Sum(Array(.Sheets("Sheet1").Range("A1"), .Sheets("Sheet2").Range("A1"), .Sheets("Sheet3").Range("A1")))
    End With

    Debug.Print CStr(dSum)

End Sub

回答by airstrike

Here's a set of UDF functions that accomplish essentially the same thing. The only caveat is that the reference to the 3D range is a string i.e. "Jan:Dec!A1"as opposed to straight up Jan:Dec!A1

下面是一组完成相同功能的 UDF 函数。唯一需要注意的是，对 3D 范围的引用是一个字符串，即"Jan:Dec!A1"与直线相反Jan:Dec!A1

'Adapted from https://web-beta.archive.org/web/20060313132405/http://www.j-walk.com/ss/excel/eee/eee003.txt by Andre Terra
Function CountIf3D(Range3D As String, Criteria As String, _
    Optional Count_Range As Variant) As Variant

    Dim sTestRange As String
    Dim sCountRange As String
    Dim Sheet1 As Integer
    Dim Sheet2 As Integer
    Dim n As Integer
    Dim Count As Double

    Application.Volatile

    If Parse3DRange(Application.Caller.Parent.Parent.Name, _
      Range3D, Sheet1, Sheet2, sTestRange) = False Then
      CountIf3D = CVErr(xlErrRef)
    End If

    If IsMissing(Count_Range) Then
      sCountRange = sTestRange
    Else
      sCountRange = Count_Range.Address
    End If

    Count = 0
    For n = Sheet1 To Sheet2
      With Worksheets(n)
        Count = Count + Application.WorksheetFunction.CountIf(.Range _
    (sTestRange), Criteria)
      End With
    Next n
    CountIf3D = Count
End Function  'CountIf3D

Function SumIf3D(Range3D As String, Criteria As String, _
    Optional Sum_Range As Variant) As Variant

    Dim sTestRange As String
    Dim sSumRange As String
    Dim Sheet1 As Integer
    Dim Sheet2 As Integer
    Dim n As Integer
    Dim Sum As Double

    Application.Volatile

    If Parse3DRange(Application.Caller.Parent.Parent.Name, _
      Range3D, Sheet1, Sheet2, sTestRange) = False Then
      SumIf3D = CVErr(xlErrRef)
    End If

    If IsMissing(Sum_Range) Then
      sSumRange = sTestRange
    Else
      sSumRange = Sum_Range.Address
    End If

    Sum = 0
    For n = Sheet1 To Sheet2
      With Worksheets(n)
        Sum = Sum + Application.WorksheetFunction.SumIf(.Range _
    (sTestRange), Criteria, .Range(sSumRange))
      End With
    Next n
    SumIf3D = Sum
End Function  'SumIf3D

Function AverageIf3D(Range3D As String, Criteria As String, _
    Optional Average_Range As Variant) As Variant

    Dim sTestRange As String
    Dim sSumRange As String
    Dim Sheet1 As Integer
    Dim Sheet2 As Integer
    Dim n As Integer
    Dim Sum As Double
    Dim Count As Double

    Application.Volatile

    If Parse3DRange(Application.Caller.Parent.Parent.Name, _
      Range3D, Sheet1, Sheet2, sTestRange) = False Then
      AverageIf3D = CVErr(xlErrRef)
    End If

    If IsMissing(Average_Range) Then
      sSumRange = sTestRange
    Else
      sSumRange = Average_Range.Address
    End If

    Sum = 0
    Count = 0
    For n = Sheet1 To Sheet2
      With Worksheets(n)
        Sum = Sum + Application.WorksheetFunction.SumIf(.Range(sTestRange), Criteria, .Range(sSumRange))
        Count = Count + Application.WorksheetFunction.CountIf(.Range(sTestRange), Criteria)
      End With
    Next n
    AverageIf3D = Sum / Count
End Function  'SumIf3D

Function Parse3DRange(sBook As String, SheetsAndRange _
    As String, FirstSheet As Integer, LastSheet As Integer, _
    sRange As String) As Boolean

    Dim sTemp As String
    Dim i As Integer
    Dim Sheet1 As String
    Dim Sheet2 As String

    Parse3DRange = False
    On Error GoTo Parse3DRangeError

    sTemp = SheetsAndRange
    i = InStr(sTemp, "!")
    If i = 0 Then Exit Function

    'next line will generate an error if range is invalid
    'if it's OK, it will be converted to absolute form
    sRange = Range(Mid$(sTemp, i + 1)).Address

    sTemp = Left$(sTemp, i - 1)
    i = InStr(sTemp, ":")
    Sheet2 = Trim(Mid$(sTemp, i + 1))
    If i > 0 Then
      Sheet1 = Trim(Left$(sTemp, i - 1))
    Else
      Sheet1 = Sheet2
    End If

    'next lines will generate errors if sheet names are invalid
    With Workbooks(sBook)
    FirstSheet = .Worksheets(Sheet1).Index
    LastSheet = .Worksheets(Sheet2).Index

    'swap if out of order
    If FirstSheet > LastSheet Then
      i = FirstSheet
      FirstSheet = LastSheet
      LastSheet = i
    End If

    i = .Worksheets.Count
    If FirstSheet >= 1 And LastSheet <= i Then
      Parse3DRange = True
    End If
    End With
Parse3DRangeError:
    On Error GoTo 0
    Exit Function

End Function  'Parse3DRange

回答by guitarthrower

Untested, but try this

未经测试，但试试这个

Dim rng as string
rng = "Sheet1:Sheet3!A1"
worksheet("Sheet1").range("B1").formula = "=SUM(" & rng & ")"