宏运用入门及数组运用

《宏运用入门及数组运用》由会员上传分享，免费在线阅读，更多相关内容在教育资源-天天文库。

1、宏命令的使用方法1．1按图1步骤操作可添加控件及编写相关代码，图2为代码编写窗口图1在工作表中添加控件图2编写控件代码1．2按图3所示步骤自定义函数后就可以像使用Excel函数一样运用它了，如图4 图3自定义函数图4自定义函数的使用1．3按图5所示步骤编写自己的宏命令，例如在代码窗口输入如下代码：Subsheetcount()DimnumAsIntegernum=ThisWorkbook.Sheets.CountRange("a1")=numEndSub此函数功能是求取工作薄中所有工作表的个数，按图6方法运行该宏命令，也可以按图7所示步骤执行宏命令 图5编写宏命令图6运行宏命令图7在Excel中调用宏命令 2、用Array创建数组按Alt+F11打开VBE窗口，插入一新的模块，在代码窗口中写入代码：Subtest()红绳子=Array("辣1","辣2","辣3","辣4","辣5","辣6","辣7","辣8","辣9","辣10")EndSub按F8，逐行运行代码，程序成功创建了一个数组，数组名称是“红绳子”，"辣1","辣2","辣3","辣4","辣5","辣6","辣7","辣8","辣9","辣10"是数组元素。用Array()创建数组，各元素之间用逗号隔开。元素如果是文本，要用半角引号作定界符。练习，创建两个数组：⑴数组名为cName，数据为8个同学的姓名：林思明、曾玉婷、曾国文、林伟权、林兴发、刘卓怀、曾仙婷、林平⑵数组名为cj，数据为8个数值：99,85,92.5,70,78.5,65,84,100参考答案：数组入门01.xls点击浏览该文件★推荐：ExcelHome技术论坛的最佳学习方法★山菊花--------------------------------------------------------------------------------3、我们已经初步认识了数组，数组是把一组数保存到一个数组变量中，它有两个重要特征。⑴它是一个变量，对一组数进行统一管理带来方便。上面建立的姓名数组，我们要把它写入到工作表区域A1向右连续8个单元格中，可用命令：Range(“a1:h1”)=cname我们认识的这类数组，称为一维数组，一维数组是一个水平数组，相当于工作表中的行。如果要把姓名按垂直方向填充到单元格，可转置命令。如，把这个数组写入到A1:A8单元格中：Range(“a1:a8”)=WorksheetFunction.Transpose(cname)练习：把数组cname分别写入到单元格C4:J4、D8:D15。答案：数组入门02.xls点击浏览该文件⑵第二个重要的特征，是数组元素的有序性，一组数据有序地保存到数组变量中。数组中，用数组名加序号(称索引值)构成唯一的名称对元素进行引用。元素与序号一一对应，根据序号，可以修改和读取指定位置上的元素。把一串姓名保存到数组cName中，数据在数组中按顺序保存，默认从0开始，依次是1、2、3……，用cName(0)表示第一个姓名、cName(1)表示第二个姓名，等等。在工作表中插入一个按钮，点击按钮，把姓名填入单元格A1:A8中：PrivateSubCommandButton1_Click()cname=Array("林思明","曾玉婷","曾国文","林伟权","林兴发","刘卓怀","曾仙婷","林平")Fori=1To8Range("a"&i)=cname(i-1)NextEndSub数组序号(索引值)默认从0开始，也可以通过命令OPTIONBASE1修改为从1开始。该命令写在模块的前面。如果设定索引值从1开始，则上面的代码修改为：PrivateSubCommandButton1_Click()cname=Array("林思明","曾玉婷","曾国文","林伟权","林兴发","刘卓怀","曾仙婷", "林平")Fori=1To8Range("a"&i)=cname(i)‘工作表行号与数组序号相吻合NextEndSub参考：数组入门03.xls点击浏览该文件--------------------------------------------------------------------------------4、想起瑞士军刀Array()很好用，但有时Split()更好用，我们取出这瑞士军刀的第二利器。Split()把一个文本，根据指定的分隔符，建立一个数组。看一个实例：单元格A1中有一串姓名：朱清燕(女),林鑫,林秋静(女),林永鑫,林云艳(女),林嘉惠(女),曾文婷(女),林悦,陈诚,林伟健,赖紫岚(女),曾雁(女),赖文强,钟娟(女),张琪(女),林文彬,黄晓婷(女),李朕,林依婷(女),林佳利(女),曾德福要求：用Split()，把这串文本创建为一个数组：Subtest()XM=Split(Range("a1"),",")EndSub同样可以用本地窗口检查转换结果。注意，Split()函数把文本转换成数组，并不受OptionBase的影响，索引号总是从0开始。很自然想知道，A1单元格中有多少个姓名？创建的数组有多少个元素？瑞士军刀中有把小尺子，可以量一量：Ubound()——返回数组的最大下标。Subtest()xm=Split(Range("a1"),",")MsgBox"数组最大下标是"&UBound(xm)&Chr(13)_&"数组共有"&UBound(xm)+1&"个元素"EndSub参考：数组入门04.xls点击浏览该文件5、三维表示一个空间，四维、五维、六维呢？三维已经可以代表宽广无垠的宇宙，而数组却允许声明为4维、5维、6维，直至60维。这是一个什么样的概念？我曾经打过一个这样的比方：6、由工作表区域创建数组。与Array()和Split()相比，由工作表区域创建数组更加直观而简易，一个等号便解决问题：如：用Sheet2工作表A1:E83的数据创建一个数组：PrivateSubCommandButton1_Click()arr=Sheet2.Range("a1:e83")EndSub如果把该数据复制到Sheet1工作表相同区域，用数组操作，代码如下：PrivateSubCommandButton1_Click()arr=Sheet2.Range("a1:e83")Range("a1:e83")=arr EndSub共两句，前一句把数据保存到数组，第二句，把数组内容写入到工作表。选择第3行代码，按F9设置断点。点击按钮运行代码，到该行，程序自动中断，此时，可从本地窗口中观察到运行结果，一个83行2列的数组已经创建。也可以按F8逐行运行代码，查看数组创建的结果。参考：数组入门05.xls点击浏览该文件7、随心所欲创建数组以上几种办法都是根据已有的数据创建数组。实际使用中，更多时候，程序需要先创建数组，然后对数组进行赋值和修改。Private、Public、Dim、Static，这些语句都可声明数组变量，区别在于声明的数组的使用范围，可查看各语句的帮助。新建一个过程：Sub声明数组()DimMyArray(10)'在过程中间声明一个数组，过程结束，本数组将被释放。MyArray(1)=100'给数组赋值MyArray(5)=90EndSub按F8逐行运行程序，在本地窗口中可以看到数组MyArray()已成功创建。Dim是声明变量的语句；MyArray是数组名称；10是数组最大下标。当OptionBase0的模式下，数组的序号(索引值)从0到10，表示数组有11个元素，当OptionBase1的模式下，数组的索引值从1到10，该语句创建的数组共有10个元素。上面声明的数组是一维数组，数组最大可以定义60维。多维数组的表示方法，维与维之间用逗号相隔。Sub声明数组b()DimData(10,5)’声明一个二维数组Data(1,1)=“A001”'给数组赋值Data(1,2)=90Data(3,1)=“A003”Data(3,2)=80EndSub这里，Data是一个二维数组。二维数组的两维，我们通常用“行、列”称呼它，如Data，最大行号是10，最大列号是5，在OptionBase1模式下，表示是一个10行5列共50个元素的数组空间。在OptionBase0模式下，表示一个11行6列共66个元素的数组空间。二维数组是使用频率最高的数组形式，因为我们的工作表也是二维的。用工作表来领会二维数组，将会更加直观：Data(1,1)相当于Cells(1,1)，表示第1行，第1列；Data(5,3)相当于Cells(5,3)，表示第5行，第3列。下面的语句，声明的是一个三维数组：Sub声明数组b()Dimx(3,30,3)X(1,1,1)=“1班”'给数组赋值X(1,1,2)=“张三” X(1,1,3)=88X(1,2,1)=“1班”'给数组赋值X(1,2,2)=“李四”X(1,2,3)=95X(2,1,1)=“2班”'给数组赋值X(2,1,2)=“刘日”X(2,1,3)=92EndSub此主题相关图片如下：我们可以用工作簿的概念来迁移领会三维数组：X(1,2,3)：第1个工作表第2行第3列；X(2,14,10)：第2个工作表第14行第10列。假如我们要测量数组各维的最大下标，可以在UBound()函数使用第二个参数：UBound(x,1)，返回数组x第1维的最大下标；UBound(x,2)，返回数组x第2维的最大下标；UBound(x,3)，返回数组x第3维的最大下标。参考：数组入门06.xls点击浏览该文件8、进阶操作声明数组⑴声明数组还可以根据需要自己指定下标和上标，而且不受OptionBase的影响：Sub声明数组()DimcArr(11To20)‘声明一个数组cArr，下标从11到20，共有10个元素。cArr(11)="上"cArr(12)="中"cArr(13)="下"EndSub数组的索引值还允许为负整数：Sub声明数组()DimnArr(-10To10)‘声明一个数组nArr，下标从-10到10，共21个元素。nArr(-10)=123nArr(-9)=456nArr(-2)=789EndSub可变的下标给你的程序带来灵活性，推荐另一个函数：UBound()函数的姐妹花LBound()。LBound()可以返回指定数组维可用的最小下标。如：xb=LBound(nArr)MsgBoxxb⑵声明数组时，还允许像声明单值变量一样为数组指定为数据类型。Sub声明数组()DimcArr(11To20)AsStringcArr(11)="上"cArr(12)="中"cArr(13)="下"EndSubAsString 表示数组cArr()是一个字符型变量，在本地窗口中可以看到，没有赋值的元素默认为空文本。Sub声明数组()DimnArr(-10To10)AsIntegernArr(-10)=123nArr(-9)=456nArr(-2)=789EndSubAsInteger表示数组nArr()是一个整型变量，在本地窗口中可以看到，没有赋值的元素默认为0。没有指定类型的变量，默认为Variant，没有赋值的元素默认为“空值”。此主题相关图片如下：参考：数组入门07.xls点击浏览该文件⑶变量的级别问题以上都是在过程内部声明变量，当过程结果时，变量自动被释放。在模块级别中声明变量，对该模块中的所有过程都是可用的。参考：数组入门08.xls点击浏览该文件如果要声明级别更高的变量，可使用Public语句。详见帮助。9　、动态数组以上声明的数组都属于“静态数组”，每个数组有确定的维数，每维有确定的最大下标。与之相对应的是动态数组。程序中，数组的大小有时不是固定的，而是随数据的变化而变化。声明数组的时候，不指定数组的维数与下标，这样的数组就是动态数组，在程序中可用命令Redim为动态数组变量重新分配存储空间。例：单元格A1中有一串姓名：朱清燕(女),林鑫,林秋静(女),林永鑫,林云艳(女),林嘉惠(女),曾文婷(女),林悦,陈诚,林伟健,赖紫岚(女),曾雁(女),赖文强,钟娟(女),张琪(女),林文彬,黄晓婷(女),李朕,林依婷(女),林佳利(女),曾德福要求：把名单整理成两列，第一列是姓名，第二列是性别，把它输出到B:C两列。与例4相同，用Split()，把这串文本创建为一个数组，然后根据这个数组的大小，创建另一个数组：ReDimArr(1Tos,1To2)其中s是数组xm()的最大下标，xm()有多少行，Arr()就有多少行，列数为2，一列是姓名，另一列是性别。参考：数组入门09.xls点击浏览该文件允许使用ReDim语句反复地改变数组的元素以及维数的数目，请自己练习。10、对同一数组的反复定义对同一数组反复改变大小的时候，也有规则必须遵守。如果数组中存在数据，再次定义的时候默认把原数据清除，重新定义后的数组是一个空的数组。如果你要保留原来的数据，必须加上参数Preserve。使用Preserve参数又有一个限制，只能改变最后一维的大小，比如二维数组，只能改变第2维(列)的大小而不能改第1维(行)的大小。还是使用上个示例，要求把A1单元格中的女同学的姓名整理成两列，第一列序号，第二列是姓名。 使用数组Arr()记录整理结果，我们在遍历原始数组xm()的时候，根据性别的判断即时改变Arr()的大小。但不能把数组Arr()定义为n行2列，第1列保存序号，第2列保存姓名，因为我们必须使用Preserve参数保留原来的数据，此时数组Arr(n,2)的第一维是不允许改变的。为了解决为个矛盾，我们可以把数组定义为2行n列，第一行记录序号，第二行记录姓名，列数在程序过程中反复改变。n=n+1ReDimPreserveArr(1To2,1Ton)'改变动态数组的大小，共2行，n列数组第1行记录序号，第2行记录姓名：Arr(1,n)=nArr(2,n)=Left(Xm(i),Len(Xm(i))-3)对数组xm()遍历结束后，把结果写入工作表，因为数组的方向与结果要求的方向不一致，所以要加一个转置函数：Range("b6").Resize(n,2)=WorksheetFunction.Transpose(Arr)参考：数组入门10.xls点击浏览该文件11、搜索数组园子里是不是有红苹果？你手中的牌是不是有红桃K？我们用眼睛一瞄就知道。数组元素中是不是有“张三”，是不是有“李四”，我们如何知道？不用担心，Filter()函数刚好可以帮你忙。Filter()函数在一维数组中搜索文本型数据，并把结果保存到指定的数组中。A1单元格是以逗号分隔的姓名串，部分有重复，要求剔除其中重复的姓名，把不重复的姓名写入单元格A2向右的区域。第一步，把所有姓名保存到数组xm()中；第二步，建立一个数组Arr()保存结果，遍历xm()过程中，检查Arr()中是否存在当前的姓名，用命令：Temp=Filter(Arr,xm(i))'在数组Arr()中搜索当前值xm(i)，如果找到，结果保存到Temp()数组中，如果找不到，返回一个空的数组Temp()根据Temp()数组的大小，可以知道当前值xm(i)是不是一个重复的值。参考：数组入门11.xls点击浏览该文件12、连接数组元素有一个函数，是Split()的反向操作函数，把数组的元素连接成一个字符串，这个函数就是Join()。在上例中，把要求改为，剔除重复值后，把结果依原样输出到A2单元格中。我们只需改变最后一行代码：把这一行：'Range("a2").Resize(1,r)=Arr'填充到工作表换成这一行，用上Join()函数，把Arr()数组中的元素用“,”连接成一个字符串：Range("a2")=Join(Arr,",")13、清除数组当数组不需要使用时，可用Erase语句清除数组元素，释放变量占用的空间。语句：Erasexm,Arr每条Erase语句，可清除一个数组，也可同时清除多个数组，数组名之间用逗号分隔。补充一些大家不太了解的数组用法，对于数组感兴趣的朋友在您掌握了一些基本的取值、赋值、循环等技巧之后，不妨在了解一些也许你们不太了解的数组用法，这些都会是很有用的数组编程技巧1，取工作表区域的绝对引用公式到数组：arr=[a1:c5].Formula 2，取工作表区域的相对引用公式到数组：arr=[a1:c5].Formular1c13，取工作表区域的转置到数组：arr=Application.Transpose([a1:c5])数组间也可以转置：arr1=Application.Transpose(arr)4，取数组arr的第n列赋值到某列区域：[e1:e5]=Application.Index(arr,,n)或者赋值产生一个新数组：arr1=Application.Index(arr,,n)5，同理，取数组arr的第n行赋值到某行区域：[a6:c6]=Application.Index(arr,n,)或者赋值产生一个新数组：arr1=Application.Index(arr,n,)