夜雨聆风
Excel VBA秘密武器:搞定CubeField,从此跟“手动刷新”说拜拜!
别人加班两小时，你喝茶看报刷手机，只因学会了和“立方体字段”打交道
Hello大家早上中午晚上好！我是你们的老朋友，那个一边写着代码一边喝着枸杞的运营狗。
前两天，公司新来的实习生小张哭丧着脸来找我。他对着一个从SQL Server Analysis Services（SSAS）拉出来的数据透视表，想用VBA加个字段。结果，平时对普通透视表百试百灵的代码，今天全挂了！
Excel不仅不听话，还弹出一个让他绝望的错误提示：“运行时错误‘1004’”。
我看了一眼他的代码，又看了一眼他那张生无可恋的脸，深吸一口枸杞茶，缓缓吐出四个字：“这是Cube。”
今天，我们就来聊聊这个让无数VBA新手翻车、让老手直呼真香的Excel VBA对象——CubeField（立方体字段）。掌握它，你就能在“OLAP多维数据集”这个高端局里横着走。
一、 普通PivotField和CubeField，到底差在哪？
如果把普通的Excel数据表比作“农家乐”（数据都在本地，一目了然），那OLAP数据源就是“五星级酒店大厨的后厨”（数据在远端的SQL Server Analysis Services等服务器上，是一个立方体）。
面对农家乐，你可以随便进后厨指指点点（PivotField），想怎么炒菜就怎么炒。但在五星级酒店后厨（OLAP数据源），你只能通过一个特定的窗口点菜，这个窗口就是 CubeField 。
划重点：
普通透视表操作的是 PivotField。
基于OLAP立方体的透视表，操作的是 CubeFields 集合下的 CubeField 。
它的名字通常长这样：”[Customers].[Country]” 或者 “[Measures].[Sum of Sales]”。方括号加点号，充满了后现代科技感 。
二、 初识高富帅：如何正确地引用CubeField？
想要征服这个“高富帅”，第一步是正确地叫出它的名字。
很多同学像小张一样，录制宏的时候发现代码里出现了 CubeFields，于是照猫画虎：
‘错误的姿势：你以为它还是那个农家乐
Sub 错误示范()
With ActiveSheet.PivotTables(“透视表1”).PivotFields(“[Sales].[Amount]”)
.Orientation = xlDataField
End With
End Sub
错！大错特错！ 对于OLAP源，你不能用 PivotFields 去调用一个立方体字段。
正确的姿势应该是这样的：
Sub 正确示范_引用CubeField()
Dim pt As PivotTable
Set pt = ActiveSheet.PivotTables(“透视表1”)
‘使用CubeFields集合，传入字段的唯—名称
With pt.CubeFields(“[Measures].[Sales Amount]”)
.Orientation = xlDataField ‘把它拖到数值区
End With
With pt.CubeFields(“[Product].[Category]”)
.Orientation = xlRowField ‘把它拖到行标签
End With
End Sub
看到了吗？这就是面向“立方体”编程的基本修养。
三、 坑王驾到：当你想把CubeField放进数值区
这是新手翻车率最高的地方，也是我们今天要重点拆解的终极陷阱。
假设我们想把一个度量值（比如销售额）拖到数值区求和，你可能天真的以为直接设置 Orientation = xlDataField 就完事了。
Too young, too simple！ 不信你试试这段代码：
Sub 翻车现场_无法求平均值()
Dim pt As PivotTable
Set pt = ActiveSheet.PivotTables(“透视表1”)
‘试图把销售额拖到数值区，并求平均值
With pt.CubeFields(“[Measures].[Sales Amount]”)
.Orientation = xlDataField
‘下面这行代码会报错！或者根本没反应！
.Function = xlAverage
End With
End Sub
Excel会无情地告诉你：无效的过程调用或参数 。
为什么？
因为 [Measures].[Sales Amount] 在立方体里是一个“原材料”。当你把它拖进数值区的一瞬间，Excel会在幕后生成一个新的“菜”——数据字段。这个新字段的名字可能叫“求和项：Sales Amount”或者“平均值：Sales Amount”。你不能再通过原来的CubeField去设置这个新菜的做法（是蒸是煮） 。
正确的做法是什么？
你需要使用 AddDataField 方法，在“点菜”的那一刻就告诉厨师你要的做法。
Sub 高手操作_在添加时指定聚合方式()
Dim pt As PivotTable
Dim newDataField As PivotField
Set pt = ActiveSheet.PivotTables(“透视表1”)
‘使用AddDataField方法，一次性完成添加并指定聚合方式为“平均值”
Set newDataField = pt.AddDataField( _
Field:=pt.CubeFields(“[Measures].[Sales Amount]”), _
Caption:=”平均销售额”, _
Function:=xlAverage) ‘关键就在这里！
‘如果你还想对这个数值字段设置数字格式，现在可以通过返回的newDataField来操作
newDataField.NumberFormat = “#,##0.00″
End Sub
这段代码的意思是：我要这个 [Measures].[Sales Amount] 字段，但是放进透视表后，请显示为“平均销售额”，计算方式取平均值 。
四、 炫技时刻：像黑客一样操控成员选择
既然叫CubeField，它和普通的字段还有一个巨大的不同——它自带一个“树形视图”（TreeviewControl）。
这是什么概念？就像你在浏览淘宝的商品分类一样：电子产品（一级）下面有手机（二级），手机下面有iPhone 15（三级）。CubeField允许你用VBA精确控制展开到哪一级，勾选哪个具体的项。
Sub 操控树形视图_只显示特定产品()
Dim cubeF As CubeField
Set cubeF = ActiveSheet.PivotTables(“透视表1”).CubeFields(“[Product].[Product Categories]”)
‘这玩意儿就像是遥控器，控制着字段的层级展开状态
With cubeF.TreeviewControl
‘告诉Excel，只显示这几个特定的成员，其他的全藏起来
.Hidden = Array( _
Array(“”, “”, “”), _ ‘第一级全隐藏
Array(“[Product].[Product Categories].[电子产品]”, _
“[Product].[Product Categories].[服装]”), _ ‘第二级只展开这两个
Array(“[Product].[Product Categories].[电子产品].[手机]”, _
“[Product].[Product Categories].[服装].[男装]”) _ ‘第三级只选这两)
End With
End Sub
这段代码看起来有点复杂？没关系，你可以理解为：我们给Excel画了一张“藏宝图”，告诉他哪条路（层级）可以走通，哪条路（隐藏项）是死路 。
五、 避坑指南：CubeField的三大纪律八项注意
名字必须全须全尾： 在代码中引用时，字段名必须和在立方体中定义的一模一样，包括方括号和点号。少一个括号都不行 。
不能直接给CubeField设置筛选？不一定！
虽然 CubeField 本身没有 AutoFilter 方法，但OLAP的筛选是通过 PivotFilters 结合 CubeField 来做的。比如筛选日期范围：
With pt.CubeFields(“[Date].[Date]”)
.ClearAllFilters
‘这里的Filter是针对立方体的MDX表达式
.PivotFilters.Add2 Type:=xlDateBetween, Value1:=Range(“startDate”), Value2:=Range(“endDate”)
End With
注意：这种方法通常对“日期”这种层级结构有效 。
手动刷新？不存在的： 操作完 CubeField 后，记得加上一句 pt.RefreshTable。立方体不像本地数据，你不刷新，它还以为你在逗它玩。
写在最后
当小张看完我的代码，他若有所思地问：“哥，你咋懂这么多？”
我微微一笑，指着屏幕上的 CubeField 说：“当你见过足够多的‘运行时错误’，你也会变成大神。只不过有些人选择绕道走，而我选择——写篇文章教会你们，然后让你们请我喝奶茶。”
好了，今天的Excel VBA装逼指南就到这里。如果你也被CubeField坑过，或者你身边也有把透视表玩崩溃的同事，欢迎点赞、在看、转发，把这份快乐（或者知识）传递下去！
咱们下期见，拜了个拜！