VBA学习资料.docx
《VBA学习资料.docx》由会员分享，可在线阅读，更多相关《VBA学习资料.docx（13页珍藏版）》请在冰豆网上搜索。
VBA学习资料
创建新工作簿更好的方法是将其分配给一个对象变量。
下例中，由Add方法返回的Workbook对象分配给了对象变量newBook。
然后，又设置了newBook的若干属性。
使用对象变量可以很容易地控制新工作簿。
SubAddNew（）
SetNewBook=Workbooks.Add
?
?
WithNewBook
?
?
?
?
?
?
.Title="AllSales"
?
?
?
?
?
?
.Subject="Sales"
?
?
?
?
?
?
.SaveAsFilename:
="Allsales.xls"
?
?
EndWith
EndSub
81-100
81、Rem:
将活动单元格从A5移到A6，并将A6单元格的数值保存到x变量中
range（"A5"）.select此命令就是选择A5单元格
Activecell.offset（1,0）.select
X=activecell.value
82、用代码窗口中可用F8运行宏，Alt+F8显示宏对话框，Alt+F11可以打开VBE编辑器
83、OptionExplicit?
?
在程序代码前使用该命令，则变量要在声明后才能使用，否则编译程序无法识别该变量，从而产生错误信息
84、Load语句用于加载窗体，加载后将占用内存，Hide方法使窗体隐藏，但仍在内存中，故不再使用窗体时应使用Uuload语句及时卸载，将内存交还系统，Show方法用来显示一个窗体，格式：
窗体名称.show 模式，模式可取0或1，为1时，不能到其他窗体操作，只有关闭该窗体后才能对其他窗体操作
85、控件的命名规则，通常使用3个字母的前缀命名控件，如Label的前缀为lbl,初学者最好养成良好的命名习惯
86、按下Ctrl+Enter键单元格不移动。
87、按下F2键直接在单元格内编辑，不需动鼠标
88、命令按钮不支持双击（Dbclick）事件
89、PrivateSubCheckBox1_Click（）
IfCheckBox1.ValueThen
MsgBox"333"
Else
MsgBox"666"
EndIf
EndSub
本段的主要用意在于明白了IfCheckBox1.ValueThen与IfCheckBox1.Value=TrueThen
90、Timer是计时器,功能是按指定时间间隔产生定时事件
91、在一个语句要分行显示的地方加一个或多个空格，加一个下划线＿然后回车转入下一物理行，作用在于代码一行写不下时续行，程序代码中一行较短时可加：
把多行连成一行显示
92、ifa>15then
b=10
else
b=100
endif
可改为如下if语句
b=iif（a.15,10,100）
93、F12用于启动另存为对话框
94、x=shell（calc.exe,1）可打开计算器程序
95、call语句格式：
Call<子过程名>[（<实际参数表>）]如果过程本身没有参数，则实参和括号可省略，并报参数放在括号中，另一个调用Sub过程的方法是:
<子过程名>[<实际参数表>]比前一个少了Call和括号，子过程调用语句的实参在数目、类型、排列上与子过程定义语句的形式参数表一致
96、自定义函数一例,求abc三个数的平均
PrivateFunctionPassed（ByValaAsinteger,ByValbAsinteger,ByValcAsinteger）
ave=（a+b+c）/3
EndSub
在定义时必须向函数过程名赋值，而子程序名不能赋值
97、在工作表任意单元格输入=Cell（"filename"）可获得文件的完整路径、文件名和工作表名
98、在打开Excel文件时按住Shift键，将不运行VBA过程，可防止宏病毒，单击文件 关闭命令，在点关闭时按住Shift键将在不运行VBA过程的情况下关闭工作簿，可防止关闭时自动运行的宏病毒。
99、让一个变量得到单元格A1到A5的总和（变量设为X）,
X=Sum（Range（“A1:
A5”））错
5”））正确
也就是说必须通过Application的WorksheetFunction属性间接调用工作表函数
100、Rem和'是注释符，注释语句是非执行语句，要养成对代码注释的习惯。
101-120
1选择活动工作表下一张工作表，
选上一张
102、单元格B2：
B10数值不全为空用IF语句表达
ForEachclinActiveSheet.Range（"B2:
B10"）
Ifcl.Value<>""then
msgbox"有非空单元格"
ExitFor
endif
next
103、用Range引用单元格和单元格区域
Range（"A1"）单元格A1
Range（"A1:
B5"） 从单元格A1到B5区域
Range（"A1:
B5,B1:
B7"） 多块的选定区域
Range（"A:
A"） A列
Range（"1:
1"） 第一行
Range（"A:
C"） A列到C列的区域
Range（"1:
5"） 第1行到第5行的区域
Range（"1:
1,3:
3"） 第1、行
Range（"A:
A,C:
C"） A列、C列
104、用Cells及编号引用单元格
Cells（6,1）A6单元格
如果对工作表用Cells属性时不指定索引，表示引用工作表上的所有单元格，下例清除活动工作簿中工作表Sheet1上所有单元格的内容
Worksheets（“sheet1”）.Cells.ClearContents
105、可用变量代入单元格索引值，故Cells属性非常适用于在单元格区域中循环，如：
Forcounter=1To20
Worksheets（“sheet1”）.Cells（counter,3）.value=counter
Nextcounter
106、引用行或列
Rows
（1）第1行
Rows 所有行
Columns
（1）?
?
第1列
Columns（“C”）第3列
Columns工作表上所有列
106、可用方括号将A1样式的引用或命名区域的名称括起来，作为Range属性的快捷方式，这样就不必键入Range和引号，如
Worksheets（“sheet1”）.[A1:
B2].clearContents
[MyRange].Value=30
107、用Offset处理按相对于其他单元格的某一位置的常用办法是使用Offset属性，本例将活动工作表上活动单元格下一行和右边三列的单元格的内容设置为下划线，如：
ActiveCell.Offset（1,3）.font.Underline=XlDouble
108、把别的工作表Sheet2数据，读到当前工作表的方法列举
1）[A1]=Sheet2.[A1]?
?
把Sheet2A1单元格的数据，读到A1单元格
2）[A2:
A4]=Sheet2.[B1]?
?
把Sheet2单元格B1的数据读到A2:
到A4单元格
3）Range（B1”）=Sheet2.Range（“B1”）?
?
把Sheet2工作表单元格B1数据，读到B1单元格
4）Range（“C1:
C3”）=Sheet2.Range（“C1”）?
?
把Sheet2工作表单元格C1数据，读到C1:
C3
5）Cells（1,4）=Sheet2Cells（1,4）?
?
把Sheet2工作表单元格D1数据，读到D1单元格
6）Range（Cells（1,5）,Cells（5,5）=Sheet2.Cells（1,5）?
?
把sheet2工作表单元格E1数据，读到E1:
E5单元格
7）Selection.Value=Sheet2.[F1]?
?
把Sheet2工作表单元格[F1]数据，读到任何你点选的单元格
109、Sub前有个Private表示是私有子程序，这个子程序不会出现在“宏”对话框中
110、Subtest（）
ActiveSheet.Calculate
EndSub重算活动工作表
111、编程前应该尽可能地多了解Excel对象的属性、方法
112、每一个Excel对象的属性、方法的调用都要通过OLE连接的一个或多个调用，这些OLE调用都是需要时间的，减少使用对象引用能加快VBA代码的运行
113、使用With语句
Workbooks
（1）.Sheets
（1）.Range（“A1:
A1000”）.font.Name=”Pay”
Workbooks
（1）.Sheets
（1）.Range（“A1:
A1000”）.Font.Fontstyle=”Bold”…
改用With语句则运行速度加快
例：
WithWorkbooks
（1）.Sheets
（1）.Range（“A1:
A1000”）.font
.Name=”Pay”
.Fontstyle=Bold”
…
EndWith
114、用set设置对象变量，以减少对象的访问，如：
SetMyRange=Workbooks
（1）.Sheets
（1）
Mysheet.Range（“A1”）.Value=100
Mysheet.Range（“A2”）.Value=200
比直接用Workbooks
（1）.Sheets
（1）.Range（“A1”）.Value=100
Workbooks
（1）.Sheets
（1）.Range（“A2”）.Value=200运行快
115、在循环中要尽是减少对象的访问
Fork=1To100
Sheets（“sheet1”）.select
Cells（k,1）.value=Cells（1,1）.Value
Nextk
更快的代码是
setTheValue=Cells（1,1）.Value
Sheets（“sheet1”）.select
Fork=1To100
Cells（k,1）.value=TheValue
116、减少对象的激活和选择
如果你是通过录制宏的来学习VBA的程序里一定充满了对象的激活和选择，如Workbooks（XXX）.active、Sheets（XXX）.Select?
?
、Range（XXX）.Select等，但事实上大多数情况下这些操作不是必须的，如
Sheets（“sheet1”）.Select
Range（“A1”）.Value=100
Range（“A2”）.Value=200
可改为Withsheets（“Sheet3”）
.Range（“A1”）=100
.Range（“A2”）=200
117、关闭屏幕更新是提高运行速度的最有效的办法，推荐使用
Application.ScreenUpdate=False
程序运行后再改回来
118、VBA中默认的数据类型是Variant，你必须选择使用何种数据类型，因为Variant数据类型占用存储空间较大（16或22字节）而且它将影响程序的性能，Vba必须识别Variant类型的变量中存储了何种数据类型。
119、再列一个自定义函数计算价格为10%为运费的简单函数例子
PublicFunctionShipping（Price）
Shipping=Price*0.1
EndFunction
如还是不懂的话，将上述过程复制到模块中，然后在工作表任意单元格中输入=Shipping（C1）你就会明白
120、ActiveWindow.DisplyGridlines=False?
?
此句用来关闭网格线。
1、如何更改excel默认的行列标签的颜色？
桌面-属性-外观-项目-高级_已选定的项目，设置颜色。
2、系统提供的排序功能最多只能同时依据三个关键字来进行。
如果需要依据三个以上的条件来排序，该如何实现？
答：
虽然系统一次最多只能同时对三个关键字来排序，但是我们可以利用设置排序关键字的优先级，通过多次排序来实现关键字大于3个以上的排序。
具体操作如下：
首先打开工作表。
单击“数据”菜单中的“排序”项，依次在“主要关键字”、“次要关键字”、“第三关键字”中选择优先级最低的几个排序关键字，然后执行排序操作。
接下来再依次选择优先级较高的几个关键字，进行2次排序。
这样直到最高优先级的关键字排序完成为止，就可以实现了对三个以上关键字的排序操作了。
3、若A1-A6中有大于0和小于0的数，请问怎样将其中小于0的数所在的行自动删除。
fori=6to1step-1
ifcells（i,1）<0thenrows（i）.Delete
nexti
4、请问：
INDEX（data，，1）中的data是什么意思
是定义的名称，具体是什么要看文件。
按ctrl+F3看看。
5、快速插入行（列）的快捷键，CTrl+键盘+
6、桌面上的"网上邻居"没有了,在我的电脑找到了,移动到桌面只是个快捷方式,
在桌面点右键选属性，点“桌面”“自定义桌面”把“网上邻居”前打上勾，确定
7、如何获取一个月的最大天数？
:
"=DAY（DATE（2002,3,1）-1）"或"=DAY（B1-1）",B1为"2001-03-01
8、数据区包含某一字符的项的总和,该用什么公式
=sumif（a:
a,"*"&"某一字符"&"*",数据区）
9、能否在EXCEL中定时打印?
PrivateSubWorkbook_Open（）
Application.OnTime"9:
30:
00","wlqPrint"
'将"9:
30:
00"改为要自动打印的时间
EndSub
SubwlqPrint（）'打印
EndSub
10、比方说要求得g在图示区域中所在列号，如何表达？
数组公式，按ctrl+shift+enter结束。
=MAX（（A1:
F4="g"）*COLUMN（A1:
F4））
11、勾怎么输入
按住ALT键输入41420后放开ALT键√
12、将单元格中的数全部变成万元表示（？
？
？
）
自定义单元格格式：
0"."0,或：
0!
.0000
13、自定义名称中的引用范围中可以直接粘贴公式
从编辑栏里Ctrl+C复制，然后Ctrl+V粘贴
14、如果一个单元格中既有数字又有字母，怎么提取其中的数字呢？
Functiongetnumber（rngAsString）AsString
'自定义函数作用：
提取当前单元格中的数字
DimmylenAsInteger
DimmystrAsString
mylen=Len（rng）
ForI=1Tomylen
?
mystr=Mid（rng,I,1）
?
IfAsc（mystr）>=48AndAsc（mystr）<=57Then
getnumber=getnumber&mystr
?
?
EndIf
NextI
EndFunction
20、在B1中同步显示A列中最后一行的内容
最后一行为文本：
=offset（$b$1,MATCH（CHAR（65535）,b:
b）-1,）
最后一行为数字：
=offset（$b$1,MATCH（9.9999E+307,b:
b）-1,）
或者：
=lookup（2,1/（b1:
b1000<>""）,b1:
b1000）
26、请问如何设置单元格，令其不接受包含空格的字符
选定A列
数据——有效性——自定义——公式
=iserror（find（"",a1））
数据－－有效性－－自定义－－公式
=len（a1）=len（trim（a1））
30、原来的函数是=a1+a4+a7+a10+a13+a16+a19+a22.....
现在想用offset来简化公式，我只会用比如a4=offset（a1,3,0）的，不会弄出一个数组出来实现上面的效果
sum（n（offset（a1,（row（1:
10）-1）*3,）））
row（1:
10）={1;2;3;4;5;6;7;8;9;10}
（row（1:
10）-1）*3={0;3;6;9;12;15;18;21;24;27}
自A1向下偏移，就是a1、a4、a7、a10、a13、、、a28
31、未被发现的两个日期格式符号?
?
1、bb或bbbb：
如2005-1-1设置自定义格式bb或bbbb,结果为48或2548,与2005年份的差为543，发现任何日期这个差数是固定的，经查询有关资料，公元前544元是佛历元年，所以我认为这个与佛历有关（佛教的英文是B开头的）
2、e：
对日期设置自定义格式e，结果是公历的四位年份，为2005-1-1显示为2005,完全可以代替yyyy格式符号
第一个格式在EXCEL使用中可能从来也不会用到，权作一个小知识吧
以上是在EXCEL2003下发现的，经检验在EXCEL2000下没有
32、工作表的A1单元格为B1:
H1的总和,B1:
H1又有其它公式,如何让A1当为负数时,让它不显示负数而显示0,其它时候为正常总和!
if（sum（B1:
H1）<0,0,sum（B1:
H1））
自定义单元格格式：
G/通用格式;"0";0
正数，负数，零，不过第一节中正数用，分隔要如何设定？
#,#.##;"0";0小数点后面保留了两位，可根据需要调节小数点后面#的个数
33、发现在名称定义中只能逐个删除被定义的名称，有何方法一次全部删除表中的名称？
SubDelName（）
?
?
ForEachNameInThisWorkbook.Names
?
?
?
?
?
?
Name.Delete
?
?
Next
EndSub
34、在一个工作表中引用其他工作表中的数据,但是被引用的工作表不是固定的,根据我输入的工作表名自动选择相应的工作表中的数据,请问在公式里怎样引用?
=INDIRECT（"A1"&"!
"&"E1"）?
?
?
?
A1为工作表名
41、如果数值的有效性是基于已命名的单元格区域，并且在该区域中有空白单元格，则设置“忽略空值”复选框将使有效单元格中输入的值都有效。
同样，为有效性公式所引用的任何单元格也如此：
如果引用的单元格是空值，则设置“忽略空值”复选框将使有效单元格中输入的值都有效。
42、每次默认的都是自动更新,所以我通过[编辑]-[链接]-[启动提示]里设置选择[不显示该警告,但是更新链接],可是设置好了以后,每次重新打开工作薄,都提示是否更新一下链接文件内容...我不知道为什么会这样,我希望得到帮助
这个管用，而且自动更新链接。
在“工具”菜单上，单击“选项”，再单击“编辑”选项卡，清除“请求自动更新链接”复选框。
50、转换A1与R1C1引用样式
SubRC_A1（）
?
?
WithApplication
?
?
?
?
?
?
If.ReferenceStyle=xlR1C1Then
?
?
?
?
?
?
?
?
.ReferenceStyle=xlA1
?
?
?
?
?
?
Else
?
?
?
?
?
?
?
?
.ReferenceStyle=xlR1C1
?
?
?
?
?
?
EndIf
?
?
EndWith
EndSub
56、目的：
表中>50000的单元格红色显示。
做法：
选择整张表，在条件格式命令中，设置了“>50000以红色填充单元格“的条件，出现的问题：
表头（数值为文本）的单元格也呈红色显示。
我知道，原因是因为区域选择得不对，如果只选择数字区域不会出现这种情况，如果表结构简单，则好处理，如果表格结构复杂，这样选择就很麻烦。
有没有办法选择整张表，但是表头（数值为文本）的单元格不被条件格式。
答：
条件格式设置公式=--A1>50000
问=--A1>50000中的--代表什么意思，
答：
转变为数值.与+0,*1,是一样的效果。
57、、如何打印行号列标？
?
?
答：
文件菜单-----页面设置---工作表----在打印选项中的行号列标前打勾。
58、如何打印不连续区域？
?
?
答：
按CTRL键不松，选取区域，再点文件菜单中的打印区域--设置打印区域。
59、打印时怎样自动隐去被0除的错误提示值?
?
?
答：
页面设置—工作表，错误值打印为空白
如何设置A1当工作表打印页数为1页时,A1=1,打印页数为2页时,A2=2,...?
?
?
答：
插入名称a=GET.DOCUMENT（50,"Sheet1"）&T（NOW（）），在A1输入=a
69、如何用函数来获取单元格地址
=ADDRESS（ROW（）,COLUMN（））
70、求A1:
B10中A列等于1的对应B列中的最小值
=min（if（a1:
a10=1,b1:
b10））
输入后按ctrl+shift+enter完成。
72、如何统计A1：
A10，D1:
D10中的人数？
答：
=COUNTA（A1:
A10,D1:
D10）答：
是不是按下了ScrollLock键。
方法一：
使用自定义函数：
Functionbookname（）
bookname=ActiveSheet.Name
EndFunction
使用：
在单元格中输入公式：
=bookname（）?
?
，即可返回当前工作簿的标签名字
方法二：
使用系统函数Cell（）：
在单元格中输入公式：
=Cell（"filename"）?
?
就会返回该工作簿和工作表的名字（包括绝对路径名），剩下的就根据你自己的需要运用一些文本处理函数对它进行处理就行了
（说明：
该函数必须在工作簿已经保存的情况下才生效）
103、今天是10月31日，我希望一个月后应该是11月30日
工具＿加载宏VBA分析工具库
=edate（today（）,1）
120、VBA从哪学起
1、录制宏；
2、数据类型（主是整型和字符串型）；
3、程序结构（主要是分支，判断，循环）；
4、EXCEL对象（单元格，工作表，工作薄
124、请教如何筛选出特殊颜色的数据。
定义名称.X=GET.CELL（24,单元格）,辅助列=X
按辅助列筛选
125、如何快速查看名称
133、对于一些不可打印的字符（在Excel显示中类似空格），直接用替换方法不容易去掉。
=SUBSTITUTE（CLEAN（A1）,"",""）
139、图片批注
右击带批注的单元格》编辑批注》在批注边框上右击》设置批注格式》颜色与线条》单击“颜色”》填充效果》图片》选择图片.
141、在一年中，显示是第几天用什么函数呢？
=a1-date（year（a1）,1,0）
将单元格格式设置为常规
144、如何用公式求出最大值所在的行？
如A1：
A10中有10个数，怎么求出最大的数在哪个单元格？
=MATCH（LARGE（A1:
A10,1）,A1:
A10,0）
=ADDRESS（MATCH（SMALL（A1:
A10,COUNTA（A1:
A10））,A1:
A10,0）,1）
=ADDRESS（MATCH（MAX（A1:
A10,1）,A1:
A10,0）,1）
145、返回最末行位置
1、如果能确定某一行的数据到达数据区的最后一行：
能确定某列数据间不会有空格，使用counta对该列计数；
不能确定某列数据间会不会有空格，使用lookup找最后单元格行号；
2、什么都不能确定，使用数组公式max（（a1:
j5<>""）*row（a1:
j5））
146、如何统计成绩大于600且班级是5班的学生人数
"=sum（（a2:
a100=5）*（j2:
j100>600））"，不包括引号，然后同时按住shift和ctrl再按enter键。
147、如何让工作表奇数行背景是红色偶数行背景是蓝色？
用条件
升级会员 