excelvba学习总结.docx

excelvba学习总结.docx

《excelvba学习总结.docx》由会员分享，可在线阅读，更多相关《excelvba学习总结.docx（15页珍藏版）》请在冰点文库上搜索。

excelvba学习总结

excelvba学习总结

【篇一：

01-excelvba学习总结-基础知识】

excelvba学习总结-基础知识

1.什么是vba？

当前辈们使用excel的时候，他们惊奇的发现：

excel那是相当的彪悍，几乎任何的数据分析与处理，它都可以近乎完美（实际上不可能完美）完成。

但是在使用的过程中，他们也同样发现，有很多工作是要重复做的。

如果要想自动重复的完成这些工作，必须要借助其他的编程语言和工具。

微软为了简化这个过程，开发了一个通用的自动化语言，这个就是vba（visualbasicforapplication）。

所有支持vba的应用程序都可以方便的自动化执行某些固定的步骤，除此以外，支持vba的应用程序之间也可以通过vba这个平台进行互操作。

由于这里讨论的是excel中的vba，所以我后面的总结内容都是以excel中的vba为主。

vba是vb的一个子集，它们之间有些地方是不同的：

?

vb程序可以独立的部署和运行，但是vba程序不能离开宿主

程序运行。

vb程序是一个编译型的语言，程序需要编译后执行，而vba

程序是解释执行的脚本语言。

?

2.vba能做什么？

知道了vba是什么东东后，那么它能干什么呢？

确定的说，vba基本能做一切excel能做的事，比如打印，生成报表，分析数据，生成图表等，这是vba最大的优点。

不需要额外实现这些标准的功能，只需要简单的调用就可以了，excel已经做好了一切准备。

除了这个好处，vba其实还能处理很多的任务：

?

自定义excel的外观，菜单，工具栏等。

重复执行自动化操作。

操作文件和文件夹

访问数据库并执行相关操作

访问网络

操作xml

获取系统信息

操作和自动化office其它组件（支持vba的其他公司的产品也可

以操作）?

?

?

?

?

?

?

除了上述的任务外，vba还有很多其它的功能有待发掘。

3.vba与macro

macro是一组excel能理解并执行的命令集合。

借助宏录制器，我们能得到这些宏命令翻译后的vba代码。

这是最简洁获得vba代码的方式，也是vba开发最主要的模式。

4.vba小结

vba的语法很简单，这里就不再详述了。

我个人学习新语言的习惯都是，先了解一下这个语言出现的背景，然后了解一下语言改进的地方，最后是实践一下基本的语法，研究语言运行的机制和部署情况。

经过这个步骤以后，我就做几个实际小例子巩固巩固。

我学习vba的小结如下：

?

module是vba组织代码的最小单元。

vba是不分大小写的，所以更要养成良好的编程习惯。

代码可以一行写多句，用:

隔开，也可以一句写在多行，行末

用_标识。

module成员的访问限定：

?

?

?

public:

当前程序中的所有模块都可以访问该成员，如果方法默认不加限定符的话，当public处理；public成员只能在module中定义。

dim/private:

只有本module内部的所有方法才能访问这些成员。

当然方法是不能用dim定义的。

friend：

只能用于对象模块或者窗体模块，作用范围是当前程序中的其他对象模块可以访问该成员。

?

const定义恒定变量的时候，只能在module中定义，不能在方

法内定义；前面可以加public/private限定。

有一点比较恶心，对象赋值用set...=，其余的一切赋值用=，

包括对象的属性赋值。

?

?

可以使用type在模块级别中定义包含一个或多个元素的用户自定义

的数据类型。

?

根据使用情况（比如需要的存储大小）选用合适的变量类型，一般

都应该明确定义变量类型，不要使用默认的variant类型。

当需要使用变长集合的时候，可以考虑使用动态数组（使用red

im和preserve）。

nothing、empty与null的比较：

?

?

nothing：

这是一个指向空对象的对象引用。

将对象引用设置为nothing，就释放了那个对象。

如果没有其他的引用指向对象，vb/vba就将销毁这个对象。

可以使用“objisnothing”的方式检查。

empty：

这是一个象integer或者string一样的变量类型，它表示了一个还没有进行初始化的变量。

它与null的意义不同，null表示没有合法数据。

例如数组，集合刚定义，还没有赋值之前就是这个状态，可以使用内置方法isempty检查。

null：

这是一个象integer或者string一样的变量类型，它表示一个没有合法数据的变量。

这有别于zero、nothing、empty或者vbnullstring。

null参与的运算，都将产生null结果。

可以用内置方法isnull检查。

?

使用foreach语句枚举集合成员。

使用for语句执行固定次数的循环。

使用dowhile/until...loop或者while...wend语句执行不定次数的循环。

使用if语句执行2分支的选择。

使用switchcase语句执行n分支的选择。

使用with语句减少重复对象的书写。

在module开始的时候，加上optionexplicit可以强制变量使用前必须声明。

类型定义的简短写法：

例如定义整形，可简写为：

dimi%integer%

long

single!

double#

currency@

string$

string*size$?

?

?

?

?

?

?

?

方法调用可以加括号，也可以不加括号；如果要是加括号，特别

是含有多个参数的时候，则前面需要加上call。

方法是支持可选参数的，参数前用optional标识并用=提供默认值。

可选参数用参数名:

=值的方式传值特别方便。

?

【篇二：

12-excelvba学习总结-代码优化之道】

excelvba学习总结-代码优化之道

每个问题都可以用不同的解决方案解决，不同的方案执行的效率却是不同的。

同样一个问题，不同的程序执行的时间可能相差数十，甚至数百倍。

所以学习一门语言，必须要了解其优化方法。

下面从几个方面总结一下vba中的代码优化之道。

一、运行环境的优化

vba是需要运行在运行环境中的，运行环境直接影响了vba程序的执行效率，所以第一个方面，我们从干扰程序执行的因素，讨论运行环境的优化。

若要提高vba程序的性能，明确禁用代码执行时不需要的功能是极其有效的优化方法。

通常，只需在代码运行后重新计算或重绘一次，这样做可以提高性能。

代码执行后，将功能恢复到其原始状态即可。

1.关闭屏幕更新

关闭屏幕更新是提高vba程序运行速度的很有效的方法，能大幅缩短运行时间。

原因很简单，后台的操作不需要反映到屏幕上了，当然就省去了很多的操作，所以可以节省很多时间。

当然了，这个操作是可以控制粒度的，比如每次vba程序的开始于结束可以设置屏幕更新，也可以在每个循环的开始与结束时设置，这个通常取决于用户的需要。

关闭屏幕更新的方法：

application.screenupdate=false

程序执行过程

...

请不要忘记vba程序运行结束时再将该值设回来

application.screenupdate=true

2.关闭自动计算

函数的自动计算对excel运行影响很大，尤其是有大量数组函数和易失性函数时，影响更为明显。

这个方面优化的最简单的方法就是关闭自动重算，启用手动重算。

通过减少重算量提高excel速度。

可以在进入主程序运行前，将计算模式设置为手动。

calculation属性是对所有工作簿进行的设置，您也可以用工作表的enablecalculation属性来设置对某个工作表是否进行重新计算。

设为手动计算

application.calculation=xlcalculationmanual

程序执行过程

...

恢复为自动计算

application.calculation=xlcalculationautomatic

3.禁用事件

禁用事件可以避免事件触发时的连锁反应。

例如在工作表的change事件中，

一个单元格的值改变影响两个以上的单元格值改变，因为事件的连续触发可以造成cpu耗尽。

可以在change事件中禁用事件

application.enableevents=false

程序执行过程

...

请不要忘记vba程序运行结束时再将该值恢复

application.enableevents=true

4.禁用状态栏

状态栏设置与屏幕更新设置是分开的，这样即使屏幕不更新，您仍可以显示当前操作的状态。

但是，如果您不需要显示每个操作的状态，则在代码运行时禁用状态栏也可以提高性能。

不显示状态栏

application.displaystatusbar=false，

程序执行过程

...

恢复显示状态栏

application.displaystatusbar=false，

5.禁用分页符

如果activesheet.displaypagebreaks设置为false，则excel不显示分页符。

代码运行时不需要重新计算分页符，在代码执行后计算分页符可以提高性能。

6.避免在频繁的事件中写代码

这个道理很简单，例如频繁的activate事件。

二、算法的优化算法代表解决问题的步骤，它直接影响了程序的执行效率，所以算法优化基本是所有语言优化代码最主要的过程；这是一个不断试验，不断总结，不断优化的过程。

这个不是这里的重点，所以只是简单分享一下我知道的方式。

深刻挖掘和应用数学模型的特性

基本上都是缩小问题的规模，递推总结出数学模型的规律，然后用程序实现就可以了。

例如下面的经典问题：

上台阶问题：

/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.137737961926092md5sum=261b85118c2ef8e1cc668bd0c07ae910sign=0d51258cd8zoom=png=4329-6339jpg=0-0target=_blank点此查看

fori=

1to10000

ifimod2=1thencells（i,1）.entirerow.interior.colorindex=23

nexti

fori=1to10000step2

cells（i,1）.entirerow.interior.colorindex=23

nexti

实现同样的功能，但却循环的次数有差异，明显是第二个循环效率更高。

6、一次性完成赋值与粘贴

在使用copy方法时，可以在一个语句中指定复制的内容及要复制到的目的地。

例如：

range（b5:

c6）.select

selection.copy

range（b8）.select

activesheet.paste

经修改后的最佳代码是：

range（b5:

c6）.copydestination:

=range（b8）

7、选用合适的操作符，加速对数字的运算

（1）当对整数进行除法时，您可以使用整型除法运算符（）而不是浮点除法运算符（/），因为无论参与除法运算的数值类型如何，浮点除法运算符总会返回double类型的值。

（2）在任何具有整数值的算术表达式中使用single或double值时，整数均将被转换成single或double值，最后的结果将是single或double值。

如果要对作为

【篇三：

13-excelvba学习总结-实战之惑】

excelvba学习总结-实战之惑

vba中很多的功能可以用内置的函数完成，其它的可以自己写函数完成。

下面几点就是我在实战中遇到比较多的，而且带有一定困惑性的典型问题。

一、返回列的名字

这是个最简单的问题，但是有时候还是很需要的，方法比较简单，就是通过address获取列的名字，比如“a”，“ab”等。

privatefunctioncolumnheader（targetasrange）asstring

columnheader=left$（right$（target.address,len（target.address）-1）,instr（1,right$（target.address,len（target.address）-1）,$）-1）endfunction

二、寻找实际使用的最后一行

这个问题在实际中经常遇到，而且实现的方式也多种多样。

使用executeexcel4macro实现

在excelvba中，内置函数executeexcel4macro用于执行一些excel4.0中的一些函数。

其中有一个特殊的函数是返回sheet使用的最后一行的，使用很简单，如下所示：

subshowlastline（）

msgboxexecuteexcel4macro（get.document（10））

endsub

application.executeexcel4macro的用法说明：

作用：

执行一个microsoftexcel4.0宏函数，然后返回此函数的结果。

返回结果的类型取决于函数的类型。

语法：

executeexcel4macro（string）

参数：

string，一个不带等号的microsoftexcel4.0宏语言函数。

所有引用必须是像r1c1这样的字符串。

如果string内包含嵌套的双引号，则必须写两个。

例如，要运行宏函数=mid（sometext,1,4），string必须为“mid（sometext,1,4）”。

返回值：

variant

说明：

microsoftexcel4.0宏不在当前工作簿或工作表的环境中求值。

也就是说所有的引用都应该是外部引用，而且需要明确指定工作簿名。

例如，要在book1中执行microsoftexcel4.0宏“my_macro”，必须使用“book1!

my_macro（）”。

如果不指定工作簿名，此方法将失效。

示例：

本示例对工作表sheet1上的c3单元格执行get.cell（42）宏函数，然后在一个消息框中显示结果。

get.cell（42）宏函数返回当前窗口左边界到活动单元格的左边之间的水平距离。

visualbasic中没有与此宏函数直接等价的函数。

worksheets（sheet1）.activate

range（c3）.select

msgboxexecuteexcel4macro（get.cell（42））

使用end属性

在excelvba中，使用end（xlup）查找最后一行是最常使用且最为简单的方法，它假设要有一列总包含有数据（数字、文本和公式等），并且在该列中最后输入数据的单元格的下一行不会包含数据，因此不必担心会覆盖掉已有数据。

但该方法有两个缺点：

（1）仅局限于查找指定列的最后一行。

（2）如果该列中最后一行被隐藏，那么该隐藏行将被视作最后一行。

因此，在最后一行被隐藏时，其数据可能会被覆盖。

但该列中间的隐藏行不会影响查找的结果。

publicfunctionlastrowincolumn（columnasstring）aslong

lastrowincolumn=range（columnrows.count）.end（xlup）.rowendfunction

使用find方法

find方法在当前工作有数据中进行查找，不需要指定列，也可以确保不会意外地覆盖掉已有数据。

其中，参数lookin指定所查找的类型，有三个常量可供选择，即xlvalues、xlformulas和xlcomments。

（1）常量xlformulas将包含零值的单元格作为有数据的单元格。

（当设置零值不显示时，该单元格看起来为空，但该参数仍将该单元格视为有数据的单元格）

（2）常量xlvalues将包含零值的单元格（如果设置零值不显示时）作为空白单元格，此时，若该单元格在最后一行，则find方法会认为该单元格所在的行为空行，因此，该单元格中的内容可能会被新数据所覆盖。

（在excel中，选择菜单“工具”——“选项”，在打开的“选项”对话框中，选择“视图”选项卡，将其中的“零值”前的复选框取消选中，则工作表中的零值都不会显示）

如果在参数lookin中使用常量xlvalues的话，还存在一个问题是：

如果您将最后一行隐藏，则find方法会认为倒数第二行是最后一行，此时您在最后一行的下一行输入数据，则会将实际的最后一行的数据覆盖。

publicfunctionlastrow（）aslong

使用常量xlformulas，因为常量xlvalues会忽略隐藏的最后一行

lastrow=cells.find（*,lookin:

=xlformulas,searchdirection:

=xlprevious）.row

endfunction

find方法中，参数lookin的默认值为xlformulas。

使用usedrange属性

usedrange方法可用于在工作表中已使用区域查找最后一行，该区域包括可能以前使用过的任何单元格，但现在其中的数据被删除了，比如目前的工作表中只有第1行至第5行共5行，其它行都无数据，但在第6行中有些单元格以前使用过（可能仅仅格式化或内容清除了，总之该行现在不含有数据），那么第6行也包含在该已使用的区域中。

此外，如果最后一行被隐藏，那么会将因此，使用该方法查找最后一行是无规律且不可靠的，它通常可能会得到预料不到的结果。

publicfunctionlastusedrow（）aslong

withactivesheet

lastusedrow=.usedrange.rows.count+.usedrange.row-1

endwith

endfunction

使用specialcells方法

也可以用specialcells方法实现查找最后一行，其常量xlcelltypelastcell代表在”已使用区域”中的最后一个单元格，与usedrange属性稍有不同的是，当您在最后一行中输入数据后，又将其删除，则此数据所在的单元格也包含在已使用的区域中，并且如果最后的行被隐藏，则将可见行的最后一行当作最后一行。

publicfunctionlastusedrow（）aslong

lastusedrow=cells.specialcells（xlcelltypelastcell）.row

endfunction

specialcells方法用于查找指定类型的值，其语法为specialcells（type,value），有两种主要的使用方式：

（1）若参数type仅考虑常量，则在查找时会忽略和覆盖由公式生成的任何数据。

（2）若参数type仅考虑由公式生成的数据，则在查找时会忽略和覆盖任何常量数据。

如果参数type是xlcelltypeconstants或者是xlcelltypeformulas，则value参数可使用常量决定哪种类型的单元格将被包含在结果中，这些常量值能组合而返回多个类型，其缺省设置是选择所有的常量或公式，而不管是何类型，可使用下面四个可选的常量：

1）xltextvalues（包含文本）;2）xlnumbers（包含数字）;

3）xlerrors（包含错误值）;4）xllogical（包含逻辑值）;

我们可以使用specailcells方法去找到其它特定类型的单元格所在的最后一行，下面是这些常量的一个完整的列表：

xlcelltypeallformatconditions（任何格式的单元格）

xlcelltypeallvalidation（带有数据有效性的单元格）

xlcelltypeblanks（所使用区域中的空白单元格）

xlcelltypecomments（包含有批注的单元格）

xlcelltypeconstants（包含有常量的单元格）

xlcelltypeformulas（包含有公式的单元格）

xlcelltypelastcell（已使用区域中的最后一个单元格（看下面））

xlcelltypesameformatconditions（有相同格式的单元格）

xlcelltypesamevalidation（有相同数据有效性条件的单元格）

xlcelltypevisible（工作表中所有可见的单元格）

使用currentregion属性

range对象的currentregion属性返回代表单元格所在的当前区域，即四周有空行的独立区域，因此，可使用此属性查找当前区域的最后一行。

但是使用其查找最后一行的一个缺点是，必须首先选取当前区域，然后进行查找。

三、自定义类型扩展

vba中扩充基本类型的基本手段就是自定义类型，主要有两种方式。

1.定义type

使用type关键字可以定义一些简单的自定义类型，这些类型使用起来就像基本类型一样使用，直接定义和赋值。

例如：

public

typeemployee

nameasstring

addressasstring

salaryasdouble

endtype

dimmanagerasemployee

manager.name=joesmith

manager.address=123mainstreet

manager.salary=40000

type定义和使用都很简单，但是它只是一种静态的数据结构，无法完成很多高级的功能；它的显著缺点如下：

（1）.无法创建type的实例，可以存放到数组中，但是无法存放到collection与dictionary中。

（2）.无法控制成员的有效范围，也就是无法验证成员的值是合法的。

（3）.只有数据，无法去定义行为。

2.定义class

正是由于type简单但是难以胜任复杂的情景，所以当需要一些负责的处理数据的场景时，往往需要定义class这种更强大的扩展。

定义class的方式也很简单，直接在工程中插入一个classmodule就可以了，可以在属性窗口中设置class的名字。

下面是示例：

privatepnameasstring

privatepaddressasstring

privatepsalaryasdouble

publicpropertygetname（）asstring

name=pname

endproperty

publicpropertyletname