excelvba学习总结.docx

1、excelvba学习总结excelvba学习总结【篇一：01-excel vba 学习总结 - 基础知识】 excel vba 学习总结 - 基础知识 1.什么是vba？ 当前辈们使用excel的时候，他们惊奇的发现：excel那是相当的彪悍，几乎任何的数据分析与处理，它都可以近乎完美(实际上不可能完美)完成。但是在使用的过程中，他们也同样发现，有很多工作是要重复做的。如果要想自动重复的完成这些工作，必须要借助其他的编程语言和工具。微软为了简化这个过程，开发了一个通用的自动化语言，这个就是vba(visual basic for application)。所有支持vba的应用程序都可以方便的自

2、动化执行某些固定的步骤，除此以外，支持vba的应用程序之间也可以通过vba这个平台进行互操作。由于这里讨论的是excel中的vba，所以我后面的总结内容都是以excel中的vba为主。 vba是vb的一个子集，它们之间有些地方是不同的： ? vb程序可以独立的部署和运行，但是vba程序不能离开宿主 程序运行。 vb程序是一个编译型的语言，程序需要编译后执行，而vba 程序是解释执行的脚本语言。 ? 2.vba能做什么？知道了vba是什么东东后，那么它能干什么呢？确定的说，vba基本能做一切excel能做的事，比如打印，生成报表，分析数据，生成图表等，这是vba最大的优点。不需要额外实现这些标准

3、的功能，只需要简单的调用就可以了，excel已经做好了一切准备。除了这个好处，vba其实还能处理很多的任务： ? 自定义excel的外观，菜单，工具栏等。 重复执行自动化操作。 操作文件和文件夹 访问数据库并执行相关操作 访问网络 操作xml 获取系统信息 操作和自动化office其它组件(支持vba的其他公司的产品也可 以操作) ? ? ? ? ? ? ? 除了上述的任务外，vba还有很多其它的功能有待发掘。 3. vba与macromacro是一组excel能理解并执行的命令集合。借助宏录制器，我们能得到这些宏命令翻译后的vba代码。这是最简洁获得vba代码的方式，也是vba开发最主要的模

4、式。 4. vba小结 vba的语法很简单，这里就不再详述了。我个人学习新语言的习惯都是，先了解一下这个语言出现的背景，然后了解一下语言改进的地方，最后是实践一下基本的语法，研究语言运行的机制和部署情况。经过这个步骤以后，我就做几个实际小例子巩固巩固。我学习vba的小结如下： ? module是vba组织代码的最小单元。 vba是不分大小写的，所以更要养成良好的编程习惯。 代码可以一行写多句，用:隔开，也可以一句写在多行，行末 用_标识。 module成员的访问限定： ? ? ? public: 当前程序中的所有模块都可以访问该成员，如果方法默认不加限定符的话，当public处理；public

5、成员只能在module中定义。 dim/private:只有本module内部的所有方法才能访问这些成员。当然方法是不能用dim定义的。 friend：只能用于对象模块或者窗体模块，作用范围是当前程序中的其他对象模块可以访问该成员。? const定义恒定变量的时候，只能在module中定义，不能在方 法内定义；前面可以加public/private限定。 有一点比较恶心，对象赋值用set.=，其余的一切赋值用=， 包括对象的属性赋值。 ? ? 可以使用type在模块级别中定义包含一个或多个元素的用户自定义 的数据类型。 ? 根据使用情况(比如需要的存储大小)选用合适的变量类型，一般 都应该明确

6、定义变量类型，不要使用默认的variant类型。 当需要使用变长集合的时候，可以考虑使用动态数组(使用red im和preserve)。 nothing、empty与null的比较： ? ? nothing：这是一个指向空对象的对象引用。将对象引用设置为nothing，就释放了那个对象。如果没有其他的引用指向对象，vb/vba就将销毁这个对象。可以使用“obj is nothing”的方式检查。 empty：这是一个象integer或者string一样的变量类型，它表示了一个还没有进行初始化的变量。它与null的意义不同，null表示没有合法数据。例如数组，集合刚定义，还没有赋值之前就是这个状

7、态，可以使用内置方法isempty检查。 null：这是一个象integer或者string一样的变量类型，它表示一个没有合法数据的变量。这有别于zero、nothing、empty或者vbnullstring。null参与的运算，都将产生null结果。可以用内置方法isnull检查。? 使用for each语句枚举集合成员。 使用for语句执行固定次数的循环。 使用do while/until.loop或者while.wend语句执行不定次数的循环。 使用if语句执行2分支的选择。 使用switch case语句执行n分支的选择。 使用with语句减少重复对象的书写。 在module开始的时

8、候，加上option explicit可以强制变量使用前必须声明。 类型定义的简短写法：例如定义整形，可简写为：dim i%integer % long single ! double # currency string $ string*size $ ? ? ? ? ? ? ? ? 方法调用可以加括号，也可以不加括号；如果要是加括号，特别 是含有多个参数的时候，则前面需要加上call。 方法是支持可选参数的，参数前用optional标识并用=提供默认值。可选参数用参数名:=值的方式传值特别方便。 ?【篇二：12-excel vba 学习总结 - 代码优化之道】 excel vba 学习总结

9、- 代码优化之道 每个问题都可以用不同的解决方案解决，不同的方案执行的效率却是不同的。同样一个问题，不同的程序执行的时间可能相差数十，甚至数百倍。所以学习一门语言，必须要了解其优化方法。下面从几个方面总结一下vba中的代码优化之道。 一、运行环境的优化 vba是需要运行在运行环境中的，运行环境直接影响了vba程序的执行效率，所以第一个方面，我们从干扰程序执行的因素，讨论运行环境的优化。 若要提高vba程序的性能，明确禁用代码执行时不需要的功能是极其有效的优化方法。通常，只需在代码运行后重新计算或重绘一次，这样做可以提高性能。代码执行后，将功能恢复到其原始状态即可。 1.关闭屏幕更新 关闭屏幕更

10、新是提高vba程序运行速度的很有效的方法，能大幅缩短运行时间。原因很简单，后台的操作不需要反映到屏幕上了，当然就省去了很多的操作，所以可以节省很多时间。当然了，这个操作是可以控制粒度的，比如每次vba程序的开始于结束可以设置屏幕更新，也可以在每个循环的开始与结束时设置，这个通常取决于用户的需要。 关闭屏幕更新的方法： application.screenupdate = false 程序执行过程 . 请不要忘记vba程序运行结束时再将该值设回来 application.screenupdate = true 2.关闭自动计算 函数的自动计算对excel运行影响很大，尤其是有大量数组函数和易失性

11、函数时，影响更为明显。这个方面优化的最简单的方法就是关闭自动重算，启用手动重算。通过减少重算量提高excel速度。可以在进入主程序运行前，将计算模式设置为手动。calculation 属性是对所有工作簿进行的设置，您也可以用工作表的enablecalculation属性来设置对某个工作表是否进行重新计算。 设为手动计算 application.calculation = xlcalculationmanual 程序执行过程 . 恢复为自动计算 application.calculation = xlcalculationautomatic 3.禁用事件 禁用事件可以避免事件触发时的连锁反应。例

12、如在工作表的change事件中，一个单元格的值改变影响两个以上的单元格值改变，因为事件的连续触发可以造成cpu耗尽。 可以在change事件中禁用事件 application.enableevents = false 程序执行过程 . 请不要忘记vba程序运行结束时再将该值恢复 application.enableevents = true 4.禁用状态栏 状态栏设置与屏幕更新设置是分开的，这样即使屏幕不更新，您仍可以显示当前操作的状态。但是，如果您不需要显示每个操作的状态，则在代码运行时禁用状态栏也可以提高性能。 不显示状态栏 application.displaystatusbar=fal

13、se， 程序执行过程 . 恢复显示状态栏 application.displaystatusbar=false， 5.禁用分页符 如果 activesheet.displaypagebreaks 设置为false，则 excel 不显示分页符。代码运行时不需要重新计算分页符，在代码执行后计算分页符可以提高性能。 6.避免在频繁的事件中写代码 这个道理很简单，例如频繁的activate事件。 二、算法的优化算法代表解决问题的步骤，它直接影响了程序的执行效率，所以算法优化基本是所有语言优化代码最主要的过程；这是一个不断试验，不断总结，不断优化的过程。这个不是这里的重点，所以只是简单分享一下我知道的

14、方式。 深刻挖掘和应用数学模型的特性 基本上都是缩小问题的规模，递推总结出数学模型的规律，然后用程序实现就可以了。例如下面的经典问题： 上台阶问题：/flowerszhong/archive/2011/09/14/2176374.html。如果空间允许的话，可以拿空间换时间 常见空间换时间算法： 保存可以利用的中间结果 动态规划中用的最多了：/retype/zoom/7e2dd18383d049649b665847?pn=5x=0y=404raww=893rawh=17o=png_6_0_0_0_0_0_0_893.25_1263.375type=picaimh=9.1377379619260

15、92md5sum=261b85118c2ef8e1cc668bd0c07ae910sign=0d51258cd8zoom=png=4329-6339jpg=0-0 target=_blank点此查看 for i =1 to 10000 if i mod 2 = 1 then cells(i, 1).entirerow.interior.colorindex = 23 next i for i = 1 to 10000 step 2 cells(i, 1).entirerow.interior.colorindex = 23 next i 实现同样的功能，但却循环的次数有差异，明显是第二个循环效

16、率更高。 6、一次性完成赋值与粘贴 在使用copy方法时，可以在一个语句中指定复制的内容及要复制到的目的地。 例如： range(b5:c6).select selection.copy range(b8).select activesheet.paste 经修改后的最佳代码是： range(b5:c6).copy destination:=range(b8) 7、选用合适的操作符，加速对数字的运算 (1)当对整数进行除法时，您可以使用整型除法运算符()而不是浮点除法运算符(/)，因为无论参与除法运算的数值类型如何，浮点除法运算符总会返回double类型的值。 (2)在任何具有整数值的算术表达

17、式中使用single或double值时，整数均将被转换成single或double值，最后的结果将是single或double值。如果要对作为【篇三：13-excel vba 学习总结 - 实战之惑】 excel vba 学习总结 - 实战之惑 vba中很多的功能可以用内置的函数完成，其它的可以自己写函数完成。下面几点就是我在实战中遇到比较多的，而且带有一定困惑性的典型问题。 一、返回列的名字 这是个最简单的问题，但是有时候还是很需要的，方法比较简单，就是通过address获取列的名字，比如“a”，“ab”等。 private function columnheader(target as r

18、ange) as string columnheader = left$(right$(target.address, len(target.address) - 1), instr(1, right$(target.address, len(target.address) - 1), $) - 1) end function 二、寻找实际使用的最后一行 这个问题在实际中经常遇到，而且实现的方式也多种多样。 使用executeexcel4macro实现 在excel vba中，内置函数executeexcel4macro用于执行一些excel 4.0中的一些函数。其中有一个特殊的函数是返回sh

19、eet使用的最后一行的，使用很简单，如下所示： sub showlastline() msgbox executeexcel4macro(get.document(10) end sub application.executeexcel4macro的用法说明： 作用：执行一个 microsoft excel 4.0 宏函数，然后返回此函数的结果。返回结果的类型取决于函数的类型。 语法：executeexcel4macro(string) 参数：string，一个不带等号的 microsoft excel 4.0 宏语言函数。所有引用必须是像 r1c1 这样的字符串。如果 string 内包含嵌

20、套的双引号，则必须写两个。例如，要运行宏函数 =mid(sometext,1,4)，string 必须为 “mid(sometext,1,4)”。 返回值：variant 说明：microsoft excel 4.0 宏不在当前工作簿或工作表的环境中求值。也就是说所有的引用都应该是外部引用，而且需要明确指定工作簿名。例如，要在 book1 中执行 microsoft excel 4.0 宏“my_macro”，必须使用“book1!my_macro()”。如果不指定工作簿名，此方法将失效。 示例：本示例对工作表 sheet1 上的 c3 单元格执行 get.cell(42) 宏函数，然后在一个

21、消息框中显示结果。get.cell(42) 宏函数返回当前窗口左边界到活动单元格的左边之间的水平距离。visual basic 中没有与此宏函数直接等价的函数。 worksheets(sheet1).activate range(c3).select msgbox executeexcel4macro(get.cell(42) 使用end属性 在excelvba中，使用end(xlup)查找最后一行是最常使用且最为简单的方法，它假设要有一列总包含有数据(数字、文本和公式等)，并且在该列中最后输入数据的单元格的下一行不会包含数据，因此不必担心会覆盖掉已有数据。但该方法有两个缺点： (1) 仅局限

22、于查找指定列的最后一行。 (2) 如果该列中最后一行被隐藏，那么该隐藏行将被视作最后一行。因此，在最后一行被隐藏时，其数据可能会被覆盖。但该列中间的隐藏行不会影响查找的结果。 public function lastrowincolumn(column as string) as long lastrowincolumn = range(column rows.count).end(xlup).row end function 使用find方法 find方法在当前工作有数据中进行查找，不需要指定列，也可以确保不会意外地覆盖掉已有数据。其中，参数lookin指定所查找的类型，有三个常量可供选择，

23、即xlvalues、xlformulas和xlcomments。 (1) 常量xlformulas将包含零值的单元格作为有数据的单元格。(当设置零值不显示时，该单元格看起来为空，但该参数仍将该单元格视为有数据的单元格) (2) 常量xlvalues将包含零值的单元格(如果设置零值不显示时)作为空白单元格，此时，若该单元格在最后一行，则find方法会认为该单元格所在的行为空行，因此，该单元格中的内容可能会被新数据所覆盖。(在excel中，选择菜单“工具”“选项”，在打开的“选项”对话框中，选择“视图”选项卡，将其中的“零值”前的复选框取消选中，则工作表中的零值都不会显示) 如果在参数lookin

24、中使用常量xlvalues的话，还存在一个问题是：如果您将最后一行隐藏，则find方法会认为倒数第二行是最后一行，此时您在最后一行的下一行输入数据，则会将实际的最后一行的数据覆盖。 public function lastrow() as long 使用常量xlformulas，因为常量xlvalues会忽略隐藏的最后一行 lastrow = cells.find(*, lookin:=xlformulas, searchdirection:=xlprevious).row end functionfind方法中，参数lookin的默认值为xlformulas。 使用usedrange属性 u

25、sedrange方法可用于在工作表中已使用区域查找最后一行，该区域包括可能以前使用过的任何单元格，但现在其中的数据被删除了，比如目前的工作表中只有第1行至第5行共5行，其它行都无数据，但在第6行中有些单元格以前使用过(可能仅仅格式化或内容清除了，总之该行现在不含有数据)，那么第6行也包含在该已使用的区域中。此外，如果最后一行被隐藏，那么会将因此，使用该方法查找最后一行是无规律且不可靠的，它通常可能会得到预料不到的结果。 public function lastusedrow() as long with activesheet lastusedrow =.usedrange.rows.coun

26、t + .usedrange.row - 1 end with end function 使用specialcells方法 也可以用specialcells方法实现查找最后一行，其常量xlcelltypelastcell代表在”已使用区域”中的最后一个单元格，与usedrange属性稍有不同的是，当您在最后一行中输入数据后，又将其删除，则此数据所在的单元格也包含在已使用的区域中，并且如果最后的行被隐藏，则将可见行的最后一行当作最后一行。 public function lastusedrow() as long lastusedrow = cells.specialcells(xlcellty

27、pelastcell).row end function specialcells方法用于查找指定类型的值，其语法为specialcells(type,value)，有两种主要的使用方式： (1) 若参数type仅考虑常量，则在查找时会忽略和覆盖由公式生成的任何数据。 (2) 若参数type仅考虑由公式生成的数据，则在查找时会忽略和覆盖任何常量数据。 如果参数type是xlcelltypeconstants或者是xlcelltypeformulas，则value参数可使用常量决定哪种类型的单元格将被包含在结果中，这些常量值能组合而返回多个类型，其缺省设置是选择所有的常量或公式，而不管是何类型，

28、可使用下面四个可选的常量： 1) xltextvalues(包含文本); 2) xlnumbers(包含数字); 3) xlerrors(包含错误值); 4) xllogical(包含逻辑值); 我们可以使用specailcells方法去找到其它特定类型的单元格所在的最后一行，下面是这些常量的一个完整的列表： xlcelltypeallformatconditions (任何格式的单元格) xlcelltypeallvalidation (带有数据有效性的单元格)xlcelltypeblanks (所使用区域中的空白单元格) xlcelltypecomments (包含有批注的单元格) xlc

29、elltypeconstants (包含有常量的单元格) xlcelltypeformulas (包含有公式的单元格) xlcelltypelastcell (已使用区域中的最后一个单元格(看下面) xlcelltypesameformatconditions (有相同格式的单元格) xlcelltypesamevalidation (有相同数据有效性条件的单元格) xlcelltypevisible (工作表中所有可见的单元格) 使用currentregion属性 range对象的currentregion属性返回代表单元格所在的当前区域，即四周有空行的独立区域，因此，可使用此属性查找当前区

30、域的最后一行。但是使用其查找最后一行的一个缺点是，必须首先选取当前区域，然后进行查找。 三、自定义类型扩展 vba中扩充基本类型的基本手段就是自定义类型，主要有两种方式。 1. 定义type 使用type关键字可以定义一些简单的自定义类型，这些类型使用起来就像基本类型一样使用，直接定义和赋值。例如：publictype employee name as string address as string salary as double end type dim manager as employee manager.name = joe smith manager.address = 123

31、main street manager.salary = 40000 type定义和使用都很简单，但是它只是一种静态的数据结构，无法完成很多高级的功能；它的显著缺点如下： (1).无法创建type的实例，可以存放到数组中，但是无法存放到collection与dictionary中。 (2).无法控制成员的有效范围，也就是无法验证成员的值是合法的。 (3).只有数据，无法去定义行为。2. 定义class 正是由于type简单但是难以胜任复杂的情景，所以当需要一些负责的处理数据的场景时，往往需要定义class这种更强大的扩展。定义class的方式也很简单，直接在工程中插入一个class module就可以了，可以在属性窗口中设置class的名字。下面是示例：private pname as string private paddress as string private psalary as double public property get name() as string name = pname end property public property let name