我的书签
添加书签
移除书签16.10 数据透视表缓存
数据透视表缓存即PivotCache对象是一个非常重要的“幕后英雄”，这一点在16.4.2小节中已经提到过，下面将更深入地介绍有关该对象的用法。
16.10.1 显示数据透视表的缓存索引和内存使用量
Excel应用程序使用索引编号来标识工作簿中的数据透视表缓存，每个数据透视表缓存都拥有一个唯一的索引号，在创建数据透视表时系统将自动为新产生的数据透视表缓存分配索引号。
示例16.13 显示数据透视表的缓存索引和内存使用量
打开示例文件“显示数据透视表的缓存索引和内存使用量.xlsm”，在名称为“数据透视表”的工作表中已经创建了4个数据透视表，如图16-30所示。
图16-30 工作表中的4个数据透视表
下面的代码将在“结果”工作表中输出所有的数据透视表缓存信息，如图16-31所示。由结果可以看出，4个数据透视表分别使用不同的数据透视表缓存，每个缓存都占用了65 592个字节的内存。
图16-31 数据透视表索引号与内存使用量
代码解析：
第5行代码用于忽略运行时错误，发生运行时错误时程序将继续执行。
第6行代码禁止系统显示错误提示信息。
第7行代码清除系统错误信息，这样可以确保第9行代码捕获的错误是由本过程产生的。
第8行代码为objSht变量赋值，如果工作簿中没有名称为“结果”的工作表，那么将产生错误号为9的运行时错误。
第9行代码判断是否产生了错误号为9的运行时错误。
第10行代码在当前工作簿中添加一个新的工作表，用于保存代码执行的结果。
第11行代码修改新建工作表的名称为“结果”。
如果工作簿中已经存在“结果”工作表，第13行代码将清空该工作表中的内容。
第15行代码恢复系统错误提示功能。
第16行代码恢复系统错误处理机制。
第18行代码用于设置结果的标题行。
第20行代码到第25行代码循环遍历“数据透视表”工作表中的数据透视表。
第21行代码将数据透视表的Name属性写入“结果”工作表的第1列。
第22行代码将数据透视表的Cachelndex属性写入“结果”工作表的第2列。
第23行代码将数据透视表的MemoryUsed属性写入“结果”工作表的第3列。
第26行代码激活“结果”工作表。
16.10.2 合并数据透视表缓存
在默认情况下，系统会为每个数据透视表分配数据透视表缓存，也就是每个数据透视表独占一个数据透视表缓存。如果工作簿中的数据透视表数目比较多时，将耗费大量的系统内存，甚至影响整个电脑的运行效率。在Excel中多个数据透视表可以共享一个数据透视表缓存，这样将会大大节省系统资源。
示例16.14 合并数据透视表缓存
打开示例文件“合并数据透视表缓存.xlsm”，在名称为“数据透视表”的工作表中有4个数据透视表。这4个数据透视表分别使用不同的数据透视表缓存，其序号为1到4。运行MergePvt Caches过程，将4个数据透视表全部关联到索引号为1数据透视表缓存，此时整个工作簿中只有一个数据透视表缓存，应用程序释放了其余3个数据透视表缓存所占用的系统资源，如图16-32所示。
图16-32 释放数据透视表缓存
代码解析：
第4行代码到第6行代码循环遍历工作表中的数据透视表，并修改其Cachelndex属性。
第5行代码中利用PivotCache.Index获得第一个数据透视表的数据透视表缓存索引号。
第9行代码使用消息框显示当前工作簿中所包含的数据透视表缓存的个数。
注意
在VBA代码中既可以用<PivotTable对象>.PivotCache.Index获得数据透视表缓存索引号，也可以直接查询数据PivotTable对象的CacheIndex属性，但是修改数据透视表所归属的数据透视表缓存时，只能使用Cachelndex属性。
合并数据透视表缓存除了可以节约系统资源外，也让数据透视表更新操作更方便，使用PivotCache对象的Refresh方法刷新数据透视表缓存时，所有归属于此PivotCache对象的数据透视表将同时被刷新。
在合并数据透视表缓存时，需要注意合理选择目标数据透视表缓存，即最终被多个数据透视表使用的数据透视表缓存。
假设需要将数据透视表A和数据透视表B所使用的数据透视表缓存进行合并，如果数据透视表A和数据透视表B中包含完全相同的字段，那么可以选择任何一个数据透视表缓存作为目标数据透视表缓存。如果数据透视表A中的字段是数据透视表B中字段的有效子集，也就是说数据透视表B中部分字段在数据透视表A中并不存在，此时只能选择数据透视表B所归属的数据透视表缓存作为目标数据透视表缓存，否则数据透视表B所拥有的不存在于数据透视表A中的字段将无法显示。
