vba数组学习.docx
《vba数组学习.docx》由会员分享，可在线阅读，更多相关《vba数组学习.docx（23页珍藏版）》请在冰豆网上搜索。
vba数组学习
VBA数组学习
一、数组概念
二、数组就是一个列表或者一组数据表。
它是由连续可索引的具有相同内在数据类型的元素所组成的集合，数组中每一个元素都具有唯一的索引号。
更改其中一个元素并不会影响到其它元素。
数组存在内存，可以利用索引号获取该集合中每一个子集。
数组的两个特点：
1、读写速度快
VBA读取对象中的值永远慢于读取内存中的值。
可以借助VBA数组对程序提速。
2、无法永远保存
数据存于工作表区域内，可以永久保存。
但存入内存中的变量数组和常量数组却受其作用域影响生命周期。
过程级别的私有数组变量或者常量数组在过程结束后会自动释放，结束其生命周期；
而公有的变量数组和常量数组在excel应用程序关闭后会自动释放。
也就是重新启动excel后，以前任何数组都不存在。
3、数组分类
按照数组元素是否固定来分，可以分为静态数组和动态数组；
按照数组维度来分，可以分为一维数组、二维数组等，最多只能为60维。
二、数组的维度
数组可以是一维、二维直到六十维。
而对于excel工作表来说，
excel的每一行或者每一列就可以转换成一维数组，而多行多列就可以转换成二维数组。
1、一维数组
在数组公式中，在A1：
F1区域中输入数组公式：
={1,2,3,4,5,6}
，然后按Ctrl+Shift+Enter，就可以在A1：
F1之间得到了横向区域的值。
在A1：
A6区域中输入数组公式：
={1；2；3；4；5；6}，然后按
Ctrl+Shift+Enter，就可以在A1：
A6之间得到了纵向区域的值。
而VBA数组也可以得到同样的效果：
2.1VBA代码
Sub横向数组（）
[A1:
F1]=[{1,2,3,4,5,6}]
EndSub
[{1,2,3,4,5,6}]代表是一维横向数组。
2.2VBA代码
Sub纵向数组（）
[A1:
A6]=[{1;2;3;4;5;6}]
EndSub
[{1;2;3;4;5;6}]代表是一维纵向数组。
从上面两个赋值过程来看，看到数组在VBA中的优势，不仅可以对单元格进行循环赋值，还可以把原本需要循环6次的操作集中一次完成。
2、二维数组
对于三行三列的数组，我们可以使用数组公式：
={1,1,1;2,2,2;3,3,3}
在VBA中也可以用数组表示一个二维数组，如下面例子
Sub二维数组（）
[A1:
C3]=[{1,1,1;2,2,2;3,3,3}]
EndSub
三、利用索引号获取数组中的元素
类似Range可以使用索引号访问区域中每一个单元格一样，一维数组和二维数组也可以使用索引号获取数组中每一个值。
主要有两种形式：
形式一：
Arr（Item）
形式二：
Arr（RowIndex，ColumnIndex）
这两种形式看起来在形式上和Range的索引号完全一致，但在事实上存在很多差异。
请看差异在哪里，
例子1：
Sub索引号引用数组（）
Dimarr1（）'声明数组变量arr1=Array（"wise","rose","小花鸟","小花鸭"）'对数组赋值
MsgBoxarr1
（1）'正确的引用
MsgBoxarr1（1,1）'错误的引用
EndSub
在例子1中，对于一维数组的两种索引方式只能前者可以正常执行，后者会产生错误。
例子2：
Sub索引号引用数组1（）
Dimarr1（）'声明数组变量
arr1=[{1,1,1,1;2,2,2,2;3,3,3,3;4,4,4,4}]'对数组赋值
MsgBoxarr1（4,2）'正确的引用
MsgBoxarr1（4）'错误的引用
EndSub
对于二维数组，例子2中两种方式只能使用第一种方式，而后者会产生错误。
当使用索引号引用数组时候，值得注意是：
第一个元素的默认索引值。
在默认状态下，如果模块中未指定第一个元素的索引号，那么默认为0。
即数组中arr中的第一个值用arr（0）来表示，最后一个元素的索引号则为数组元素个数减1来表示。
如下例子：
Sub索引号引用数组（）
Dimarr1（）'声明数组变量
arr1=Array（"wise","rose","小花鸟","小花鸭"）'对数组赋值
MsgBoxarr1
（1）'正确的引用
EndSub
该例子显示的结果是rose而不是wise。
如果不习惯这种默认的索引方式，可以利用optionbase语句。
optionbase1：
表示数组中第一个元素的索引号为1
optionbase语句只能置于模块的顶部，并且可选值只能为0或1
。
因为默认状态为0，那么optionbase0可以忽略。
四、声明数组与赋值
1、声明数组变量声明数组和声明其他变量一样，可以使用dim、static、private或public等语句声明。
一般来说，有两种声明方式，一种是静态数组声明，该种声明方式在声明数组变量时已经指明了数组的大小。
一种是动态数组声明，它在程序运行后，数组大小可以被重置、改变。
数组声明：
当数组变量的参数是一个数值时，表示它是一维横向数组，元素个数等于该值加1。
如：
（1）dimarr（5）:
表示声明一个具有6个元素一维横向数组，其数据类型是变体变量variant；
（2）dimarr（4）asbyte:
表示声明一个具有5个元素的横向数组，其数据类型为byte
如果借助to关键字，可以指定数组第一个元素的索引值。
如
（1）dimarr（1to3）asstring:
表示声明一个具有3个元素的一维横向数组，数据类型是string,其第一个元素索引号为1；
（2）如果需要声明二维数组，可以使用逗号将参数分开，其形式为arr（一维，二维），如：
dimarr（3,2）asstring:
表示声明一个四行三列的二维数组，默认第一元素索引值为0；
dimarr（1to3,1to2）asstring:
表示声明一个三行二列的
二维数组
2、对数组变量赋值
数组赋值通常采用三种方式：
利用循环逐个赋值、利用Array对
一维数组变量赋值、直接将区域赋予数组。
循环赋值：
Sub数组赋值（）
Dimarr（3）AsString,ItemAsInteger'循环数组四个元素
ForItem=0To3'逐个赋值，将A1：
A4值赋予每个变量
arr（Item）=Range（"A"&Item+1）
Next
MsgBoxarr
（1）
EndSub
Array数组赋值：
Sub数组赋值1（）
DimarrAsVariant'必须使用变体变量'一次性对数组赋值，横向一维数组
arr=Array（"wise","Rose","鸟","鸭"）
MsgBoxarr
（1）
EndSub
也可以对一维数组进行纵向赋值：
Sub数组赋值2（）
DimarrAsVariant'必须使用变体变量'一次性对数组赋值，纵向一维数组
arr=WorksheetFunction.Transpose（Array（"wise","Rose","鸟","鸭"））
[D1:
D4]=arr
EndSub
区域赋值：
Sub区域赋值（）
Dimarr
arr=[A1:
A6]
MsgBoxarr（4,1）
EndSub
五、静态数组和动态数组
静态数组在执行期间不可以改变其上界（最后一个元素的索引号
），而动态数组可以随时修改其上界。
如：
dimarr（10）aslong dimarr（1to100）等，这些都是静态数组。
而对于动态数组，需要dim语句配合Redim语句或者RedimPreserve语句来实现。
Redim语句或者RedimPreserve语句的作用是为了动态数组变量重新分配内存空间，包括指定的维数及声明其上界。
但Redim语句重置数组大小，会使数组中的值丢失；而RedimPreserve语句重置数组的大小时可以保留原数组中的值。
可以使用Redim语句反复地改变数组的元素及维数的数目，但是不能将一个数组定义为某种数据类型后，再使用Redim将该数组改成为其他数据类型，除非是variant所包含的数组。
具体看以下数组：
Suba（）
Dimarr1（）,arr2（）
arr1=[A1:
D11].Value
arr2=[A1:
D11].Value
ReDimarr1（1To2,1To3）'重置数组大小为2行3列的二维数组
ReDimPreservearr2（1To11,1To3）'重置数组大小为11行3列的二维数组
MsgBoxarr1（2,3）
MsgBoxarr2（2,3）
EndSub
六、内置数组函数之Array函数
Array函数用于创建一个包含数组的Variant。
它只能创建一维横向数组。
如下例：
Subaa（）
DimarrAsVariant
arr=Array（"wise","Rose","susu","ting"）
MsgBoxarr
（1）
EndSub
Array方式创建数组，默认状态下下界为0，随着optionbase语句的设置而变化。
Subbb（）
MsgBoxArray（"wise","Rose","susu","ting"）
（1）
EndSub
如该程序，也是和aa模块的效果一样。
另外，Array可以一次完成赋值，如：
Subcc（）
[D1:
G1]=Array（"wise","Rose","susu","ting"）
EndSub
Array的参数个数可以就是数组的上界，数组上界的大小受计算机的可用内存限制，内存越大，它支持的上界就越大。
Array的参数各元素的值可以不互相干扰，它可以是任意数组数据。
如下例子：
Subdd（）
arr=Array（"wise",Date,123,Format（today,"yyyy"）,13）
Fori=0ToUBound（arr）
Cells（i+1,1）=arr（i）
Nexti
EndSub
注意：
Array只能对Variant型变量赋值，且声明该变量时不能包含括号
七、内置数组函数之ISArray函数
Isarray函数可以返回Boolean值，指出其参数是否为一个数组。
具体请看以下例子：
例子1：
Suba1（）
IfIsArray（Array（"wise","Rose",12））Then
MsgBox"这是一个数组"
Else
MsgBox"这不是一个数组"
EndIf
EndSub
例子2：
Suba2（）
IfIsArray（[A1:
A10].Value）Then
MsgBox"这是一个数组"
Else
MsgBox"这不是一个数组"
EndIf
EndSub
例子3：
SubA3（）
IfIsArray（Range（"A1"）.Value）Then
MsgBox"这是一个数组"
Else
MsgBox"这不是一个数组"
EndIf
EndSub 对比以上三个例子，就可以理解Isarray函数的作用。
一、数组的分类
按元素数目分：
元素数目大小固定的数组和元素数目大小不固定的动态数组。
按维数分：
一维数组、多维数组。
Arr（1to12）、Arr1（0to24）----一维固定数组；
Arr2（1to5,1to8）----二维固定数组；
Arr3（5to10,6to12,1to100）----三维固定数组。
动态数组
DimArr2（）,r%
r=r+1
ReDimPreserveArr2（1Tor）―――动态数组；可以重新声明（只有最后一维的数目才能重新声明）；
用了关键字Preserve可确保原来包含数据的数组中的任何数据都不会丢失
二、数组的赋值
2.1，单元格区域保存到数组
arr=[e22:
i24]
arr=Range（“e22:
i24”）
2.2，Array函数
myArray=Array（"AAA","BBB",200,500,"2006-7-12"）
如果代码头没有OptionBase1的语句，则数组myArray的上限为4，下限为0。
即下限LBound（myArr）=0，上限UBound（myArr）=4
二维数组的第一维的上限：
UBound（Arr,1）
二维数组的第二维的上限：
UBound（Arr,2）
多维数组上限的求法一样。
2.3，把单元格区域公式赋给数组
如果a5=B4+1
arr=[a4:
c8].Formula'将单元格绝对引用公式保存到数组
[e4:
g8]=arr此时e5中的公式也＝B4+1;
如果将单元格相对引用公式保存到数组
arr=[a4:
c8].FormulaR1C1
如果a5=B4+1
[e4:
g8]=arr此时e5中的公式就＝E4+1;
三、数组的处理
3.1，数组里的最大值和最小值
最大值aa=Application.WorksheetFunction.Max（Arr）
aa=Application.WorksheetFunction.Large（Arr,1）
最小值aa=Application.WorksheetFunction.Min（Arr）
aa=Application.WorksheetFunction.Small（Arr,1）
3.2，数组里搜索
Temp=Filter（Arr,xm（i））'搜索数组
Subyy（）
DimArr（）,aa$,x%
aa="asssfffssssaaasss":
bb="s"
Forx=1ToLen（aa）
ReDimPreserveArr（1Tox）
Arr（x）=Mid（aa,x,1）
Nextx
temp=Filter（Arr,bb）
cc=UBound（temp）+1‘cc=”s”的个数
EndSub
用于对字符串数组进行搜索，得到一个新的数组temp，
缺点：
只告诉你某元素是否存在于数组中，而不知道其具体位置；
数组精确搜索：
SubFilterExactMatch（）
'该函数在一个字符串数组中搜索那些
'与搜索字符串完全匹配的元素。
DimastrFilter（）AsString
DimastrTemp（）AsString
DimlngUpperAsLong
DimlngLowerAsLong
DimlngIndexAsLong
DimlngCountAsLong
astrItems=Array（"a","sas","s","Sas","s","f","f","f","f","sas","s","sas","a","a","Sas","s","s"）
strSearch="Sas"
'为搜索字符串而过滤数组。
astrFilter=Filter（astrItems,strSearch）
'存储结果数组的上限和下限。
lngUpper=UBound（astrFilter）
lngLower=LBound（astrFilter）
'将临时数组调整到相同大小。
ReDimastrTemp（lngLowerTolngUpper）
'在经过滤的数组的每个元素中循环。
ForlngIndex=lngLowerTolngUpper
'检查该元素是否与搜索字符串完全匹配。
IfastrFilter（lngIndex）=strSearchThen
'在另一个数组中存储完全匹配的元素。
astrTemp（lngCount）=strSearch
lngCount=lngCount+1
EndIf
NextlngIndex
'重新调整包含完全匹配的元素的数组的大小。
ReDimPreserveastrTemp（lngLowerTolngCount-1）
'返回包含完全匹配的元素的数组。
[a5].Resize（1,UBound（astrTemp）+1）=Application.Transpose（astrTemp）
EndSub
3.3，转置
取工作表区域的转置到数组：
arr=Application.Transpose（[a1:
c5]）‘此时arr是转置成3行5列的数组，arr（1to3,1to5）
[e1:
i3]=arr‘此时3行5列。
数组间也可以转置：
arr1=Application.Transpose（arr）
取数组arr的第n列赋值到某列区域：
[e1:
e5]=Application.Index（arr,0,n）
也可写成[e1:
e5]=Application.Index（arr,,n）
赋值产生一个新数组：
arr1=Application.Index（arr,0,n）
取数组arr的第n行赋值到某行区域：
[a6:
c6]=Application.Index（arr,n,0）
也可写成[a6:
c6]=Application.Index（arr,n）省略0，也省略了“，“
赋值产生一个新数组：
arr1=Application.Index（arr,n）
3.4，数组的比较（字典法）
题目：
将A列中的数据与C列相比较,输出C列中没有的数据到D列：
Subcc（）
‘by:
ccwan
Dimarr,brr,i&,x&,dAsObject
arr=Range（"a1:
a"&[a65536].End（xlUp）.Row）
brr=Range（"c1:
c"&[c65536].End（xlUp）.Row）
Setd=CreateObject（"scripting.dictionary"）
Fori=1ToUBound（arr）
d（arr（i,1））=""
Next
Forx=1ToUBound（brr）
Ifd.exists（brr（x,1））Then
d.Removebrr（x,1）
EndIf
Next
[d1].Resize（d.Count,1）=Application.Transpose（d.keys）
EndSub
3.5，数组的排序
字符串数组不能用Large（Arr,i）或者Small（Arr,i）来排序；
但数值数组可以；
一个很好的字典+数组排序的实例：
Subyy1（）
‘by:
oobird
Dimi%,cAsRange,x,dAsObject
Setd=CreateObject（"Scripting.Dictionary"）
ForEachcInSheet2.UsedRange
Ifc.Value<>""Then
IfNotd.exists（c.Value）Then
d.Addc.Value,1
Else
d（c.Value）=d（c.Value）+1
EndIf
EndIf
Next
k=d.keys:
t=d.items'k是各个不重复值，t是各个不重复值的个数
ReDimx（1To2,1Tod.Count）
Fori=1Tod.Count
x（2,i）=Application.Large（k,i）‘从大到小排序
x（1,i）=d（x（2,i））
Nexti
WithSheet1
.[b2].Resize（2,i-1）=x
ReDimx（1To2,1Tod.Count）
Fori=1Tod.Count
x（1,i）=Application.Max（t）‘从大到小排序
w=Application.Match（x（1,i）,t,0）–1‘查找此值在不重复值系列中的排位，因为w是从0开始的，所以－1
x（2,i）=k（w）‘求得对应的不重复值
t（w）=""‘使前面的最大值为空，继续循环
Nexti
.[b5].Resize（2,i-1）=x‘两行一起赋值给B5开始的单元格
EndWith
EndSub
字符串数组的排序，可以使用辅助列，把数组各元素依次赋给单元格，然后对这些单元格运用Excel自有的数据排序功能进行排序，再把单元格排过序的值重新赋给数组。
3.6，数组赋给单元格区域
r=Ubound（Arr）r为一维数组的上限;
Range（"a2"）.Resize（1,r）=Arr'填充到工作表的一行之中（Arr为一维数组）
或者写成Range（"a2"）.Resize（1,Ubound（Arr））=Arr
二维数组Arr（100,5）
Range（“a1”）.Resize（100,5）=Arr
[a1:
e100]=Arr
或者写成Range（"a1"）.Resize（Ubound（Arr,1）,Ubound（Arr,2））=Arr
赋值方面的补充：
Suby（）
Dimarr
arr=[mmult（row（1:
100）,column（a:
f））]
[a1].Resize（100,6）=arr
EndSub
Subyy（）
Dimarr
arr=[column（a:
z）^3]
MsgBoxJoin（arr,","）
arr=[transpose（row（1:
222））]
MsgBoxJoin（arr,","）
EndSub
Subyyy（）
Dimarr
arr=Split（"abcdefg"）
MsgBoxJoin（arr,","）
EndSub
--------------------------------------------------------------------------------
红绳子=Array（"辣1","辣2","辣3","辣4","辣5","辣6","辣7","辣8","辣9","辣10"）
cName=Array（"林思明","曾玉婷","曾国文","林伟权","林兴发",
升级会员 