Excel VBA数组入门教程

计算机语言、软件、硬件
回复
peng
Site Admin
帖子: 199
注册时间: 周五 11月 01, 2019 9:06 am

Excel VBA数组入门教程

帖子 peng »

Excel VBA数组入门教程

1. 前言:不要把VBA数组想的太神秘,它其实就是一组数字而已。

2. 数组的维数:

Sub 数组示例()
Dim x As Long, y As Long
Dim arr(1 To 10, 1 To 3) '创建一个可以容下10行3列的数组空间
For x = 1 To 4
For y = 1 To 3
arr(x, y) = Cells(x, y) '通过循环把单元格区域a1:c4的数据装进数组中
Next y
Next x
MsgBox arr(4, 3) '根据提供的行数和列数显示数组
arr(1, 2) = "我改一下试试" '你可以随时修改数组内指定位置的数据
MsgBox arr(1, 2)
End Sub

总结:二维是由行和列表示的数组,如ARR(3,2)表示数组中第3排第2列的元素。而一维数组只是由一个元素决定,如ARR(4)表示数组中第4个元素

3. 把单元格数据搬入内存:

一、声明:

Dim arr as Variant '声明一个变量,不能声明其他数据类型

Dim arr(1 to 10, 1 to 2 ) , 这种声明也是错误的,固定大小的VBA数组是不能一次性装入单元格数据

或:dim arr() 这种声明方式是声明一个动态数组,也可以装入单元格区域,构成一个VBA数组。

二、装入

arr =range("a9:c100") '装入很简单,变量 = 单元格区域

三、读出

装入数组后的单元格数值,可以按 数组名称(行数,列数) 直接读取该位置的值,如下面的代码。

Msgbox arr(3,2) '就可以取出搬过去的而构成的数组第3行第2列的内容

四、示例

Sub s3()

Dim arr() '声明一个动态数组(动态指不固定大小)
Dim arr1 '声明一个Variant类型的变量
arr = Range("a1:c7") '把单元格区域A1:C7的值装入数组arr

arr1 = Range("a1:c7") '把单元格区域A1:C7的值装入数组arr1
MsgBox arr(1, 1) '读取arr数组中第1行第1列的数值
MsgBox arr1(2, 3) '读取arr1数组的第2行第3列的数值
End Sub

4. 把单元格数据搬入内存:

Sub test()
Dim arr '声明一个变量用来盛放单元格数据
Dim x As Integer
arr = Range("a2:d5") '把单元格数据搬入到arr里,它有4列4行
For x = 1 To 4 '通过循环在arr数组中循环
arr(x, 4) = arr(x, 3) * arr(x, 2) '数组的第4列(金额)=第3列*第2例
Next x
Range("a2:d5") = arr '把数组放回到单元格中
End Sub

Sub test1()
Dim arr(1 To 5) '声明一维数组
For x = 1 To 5
arr(x) = x * 2 '通过循环给每个位置赋值
Next x
Range("A1:E1") = arr '把数组导入到excel中的a1:e1单元格中
Range("A1:A5") = Application.Transpose(arr) '如果是放在一列中,就需要对数组进行转置后再存放
End Sub

5. 动态数组的声明:

Sub darr()
Dim arr() '声明一个动态的arr数组(不知道它能盛多少数据)
Dim k
k = Application.WorksheetFunction.CountIf(Range("a2:a6"), ">10") '计算大于10的个数
ReDim arr(1 To k) '再次声明arr的大小,正好盛下k数量的值
For x = 2 To 6
If Cells(x, 1) > 10 Then
m = m + 1
arr(m) = Cells(x, 1) '通过循环把大于10的数字装入数组
End If
Next x
MsgBox arr(2)
End Sub

6. 动态数组的声明:

arr(-19 to 8) 这个数组的编号就是从-19开始的.那么它的最小编号就是-19,最大编号是8, 如果用语句返回就是:

Sub t1()

Dim arr(-19 To 8)

MsgBox UBound(arr) '返回最大编号,结果为8

MsgBox LBound(arr) '返回最小编号,结果为-19

End Sub

如果是有行列组成的二维数组呢?二维数组返回行的下标和列的下标见下例

Sub t2()
Dim arr(-19 To 8, 2 To 5)

MsgBox UBound(arr) '返回第1维(行的)最大编号,结果为8

MsgBox LBound(arr) '返回第1维(行的)小编号,结果为-19
MsgBox UBound(arr, 2) '返回第2维(列的)最大编号,结果为5
MsgBox LBound(arr, 2) '返回第2维(列的)最小编号,结果为2
End Sub

Sub t3()
Dim arr
arr = Sheets(1).UsedRange 'Usedrange的行数和列数是未知的
MsgBox UBound(arr, 1) '可以计算这个区域有多少行
MsgBox UBound(arr, 2) '可以计算出这个区域有多少列
End Sub

7. 使用Array函数创建常量数组:

使用Array函数创建数组

1维常量数组:Array("A",1,"C")

2维常量数组: Array(Array("a", 10), Array("b", 20), Array("c", 30))

也可以调用excel工作表内存数组:

1维数量: [{"A",1,"C"}]

2维数量:[{"a",10;"b",20;"c",30}]

内存常量数组有什么作用呢?

1、简化赋值

比如:我需要给数组arr分别赋值10 ,20,30,40 ,一般就需要分别赋值,即:

arr(1)=10

arr(2)=20

arr(3)=30

arr(4)=40

而使用常量数量,只一句话:

arr=array(10,20,30,40)

2、调用工作表函数时使用:

Sub mylook()
Dim arr
arr = [{"a",10;"b",20;"c",30}]
MsgBox Application.VLookup("b", arr, 2, 0) '调用vlookup时可以作为第二个参数
End Sub

8. 数组的合并和字符串拆分(Join & Split):


多个字符的合并和字符串按规律的拆分是经常遇到的,如:

A-REW-E-RWC-2-RWC 按分隔符-拆分成6个字符放在一个数组中

有一组数array(23,45,7,1,76)想用分隔符-连接成一个字符串

上面两种情况VBA提供了一对函数,即:

split(字符串,"分隔符") 拆分字符串

join(数组,"分隔符") 用分隔连接数组的每个元成一个字符串

Sub t1()
Dim arr, myst As String
myst = "A-REW-E-RWC-2-RWC"
arr = Split(myst, "-") '按-分隔成一组数装入数组中
'MsgBox arr(0) '显示数组的第一个数(分隔后的数组最小下标为0,不是1),显示结果为A
MsgBox Join(arr, ",") '再用","把数组的每个值连接成一个字符串,结果为"A,REW,E,RWC,2,RWC"
End Sub

值得注意的是:split和join只能对一维数组进行操作,如果是单元格或二维数组怎么办?只有一条途径,想办法转换为一维数组:

Sub t2()
Dim ARR
ARR = Application.Transpose(Range("a1:a3")) '用转置的方法,把单元格一列数据转换成一维数组
MsgBox Join(ARR, "-")
End Sub

9. Filter函数实现数组筛选:

数组的筛选就是根据一定的条件,从数组中筛选符合条件的值,组成一个新的数组,实现数组筛选的VBA函数是:

Filter函数

用法:Filter(数组, 筛选的字符, 是否包含)

Sub DD()
arr = Array("ABC", "A", "D", "CA", "ER")
arr1 = VBA.Filter(arr, "A", True) '筛选所有含A的数值组成一个新数组
arr2 = VBA.Filter(arr, "A", False) '筛选所有不含A的数值组成一个新数组
MsgBox Join(arr2, ",") '查看筛选的结果
End Sub

遗憾的是函数只能进行模糊筛选,不能精确匹配。

10. VBA数组入门教程之10(大结局):他山之石):

他山之石,可以攻玉,VBA中除可以利用的VBA函数外,还可以调用众多的Excel工作表函数对数组进行分解、查询和分析等,调用工作表函数可以省去循环判断的麻烦,进而提高运行效率。

一、数组的最值

1、Max和Min

工作表函数Max和Min是求最大值和最小值的函数,同样在VBA中也可以求数组的最大值和最小值。如:

Sub t()
arr = Array(1, 35, 4, 13)
MsgBox Application.Max(arr) '最大值
MsgBox Application.Min(arr) '最小值
End Sub

2、large和small

工作表函数large和small 是返回一组数的第N大和第N小,对VBA数组同样适用,如:

Sub t1()
arr = Array(1, 35, 4, 13)
MsgBox Application.Large(arr, 2) '第2大值
MsgBox Application.Small(arr, 2) '第2小值
End Sub

二、数组的统计与求和

1、Sum

Sum函数可以在工作表中求,同样也可以对VBA数组求和,如:


Sub t2()
arr = Array(1, 35, 4, 13)
MsgBox Application.Sum(arr) '对数组进行求和
End Sub

2、Count和Counta

Count和Counta可以统计数组中数字的个数和数字+文本的个数。

Sub t3()
arr = Array(1, 35, "a", 4, 13, "b")
MsgBox Application.Count(arr) '返回数字的个数4
MsgBox Application.CountA(arr) '返回数组文本和数字的总个数
End Sub

三、数组的查询和拆分

1、Mach查询数组

Match函数可以查询一个指定值在一组数中的位置,它也可以用于VBA数组的查询。如:

Sub t4()
arr = Array(1, 35, 4, 13)
MsgBox Application.Match(4, arr, 0) '查询数值4在数组Arr中的位置
End Sub

2、Index拆分数组

数组的拆分在VBA中是一个难题,如果是按行拆分数组,除了用循环外也只能借用API函数完成了。幸好我们可以借用工作表函数index达到按列拆分数组,即多列构成的数组,你可以任意拆分出一列构成新的数组。方法是:Application.Index(数组, , 列数) ,例:

Sub t2()
arr2 = Range("A1:B4") '把单元格区域A1:B4的值装入数组arr2
arr3 = Application.Index(arr2, , 2) '把数组第2列拆分出来装入新数组arr3中,新数组为二维数组
MsgBox arr3(2, 1) '取出新数组第2行的值
End Sub

四、数组维数的转换

Transpose转置数组在工作表中可以把行列转换。在VBA中同样也可以做到转换的效果。

1、一维转二维。

Sub t9()
arr = Array(1, 35, "a", 4, 13, "b")
arr1 = Application.Transpose(arr)
MsgBox arr1(2, 1) '转换后的数组是1列多行的二维数组
End Sub

2、二维数组转一维。

Sub t2()
arr2 = Range("A1:B4")
arr3 = Application.transpose(Application.Index(arr2, , 2)) '取得arr2第2列数据并转置成1维数组
MsgBox arr3(2,)
End Sub

注:在转置时只有1列N行的数组才能直接转置成一维数组

思考题:我要把a1:c1中的内容用"-"连接起来,下面代码中为什么用了两次transpose

Sub t10()
arr = Range("A1:C1")
MsgBox Join(Application.Transpose(Application.Transpose(arr)), "-")
End Sub

编后话:用于VBA数组的工作表函数我只是列出了一部分,其实象vlookup,Lookup等等函数也可以用于处理VBA数组,大家有空了就去尝试下吧。

peng
Site Admin
帖子: 199
注册时间: 周五 11月 01, 2019 9:06 am

Re: Excel VBA数组入门教程

帖子 peng »

VBA自学资料(7)VBA中的Match函数

MATCH函数说明:
MATCH(lookup_value,lookup_array,match_type)
Lookup_value为需要在数据表中查找的数值。
Lookup_value为需要在Look_array中查找的数值。例如,如果要在电话簿中查找某人的电话号码,则应该将姓名作为查找值,但实际上需要的是电话号码。
Lookup_value可以为数值(数字、文本或逻辑值)或对数字、文本或逻辑值的单元格引用。
Lookup_array可能包含所要查找的数值的连续单元格区域。Lookup_array 应为数组或数组引用。

Match_type为数字-1、0或1。Match-type 指明 Microsoft Excel 如何在 lookup_array 中查找 lookup_value。
如果 match_type 为 1,函数MATCH 查找小于或等于lookup_value的最大数值。Lookup_array 必须按升序排列:...、-2、-1、0、1、2、...、A-Z、FALSE、TRUE。
如果 match_type 为 0,函数 MATCH 查找等于lookup_value的第一个数值。Lookup_array 可以按任何顺序排列。
如果 match_type 为 -1,函数 MATCH 查找大于或等于 lookup_value 的最小数值。Lookup_array 必须按降序排列:TRUE、FALSE、Z-A、...、2、1、0、-1、-2、...,等等。
如果省略 match_type,则假设为 1。
说明:
函数 MATCH 返回 lookup_array 中目标值的位置,而不是数值本身。例如,MATCH('b',{'a','b','c'},0) 返回 2,即“b”在数组 {'a','b','c'} 中的相应位置。
查找文本值时,函数 MATCH 不区分大小写字母。

如果函数 MATCH 查找不成功,则返回错误值 #N/A。
如果 match_type为0且lookup_value为文本,lookup_value可以包含通配符、星号 (*) 和问号 (?)。星号可以匹配任何字符序列;问号可以匹配单个字符

用VBA操作MATCH函数的要点:
1、使用前要写一句
On Error Resume Next
2、使用方法:
application.WorksheetFunction.Match(,,,)
例子:查询值:B1单元格,查询区域A列,得到的数字赋给变量y
Sub abc()
Dim y
On Error Resume Next
y = Application.WorksheetFunction.Match(Range('b1'), 搜索Columns('a'), False)
MsgBox y
End Sub

peng
Site Admin
帖子: 199
注册时间: 周五 11月 01, 2019 9:06 am

Re: Excel VBA数组入门教程

帖子 peng »

Excel VBA解读(154): 数据结构——数组常用操作示例代码

本文主要给出使用数组常用的一些代码,供有需要时参考。

遍历数组

下面的代码从数组第一个元素开始遍历所有数组元素:

For i = LBound(arr) To UBound(arr)
Debug.Print arr(i)
Next i

或者:

For i = LBound(arr, 1) To UBound(arr, 1)
Debug.Print arr(i)
Nexti

上面的代码遍历一维数组,下面的代码遍历二维数组:

For i = LBound(arr, 1) To UBound(arr, 1)
For j = LBound(arr, 2) To UBound(arr, 2)
Debug.Print arr(i, j)
Next j
Next i

如果将计数变量声明为variant型,那么还可以使用For Each循环遍历数组:

Dim item As Variant
Dim arr(6) As Long
Dim i As Long
For i = 0 To 6
arr(i) = i
Next i

For Each item In arr
Debug.Print item
Next item

如果使用工作表单元格区域中的数据快速填充数组,那么也可以使用For Each循环遍历数组元素:

Dim arr As Variant
Dim item As Variant
arr= Worksheets("Sheet1").Range("A1:C5")
For Each item In arr
Debug.Print item
Next item

传递数组
下面的代码将主过程中的数组传递给被调用过程:

Sub test11()
Dim myArr(5) As Long
Dim i As Long
For i = 0 To 5
myArr(i) = i
Next i
MyArray myArr()
End Sub

Sub MyArray(ByRef arr() As Long)
Dim i As Long
For i = LBound(arr) To UBound(arr)
Debug.Print arr(i)
Next i
End Sub

下面的代码从函数过程中返回数组:

Sub test31()
Dim myArray() As Long
Dim i As Long
myArray = GetArray
For i = LBound(myArray) To UBound(myArray)
Debug.Print myArray(i)
Next i
End Sub

Function GetArray() As Long()
Dim arr(5) As Long
Dim i As Long
For i = 0 To 5
arr(i) = i
Next i
GetArray = arr
End Function

获取数组中元素的数量
下面的自定义函数可以返回传递给它的任何维数的数组的元素数:

'返回数组元素的数量

Function ArrayElemNum(arr As Variant) As Long
On Error GoTo E
Dim i As Long
Do While True
i = i + 1
ArrayElemNum = IIf(ArrayElemNum = 0, 1,ArrayElemNum) _
* (UBound(arr, i) - LBound(arr,i) + 1)
Loop
Exit Function
E:
If Err.Number = 13 Then
Err.Raise vbObjectError,"ArrayElemNum", _
"传递给ArrayElemNum函数的参数不是数组."
End If
End Function

测试ArrayElemNum函数的代码及结果如下:

Sub testArrayElemNum()
Dim arr1() As Long
'返回0
Debug.Print ArrayElemNum(arr1)
Dim arr2(5) As Long
'返回6
Debug.Print ArrayElemNum(arr2)
Dim arr3(5, 2) As Long
'返回18
Debug.Print ArrayElemNum(arr3)
End Sub

排序数组元素
下面的代码使用快速排序算法对数组元素排序:

Sub QuickSort(arr As Variant, first As Long, last As Long)
Dim vCentreVal As Variant
Dim vTemp As Variant
Dim lTempLow As Long
Dim lTempHi As Long

lTempLow = first
lTempHi = last
vCentreVal = arr((first + last) \ 2)

Do While lTempLow <= lTempHi
Do While arr(lTempLow) < vCentreValAnd lTempLow < last
lTempLow = lTempLow + 1
Loop
Do While vCentreVal < arr(lTempHi)And lTempHi > first
lTempHi = lTempHi - 1
Loop
If lTempLow <= lTempHi Then
'交换数值
vTemp = arr(lTempLow)
arr(lTempLow) = arr(lTempHi)
arr(lTempHi) = vTemp
'移到下一位置
lTempLow = lTempLow + 1
lTempHi = lTempHi - 1
End If
Loop

If first < lTempHi Then QuickSort arr,first, lTempHi
If lTempLow < last Then QuickSort arr,lTempLow, last

End Sub

下面的代码测试快速排序代码:

Sub testQuickSort()
Dim arr(5) As Long
arr(0) = 5: arr(1) = 3
arr(2) = 1: arr(3) = 2
arr(4) = 6: arr(5) = 4
QuickSort arr, LBound(arr), UBound(arr)

Dim i As Long
For i = LBound(arr) To UBound(arr)
Debug.Print arr(i)
Next i
End Sub

回复