【数值策划】聊聊 VBA 数组的那些坑

apollogt

为什么使用数组？
1. 缩减工作薄文件大小，提高运行效率

一般而言只是使用 Excel 的内置工作表函数，在运算方面还是很高效的，但有时因为一个单元格牵扯的计算太多，比如调用多单元格数据，对结果文本进行部分替换，按照优先级和条件判断来确定不同的返回结果，这都会造成一个单元格的公式文本过长，当以此单元格为基础进行数千行的相对引用填充时，必然会导入工作薄快速的膨胀；

如果因为某些复杂的处理，而使用了自定义函数，且应用的单元格也是几千行级别的，那么用户甚至会被迫选择在 "公式选项卡->计算选项"，将自动计算变更为手动计算的方式来避免这种 Excel 自动进行全局计算的引发卡顿问题；

使用数组避免这一问题的方式就是将计算在内存完成，单元格只写入一个数值或字符串；

2. 数组的运算效率远高于读写单元格

看个实例来体会一下单元格逐个处理，和数组内存处理后统一导入的效率差异；
插入代码块的高亮显示实在不理想，先贴图吧；



FillRandom 在 A1:  D10000 这四万个单元格中生成随机数；拷贝随机数为常数，避免触发自动计算带来的开销；
任务目标：对每个随机数求平方，结果放置回工作表；
ForEachCellImplement 使用逐个单元格取值，计算后放回的方式实现；
ArrayImplement 使用将 A1: D10000 导入数组，在内存中完成计算，再统一放回工作表的方式实现；
前者耗时 12718 毫秒，后者 47 毫秒；约 270 倍的效率差异；



3. 在某些应用场景下，数组处理问题最简单

把一行或一列单元格的数据以逗号拼接成字符串，在不使用数组的情况下一般会这样做；
即便省略 resStr，If 语句整体压缩在一行，也需要 5 行代码，其中包含循环结构和条件判断处理字符串拼接的开头部分 ；
如果使用数组，只需要一行就可以了；




4. 强化部分 Excel 功能

如，SpecialCells，也就是 Excel 快捷键 F5 定位条件，Excel 中可以批量定位一种类型的单元格区域，但对定位结果进行多种条件处理，再将数据放回或者以这些数据为基础再进行其他数据的生成，Excel 菜单功能是无法完成的；
Find 也就是 Excel 的 Ctrl + F，查找功能，同样可以利用 VBA 来完成，一旦拿到返回的一组单元格区域，处理方式就灵活多样了；
整体来看数组可以极大的拓展 “返回值为单元格区域” 的功能的操作边界；

5. VBA 其他容器使用效果也不理想

比如 Collection（类似 Python 中的 List），Dictionary等，但支持的方法太有限，同时转换类型也没有便捷的方法来支持；

这一点我个人理解是 Microsoft 还是希望用户以 Range 为核心，配合 Excel 本身的功能，以及这些功能对应的 VBA 调用，以此来完成任务目标； 如果用户的任务目标超越了这个界限用户就应该去使用 Microsoft 的其他产品如 Power BI，VSTO（Visual Studio Tools for Office）借助 C# 应该是想干什么都可以了；或者干脆极易上手 Python，借助 pandas 和 numpy 这些工具来处理 .xlsx .csv 等等；

坑的原因

VBA 数组的坑主要是由三个原因引起的：
1.脚本语言的用户对数据类型的重视度不足；
2.Range对象的存在，造成了数组一些潜规则式的转换机制；可以在下文 “感受 Range 的混乱” 部分体会一下；
3.静态数组和动态数组的一些限制和数据导入规则；这远没有静态语言数组声明后全生命周期大小不可变，扩容需要重新创建来的清晰；

感受 Range 的混乱

如果这部分看蒙了就暂时过的它吧，后面看过 “Array() 和 Range 对象”，“数组初始化方式3”，“数组导入到单元格区域”，在回过头来理解一下这个部分会更有收获；

1.Range("") 赋值给未声明的变量，TypeName 变为 Variant；TypeName() 是获取变量的数据类型；
2.Variant() 不能使用 Range 类型的属性，如，.Address；
3.想要使用 Range 类型的属性，先 Set varName = Range("")；
4.对于被 Set 成 Range 类型的变量：
（1）IsArray() 仍然是 True；
（2）不能将 Range 类型的变量赋值给已经声明的数组，报错 “类型不匹配”；
（3）可以赋值给未声明的变量或 Variant 类型的变量（Dim x 或 Dim x as Variant），该 Range 类型的变量会自动转型为 Variant()；

最让人懵逼的地方是 Set 一个 Range() 它的 TypeName 是 Range，IsArray 是 True，但不能把它赋值给一个数组，如果把它赋值给一个 未声明的 或 Variant 变量，它又被自动转换成了 Variant()，看下面这个例子，注意 TypeName：





以下主要从以下几个方面来谈谈数组避坑：

• 数组声明
• Array() 方法 和 Range 对象
• 数组初始化
• 数组导入到单元格区域
• 数组作为参数和返回值
  

对于脚本语言我个人更倾向，不开启强制变量声明；

在不声明的状态下，直接对一个变量赋值，某些时候是更好的策略，比如，Filter() 的返回值，如果声明了 Variant() 去接收则报错（必须声明为 String()），For Each 的临时变量，会强制用户声明等等；如果要开启强制声明，在模块最上方加入如下语句：

1. Option Explicit

复制代码

数组声明


静态数组

在 Option Base 的指定值不同的情况下，不设置编号的静态数组声明的大小是不同的；



动态数组




Array() 和 Range 对象
讨论初始化之前，先来看两个给数组赋值的常用形式；
所谓一次性装入，就是以一个数组或对象为数组赋值，赋值在一条语句中完成，Array() 和 Range 对象都属于一次装入；
非一次性装入，就是利用循环结构逐个调用数组元素并进行赋值；

1. Array() 函数

Array(ParamArray ArgList() As Variant)

注意 Array() 方法的参数和数据类型是 Variant，其函数的返回值是 Variant()；




官方文档 Variant 数据类型
Variant 包含除固定长度 String 数据以外的任何类型的数据；也就是说 Array() 的元素可以是任意类型，也可以是数组；

2. Range 对象

先说明一下本文用到的几个称呼，它们都是 Range对象 ：
（1）单元格区域，特指工作表中由单元格（Cell）所组成的 Range；
（2）Range 类型，将 Range("A1:C3") 这种形式通过 Set 设置后得到的对象变量；
（3）Range("")，特指赋值给变量，数据类型转型为 Variant() 的 Range；
（4）Range 对象，指代 （1）-（3）中的情况，需要结合上下文来理解；




数组初始化


几条规则：

1.静态数组不能一次性装入数据；
2.只声明是数组、大小及编号的数组，系统会分配为 Variant类型数组；
3.只声明一个变量 (如，Dim varTemp)，默认的数据类型 TypeName(varTemp) 是 Empty，varTemp 可以接收动态或静态数组，且可以是任意类型的数组；这种声明方式实际上就相当于在 非 Option Explicit 情况下，不声明直接使用变量；
4.可以给 Variant数组 的元素赋值任意类型（逐个赋值的方式），但不能将 非Variant数组，赋值给 Variant数组（一次装入的模式），实际这一条是特指动态数组的，因为静态数组已经被不能一次装入的规则限制了；
5.可以用静态数组为动态数组赋值，但静态数组只能是 Variant数组；




在 VBE 本地窗格对比一下数组元素的顺序 和 For Each 取元素的顺序；





推荐使用不声明的方式，因为部分函数，如 Filter() 的返回值是 String()，Variant 数组使用元素逐个赋值的方式可以接收 String 类型的数据，但 Variant数组 不能接收 String数组 的一次性赋值，如果一定要为数组声明类型则需要声明为 Dim arrDyn() as String，这点个人觉得挺坑的；




数组清理

这个用的不多顺便提一下吧；

1. Erase arraylist

复制代码

arraylist 是一个或多个用逗号分隔开的需要清除的数组变量；只给出数组名，不带括号和下标；

Erase 释放动态数组所使用的内存，下次引用该动态数组之前，必须 ReDim





数组导入到单元格区域


数组导入到单元格区域：
如果 数组 的大小超过了单元格区域的大小，则相当于数组被单元格区域截取，如，例1；
如果 单元格区域 的大小超过了数组的大小，则不足的部分以 "#N/A" 来填充，如，例2；



例2，数组的大小是 3行4列，单元格区域的大小是 4行5列，不足的部分被 #N/A填充了；





虽然在数组初始化 方式3 中，提到 Range("") 赋值给数组，即便只有一行，它也是二维数组，但是将数组赋值给单元格区域时，可以使用一维数组；



数组的转置



前面提到从 Range("") 导入的数组，即是是一行，也是二维数组，即 Range("A1:J1") 是 (1 to 1, 1 to 10) 的二维数组；
(1 to 1, 1 to 10) 形式的数组，也可以通过 Transpose 转置为一列数组，因此可以利用这个机制将一个 (1 to 1, 1 to 10) 转变为一个 (1 to 10) 的数组；



这也就文章开头部分的两次转置的原理；在 VBE 本地窗口看一下结构



数组作为参数和返回值



结束
数组日常使用能看到的出现概率较高的坑应该是都在这里了，以后想到其他的再补充吧，欢迎交流！

最后在补充一句，在文章开头的部分测试了四万个单元格的逐个读取和逐个写入，后来我又单独跑了一下逐个读取，时间是16毫秒左右，所以可以确定消耗资源的是单元格写入，在操作中尽量避免对单元格的写入吧。


首发于知乎 https://zhuanlan.zhihu.com/p/126184950