关于EXCEL中VBA的数组理解.docx

关于EXCEL中VBA的数组理解.docx

《关于EXCEL中VBA的数组理解.docx》由会员分享，可在线阅读，更多相关《关于EXCEL中VBA的数组理解.docx（9页珍藏版）》请在冰豆网上搜索。

关于EXCEL中VBA的数组理解

关于EXCEL中VBA的数组理解

一、什么是VBA数组

不要把VBA数组想的太神秘，它其实就是一组数字而已。

把一组数按行、列排在一起，放在内存中。

就是一个VBA数组。

二、数组的维数

数组的维数其实和文件管理的目录一样的，只有一层时就是一维。

如果有两层则是二维数组（有行有列为二维），如果有三层，则是三维数组。

。

VBA中支持1~3维数组运算。

Sub数组示例（）

DimxAsLong,yAsLong

Dimarr（1To10,1To3）'创建一个可以容下10行3列的

数组空间

Forx=1To4

Fory=1To3

arr（x,y）=Cells（x,y）'通过循环把单元格区域a1:

c4的

数据装进数组中

Nexty

Nextx

MsgBoxarr（4,3）'根据提供的行数和列数显示数组

arr（1,2）="我改一下试试"'你可以随时修改数组内指定位置的数据

MsgBoxarr（1,2）

EndSub

总结：

二维是由行和列表示的数组，如ARR（3,2）表示数组中第3排第2列的元素。

而一维数组只是由一个元素决定，如ARR（4）表示数组中第4个元素

三、把单元格数据搬入内存：

一、声明：

DimarrasVariant'声明一个变量，不能声明其他数据类型

Dimarr（1to10,1to2）,这种声明也是错误的，固定大小的VBA数组是不能一次性装入单元格数据

或：

dimarr（）这种声明方式是声明一个动态数组，也

可以装入单元格区域，构成一个VBA数组。

二、装入

arr=range（"a9:

c100"）'装入很简单，变量=单元格区

域

三、读出

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

Msgboxarr（3,2）'就可以取出搬过去的而构成的数组

第3行第2列的内容

四、示例

Subs3（）

Dimarr（）'声明一个动态数组（动态指不固定大小）

Dimarr1'声明一个Variant类型的变量

arr=Range（"a1:

c7"）'把单元格区域A1：

C7的值装入

数组arr

'把单元格区域A1：

C7的值装入

arr1=Range（"a1:

c7"）

数组

arr1

值

MsgBoxarr（1,1）

'读取arr数组中第1行第1列的数

值

MsgBoxarr1（2,3）

'读取arr1数组的第2行第3列的数

EndSub

四、把内存数据搬入单元格

'声明一个变量用来盛放单元格数据

DimxAsInteger

arr=Range（"a2:

d5"）'把单元格数据搬入到arr里，它有4列4行

Forx=1To4'通过循环在arr数组中循环

arr（x,4）=arr（x,3）*arr（x,2）'数组的第4

列（金额）=第3列*第2例

Nextx

Range（"a2:

d5"）=arr'把数组放回到单元格中

EndSub

Subtest1（）

Dimarr（1To5）'声明一维数组

Forx=1To5arr（x）=x*2'通过循环给每个位置赋值

Nextx

Range（"A1:

E1"）=arr'把数组导入到excel中的a1:

e1单元格中

Range（"A1:

A5"）=Application.Transpose（arr）'如果是放在

一列中,就需要对数组进行转置后再存放

EndSub

五、动态数组的声明

Subdarr（）

Dimarr（）'声明一个动态的arr数组（不知道它能盛多少数

据）

Dimk

k=Application.WorksheetFunction.CountIf（Range（"a2:

a6

"）,">10"）'计算大于10的个数

ReDimarr（1Tok）'再次声明arr的大小,正好盛下k数量的值

Forx=2To6

IfCells（x,1）>10Thenm=m+1

arr（m）=Cells（x,1）'通过循环把大于10的数字装入数组EndIf

Nextx

MsgBoxarr

（2）

EndSub

六、数组的上标（IBOUND）和下标（UBOUND）

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

Subt1（）

Dimarr（-19To8）

MsgBoxUBound（arr）'返回最大编号,结果为8

MsgBoxLBound（arr）'返回最小编号,结果为-19

EndSub如果是有行列组成的二维数组呢?

二维数组返回行的下标和列的下标见下例

Subt2（）

Dimarr（-19To8,2To5）

MsgBoxUBound（arr）'返回第1维（行的）最大编号,结果为8

MsgBoxLBound（arr）'返回第1维（行的）小编号,结果为-19

MsgBoxUBound（arr,2）'返回第2维（列的）最大编号,结果为5

MsgBoxLBound（arr,2）'返回第2维（列的）最小编号,结果为2EndSub

arr=Sheets

（1）.UsedRange'Usedrange的行数和列数是未知

MsgBoxUBound（arr,1）'可以计算这个区域有多少行

MsgBoxUBound（arr,2）'可以计算出这个区域有多少列EndSub

七、使用Array函数创建常量数组：

使用Array函数创建数组

1维常量数组：

Array（"A",1,"C"）

2维常量数组：

Array（Array（"a",10）,Array（"b",20）,Ar

ray（"c",30））

也可以调用

1维数量：

excel工作表内存数组：

[{"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、调用工作表函数时使用：

arr=[{"a",10;"b",20;"c",30}]

调用vlo

MsgBoxApplication.VLookup（"b",arr,2,0）okup时可以作为第二个参数

EndSub

八、数组的合并和字符串拆分（Join&Split）：

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

A-REW-E-RWC-2-RWC分隔符-拆分成6个字符放在一个数组中有一组数array（23,45,7,1,76）想用分隔符-连接成一个字符串上面两种情况VBA提供了一对函数，即：

split（字符串,"分隔符"）拆分字符串

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

Subt1（）

Dimarr,mystAsString

myst="A-REW-E-RWC-2-RWC"

arr=Split（myst,"-"）'按-分隔成一组数装入数组中

'MsgBoxarr（0）'显示数组的第一个数（分隔后的数组最小下标为0,不是1）,显示结果为A

MsgBoxJoin（arr,","）'再用","把数组的每个值连接成一

个字符串,结果为"A,REW,E,RWC,2,RWC"

EndSub值得注意的是：

split和join只能对一维数组进行操作，如果是单元格或二维数组怎么办？

只有一条途径，想办法转换为一维数组：

Subt2（）

DimARR

ARR=Application.Transpose（Range（"a1:

a3"））‘用转置的方法，把单元格一列数据转换成一维数组

MsgBoxJoin（ARR,"-"）

EndSub

九、Filter函数实现数组筛选:

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

Filter函数

用法：

Filter（数组,筛选的字符,是否包含）

SubDD（）

arr=Array（"ABC","A","D","CA","ER"）

arrl=VBA.Filter（arr,"A",True）'筛选所有含A的数值组

成一个新数组

arr2=VBA.Filter（arr,"A",False）'筛选所有不含A的数

值组成一个新数组

MsgBoxJoin（arr2,","）'查看筛选的结果

EndSub

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

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

一、数组的最值

1、Max和Min

工作表函数Max和Min是求最大值和最小值的函数，同样在VB

A中也可以求数组的最大值和最小值。

如：

Subt（）

arr=Array（1,35,4,13）

MsgBoxApplication.Max（arr）'最大值

MsgBoxApplication.Min（arr）'最小值

EndSub

2、large和small

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

Subt1（）

arr=Array（1,35,4,13）

MsgBoxApplication.Large（arr,2）'第2大值

MsgBoxApplication.Small（arr,2）'第2小值

EndSub

二、数组的统计与求和

1、Sum

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

Subt2（）

arr=Array（1,35,4,13）

MsgBoxApplication.Sum（arr）'对数组进行求和

EndSub

2、Count和Counta

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

Subt3（）

arr=Array（1,35,"a",4,13,"b"）

MsgBoxApplication.Count（arr）'返回数字的个数4

MsgBoxApplication.CountA（arr）‘返回数组文本和数字的

总个数

EndSub

三、数组的查询和拆分

1、Mach查询数组

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

如：

Subt4（）

arr=Array（1,35,4,13）

MsgBoxApplication.Match（4,arr,0）'查询数值4在数

组Arr中的位置

EndSub

2、Index拆分数组

数组的拆分在VBA中是一个难题，如果是按行拆分数组，除了用循环外也只能借用API函数完成了。

幸好我们可以借用工作表函数index达到按列拆分数组，即多列构成的数组，你可以任意拆分出一列构成新的数组。

方法是：

Application.Index（数组,,列数），例：

Subt2（）

arr2=Range（"A1:

B4"）‘把单元格区域A1：

B4的值装入数组

arr2

arr3=Application.Index（arr2,,2）'把数组第2列拆

分出来装入新数组arr3中，新数组为二维数组

MsgBoxarr3（2,1）'取出新数组第2行的值

EndSub

四、数组维数的转换

Transpose转置数组在工作表中可以把行列转换。

在VBA中同样

也可以做到转换的效果。

1、一维转二维。

Subt9（）

arr=Array（1,35,"a",4,13,"b"）

arr1=Application.Transpose（arr）

MsgBoxarr1（2,1）‘转换后的数组是1列多行的二维数组

EndSub

2、二维数组转一维。

Subt2（）

arr2=Range（"A1:

B4"）

arr3=Application.transpose（Application.Index（arr2,,

2））'取得arr2第2列数据并转置成1维数组

MsgBoxarr3（2,）

EndSub

注：

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

思考题：

我要把a1:

c1中的内容用“-”连接起来，下面代码中为什么用了两次transpose

Subt10（）

arr=Range（"A1:

C1"）

MsgBoxJoin（Application.Transpose（Application.Transpose（arr））,"-"）

EndSub

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