聊到VBA我敢说十个新手里有八个都在Range(A1:C10).Value上栽过跟头。你可能会写一句arr Range(A1:C10).Value然后理所当然地认为arr就是一组按行排列的数据甚至想用Join(arr, ,)把它拼成字符串结果屏幕上一行鲜红的类型不匹配。问题出在哪出在Range.Value返回的数组和你脑补的数组根本不是同一个东西。这篇就把这个“不是同一个东西”掰开揉碎讲清楚顺便把二维数组读取、遍历、写回和字典配合的玩法都过一遍适合刚入门VBA、已经开始接触数组但被各种报错劝退的朋友也适合想提升批量处理速度的老手。1. 先把底层逻辑搞清Range.Value返回的到底是什么1.1 多单元格区域返回二维数组单单元格返回普通值直接看代码Dim arr As Variant arr Range(A1:C10).Value如果A1:C10是10行3列的连续区域这行代码执行后arr就是一个二维数组行数是10列数是3。注意这个“二维”是强制性的哪怕你只读一行或者一列只要区域里超过一个单元格得到的结果也永远是二维数组不会自动“瘦身”成一维数组。那读单个单元格呢比如Dim val As Variant val Range(A1).Value此时val就是一个普通的Variant值可能是数字、字符串、日期或者错误值反正不是数组。这个区别很容易被忽略很多人把单格结果当数组去取val(1, 1)直接报下标越界反过来也有人把多格结果当普通值去拼接一样报类型不匹配。所以拿到Value以后第一件事是搞清楚它到底是不是数组。你可以在立即窗口里敲一句TypeName(arr)如果是Variant()就是数组如果是String/Double/Date之类的就是普通值。1.2 数组下标从1开始Excel坐标系的延续很多从Python、JavaScript转过来的人看到VBA数组的第一反应是为什么arr(0, 0)不存在因为在VBA里从单元格区域直接读出来的数组下界LBound是1不是0。行号从上界1开始算列号也是从1开始算。也就是说arr(1, 1)对应A1arr(2, 3)对应C2。这个设计其实很贴心因为Excel表格本身就是从第1行第1列开始的数组的行列编号和单元格坐标完全对得上。但坏处也明显如果你习惯了零基数组写循环的时候很容易把循环变量从0开始一运行就“下标越界”。用代码验证一下Debug.Print LBound(arr, 1) 输出 1 Debug.Print UBound(arr, 1) 输出 10 Debug.Print LBound(arr, 2) 输出 1 Debug.Print UBound(arr, 2) 输出 3记住这四个函数以后排查数组问题基本上靠它们。1.3 元素类型是Variant空值、错误值都在里面Range.Value读出的是一个Variant类型的二维数组意味着数组里的每一个元素都是Variant。为什么不用String数组或者Double数组因为一个单元格区域里可能什么都有数字、文本、日期、布尔值、错误值、空单元格。只有Variant才能毫无压力地把这些全装进去。这一点对后续处理影响很大。比如空单元格在数组里对应的是Empty而不是空字符串。你判断一个格子是不是空应该写IsEmpty(arr(i, j))而不是arr(i, j) 。再比如有些单元格是公式产生的错误值读进数组后就是一个VarType为vbError的Variant你用字符串函数去处理它一样会翻车。所以读数组之前心里要有数这不是一个“干净”的数组里面装的是Excel世界的各种“原住民”。2. 读取与遍历声明、赋值、循环的正确姿势2.1 用Variant变量接收别用定长数组接收Range.Value最安全的方式是把它赋给一个Variant变量而不是一个预先声明好维度的数组。我见过很多新手这么写Dim arr(1 To 10, 1 To 3) As Variant arr Range(A1:C10).Value如果区域大小恰好是10行3列有时候能运行但一旦区域尺寸变了你就会遇到“不能给数组赋值”或者“类型不匹配”。原因很简单VBA不允许直接把一个数组整体塞进一个已经固定维度的数组变量里。最省心的写法是Dim arr As Variant arr Range(A1:C10).Value不需要ReDim不需要预设大小VBA会根据右侧返回的数组自动给arr分配好维度。这种方式读出来之后arr的维度与区域严格对应想改大小再单独ReDim Preserve或者用别的数组去接。有人会问动态数组行不行Dim arr() As Variant之后再arr Range(...).Value在很多时候也是能跑的但你一旦对arr做过ReDim再想整体赋值就报错了。与其每次都要记住这条规则不如统一用Dim arr As Variant把赋值交给VBA去处理你的代码反而更短、更稳。2.2 遍历数组搞懂UBound和LBound拿到数组以后最常见的操作就是遍历。二维数组的遍历一般用两层循环外层管行内层管列Dim i As Long, j As Long For i LBound(arr, 1) To UBound(arr, 1) For j LBound(arr, 2) To UBound(arr, 2) Debug.Print 第 i 行第 j 列 arr(i, j) Next j Next i这里有两个细节容易踩坑。第一循环变量建议用Long不要用Integer。Excel 2007以后行数已经超过65536Integer很容易溢出。第二循环的边界不要写死用LBound和UBound去取这样即使区域变了代码也不用改。如果你确定是从单元格读出来的数组下界一定是1所以写成For i 1 To UBound(arr, 1)也可以。但如果你后面把数组传给了别的函数或者用Transpose处理过下界可能发生改变保险起见还是用LBound判断一下。习惯这种写法之后不管遇到什么数组都不会蒙。2.3 单行单列区域的特殊处理arr Range(A1:C1).Value这个arr是1行3列的二维数组不是3个元素的一维数组。arr Range(A1:A10).Value这个arr是10行1列的二维数组也不是10个元素的一维数组。很多VBA内置函数只吃一维数组比如Join你满心欢喜想把一行数据拼成逗号分隔字符串直接写Join(arr, ,)结果肯定是报错。这时候你有两个选择要么手动循环拼接要么借助Application.Index或者Transpose把二维数组掰成一维。举一个实际能用的掰一维方法针对单列Dim oneD As Variant oneD Application.Transpose(Range(A1:A10).Value)Transpose会把10行1列的二维数组转成另一种结构这个操作在数据量不大时能满足大部分需求。对于单行则需要转两次不过Transpose有历史遗留的大小限制元素多的时候容易出幺蛾子所以我更推荐用Application.Index这个函数来提取一维数据。Application.Index(arr, 0, 1)可以返回指定列的一维数组Application.Index(arr, 1, 0)可以返回指定行的一维数组。这个函数在VBA里非常好用但知道的人不多我先卖个关子后面案例里会用到。2.4 快速写回单元格一次赋值别循环数组的好处不只是读取快写回也快。你完全可以把处理完的数组一次性赋给目标区域省去逐格写入的漫长循环Dim result As Variant 假设result是从某个区域读出来或者处理后的二维数组 Range(E1:G10).Value result这里唯一需要注意的是目标区域的行列数必须和数组的维度一致。比如result是10行3列那你写到E1:G10没问题写到E1:E10就会报错。如果你只想写入数组的一部分可以用Application.Index先切片再赋值。举个例子从整个10行3列数组里取第2列的所有行然后写到F列Dim col2 As Variant col2 Application.Index(arr, 0, 2) 返回一个一维数组 Range(F1:F10).Value col2这里有个坑Application.Index返回的一维数组如果直接赋值给一列单元格有时候能自动填充有时候只填第一格不同环境表现不一样。如果写不进去用WorksheetFunction.Transpose包一层再赋值。实操中我经常是先试不行就转置反正也不费事。3. 实战用数组字典完成一次统计和回填3.1 场景设定和一次性读取理论讲再多不如跑一个完整案例。假设你的A1:C10里有这样一张表A列姓名B列部门C列金额。目标按部门统计金额合计并且把每个部门的总金额填到E列和F列E列部门F列合计。先一次性把数据读进数组Dim data As Variant data Range(A1:C10).Value Dim dict As Object Set dict CreateObject(Scripting.Dictionary) Dim i As Long Dim dept As String Dim amount As Double接下来遍历数组把部门和金额累加到字典里。这里就用到了VBA里非常经典的数组字典组合For i 1 To UBound(data, 1) dept CStr(data(i, 2)) amount CDbl(data(i, 3)) If dict.Exists(dept) Then dict(dept) dict(dept) amount Else dict(dept) amount End If Next i为什么用字典因为部门可能有重复用字典可以自动去重并维护一个键值对Key是部门名称Value是累计金额。这一步如果用两个循环去匹配数据量一大就慢得不能看。字典是哈希表查找几乎O(1)和数组配合起来是VBA性能利器。3.2 用字典汇总后生成结果数组字典循环完成后要把结果搬回单元格。可以一条一条写但既然我们讲数组就顺便把结果也放进一个二维数组再一次写回Dim keys As Variant keys dict.keys Dim resultArr As Variant ReDim resultArr(1 To dict.Count, 1 To 2) As Variant Dim k As Long For k 0 To dict.Count - 1 resultArr(k 1, 1) keys(k) resultArr(k 1, 2) dict(keys(k)) Next k Range(E1).Resize(dict.Count, 2).Value resultArr注意dict.keys返回的是一个一维数组下标从0开始所以循环变量k从0到dict.Count - 1但resultArr的下标是1到dict.Count中间用k 1对齐。这种下标错位问题很典型写的时候容易晕建议先在纸上画一下。写回的时候用了Resize这样即使部门数量变化目标区域也会跟着数组的大小走比写死E1:F10更灵活。3.3 把结果数组转成字符串有时候你不想写回单元格只想把结果放在文本框或者日志里那就得把数组转成字符串。之前说过Join只吃一维数组resultArr是二维直接Join会报错。我习惯写一个通用的小函数专门把二维数组转成带分隔符的字符串Function Array2DToString(arr As Variant, Optional sep As String ,) As String Dim i As Long, j As Long Dim line As String Dim allLines As String For i LBound(arr, 1) To UBound(arr, 1) line For j LBound(arr, 2) To UBound(arr, 2) line line arr(i, j) sep Next j If Len(line) 0 Then line Left(line, Len(line) - Len(sep)) allLines allLines line vbCrLf Next i Array2DToString allLines End Function这个函数把每一行用sep拼起来再用换行符隔开。调试的时候直接在立即窗口Debug.Print Array2DToString(resultArr)一眼就能看到数组内容比逐个格排查快多了。如果数组只有一列你也可以用Application.Transpose把它变成一维数组后再配合Join但我不建议依赖这个操作原因之前说过Transpose在数据量大时有隐患。3.4 写回与性能对比我见过很多人在Excel里做数据清洗一个单元格一个单元格地读、判断、写几百行数据还能忍几万行就开始转圈。用数组一次性读写性能提升是数量级的。举个例子10万行数据每行做一次比较和累加。直接循环Range.Cells读取在我机器上跑大约要十几秒甚至更久一次性读进数组再循环数组最后写回整个过程一般一秒上下。为什么会差这么多因为VBA每次和Excel交互都要经过COM调用从VBA到Excel对象模型再回来这个开销非常大。数组操作全程在内存里进行根本不碰Excel自然快几个量级。所以一个很朴素的优化原则能用数组解决的别碰单元格能一次读写的别循环单格。这个原则几乎适用于所有Excel VBA批量处理场景做报表、清洗数据、拆分合并表格通通适用。4. 常见坑位与排查方法我走过的弯路4.1 下标越界arr(0,0)为什么报错最常见的报错就是“下标越界”。原因我在1.2里说过从Range.Value读出来的二维数组下界是1。arr(0, 0)不存在arr(1, 1)才是第一个元素。我一开始写代码时习惯用零基循环结果报错报得怀疑人生。排查方法很简单在立即窗口打印UBound(arr, 1)、UBound(arr, 2)、LBound(arr, 1)、LBound(arr, 2)这四个值能立刻告诉你数组边界在哪。还有一个隐蔽的越界发生在用dict.keys返回的数组上。字典的keys数组下界是0如果你用For k 1 To dict.Count去遍历最后一次肯定会越界。数组来源不同下界可能不同拿到数组先打印边界能省一大半调试时间。4.2 类型不匹配你以为读的是数组其实是个对象“类型不匹配”是另一个高频报错。最常见的场景是想要数组但实际区域只有一个单元格Value返回的是标量于是你把它当数组用就直接炸了。还有一种情况你用了Set arr Range(A1:C10)把Range对象赋给了一个非对象变量也会报类型不匹配。记住Range(A1:C10).Value是取值Range(A1:C10)是取对象两者完全不是一回事。如果你不确定Value返回的是不是数组用TypeName判断一下If TypeName(arr) Variant() Then 是数组 Else 不是数组按单值处理 End If4.3 空单元格和错误值Empty不是空字符串空单元格读进数组后元素是Empty。很多人在循环里判断If arr(i, j) 结果空单元格跳不过去因为Empty和空字符串比较在某些情况下会得到True但更多时候会带来隐藏Bug。正确写法是用IsEmpty(arr(i, j))。错误值更麻烦。如果单元格是#N/A或者#DIV/0!数组里对应位置的VarType是vbError你用CStr去转它得到的是“Error 2042”这样的东西而不是单元格显示的样子。所以在做数值计算之前要用IsError判断一下否则会把错误值当成数值去累加最后结果全是乱的。4.4 多区域读取非连续区域的Value没那么好惹有些同学喜欢一次性读取多个不连续区域比如Range(A1:A10, C1:C10).Value以为能得到一个10行2列的数组。但现实很骨感非连续区域的Value返回结果在不同Excel版本里行为不一致有时候只返回第一块区域的内容有时候直接报错。我踩过坑之后就给自己定了一条规矩读取数据尽量用连续区域哪怕中间有空列也把空列一起读进来再处理。如果实在要处理非连续区域稳妥做法是分别读取每个Area再手动拼到一起Dim rng As Range Dim area As Range Set rng Range(A1:A10, C1:C10) For Each area In rng.Areas 单独读area.Value并处理 Next area虽然多写几行但结果可控不会在不同环境里出现“薛定谔的数组”。4.5 数组写回维度对不上立刻翻脸写回报错的原因就一个目标区域的行列数和数组的上界不匹配。假设数组是10行3列你写进Range(E1:F10)那肯定会报错。如果数组是10行1列你写进Range(E1:E10)一般没问题但如果数组是一维的直接写进多行区域有时也会报错。最保险的做法是写回之前先看一眼UBound然后用Resize把目标区域设置成和数组一样大。数组切片写回也有坑。比如用Application.Index取出单列后返回的是一维数组直接赋值给一列Excel区域有可能只填第一个元素或者报错。我在2.4里提过遇到这种情况用WorksheetFunction.Transpose包一下或者明确写成二维数组再赋值。总之写回不成功先别急着骂VBA检查维度。5. 延伸WPS、动态数组与数组的更多玩法5.1 WPS里VBA数组行为基本一致但要注意版本现在WPS也支持VBA了装了对应模块之后很多人把Excel里的代码搬到WPS里跑。好消息是Range.Value返回二维数组这个底层行为在WPS里和Excel基本一致LBound为1这个特性也没有改变。所以这篇里讲的内容在WPS里大部分都能直接用。但有两个地方要留个心眼。第一WPS的VBA对某些对象模型的支持并不完全和微软一致个别函数和属性的边界行为会有差异比如WorksheetFunction里某些统计函数的参数限制。第二如果代码里用了后期绑定CreateObject(Scripting.Dictionary)在WPS里只要系统有scrrun.dll就能跑但如果你用VBA插件自带的运行环境偶尔会遇到引用缺失的问题。最稳妥的办法是在WPS里先跑一个小测试把UBound、LBound、TypeName全部打印出来确认环境和Excel没有出入再放心跑大批量任务。5.2 动态数组与数组切片别让固定维度困住你Range.Value读出来的数组维度是固定的但你在处理过程中经常需要动态生成结果数组。比如前面统计部门的例子只有循环结束后才知道有多少部门所以你用ReDim resultArr(1 To dict.Count, 1 To 2)来动态定义大小这是非常常见的用法。再提一个很有用的数组操作——用Application.Index做切片。二维数组太大我们经常只想取其中几列。Application.Index(arr, 0, 2)可以取出第2列所有行变成一个一维数组Application.Index(arr, 3, 0)可以取出第3行所有列。这个函数的威力在于你不需要写循环就能从数组里抽出任意行或列配合写回和转置能少写很多代码。不过Application.Index也有脾气它要求第二个参数行号和第三个参数列号至少有一个是0才能返回数组。两个都是具体数字时它返回的是那个交叉点的单值。别问我是怎么知道的我第一次用它取单行的时候改了半天参数才搞明白。5.3 关于性能的两个额外建议最后再补充两个我自己的数组优化心得。第一个读数组之前先想清楚区域大小尽量避免直接读取整列。整列的Value返回的数组包含1048576个元素哪怕你只要前面100行内存和时间都浪费了。用Range(A1:A100)精确控制范围数组小遍历快。第二个如果数组处理过程中不需要修改原数据只是读取可以尝试把数组赋值给另一个变量时直接用arr2 arrVBA会复制整个数组。但要注意这也是拷贝不是引用修改arr2不会影响arr。想清楚是要拷贝还是引用能避免很多“改了A没改B”的困惑。写到这里突然想起我刚开始写VBA时的一个小习惯不知道对你有没用每次读完数组我第一件事就是在立即窗口输入“? UBound(arr,1) x UBound(arr,2)”把行列数打出来。这个动作我保持了很久虽然看起来多此一举但确实帮我少踩了无数个下标越界。数组在VBA里就是一块内存快照你摸清了它的维度、下界和元素类型后面的一切都顺了。以后你再看到“读取Range.Value得到数组”这句话第一反应不再是头疼而是心里有数哦一个从1开始编号的二维Variant数组而已。