数组 – EXCEL VBA（11）-淘娜娜副业社

声明数组的方法和说明其它任何变量一样，可以用Dim（、、）语句或声明任何其它变量的方法来说明数组变量。各种声明数组的样例代码如下：

Dim arr1(5) As Integer          '定义6个元素的一维数组变量（默认下标从0开始）
Dim arr2(0 to 5) As Integer   '定义6个元素的一维数组变量（指定下标起始值）
Dim arr3(1 to 5) As Integer   '定义5个元素的一维数组变量（指定下标起始值）
Dim arr4(-5 to 5) As Integer  '定义11个元素的一维数组变量（下标起始值可以为负数）
Dim arr5(3,4) As Integer        '定义4行5列20个元素的二维数组变量
Dim arr6(1 to 3,1 to 4) As Integer  '定义3行4列12个元素的二维数组变量

III、数组初始化

数组具有多个元素，可以保持多个数据。与单个变量不同，数组变量创立后需要通过批量赋值进行初始化。常用初始化方法如下列代码：

Dim x As Double
'使用循环语句初始化数组
Dim i As Integer
Dim j As Integer
Dim arr(10) As Integer
For i = 0 To 10
  '产生10-99随机整数
  arr(i) = Application.WorksheetFunction.RandBetween(10, 99)
Next i
x = Application.WorksheetFunction.Sum(arr)  '一维数组求和
Dim arrs(10, 10) As Integer
For i = 0 To 10
  For j = 0 To 10
    arrs(i, j) = Application.WorksheetFunction.RandBetween(10, 99)
  Next j
Next i
x = Application.WorksheetFunction.Sum(arrs)  '二维数组求和
'使用Array函数初始化数组
Dim a As Variant
a = Array(2, 4, 3, 6, 7, 8)
MsgBox a(2)    '显示为3
'使用已有数组初始化数组
Dim b As Variant
b = a
MsgBox b(3)    '显示为6
b = arrs
MsgBox b(3,3)
'对当前表格指定区域求和
x = Application.WorksheetFunction.Sum(Range("A2:D19")) 
'对sheet4指定区域求和 
'x = Sheet4.Application.WorksheetFunction.Sum(Range("A2:D19"))
'设置区域对象  
'Set rng = Application.Range(Cells(1, 1), Cells(11, 11))  
'rng.Value = arrs  '将数组赋值给区域
'x = Application.WorksheetFunction.Sum(rng)  '对区域求和
x = Application.WorksheetFunction.Sum(arrs)  '对多维数组求和
Set rng = Application.Range(Cells(1, 1), Cells(1, 6))
rng.Value = a
x = Application.SumIf(rng, ">4")
MsgBox x  '显示21

IV、动态数组

在很多情况下，数组的元素个数（长度）无法事先确定，需要在程序中改变数组的程度，因此出现了动态数组。动态数组相关代码：

Dim i As Integer
Dim arr As Variant  '定义变体型变量
arr = Array(1, 2, 3, 4, 5, 6)  '设置变体型变为数组
MsgBox arr(0)       '显示为1
MsgBox UBound(arr)  '显示数组上限
i = Val(InputBox("请输入数组上限", "定义动态数组", 5))
ReDim arr(i)
MsgBox arr(0)       'ReDim重新定义上限后数组元素消失
MsgBox UBound(arr)  '数组上限为对话框输入值
arr = Array(1, 2, 3, 4, 5, 6)
i = Val(InputBox("请输入数组上限", "定义动态数组", 5))
ReDim Preserve arr(i)  'ReDim加Preserve重新定义上限后数组元素不消失
MsgBox arr(0)       '显示为1
MsgBox IsArray(arr) '显示arr变量是否为数组
MsgBox LBound(arr)  '显示数组下限
MsgBox UBound(arr)  '显示数组上限
Erase arr           '删除原数组

V、用数组填充单元格区域

在EXCEL要处理大量数据时，可使用循环从单元格中读取数据，经过处理后再写回单元格区域中。这种方式比在数组中处理数据的速度要慢。因此，处理大数据时，可先将数据保存到数组中，经过加工后再将数组数据批量写回单元格区域。

EXCEL工作表是二维结构，由行和列组成。这种特性与二维数组一致，因此可以很方便地将工作表区域和二维数组进行交换。样例代码如下：

Dim x As Double
Dim y As Double
Dim i As Integer
Dim j As Integer
Dim arrs(10, 10) As Integer
Dim arrs1 As Variant
For i = 0 To 10
  For j = 0 To 10
    arrs(i, j) = Application.WorksheetFunction.RandBetween(10, 99)
  Next j
Next i
Set rng = Application.Range(Cells(1, 1), Cells(11, 11))  '设置区域对象
rng.Value = arrs  '将数组批量赋值给单元格区域
x = Application.WorksheetFunction.Sum(rng)   '对区域求和
y = Application.WorksheetFunction.Sum(arrs)  '对多维数组求和
arrs1 = Range(Cells(1, 1), Cells(11, 11))    '将单元格区域数据批量赋值给数组
msgbox isarray(arrs1)    '显示True

VI、数组常用函数及方法

在EXCEL要处理大量数据时，由于EXCEL二维结构工作表与二维数组一致，通常先将数据保存到数组中，经过加工函数处理后再将数组数据批量写回单元格区域。

Dim i As Integer
Dim j As Integer
Dim arr() As Integer
Dim arrs(10, 20) As Integer
For i = 0 To 10
  For j = 0 To 20
    arrs(i, j) = Application.RandBetween(10, 99)
  Next j
Next i
'使用Array创建数组
arr = Array(1, 2, 3, 4, 5, 6, 7, 8, 9, 10) 
[a1].Resize(1, 10) = arr
'IsArray函数判断变量是否是数组
MsgBox IsArray(arr)
'判断一维数组上、下限
MsgBox LBound(arr)      '下限
MsgBox UBound(arr)      '上限
'判断二维数组行、列数
MsgBox UBound(arrs, 1)
MsgBox UBound(arrs, 2)
'判断二维数组行、列下限
MsgBox LBound(arrs, 1)
MsgBox LBound(arrs, 2)
'使用Split创建数组（分隔符为“,”）
arr = Split("张一，徐一，陈一，王一，许一，张二，陈二，张三，徐二，张四", "，")
'使用Join将数组连接为字符串（连接符为“-”）
Dim mArr As Variant
mArr = Array(1, 5, 2, 5, 3, 5, 4, 5, 5, 5, 6, 5)
Dim numenstr As String
numenstr = Join(arr, "-")
MsgBox numenstr
'使用Filter筛选数组元素
oArr = Filter(arr, "二")          '筛选元素中含“二”的元素
oArr = Filter(arr, "二" , True)   '筛选元素中含“二”的元素
oArr = Filter(arr, "二" , False)  '筛选元素中不含“二”的元素
MsgBox oArr(1)
'数组转置后复制给单元格
[a1].Resize(10, 1) = Application.WorksheetFunction.Transpose(arr)
'数组直接等于单元格区域
arr = [a1:a10]

2、运用EXCEL函数处理数组和单元格区域

对于大数据来说，数组的存储能力和处理速度优于单元格区域对象，但比较R、语言，VBA自带数组处理函数和方法较少，无法满足实际需要。这里介绍通过对象调用EXCEL函数处理数组，至于通过自定义函数和类函数来扩展数组功能的内容在后面介绍。

针对单元格区域数据处理，EXCEL自带大量统计、字符串、日期和时间等函数，在很多场合下，可以通过VBA的对象调用EXCEL函数来处理数组。

I、数组和单元格区域相互转换

利用VBA的对象调用EXCEL函数来处理数组，首先要实现两者的互相转换。样例代码如下：

'数组填充单元格
Dim i As Integer
Dim j As Integer
Dim arr As Variant  '定义变体型变量
arr = Array(1, 2, 3, 4, 5, 6)  '设置变体型变为一维数组
Set rng = Sheet6.Range(Cells(1, 1), Cells(1, 6))  '设置列区域对象
rng.Value = arr  '将一维数组赋值给列区域单元格（注意只能按列填充）
Dim arrs(10, 10) As Integer  '定义数组
'数组填充（Application对象调用EXCEL的函数RandBetween）
For i = 0 To 10
  For j = 0 To 10
    arrs(i, j) = Application.WorksheetFunction.RandBetween(10, 99)
  Next j
Next i
Set rngs = Sheet6.Range(Cells(10, 1), Cells(20, 11))  '设置指定表格区域对象
'Set rngs = Application.Range(Cells(1, 1), Cells(11, 11))  '设置当前表格区域对象
rngs.Value = arrs  '将二维数组批量赋值给单元格区域
'单元格填充数组
arr = Range(Cells(1, 1), Cells(1, 6))    '将行单元格区域数据批量赋值给一维数组
MsgBox IsArray(arr)    '显示True
MsgBox arr(1, 2)
arr = Range(Cells(10, 2), Cells(15, 2))  '将列单元格区域数据批量赋值给一维数组
MsgBox IsArray(arr)    '显示True
MsgBox arr(2, 1)
Dim arrs1 As Variant    '定义多维数组变体型变量
arrs1 = Range(Cells(10, 1), Cells(15, 6))  '将列单元格区域数据批量赋值给多维数组
MsgBox IsArray(arrs1)    '显示True
MsgBox arrs1(3, 2)

II、通过对象调用EXCEL函数处理数组单元格区域

在需要调用EXCEL函数时，需要输入.或后加函数才有函数选项，两者的区别是是调用的工作表原始函数，而.是原始函数为了与EXCEL.VBA对接而“封装”过的函数。当有的函数两者都存在时，如SUM函数，调用数据处理效率高；反之，调用.，因为可调用的函数多。样例代码如下：

Dim x As Integer
Dim y As Double
Dim arr As Variant
Dim arrs(10, 10) As Integer
arr = Array(1, 2, 3, 4, 5, 6)
For i = 0 To 10
  For j = 0 To 10
    arrs(i, j) = Application.WorksheetFunction.RandBetween(10, 99)
  Next j
Next i
'调用Application所属函数
x = Application.RandBetween(10, 99)  '调用EXCEL的RandBetween函数
MsgBox x
x = Application.Sum(arr)  '一维数组求和
MsgBox x
x = Application.Sum(arrs)  '多维数组求和
MsgBox x
'调用Application.WorksheetFunction所属函数
y = Application.WorksheetFunction.StDev_P(arr)  '一维数组求标准差
MsgBox y
'将数组转换为单元格区域后计算
Set rng = Sheet6.Range(Cells(1, 1), Cells(1, 6))  '设置列区域对象
rng.Value = arr  '将一维数组赋值给列区域单元格
x = Application.WorksheetFunction.SumIf(rng, ">" & 3)
MsgBox x
Set rngs = Sheet6.Range(Cells(10, 1), Cells(20, 11))  '按Cells设置区域对象
rngs.Value = arrs  '将多维数组赋值给区域单元格
x = Application.WorksheetFunction.SumIf(rngs, ">" & 50)
MsgBox x
Set rngs = Sheet6.Range("A10:K20")  '按工作表地址设置区域对象
x = Application.WorksheetFunction.SumIf(rngs, ">" & 50)
MsgBox x
'按工作表地址计算（和数组无关）
x = Application.WorksheetFunction.Sum(Sheet6.Range("A10:A20"))
MsgBox x
x = Application.WorksheetFunction.SumIf(Sheet6.Range("A10:A20"), ">" & 50)
MsgBox x
x = Application.WorksheetFunction.SumIfs(Sheet6.Range("A10:A20"), _ 
 Sheet6.Range("B10:B20"), ">" & 50)
MsgBox x
x = Application.WorksheetFunction.Sum(Sheet6.Range(Cells(10, 1), Cells(20, 1)))
MsgBox x
x = Application.WorksheetFunction.SumIf(Sheet6.Range(Cells(10, 1), Cells(20, 1)), ">" & 50)
MsgBox x
x = Application.WorksheetFunction.SumIfs(Sheet6.Range(Cells(10, 1), Cells(20, 1)),   _ 
Sheet6.Range(Cells(10, 2), Cells(20, 2)), ">" & 50)
MsgBox x
End Sub

注：当某行VBA代码过长，可用代码连接符号” _ “断开，注意” _ “要和左右有空格

熟练运用EXCEL VBA数组技术可以大大提高处理大量数据效率，是提升EXCEL性能的重要途径。

———END———
限时特惠： 本站每日持续更新海量各大内部创业教程，永久会员只需109元，全站资源免费下载点击查看详情
站长微信： nanadh666

声明：1、本内容转载于网络，版权归原作者所有！2、本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。3、本内容若侵犯到你的版权利益，请联系我们，会尽快给予删除处理！

数组 – EXCEL VBA（11）

站长简介

最新实战项目

小红书引流私域旅游资料售卖，复制粘贴，三分钟一篇图文，日入1000+，小白终身可做

剪映电脑版实战教程，0基础打造短视频剪辑高手，详解功能、工具与实操案例

短视频制作与运营，从0到10万粉丝变现方法，剪映特效与热门视频制作教程

【高端精品】外面收费1988的咔咔一通剪视频批量处理，合并，裁剪，转图片，视频去重等功能【剪辑助手+详细教程】

主播直播特训营：抖音直播间运营知识+开播准备+流量考核，轻松上手

同城店铺短视频引流变现：掌握抖音平台规则，打造爆款内容，实现流量变现

小红书实战运营课：从开通店铺到直播话术，打造爆款笔记与高效直播

AI工具PPT制作教程：AI工具助力PPT制作，高质量PPT一分钟搞定

数组 – EXCEL VBA（11）

相关文章

站长简介

最新实战项目