关于EXCEL中VBA的数组理解

关于EXCEL中VBA的数组理解一、什么是VBA数组不要把VBA数组想的太神秘，它其实就是一组数字而已。把一组数按行、列排在一起，放在内存中。就是一个VBA数组。二、数组的维数数组的维数其实和文件管理的目录一样的，只有一层时就是一维。如果有两层则是二维数组（有行有列为二维），如果有三层，则是三维数组。。VBA中支持1~3维数组运算。Sub数组示例()DimxAsLong,yAsLongDimarr(1To10,1To3)'创建一个可以容下10行3列的数组空间Forx=1To4Fory=1To3arr(x,y)=Cells(x,y)'通过循环把单元格区域a1:c4的数据装进数组中NextyNextxMsgBoxarr(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的值装入数组arrarr1=Range(a1:c7)'把单元格区域A1：C7的值装入数组arr1MsgBoxarr(1,1)'读取arr数组中第1行第1列的数值MsgBoxarr1(2,3)'读取arr1数组的第2行第3列的数值EndSub四、把内存数据搬入单元格Subtest()Dimarr'声明一个变量用来盛放单元格数据DimxAsIntegerarr=Range(a2:d5)'把单元格数据搬入到arr里，它有4列4行Forx=1To4'通过循环在arr数组中循环arr(x,4)=arr(x,3)*arr(x,2)'数组的第4列(金额)=第3列*第2例NextxRange(a2:d5)=arr'把数组放回到单元格中EndSubSubtest1()Dimarr(1To5)'声明一维数组Forx=1To5arr(x)=x*2'通过循环给每个位置赋值NextxRange(A1:E1)=arr'把数组导入到excel中的a1:e1单元格中Range(A1:A5)=Application.Transpose(arr)'如果是放在一列中,就需要对数组进行转置后再存放EndSub五、动态数组的声明Subdarr()Dimarr()'声明一个动态的arr数组(不知道它能盛多少数据)Dimkk=Application.WorksheetFunction.CountIf(Range(a2:a6),10)'计算大于10的个数ReDimarr(1Tok)'再次声明arr的大小,正好盛下k数量的值Forx=2To6IfCells(x,1)10Thenm=m+1arr(m)=Cells(x,1)'通过循环把大于10的数字装入数组EndIfNextxMsgBoxarr(2)EndSub六、数组的上标(lBOUND)和下标(UBOUND)arr(-19to8)这个数组的编号就是从-19开始的.那么它的最小编号就是-19,最大编号是8,如果用语句返回就是:Subt1()Dimarr(-19To8)MsgBoxUBound(arr)'返回最大编号,结果为8MsgBoxLBound(arr)'返回最小编号,结果为-19EndSub如果是有行列组成的二维数组呢?二维数组返回行的下标和列的下标见下例Subt2()Dimarr(-19To8,2To5)MsgBoxUBound(arr)'返回第1维(行的)最大编号,结果为8MsgBoxLBound(arr)'返回第1维(行的)小编号,结果为-19MsgBoxUBound(arr,2)'返回第2维(列的)最大编号,结果为5MsgBoxLBound(arr,2)'返回第2维(列的)最小编号,结果为2EndSubSubt3()Dimarrarr=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),Array(c,30))也可以调用excel工作表内存数组：1维数量：[{A,1,C}]2维数量：[{a,10;b,20;c,30}]内存常量数组有什么作用呢？1、简化赋值比如：我需要给数组arr分别赋值10,20,30,40，一般就需要分别赋值，即：arr(1)=10arr(2)=20arr(3)=30arr(4)=40而使用常量数量，只一句话：arr=array(10,20,30,40)2、调用工作表函数时使用：Submylook()Dimarrarr=[{a,10;b,20;c,30}]MsgBoxApplication.VLookup(b,arr,2,0)'调用vlookup时可以作为第二个参数EndSub八、数组的合并和字符串拆分（Join&Split）：多个字符的合并和字符串按规律的拆分是经常遇到的，如：A-REW-E-RWC-2-RWC按分隔符-拆分成6个字符放在一个数组中有一组数array(23,45,7,1,76)想用分隔符-连接成一个字符串上面两种情况VBA提供了一对函数，即：split（字符串,分隔符）拆分字符串join(数组,分隔符)用分隔连接数组的每个元成一个字符串Subt1()Dimarr,mystAsStringmyst=A-REW-E-RWC-2-RWCarr=Split(myst,-)'按-分隔成一组数装入数组中'MsgBoxarr(0)'显示数组的第一个数(分隔后的数组最小下标为0,不是1),显示结果为AMsgBoxJoin(arr,,)'再用,把数组的每个值连接成一个字符串,结果为A,REW,E,RWC,2,RWCEndSub值得注意的是：split和join只能对一维数组进行操作，如果是单元格或二维数组怎么办？只有一条途径，想办法转换为一维数组：Subt2()DimARRARR=Application.Transpose(Range(a1:a3))‘用转置的方法，把单元格一列数据转换成一维数组MsgBoxJoin(ARR,-)EndSub九、Filter函数实现数组筛选:数组的筛选就是根据一定的条件,从数组中筛选符合条件的值,组成一个新的数组，实现数组筛选的VBA函数是：Filter函数用法：Filter(数组,筛选的字符,是否包含)SubDD()arr=Array(ABC,A,D,CA,ER)arr1=VBA.Filter(arr,A,True)'筛选所有含A的数值组成一个新数组arr2=VBA.Filter(arr,A,False)'筛选所有不含A的数值组成一个新数组MsgBoxJoin(arr2,,)'查看筛选的结果EndSub遗憾的是函数只能进行模糊筛选，不能精确匹配。他山之石，可以攻玉，VBA中除可以利用的VBA函数外，还可以调用众多的Excel工作表函数对数组进行分解、查询和分析等，调用工作表函数可以省去循环判断的麻烦，进而提高运行效率。一、数组的最值1、Max和Min工作表函数Max和Min是求最大值和最小值的函数，同样在VBA中也可以求数组的最大值和最小值。如：Subt()arr=Array(1,35,4,13)MsgBoxApplication.Max(arr)'最大值MsgBoxApplication.Min(arr)'最小值EndSub2、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、SumSum函数可以在工作表中求，同样也可以对VBA数组求和，如：Subt2()arr=Array(1,35,4,13)MsgBoxApplication.Sum(arr)'对数组进行求和EndSub2、Count和CountaCount和Counta可以统计数组中数字的个数和数字+文本的个数。Subt3()arr=Array(1,35,a,4,13,b)MsgBoxApplication.Count(arr)'返回数字的个数4MsgBoxApplication.CountA(arr)‘返回数组文本和数字的总个数EndSub三、数组的查询和拆分1、Mach查询数组Match函数可以查询一个指定值在一组数中的位置，它也可以用于VBA数组的查询。如：Subt4()arr=Array(1,35,4,13)MsgBoxApplication.Match(4,arr,0)'查询数值4在数组Arr中的位置EndSub2、Index拆分数组数组的拆分在VBA中是一个难题，如果是按行拆分数组，除了用循环外也只能借用API函数完成了。幸好我们可以借用工作表函数index达到按列拆分数组，即多列构成的数组，你可以任意拆分出一列构成新的数组。方法是：Application.Index(数组,,列数)，例：Subt2()arr2=Range(A1:B4)‘把单元格区域A1：B4的值装入数组arr2arr3=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列多行的二维数组EndSub2、二维数组转一维。Subt2()arr2=Range(A1:B4)arr3=Application.transpose(Application.Index(arr2,,2))'取得arr2第2列数据并转置成1维数组MsgBoxarr3(2,)EndSub注：在转置时只有1列N行的数组才能直接转置成一维数组思考题：我要把a1:c1中的内容用“-”连接起来，下面代码中为什么用了两次transposeSubt10()arr=Range(A1:C1)MsgBoxJoin(Application.Transpose(Application.Transpose(arr)),-)EndSub用于VBA数组的工作表函数我只是列出了一部分，其实象vlookup，Lookup等等函数也可以用于处理VBA数组，大家有空了就去尝试下吧。