vba - Excel VBA - 为联合方法动态提供范围

Question

用户已定义要在 Excel 中打印的命名范围。

我正在将这些范围读入 VBA 数组。有没有办法将范围名称提供给 Union 方法以设置不连续的打印范围。

例如，类似:ActiveSheet.PageSetup.PrintArea = Union(Range(array(1)), Range(array(2))).Address
数组中保存的范围数量可能会有所不同。我已经尝试过遍历数组并构建一个字符串变量，但没有成功。

任何帮助，将不胜感激。

Answer 1

您必须替换语句中的实际范围名称或对象，但这里是如何使用 Union设置 PrintArea 的函数:

Sub foo()
    Dim setup As PageSetup
    Set setup = ActiveSheet.PageSetup

    setup.PrintArea = Union(Range("MyRange1"), Range("MyRange2")).Address
End Sub

What I'm actually looking for is a method to construct the Union statement using range names that are held in an array

OK，那就用上面的方法和一个自定义函数循环构造Union:

Sub foo()
    Dim setup As PageSetup
    Dim RangeArray(1) As Range

    Set setup = ActiveSheet.PageSetup
    Set RangeArray(0) = Range("MyRange1")
    Set RangeArray(1) = Range("MyRange2")

    setup.PrintArea = GetUnion(RangeArray)
End Sub
Function GetUnion(arr As Variant) As String
    Dim itm As Variant
    Dim ret As Range
    For Each itm In arr
        If Not ret Is Nothing Then
            Set ret = Union(ret, itm)
        Else
            Set ret = itm
        End If
    Next
    If Not ret Is Nothing Then
        GetUnion = ret.Address
    Else
        GetUnion = ""  'May cause an error...
    End If
End Function