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

发表于2020-04-07
评论1 2.3k浏览

想免费获取内部独家PPT资料库?观看行业大牛直播?点击加入腾讯游戏学院游戏策划行业精英群

361498939

前一段整理 异度之刃2 的数据,被本以为很熟悉的VBA数组坑了一道,于是花了些时间仔细梳理了一下数组,分享一点心得供大家参考吧!

 

为什么使用数组?

 

1. 缩减工作薄文件大小,提高运行效率

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

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

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


 

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

看个实例来体会一下单元格逐个处理,和数组内存处理后统一导入的效率差异;

插入代码块的高亮显示实在不理想,先贴图吧;

nNratWSuWR6hbULtqgLj.png

 

FillRandom 在 A1:D10000 这四万个单元格中生成随机数;拷贝随机数为常数,避免触发自动计算带来的开销;

任务目标:对每个随机数求平方,结果放置回工作表;

ForEachCellImplement 使用逐个单元格取值,计算后放回的方式实现;

ArrayImplement 使用将 A1:D10000 导入数组,在内存中完成计算,再统一放回工作表的方式实现;

前者耗时 12718 毫秒,后者 47 毫秒;约 270 倍的效率差异;

 

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

把一行或一列单元格的数据以逗号拼接成字符串,在不使用数组的情况下一般会这样做;

即便省略 resStr,If 语句整体压缩在一行,也需要 5 行代码,其中包含循环结构和条件判断处理字符串拼接的开头部分 ;

如果使用数组,只需要一行就可以了;

n6ChMbf3VLvmp4UJpHFS.png

 

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:

XkZ68V19LYk2GdTyends.png

 

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

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

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

Option Explicit

 

数组声明

静态数组

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

fc9ldm61b4lfCF7C0tWn.png

 

动态数组

aYq8sGmW3UQEUSw1MwMn.png

 

Array() 和 Range 对象

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

 

1. Array() 函数

Array(ParamArray ArgList() As Variant)
注意 Array() 方法的参数和数据类型是 Variant,其函数的返回值是 Variant();

t0FJvGTby7vzYR0Fi6r4.png

https://docs.microsoft.com/zh-cn/office/vba/Language/Reference/User-Interface-Help/variant-data-type

Variant 包含除固定长度 String 数据以外的任何类型的数据;也就是说 Array() 的元素可以是任意类型,也可以是数组;

 

2. Range 对象

先说明一下本文用到的几个称呼,它们都是 Range对象 :

(1)单元格区域,特指工作表中由单元格(Cell)所组成的 Range;

(2)Range 类型,将 Range("A1:C3") 这种形式通过 Set 设置后得到的对象变量;

(3)Range(""),特指赋值给变量,数据类型转型为 Variant() 的 Range;

(4)Range 对象,指代 (1)-(3)中的情况,需要结合上下文来理解;

FLosM4GkKEGhadW52y8A.png

 

数组初始化

 

几条规则: 
 

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

 

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

 

OqSZ5OcYvhm2mv8TbBni.png

 

YrMDKnE1AukRXJImLiZw.png

 

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

 

gZgNIPMvqcYLNPpYE90K.png

 

数组清理 
这个用的不多顺便提一下吧; 
Erase arraylist 
arraylist 是一个或多个用逗号分隔开的需要清除的数组变量;只给出数组名,不带括号和下标; 
Erase 释放动态数组所使用的内存,下次引用该动态数组之前,必须 ReDim;

 

hMHytQcPIcc80tuHMknv.png

 

数组导入到单元格区域

数组导入到单元格区域:

如果 数组 的大小超过了单元格区域的大小,则相当于数组被单元格区域截取,如,例1;

如果 单元格区域 的大小超过了数组的大小,则不足的部分以 "#N/A" 来填充,如,例2;

xsLmuGcl8jsg7FcnsCP8.png

 

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

 

fmWUVFB8av4onijrd18F.png

 

T4TguDH0m0nDmayafF3s.png

 

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

 

YvSQjgUAiTby8XHYilK1.png

 

数组的转置

 

dpTggF1lZwKht4cEvLtT.png

 

前面提到从 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) 的数组;

 

Pc0qPzLfw6FAwpwEWY67.png

 

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

 

KhdstOnX2CanRbBYMDai.png

 

数组作为参数和返回值

 

vkwuQN0fzNUz1Gd1Z40d.png

 

结束

 

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

 

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

@

 

 

 

 

 

  • 允许他人重新传播作品,但他人重新传播时必须在所使用作品的正文开头的显著位置,注明用户的姓名、来源及其采用的知识共享协议,并与该作品在磨坊上的原发地址建立链接
  • 不可对作品做出任何形式的修改
  • 不可将作品进行商业性使用

如社区发表内容存在侵权行为,您可以点击这里查看侵权投诉指引

标签:

游戏学院公众号二维码
腾讯游戏学院
微信公众号

提供更专业的游戏知识学习平台