Excel VBA教程

Excel VBA教程 Excel VBA教程之一:写在前面 VBA功能如何强大，这已勿庸费言，但如何才能很快进阶，写出自己满意的程序，却取决于个人努力和悟性。我下了很大功夫却收效甚微，有时，高手一句点拨却胜读三天书。所以，我把个人学习所得作一点整理，希望有助于后学。 只所以会有看书没用之感，一来是因为市面(我国)上大多数计算机类图书多为粗制滥造之作(这话稍有唐突)，二来因为计算机这东西太呆板，不见得新出现的问 题 快递公司问题件快递公司问题件货款处理关于圆的周长面积重点题型关于解方程组的题及答案关于南海问题 就能立即得到某本书籍上的解释，新问题总是层出不穷的。 想把一门编程语言全部搞懂，再去写点程序，是一个十分愚蠢的想法。你想盖房子，难道你想先学会制造砖头、玻璃等,我承认，把语言弄懂再做事情会很顺手，但是，你知道吗，有多少人在弄懂一门晦涩难懂的语言之前就放弃了呢,比如，什么叫面向对象编程,OOP和以前的流线性编程有什么异同,我还是大力提倡学以致用，别指望什么事你都能成专家，计算机世界的“罗氏定理”就是:“毕其一生，不能穷其万一”，你记住了吗, 那么，对于没有编程基础的人，怎样才能进步得快呢,我有几点思考: 1.你一定要带着明确的目的去做一件具体的事。这样，才会在做的过程中发现问题，并逐步去解决这些问题。比如，我想做一个《学 生成绩分析系统》，我就会想到怎么才能把学生分数分成不同的班、怎么才能算出总分、平均分、 标准 excel标准偏差excel标准偏差函数exl标准差函数国标检验抽样标准表免费下载红头文件格式标准下载 差等、怎样比较各班的情况，怎样用图形显示更直观，怎么样才能把某个学生的历次成绩放在一起比较等。 2.你动手去做一做，一点一点地完成你上面的设想，不必因为这个大工程没做出来而忽略你在局部的突破，这样，你会越来越有信心，这就够了，还有什么比有信心做事的感觉更好的了吗, 3.合理及时利用网络资源，这绝对是个好东西，或许在你看起来很了不起的问题，在别人看来简直不算什么，一定会有人为你解答。上网并不只是QQ和game啊～ 同时要好好利用软件的帮助系统，这才是最权威的解释，很多计算机书籍抄自帮助，你想，微软的专家写的，绝对原创，他还能去抄谁, 4.做了一些工作之后，再回过头来看书，就会感觉很亲切，觉得写书的人原来也不是一无是处，也是很辛苦的(不要过分自我批评了)。不过，要看书，计算机类的，我百分之一万地推荐你看外国人写的，首先，软件是人家编的，当然更了解。其次，外国人写的，又被介绍到中国来，也是比较优秀的了。三来，我确实作过对比，看老外的书收益最大。语言不通的问题怎么办,金山词霸一下呗。 我为什么要写这个东西,一是因为我想帮助别人，二是因为我想因此促进自己，三是因为我反感某些号称程序员的家伙，似乎编程是一件多么了不起的事，用一些极端人士的话来说:“编程有什么了不起,不就是判断和循环嘛”，说得很有道理，从本质上说，计算机就 是在做判断和循环，不信你就跟着我来看～ Excel VBA教程之二:从一个界面 设计 领导形象设计圆作业设计ao工艺污水处理厂设计附属工程施工组织设计清扫机器人结构设计 开始 [ 2008-10-13 16:16:00 | By: 醉拍春衫 ] 一个好的软件，除了运算速度快、体积小后，更重要的是，它的用户界面是不是很友好、亲切，“友好、亲切”这样的词汇在开始接触计算机语言时，感觉很好笑，但现在不一样了，我感觉这两个词最贴切～界面友好，就是引导用户能很快找到他需要的服务，最能想到他的心思，最体贴他，让他不时地心动，不时地赞叹～界面友好，就是能让用户在不经意间学到好多知识，无形中丰富了他自己，让他有美的享受～界面友好，还在于一目了然的界面却能让他需要的功能呼之即来，挥之即去～如此等等。 那么，我想做一个《学生成绩分析系统》，我该怎么做呢, 以下的界面你满意吗, 这个界面上有你做成绩分析的各项功能，从考试的组织生成考号、到学科组录入各科成绩、到录错后的修改、到统计分析生成各班情况、各学科组情况、人头数、分数线的预测、到打印、存档、调用。最为特色的功能是深度跟踪，它可以自动把一个学生的历次成绩都调出来，进行历史分析，看一看一位学生的成绩为什么会好或坏，这个功能能不让人心动吗,如果你不知道各个按钮的作用， 旁边就是帮助系统，告诉你它们的功能是什么，你应当怎么去做。你还有什么问题呢,如果有，我已经提供了E-mail地址，告诉我啊，可以为你量身定做～要说多余，就是版权信息了，呵呵，我也是劳动，你就尊重一下没什么大不了的啊～ 以上这个界面，可以说十分友好了。 那么，我到哪里去做这个东西呢,要编程吗, 不用编程，打开Excel，按下Alt+F11，就可以了，我还可以右击工作表，选查看代码，还有其它 方法 快递客服问题件处理详细方法山木方法pdf计算方法pdf华与华方法下载八字理论方法下载 ，我的意见是一种就够了。我们的目的是去公园玩，而不是总结有几条路可以到那里，别开小差，快回来～ 打开Excel，按下Alt+F11，你看到了下图: 再点击“插入”菜单，选“用户窗体”，然后得到下图 中间有个工具箱，上面有按钮啊、文本框啊、标签啊等等，你只要把它们拖到到右边那个带有“麻麻点”的窗体中，并调整一下它们的大小和位置，就会做得比我还好了了。精确调整它们，要用到左下角的属性窗口。怎么拖知道吗,不是用绳子捆住，不是用牛拉，而是用鼠标～ 建立了界面之后，你除了长舒一口气外，是不是体会到一点喜悦呢,生活就是这样，找个空偷着笑，不过，还是要干点实事的哟～ 我猜到了，现在你比我还急了，你更想赋于每个按钮一定的功能，希望点它一下，它就能做点事情，你的想法太对了，每一个按钮，当我们给它各写一段程序，让它能实现一定的功能时，我们就把叫做面向这个对象编程，对象，在这里，就是按钮。 希望那些比较“zhuai”的程序员别面带轻蔑，你倒给我说说，如何才能让不懂程序的同志很快理解“面向对象编程”这个意思, 我们下一期见～ Excel VBA教程之三:把程序写在哪 [ 2008-10-14 9:42:00 | By: 醉拍春衫 ] 回顾上一教程我们所做的工作，我们已经找到了制作窗体界面的工具，并能试着做出很酷的窗体以表达我们的思想和我们对这个世界的爱。但这只是一个框架、一个躯壳，还没有灵魂，还不能做任何事情，要想让它做点事情，我们必须让每个按钮去执行一定的功能，而这此功能，就要靠代码来实现，也就是编程。可是，我们把程序写在哪里呢,是单元格中吗, 程序可以写在以下几个地方:代码主要写在模块中，模块可以理解为你专们存放代码的一个和笔记本，你可以在你心爱的本子上写上各个故事，表达你不同的思想情感，如教学反思等。代码也可以写在窗体中、工作表中、工作簿中、类模块中等，它们有区别，在未来，你会明白区别在哪儿的。就象调制饮料，不同的原料配比，口味自然不同，但目前，我们最需要的，是弄点东西解解渴，有功夫再去细细探讨它们的区别吧。 目前，我们学习如何在模块中书写代码。模块在哪儿呢,打开VBE，也就是按Alt+F11后出现的那个东东，左边有个名叫“工程资源管理器”的窗口如下图所示，如果没有，可以从“视图”菜单中调出。这个工程资源管理器中在新建文件时，并没有模块，可以用“插入”菜单插入一个模块，如果高兴，你可以多插入几个模块试试看，当然，数量并不意味着你很富有。 双击你插入的模块，或右击它，选查看代码，将跳出一个象是记事本东西，这就是我们代码的安身之所了。 这里是你可以大展鸿图的地方了，可是，除了报复性地敲入几个字母后，你可能写不出一点程序来，别着急，VBA的语法可能是厚厚的一本书，但跟着我，你用不着去看书就会写出象模象样的程序来，就象一个老练的程序员。 我必须告诉你，没有规矩不成方圆，在计算机世界是通用的，不管你多么地财大气粗或才大志远，电脑要求你必须按规矩办事，否则，它不吃你这一套，哪怕你是一位领导。 这个规矩就是语法，目前，我告诉你，你写在的程序必须位于sub和endsub之间。就象下在的代码一样: Sub建立工作表() Sheets.Add End Sub 我想试试()就是你写的程序的名称，后面必须加括号，然后换一行，写上你要做物事情，试什么呢,Sheets.Add这句的意思是，新增加一个工作表，最后，告诉计算机，你的程序完了，End Sub就是结束的标志。这段程序每运得一次，Excel工作簿中将多出一张工作表。你点击“运行子过程”按钮试试，或按一下F5看看运动结果。怎么样，比插入一张工作表的操作快多了吧,你要想产生100张工作表，那你就按100次吧。 同样的 Sub隐藏工作表() Sheets(“Sheet1”).Visible=0 End Sub 可以把Sheet1隐藏起来 而 Sub显示工作表() Sheets(“Sheet1”).Visible=0 End Sub 可以把Sheet1重新显示出来 总之，你要把代码写到模块中，格式为:要有一个名字，用英文名字更好，并在名字加一半角括号，想做的事写在中间，由Sub开头，由End Sub结尾，每行一句。 现在请回到我的《学生成绩分析系统》中来，那里有一个按钮名叫“统计分析”，你想，统计完数据后，是不是要把结果分到每个班 和每个学科,10个班级要10张表，9个学科要9张表，还可能有其它的需要，所以，这个统计分表按钮一定要有建立工作表的功能，把我上面建立工作表的代码写上去，就能建设很多的工作表，不同之处在于，我是一次叫它建立19张表，而不是一张一张地建，想知道怎么做吗, 请看下一回吧～ Excel VBA教程之四:你要删除谁 前面的学习，让我们初步体验到使用代码比较方便，代码本身也比较简单，这是VBA的优势，我们发现，编程其实并不难，不过，这只是初步，想实现比较复杂的功能，路还长着呢，好在，只要有信心，有兴趣，总是可以成功的。 那么，如果我想插入的不是一张表，而是20张表，需要运行20次上节课所提到的代码吗,其实不需要，我们只要再插入几行代码就可以了。 回顾一下: Sub建立工作表() Sheets.Add End Sub 可以利用工作表的Add属性增加工作表，如果想添加更多，只要设置一个循环就可以了。还记得教程一中的那种话吗,“编程有什么了不起,不就是判断和循环吗”，我们来验证一下。 Sub建立工作表() Dim i As Integer For i = 1 To 5 Sheets.Add Next i End Sub 这段代码一运行，一下子就可以插入5张表，如果你把5改成50，则一下子可以产生50张表，如果是手工插入，你要花多长时间啊～ 其中的for…next…就是表达循环关系的一种方式，还有其它种类，暂且不表。值得提醒的是，循环变化i在使用前，你一定要先和dim声明一下。 你发现，所生成的工作表的名称，是自动加上去的，要是在生成的时候，就给个名字，是不是会更好呢,试试下面的代码吧。 Sub creatsheet1() Dim i As Integer For i = 1 To 10 Sheets.Add ActiveSheet.Name = i Next i End Sub 一下子生成了10张表，表名从1到10，难道用它来表示一班到十班，不是非常好吗,我想到了我们的《成绩分析系统》。如果名字不是一个系列的数字，你可以使用数组来表达，比如知个学科表。 Sub creatsheet() Dim i As Integer Dim sheetName sheetName = Array("语", "数", "英", "物", "化") For i = 0 To 4 Sheets.Add After:=Sheets(Sheets.Count) ActiveSheet.Name = sheetName(i) Next i End Sub 到目前，你的工作簿中已经有很多工作表了，如果你实验的次数足够多，你还会收到一些错误的警告，那是因为你插入的工作表重名了。 如果你要删掉一张工作表，那有点麻烦，因为你必须具体而明确地指明是哪一张工作表，否则，程序不知你对谁的工作不满意了，要删除谁，你得先指明。这完全不象某些的领导，要表扬，就会表扬一大堆，有时简直是某名其妙的张冠李戴，而批评，也是一本糊涂账。 怎么删呢,看下例: Sub deleteSheets() Sheets(“sheet1”).Delete End Sub 如果你想删掉一些工作表，你想手动还是用代码自动化呢,当然是后者，看看下例。 Sub deletesheet3() Dim i As Integer For i = 1 To 5 Sheets(CStr(i)).Delete Next i End Sub 千万注意不要写成Sheets(i)，一定要加上CStr()，我就是吃了这个亏，浪费几个小时，气晕了头才发现的，计算机是多么机械的一个东西，不能越雷池一步，有点象某些死板的领导。 要是删那种不是数据系列的工作表，就得用下面的代码了。 Sub deleteSheets() Dim className className = Array("语", "数", "英", "物", "化") For i = 0 To 4 Sheets(className(i)).Delete Next i End Sub 你插入的那些有名字的工作表，将逐一被删除～每删一张，都会给一句警告，能否让它不出现呢, 再加两句代码，就可以了。 Sub Deletesheet() Dim className className = Array("语", "数", "英", "物", "化") Application.DisplayAlerts = False For i = 0 To 4 Sheets(className(i)).Delete Next i Application.DisplayAlerts = True End Sub 以上代码将用到《成绩分绩系统》统计分析按钮和退出系统按钮中，点一下统计分析，就会产生各班级和各学科分数表，只是暂时还没有数据，会有的，你得有点儿耐心。再点一下退出按钮，将把这些表给删了，会让我们的软件体积更小。 好啦，到这里，你已经可以添加你想要的任意数量的工作表，也可以把它删了，都只是弹指之间的事情，以后，你要是有什么不顺心的事，可以到这里反复添加和删除，相当于到健身房去打拳。好象，人们越来越脆弱了，据说这样可以发泄自己，让自己很快平静下来。常听到两句时髦的话:“要调整好心态”、“你不能改变世界，那你可以改变自己的心情”，我感觉人类真是越来越渺小，越来越脆弱了，这种对命运的逃避、妥协、呻吟和哀叹，真让人看不起，更有许多人，心态其实很差，往往张口避口对别人谈心态，仿佛自己人心态很好似的，不过是个掩耳盗铃的小丑～或许是虚张声势地掩盖自己的自卑而已。还有一种“心态好”，那是一种赤裸裸缺乏基本的羞耻感。扯远了，打住。 本期的作业:你得看一看数组这个东西，还要看一眼变量的声明，看帮助就可以了。 我们下期见～ Excel VBA教程之五:让你见识代码的伟大 [ 2008-12-2 15:35:00 | By: 醉拍春衫 ] 前几节讨论了VBA的林林总总，却并没有涉及到数据处理方面的知识，再有耐心的人也快要火了:“你说VBA在处理数据方面如何强大,你都说了那么多的废话，还没见到强大在哪儿,” 确实，我不懂得心理学，不象许多人，不仅懂，还能做别人的思 想工作，如:劝别人以“大局为重”、要“学会协作”、仿佛自己突然变成了外星人。其实，这些家伙自身修炼得如何，只需在他面前说三句话，保证他跳得比谁都高。这里并不排除经常给别人做 报告 软件系统测试报告下载sgs报告如何下载关于路面塌陷情况报告535n,sgs报告怎么下载竣工报告下载 的心理专家。人这动物太俗，找不到镜子，尿也不会撒了。我又开小差了，对不起啊，打住。 如果你对我这么慢的教程已经等得不奈烦，小可今天先让你体验一下我所言不虚，让你见识一下代码的伟大～然后，我得接上我前面的话题，因为，在这个问题上，你得听我的，我这么写，是十分有道理的。 今天我们就来为已经输好了的学生成绩作一个统计总分和排名，以及算一算平均分、标准差等。至于优秀率、及格率、和差分率，以及频度分布的相关数据，我们以后再说。 假如你已经有一份输好的各科成绩表，按照通常的做法，是用SUM函数求总分，再用RANK函数求排名，有意思的是，很多人并不知道RANK函数的用法，尽管帮助里写得十分清楚。这样做对学生来说，是不公平的，分数相同，凭什么我的名次要落后呢,更有的领导，早知这样，却从不改变。难道，两三年的时间，就学不会一个函数,责任心的问题～～～在一个并不在乎学生内心感受的学校里，谁会去体会孩子们的内心的煎熬呢, 我要说的是，无论是用SUM求总分，还是用RANK求排名，在VBA中都更加简单和高效，估计只要1%秒就够了，绝对不需要两三天。请看下例:假设总分算在O列，排名算在P列，假设你一共有500名 学生的分数要统计: Sub统计总分和排名1() Sheets("登分表").Activate ------------------激活要排名的工作表 Dim i As Integer ------------------------定义一个循环变量来处理多个数据 For i = 2 To 500 Cells(i, 15) = "=sum(F" & i & ":N" & i & ")" ----------算出总分填入O列 Cells(i, 16) = "=rank(O" & i & "," & "$O$2:$O$48" & ")"---------求出排名填入P列 Next i End Sub 从2开始，是因为第一列中往往是各列数据的标题，比如语文、数学、等。 好啦，就这么两行，所有人的总分和排名都已经搞定，如果你发现我的排名有问题，只要出现分数相同，而排名不同的一例情况，你这个月的生活费用，我全包了。 这里的500，这参加考试的人数，如果这个程序想通用，无论多少人参加考试都可以用的话，那么，再加上一个变量，把这个500换成变量就可以了，其余不用改变。 Sub统计总分和排名1() Sheets("登分表").Activate Dim xRows As Integer xRows = Sheets("登分表").Range("a65536").End(xlUp).Row '这个xRows是登分表A列的总行数，用于计算参与考试的总人数 Dim i As Integer For i = 2 To xRows Cells(i, 16) = "=sum(F" & i & ":N" & i & ")" Next i For i = 2 To xRows Cells(i, 18) = "=rank(P" & i & "," & "$P$3:$P$" & xRows & ")" Next i End Sub 当然，能实现这个功能的代码，还有其它的写法，我是个病人，总是想追求最简单的，有时，问题简单了，批评我的人却多了，尽管我的做法可能更高效，但批评的人似乎更高明，总能从不同的角度入手，比如，说我读书太少，唉。 以下是使用其它方法写的，可以实现完全相同的功能，请读者自行体会。 录制宏并修改的方法，也很棒，不过很多人不喜欢R1C1的表示法: Sub统计总分和排名2录制宏() Sheets("登分表").Select Dim xRows As Integer xRows = Sheets("登分表").Range("a65536").End(xlUp).Row Dim i As Integer For i = 2 To xRows Cells(i, 16).FormulaR1C1 = "=SUM(RC[-10]:RC[-2])" '计算总分 Next i For i = 3 To xRows Cells(i, 18).FormulaR1C1 = "=RANK(RC[-2],R3C16:R" & xRows & "C16)" Next i End Sub 为什么要用两个for …next…循环,因为排序问题中，如果把算总分和排序放到一起，每算一个总分都会重新排一次序，新总分出现了，还会排新序，计算量太大。用数字来说话吧，当算出10个人的总分时，这10个人的名次是在10人中排定的，如果算到15个人的总分时，又会在15人中再排一次，而只有在所有人的总分都算好，最后在所有人的总分中排定最后的顺序，才是最有效的。 本例中采用填充公式的方法，并不是十分必要的，只是为了让习惯于用“拖拉填充柄”的同志作一对比。实际上，在工作表中填充公式计算并不高明。我后面会作对比。目前这样做，已经让人感到欣喜了。 我好久没看这里，没想到还有朋友留言，真是惭愧，我接下来， 好好写一写。 Excel VBA教程之六:求出每门学科的排名 [ 2008-12-17 12:29:00 | By: 醉拍春衫 ] 有了前面的基础，我们不仅可以求出一个学生总分应该是多少句，还可以求出他的任意一门学科应该是多少名，方法类似。不过，你不要以为要把代码写好多遍，不是这样的，这里只要使用“嵌套循环”就可以实现了。什么叫“嵌套循环”呢,就是“循环”里面还有“循环”，打个比方，好象你在看电视，放映的内空是一个角色也在看电视一样。不恰当，不恰当，就那个意思吧，自责一下。 回顾一下教程之五中的循环的特点:固然那里也有两个循环，有先后关系，但它们从逻辑上是无关的，不过是你先循环一下，然后呢，轮到了我，你的对错与我无关，“不搭嘎”～然而， ”，内面则循环“一遍”。就象领导说“一句”“下“嵌套循环”就不一样了，外面循环“一次 周我们要检查”，小老百姓得在“整周”内的“七天”每天都要做做好。 嵌套循环的结构特点如下: Sub 嵌套循环() Dim i As Integer Dim j As Integer For j=1 To 50 For i = 3 To 100 do_something Next i Next j End Sub 象上面这样，一个循环放到了另一个循环的肚子里面了，就构成了“嵌套循环”。在咱们这个要排出各门学科名次的例子中，变量j用来实现数据区域列的变化，即学科的变化，i用来实现数据区域行的变化，即每个学生在该学科上分数的变化。在列取一个值如语文时，让行数循环，排出每一个学生的语文名次，循环结束后，列数增加1变成了数学，重新开始循环数学成绩。do something在本例中，就是要在各个单元格中填入用于排名的函数。 Sub 排各科名次() Sheets("登分表").Activate Dim i As Integer Dim j As Integer Dim FinalRow As Integer FinalRow = Range("E65536").End(xlUp).Row For i = 16 To 25 For j = 3 To FinalRow Cells(j, i).FormulaR1C1 = "=RANK(RC[-10],R3C[-10]:R" & FinalRow & "C[-10])" Next j Next i End Sub 运行一下上述代码，就会把6到15列的每分数都算出相应的排名，并输入到16到25列中。点击16到25列的任意一个单元格，就会在编辑栏中看到代码已经在其中输入了公式。 代码中，从16列开始，到25列结束，在第一列中，都让数据从第3行开始，到最后一行结束，每循环一次，就在对应的单元格Cells(j,i)中填入排名公式: "=RANK(RC[-10],R3C[-10]:R" & FinalRow & "C[-10])" 公式中的,-10,是因为分数在对应的名次左方10个单元格内，其它值也是可以的，只是放置名次的列要靠后了。 速度最快、代码最简洁的方式，还是设置一个单元格对象，然后在对象中填充公式，代码如下: Sub 更快速的排名方法() Sheets("登分表").Activate Dim FinalRow As Integer Dim rng As Range FinalRow = Range("E65536").End(xlUp).Row Set rng = Range(Cells(3, 16), Cells(FinalRow, 25)) rng.FormulaR1C1 = "=RANK(RC[-10],R3C[-10]:R" & FinalRow & "C[-10])" Set rng = Nothing End Sub 比较发现，对于1600行的数值，前者所用的时间在我的电脑上是16.6953秒，后者是0.64406秒。对于计算机来说，5秒的等待就是“漫长”，10秒就会让人感觉是死机，何况是16秒呢,我举这个例子只是想说明“嵌套循环”的意思，更简单的方法一定在您那里。 我们下一讲见～