VBA数组漫谈

一. 前言

数组(列表), 字典(集合), 类, 这三者不会因为语言的差异而其重要性会发生变化.

数组, 字典, 操作数据的基础容器.

类, 清晰代码结构的基础.

需要注意的是, 在vba中只有数组的概念, 没有列表的概念(当然也可以自定义封装一个类来实现列表的功能).

在看vba的数组前, 先来看看python和JavaScript中的数组.

>>> from array import array
>>>
>>>
>>> a = array('i', [1,2,3,4])
>>>
>>> print(a[0])
1

# 不允许混合存储数据
>>> a = array('i', ['a',2,3,4])
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
TypeError: an integer is required (got type str)
>>>

Welcome to Node.js v16.17.0.
Type ".help" for more information.
> {
...     const a = new Int8Array(8);
...
...     a[0] = 1;
...     console.log(a);
... }
Int8Array(8) [
  1, 0, 0, 0,
  0, 0, 0, 0
]

> {
...     const a = new Int8Array(8);
...
...     a[0] = 'a'; // 进行字符串的赋值, 并没有出现错误
...     console.log(a);
... }
Int8Array(8) [
  0, 0, 0, 0,
  0, 0, 0, 0
] // 但是并没有成功赋值.

当然JavaScript的数组和python的数组还是有些差异的, JavaScript的并不是和python那样在命名上作了明显的区别.

> {
...     const s = new Array(10);
...     s.push('a');
...     s.push(1);
...     console.log(s);
... }
[ <10 empty items>, 'a', 1 ]
undefined

在理解上可以认为, 数组是更底层的, 存储的数据必须是同一类型, 功能更少, 执行速度更快; 列表是数组的高级封装, 可以存储不同类型的数据, 功能更多, 执行速度慢.

二. 数组基本概况

2.1 Variant

变量类型中的Variant类型(这是默认的变量类型), 这是很容易搞混的部分, 难以区分variant和数组之间的差异.

可以看到 a 不需要声明为数组类型, a 就会转为数组的结构(但是其数据类型依然还是variant).

因为variant, 实际上是预先分配一个内存空间, 不确定存储什么数据, 所以会最大程度分配空间(如上, variant中使用double来存储整数)以确保数据可以存储进来.

Sub test()
    Dim a As Variant
    a = Range("a1:b3").Value

    '或者'
    dim b()
    b = Range("a1:b3").Value
    '都是类似的'
End Sub

实际上只需要知道, a = 这部分返回的数据类型(同时, 数组是无法直接以这种方式来进行赋值).

Sub tets()
    Dim a
    'array 返回的就是variant类型的数据'
    a = Array("a", 12, 2)

End Sub

2.2 声明数组

声明方式分为, 根据预先分配空间, 可以分为预先定义数组的大小和不确定大小(后期根据需要重新定义); 根据维度, 可以分为一维和多维.

Sub ab()
    '不确定大小'
    Dim a() As Long
    '一维'
    Dim b(1) As Long
    '二维'
    Dim c(1, 1) As Long

    '数组默认从0开始, 可以手动指定开始的范围'
    dim d(1 to 10, 1) as long

    '根据实际需要分配大小'
    ReDim a(1, 1)

    'a(0, 0) = 15
    '保存原有的结构/数据的基础上进行分配'
    ReDim Preserve a(1, 2)
    'a(x, y), 只能调整y的值, 而不能调整x的值'

    '重新分配'
    ReDim a(2, 2)

    '保留结构'
	ReDim Preserve a(2, 3)

End Sub

sub test()
    dim a() as long
    '这种方式是无法赋值'
    'Range("a1:b3").Value 返回的是variant'
    a = Range("a1:b3").Value
end sub

sub test()
    dim a(10) as long
    '这种方式是无法赋值'
    'Range("a1:b3").Value 返回的是variant'
    dim i as long

    '数组只能以这种方式进行赋值 a(i) = k'
    for i = 1 to 10
    	a(i) = i
	next
end sub

数组的序号默认从0开始, 需要从1开始需要在顶部声明Option Base 1.

2.3 数组与函数

2.3.1 判断上下界

Sub tests()
Dim x As Long, y As Long, m As Long, n As Long
Dim a(2, 1 To 3)

x = UBound(a)
y = LBound(a)

m = UBound(a, 2)
n = LBound(a, 2)

End Sub

2.3.2 生成"数组"

Sub tesy()
    Dim a, b

    a = Split("abnc", "n")
    b = Array(1, "a", Array(2, "b"))
End Sub

最为常见生成数组结构的函数为字符串分割的split(), 以及数组函数Array(), 这函数可以内部嵌套数组.

2.3.3 数组的应用

2.3.3.1 字符串拼接

Sub tesy()
    Dim a, b

    a = Split("abnc", "n")
    b = Array(1, "a", Array(2, "b"))

    Dim s As String
    s = Join(a, ",")
End Sub

对于拼接大量的字符串, 比如上万个字符串片段, 数组方式比直接 &符号的执行速度更快.

2.3.3.2 检索

Sub test_s()
    Dim a, b

    a = Array(1, 1, 3)

    b = Filter(a, 1)

    Dim i As Long, k As Long

    i = WorksheetFunction.Match(1, a, 1)

End Sub

WorksheetFunction这个就不需要详述了, 就是调用excel表格的内置函数, filter函数是比较有意思的, 在其他语言中, filter函数也是很常见的.

Welcome to Node.js v16.17.0.
Type ".help" for more information.
> {
...     const a = [1,2,3,4];
...     console.log(a.filter((e) => e > 1));
... }
[ 2, 3, 4 ]
undefined
>

2.3.3.3 其他

数组的转置是最为常用的.

Sub test_s()
    Dim a, b

    a = Array(1, 1, 3)

    Cells(1, 1).Resize(3, 1) = a

    ' Cells(1, 1).Resize(1, 3) = a

    b = Application.Transpose(a)

    Cells(1, 4).Resize(3, 1) = b

End Sub

默认状态下数组 a 的排列方式是纵向排布的, 在python上也是如此.

数组的用途不限于此, 应当注意在excel的工作表使用的公式其中支持参数为数组(整列, 整列), 其在vba上的使用也是类似的.

Sub test_s()
    Dim a, k

    a = Array(1, 1, 3)

    k = WorksheetFunction.Sum(a)

End Sub

2.4 判断数组初始化

Function abc() As Long()
    Dim a() As Long

    abc = a
End Function

Sub testz()
    Dim x() As Long
    x = abc()

    Dim a(1) As Long

    On Error Resume Next

    Debug.Print UBound(x)

    Debug.Print a(2)

    If Err.Number <> 0 Then Debug.Print Err.Number
End Sub

例如存在这样的场景, 根据某个函数返回的数组的长度, 来执行某个动作, 当数组尚未初始化, 而且在后续的执行中也存在数组的运算.

在进行错误捕捉时, 获得的错误都是 9, 注意需要区分其错误的来源.

或者基于API的方式来读取数组是否初始化, IsEmpty函数没用.

#If VBA7 Then
    Private Declare PtrSafe Function SafeArrayGetDim Lib "oleaut32.dll" (ByRef saArray() As Any) As Long
#Else
    Private Declare Function SafeArrayGetDim Lib "oleaut32.dll" (ByRef saArray() As Any) As Long
#End If

Sub testz()
    Dim x() As Long
    x = abc()

    Debug.Print SafeArrayGetDim(x)
End Sub

三. 构建列表

由于vba中没有列表, 实际使用数组是比较麻烦的, 特别是涉及到数据的弹出(pop), 以及判断数据的位置, 判断数据是否存在等高频操作.

可以使用类模块来封装一个列表. 一下是简单的示例.

Option Explicit
' 类模块'
'假如需要存储其他格式的数据, 可以声明variant类型数据'
Private arr() As Long
Private index As Long

Sub append(ByVal val As Long)
    arr(index) = val
    index = index + 1
End Sub

'这里只是演示, 理论上应该有两个部分组成, 存储值, 指针'
Sub pop(Optional ByVal i As Long = -1)
    If i > index Then Exit Sub
    If i < 0 Then arr(index - 1) = 0
End Sub

Private Sub Class_Initialize()
    ReDim arr(10)
    index = 0
End Sub

Private Sub Class_Terminate()
    Erase arr
End Sub

' 常规模块
Sub test()
    Dim arr As New array_module

    arr.append 1
    arr.pop
End Sub

四. 特殊的Byte

当声明一个数组为byte类型时.

以下内容涉及到计算机底层的执行逻辑和电路机制, 要先理解这部分的内容.

大端模式

所谓的大端模式( Big-endian) , 是指数据的高字节, 保存在内存的低地址中, 而数据的低字节, 保存在内存的高地址中, 这样的存储模式有点儿类似于把数据当作字符串顺序处理: 地址由小向大增加, 而数据从高位往低位放;

小端模式

所谓的小端模式( Little-endian) , 是指数据的高字节保存在内存的高地址中, 而数据的低字节保存在内存的低地址中, 这种存储模式将地址的高低和数据位权有效地结合起来, 高地址部分权值高, 低地址部分权值低, 和我们的逻辑方法一致.

大小端模式

至于为什么要区分大小端, 这是因为在计算机系统中, 我们是以字节为单位的, 每个地址单元都对应着一个字节, 一个字节为 8bit. 但是在C语言中除了8bit的char之外, 还有16bit的short型, 32bit的long型( 要看具体的编译器) , 另外, 对于位数大于 8位的处理器, 例如16位或者32位的处理器, 由于寄存器宽度大于一个字节, 那么必然存在着一个如何将多个字节安排的问题. 因此就导致了大端存储模式和小端存储模式.

Sub test()
    Dim a() As Byte, b() As Byte

    a = "我"
    'unicode, 25105'
    'a, 17, 98

    b = "a"
    ' 97
End Sub

"我", 这个汉字在vba中对应的unicode为: 25105 和byte数组中的17, 98之间的联系.

vba中以Unicode编码方式存储字符, 每个字符均以2个字节(不区分中英文)来存储(每个字节8个比特).

2 ^ 8 = 256, 即每个字节可以存储的范围为: 0 - 255.

即, 在vba中存储的字符Unicode最大范围为: 0 - (256 * 256 - 1) = 65535, 总共可以容纳65536(256 * 256)个字符.

vba本质上还是披着Unicode的皮, 内心还是ASCII的语言.

来看一个示例, chrw()函数, 通过Unicode生成对应的字符, ascw()函数生成对应字符的Unicode编码, 但是可以看到这里的ascw("过")生成的Unicode是个负数, 这是因为ascw()只能返回 integer数据范围的数据., 即 0 到 2 ^ 15 - 1 = 32767.

Sub test_s()
    Dim i As Long, s As String, k As Long

    s = ChrW$(32767)
    i = AscW(s)
    k = i And 65535

    Dim a As Long

    a = AscW(ChrW$(32768))

End Sub

导致的问题见: D7E1293/VBA/Character_string_Algorithm_sunday.bas at main - Kyouichirou/D7E1293 - GitHub, 在字符串匹配算法中涉及到读取ascw()的值, 必须多执行一步来提取到正确的值.

vba这种所谓对于Unicode的支持, 大多是这种残缺状态. 这是语言层, 控件上, 这种问题会更严重.

17, 对应的二进制: 10001 (5)

98, 对应的二进制: 1100010 (7)

即 "我" 这个字符的二进制存储为: (0, 补位)1100010, 98(10进制); (000, 补位)10001, 17(10进制).

所谓的大端模式( Big-endian) , 是指数据的高字节, 保存在内存的低地址中, 而数据的低字节, 保存在内存的高地址中, 这样的存储模式有点儿类似于把数据当作字符串顺序处理: 地址由小向大增加, 而数据从高位往低位放;

计算机处理字节序的时候, 不知道什么是高位字节, 什么是低位字节. 它只知道按顺序读取字节, 先读第一个字节, 再读第二个字节.

如果是大端字节序, 先读到的就是高位字节, 后读到的就是低位字节. 小端字节序正好相反.

使用到byte类型的数组, 一般涉及到一些底层操作, 如读取字符串的哈希值(常见的就是md5计算, 爬虫中的常见场景).

• 关于这个问题, 更多内容见理解字节序 - 阮一峰的网络日志 (ruanyifeng.com)

五. 小结

对于很多vba的使用者的一大恶习就是: 源数据和计算的新数据混淆在一起放置于同一工作簿.

直接操作源数据的存储表格, 不仅有导致源数据文件损坏的风险, 从数据交叉验证的角度, 这种操作也是极度不合理的.

数组不仅起到数据抽离载体的作用, 同时也应该注意当数据存储于数组, 其执行速度远远快于每次从表格中读取数据.