access vba 常量数组赋值_聊聊 VBA 数组的那些坑

de3e542bcfec91f4e2392c51fff0c557.png

为什么使用数组?

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

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

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

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

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

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

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

d06da48175aded264d5231efa70d94d6.png

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

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

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

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

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

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

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

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

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

99e6463bf041bc6fc1c25239ed6e094f.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:

c43f10a410ec502c4eb58564f89750d8.png

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

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

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

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

Option Explicit

数组声明

静态数组

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

08a34a844cef9e4ec2da805f0afe60b9.png

动态数组

29e99d40d1125ba6b8f8a80c7b457e10.png

Array() 和 Range 对象

讨论初始化之前,先来看两个给数组赋值的常用形式;

所谓一次性装入,就是以一个数组或对象为数组赋值,赋值在一条语句中完成,Array() 和 Range 对象都属于一次装入;

非一次性装入,就是利用循环结构逐个调用数组元素并进行赋值;

1. Array() 函数

Array(ParamArray ArgList() As Variant)

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

aa3e8df931cf7191bad9edf031b064a7.png
官方文档 Variant 数据类型​docs.microsoft.com

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

2. Range 对象

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

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

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

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

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

ae192a829a5219ef3d09dab21a37926b.png

数组初始化

几条规则:

1.静态数组不能一次性装入数据;

2.只声明是数组、大小及编号的数组,系统会分配为 Variant类型数组;

3.只声明一个变量 (如,Dim varTemp),默认的数据类型 TypeName(varTemp) 是 Empty,varTemp 可以接收动态或静态数组,且可以是任意类型的数组;这种声明方式实际上就相当于在 非 Option Explicit 情况下,不声明直接使用变量;

4.可以给 Variant数组 的元素赋值任意类型(逐个赋值的方式),但不能将 非Variant数组,赋值给 Variant数组(一次装入的模式),实际这一条是特指动态数组的,因为静态数组已经被不能一次装入的规则限制了;

5.可以用静态数组为动态数组赋值,但静态数组只能是 Variant数组;

d83dbed298ffe58e42e2b3ec94b0eeff.png

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

b089ac8bce2cc9b7a655e58fd4322b5a.png

698327e7cdf61f44261106fd07996fa5.png

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

46804a4383c4cae5569356cc590ae9fe.png

数组清理

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

Erase arraylist

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

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

91d1277b5ee66433220203d385c879ad.png

数组导入到单元格区域

数组导入到单元格区域:

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

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

e6a17490ddad40486300ac9c6a51393c.png

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

6f3e50f0e48df5d4317fcfde433a84ac.png

821c08c4a6f86fc2cd68ea66c9558d53.png

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

4a68287031b95a6ee85cae5872387c3c.png

数组的转置

4b7337ded8b04fd98714d5c3bf8c0000.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) 的数组;

e932d2007243dab1150684fe6dee460e.png

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

d9522c6a64fc1b025848bc54dabcd7fb.png

数组作为参数和返回值

edd9d408af028f2d9bd3bd289460a947.png

结束

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

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

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值