excelvba学习总结Word格式.docx
《excelvba学习总结Word格式.docx》由会员分享,可在线阅读,更多相关《excelvba学习总结Word格式.docx(15页珍藏版)》请在冰点文库上搜索。
除了上述的任务外,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,
恢复显示状态栏
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
实现同样的功能,但却循环的次数有差异,明显是第二个循环效率更高。
6、一次性完成赋值与粘贴
在使用copy方法时,可以在一个语句中指定复制的内容及要复制到的目的地。
例如:
range(b5:
c6).select
selection.copy
range(b8).select
activesheet.paste
经修改后的最佳代码是:
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
使用specialcells方法
也可以用specialcells方法实现查找最后一行,其常量xlcelltypelastcell代表在”已使用区域”中的最后一个单元格,与usedrange属性稍有不同的是,当您在最后一行中输入数据后,又将其删除,则此数据所在的单元格也包含在已使用的区域中,并且如果最后的行被隐藏,则将可见行的最后一行当作最后一行。
lastusedrow=cells.specialcells(xlcelltypelastcell).row
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