VBA中获取集合内嵌套Range地址时，报‘Object required’错误求助

嘿，我一眼就发现问题所在了——你往Collection中添加Range对象的时候，错误地加了括号，导致存储的不是对象本身，而是它的默认属性值！

错误原因 #

在VBA中，当你使用Add (object)这种带括号的写法时，括号会强制对object进行求值操作。对于Range对象来说，它的默认属性是Value，所以你实际上是把Range的值（通常是一个二维数组）添加到了Collection里，而不是Range对象本身。这就导致你从Collection中取出元素时，它并不是一个Range对象，自然调用.Address属性会触发"Object required"错误。

解决方案 #

去掉Add方法参数外的括号，直接将Range对象添加到Collection中：

修正后的Collection创建代码 #

'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

批量打印Range地址的循环实现 #

现在你可以通过循环遍历Collection，批量打印每个Range的地址了：

' 循环遍历Collection，打印所有Range的地址
Dim pivotRange As Range
For Each pivotRange In all_pivots_amounts
    Debug.Print pivotRange.Address
Next pivotRange

完整修正后的代码片段 #

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

' 批量打印地址
Dim pivotRange As Range
For Each pivotRange In all_pivots_amounts
    Debug.Print pivotRange.Address
Next pivotRange

Application.EnableEvents = True ' 记得最后恢复事件触发哦
End Sub

内容的提问来源于stack exchange，提问作者barciewicz