代码之家 › 专栏 › 技术社区 › barciewicz

如何获取嵌套在集合中的范围的地址/值

excel-vba vba excel

barciewicz · 技术社区 · 6 年前

我收集了以下范围:

Dim all_pivots_amounts As New Collection

all_pivots_amounts.Add (corporate_amounts)
all_pivots_amounts.Add (wealth_amounts)
all_pivots_amounts.Add (institutional_amounts)
all_pivots_amounts.Add (premium_amounts)
all_pivots_amounts.Add (one_bank_one_bank_amounts)
all_pivots_amounts.Add (one_bank_entrepreneurs_amounts)

Debug.Print corporate_amounts.Address
Debug.Print all_pivots_amounts(1).Address ' this line gives object required 
    error

我不知道为什么第二个 Debug.print 行给出“对象所需错误”(我也尝试过 .Items 方法)。我需要使用类似的东西,因为我想在一个循环中打印地址,而不是专门调用每个范围。

完整代码:

Sub BA_view_new()

Application.EnableEvents = False

Call SetWorkbooks
Call update_pivot_data_sources

Dim corporate_table As PivotTable
Dim wealth_table As PivotTable
Dim institutional_table As PivotTable
Dim premium_table As PivotTable
Dim one_bank_one_bank_table As PivotTable
Dim one_bank_entrepreneurs As PivotTable

Dim corporate_amounts As Range
Dim wealth_amounts As Range
Dim institutional_amounts As Range
Dim premium_amounts As Range
Dim one_bank_one_bank_amounts As Range
Dim one_bank_entrepreneurs_amounts As Range


'get pivots
Set corporate_table = BA_view_pivots_sheet.PivotTables("Corporate & Investment Banking")
Set wealth_table = BA_view_pivots_sheet.PivotTables("Wealth Management & Private Clients")
Set institutional_table = BA_view_pivots_sheet.PivotTables("Institutional Clients")
Set premium_table = BA_view_pivots_sheet.PivotTables("Premium Clients CH")
Set one_bank_one_bank_table = BA_view_pivots_sheet.PivotTables("One Bank Switzerland->One Bank Switzerland")
Set one_bank_entrepreneurs = BA_view_pivots_sheet.PivotTables("One Bank Switzerland->Bank For Entrepreneurs")

'get pivots' amounts
Set corporate_amounts = corporate_table.DataBodyRange
Set wealth_amounts = wealth_table.DataBodyRange
Set institutional_amounts = institutional_table.DataBodyRange
Set premium_amounts = premium_table.DataBodyRange
Set one_bank_one_bank_amounts = one_bank_one_bank_table.DataBodyRange
Set one_bank_entrepreneurs_amounts = one_bank_entrepreneurs.DataBodyRange

'create collection
Dim all_pivots_amounts As New Collection

all_pivots_amounts.Add (corporate_amounts)
all_pivots_amounts.Add (wealth_amounts)
all_pivots_amounts.Add (institutional_amounts)
all_pivots_amounts.Add (premium_amounts)
all_pivots_amounts.Add (one_bank_one_bank_amounts)
all_pivots_amounts.Add (one_bank_entrepreneurs_amounts)

Debug.Print corporate_amounts.Address
Debug.Print all_pivots_amounts(1).Address ' this line gives object required 
error

2 回复 | 直到 6 年前

Vityata 6 年前

您需要限定作为范围或对象添加的范围。否则,VBA会考虑它们的变体。以及 Variants 没有 .Address 属性。因此,需要一个对象:

Sub TestMe()

    Dim all_pivots_amounts As New Collection
    Dim someRange As Range
    Dim someOtherRange As Range

    Set someRange = Range("A1:A10")
    Set someOtherRange = Range("A66")

    all_pivots_amounts.Add someRange
    all_pivots_amounts.Add someOtherRange

    Debug.Print all_pivots_amounts(1).Address
    Debug.Print all_pivots_amounts.Item(1).Address
    Debug.Print all_pivots_amounts(2).Address
    Debug.Print all_pivots_amounts.Item(2).Address

End Sub

一旦你这样做,你就可以同时使用 .Item(index) 和 (1) ,如示例中所示。

传递范围变量时不带括号。如果使用括号,则vba将被强制获取参数 ByVal . 得到一个 Range() ByVal意味着,VBA正在将其值添加到集合中,因此它不再是对象,而是 Variant 。这是一个简单的例子,说明当您通过如下范围时,您在监视窗口中看到的内容:

Sub TestMe()

    Dim newColl As New Collection
    Dim someRange As Range: Set someRange = Range("A1")
    Dim someOtherRange As Range: Set someOtherRange = Range("A66")

    Range("A1") = 55
    newColl.Add (someRange)
    newColl.Add someOtherRange

End Sub

Item1 是 Double 和 Item2 是 Object/Range :

以下是关于这方面的两个参考文献:

Byref byVal from Cpearson
Refer byVal, when parameter is byRef (免责声明:来自我博客的文章)

MichaÅ Turczyn 6 年前

不正确地使用命名范围。与下面的代码比较,查看错误发生的位置:

Sub CollectionTest()
    Dim coll As New Collection
    Dim rng1 As Range
    Dim rng2 As Range

    'Set rng1 = someNamedRange 'gives an error!
    Set rng2 = Range("someNamedRange")

    'here you can see, how to add named ranges to a collection - you have two valid ways, through a variables or directly
    coll.Add someNamedRange
    coll.Add rng2
    coll.Add Range("someNamedRange")

    'MsgBox coll(1).Address 'would give an error: object required
    'both lines below work as expected
    MsgBox coll(2).Address
    MsgBox coll(3).Address
End Sub