excel_vba_从入门到精通必备

《excel_vba_从入门到精通必备》由会员分享，可在线阅读，更多相关《excel_vba_从入门到精通必备（509页珍藏版）》请在金锄头文库上搜索。

1、为入门篇：为入门篇：VBA 优势、功能与概优势、功能与概念念第一章 从 Excel 插件认识 VBA简单的说，Excel VBA 是依附于 Excel 程序的一种自动化语言，它可以使常用的 程序自动化，类似于 DOS（磁盘操作系统）中的批处理文件（后缀名“.bat” ） 。那么 它有什么具体的功能？在工作中与常规操作方式相比，具有哪些优势？笔者试图通 过一个简单却实用的插件来展现。 本章要点： 从身份证号获取个人信息 在工作中如何发挥 Excel 插件的优势1.1 从身份证号获取个人信息制作人事资料时，通常需要录入职员身份证号码，以及生日、年龄、性别等等。 除身份证号码需要手工逐一录入以外，其

2、它三项信息的录入有四种方法：手工录入、 内置公式、自定义函数法、插件法。手工输入方式效率极差，且出错机率也最高， 本节通过后三种方式来实现并比较，从而让读者对 VBA 之优势与用法得以初步认知。1.1.1 常规公式法以图 1.1 数据为例，利用公式从身份证中提取生日、年龄、性别等信息，可以有 多种方法。本例列举其中之一。图 1.1 根据身份证号提取职工年龄、生日与性别 通过公式计算职工的年龄、出生日期与性别，步骤如下：（1）在单元格 C3 输入以下公式，用于计算年龄： =DATEDIF(DATE(MID(B3,7,4-(LEN(B3)=15)*2),MID(B3,11- (LEN(B3)=15

3、)*2,2),MID(B3,13-(LEN(B3)=15)*2,2),NOW(),“Y“) （2）在单元格 D3 输入以下公式，用于计算出生日期： =TEXT(RIGHT(19 SUMSDebug.Print “A1:A10 中文本为：“; MYSTR End Sub （3）使用快捷键【Ctrl+G】显示立即窗口； （4）光标定位于代码中任意位置并按下快捷键【F5】 ，执行代码后在立即窗口 将显示图 5.2 所示的结果：图 5.1 工作表数据 图 5.2 统计结果 从这个程序过程可以得出以下结论： （1）本序中使用了四个变量，分别对应四种数据类型：表 5-1 变量与数据类型变量 数据类型 Su

4、msLong CellRange IByte MystrString （2）本例中所使用的四种数据不可以互换，任何两个变量的数据类型都无法互 换。每个变量都使用了合适的数据类型。用其中 Sums 变量的值较大，选中 Long 型 可以储存-2,147,483,648 到 2,147,483,647 之间的数据；而 Cell 变量代表单元格，则 必须使用 Range 对象类型；变量 I 在 1 到 10 之间变化，所以可以用最范围偏小的 Byte；而 Mystr 变量是文字串，那么只能用 String。 （3）如果本例不声明所有变量的数据类型，程序执行效率会有所下降，尽管下 降不多。5.1.3

5、数据类型的声明与转换认识数据的类型、占用空间及定义数据类型的重要性后，就可以在程序中声明 变量的数据类型了。 声明数据类型使用 As 语句，在 As 之后直接附上数据类型即可，中间用空格分 隔。例如： Dim a as Byte 数据类型不区分大小写，不管用“BYTE” 、 “Byte”还是“byte”都可以，但 VBA 会自动将录入数据类型转换成首字母大写、其它字符小写的格式。 如果不指定数据类型，则 VBA 自动将变量指定为变体型 Variant。即以下两句 代码具有相同的作用： Dim i Dim I as Variant 变量的数据类型根据需要也可以转换，即在声明数据类型时若使用了占用

6、内存 较大的数据类型，而在某个阶段所获取的值却很小，当用这个很小的数据去参数计 算时，就有必要将该数据转换成占用内存更小的数据类型。例如：Sub test() Dim 成绩 As Long 成绩 = 80 中间代码,利用变量“成绩”参与各种运算 End Sub 在以上代码，变量“成绩”的类型是占用 4 字节的 Long 型，但事实上它的值为 80，那么利用变量“成绩”参与后续的各种运算将浪费内存。而将其转换成 Byte 型 再参与运算，则可以提速，体现出类型转换之现实意义。 从另一个角度考虑，因不同的数据类型对小数处理的精度不同，那么数据转换 后可以实现更符合需求的精度。 可用于数据类型转换的

7、函数见表 5-2 所示：表 5-2 数据类型转换函数列表函数返回类 型expression 参数范围 CBool Boolean任何有效的字符串或数值表达式 CByteByte0 至 255 CCurCurrency-922,337,203,685,477.5808 至922,337,203,685,477.5807 CDateDate任何有效的日期表达式 CDblDouble 负数从 -1.79769313486231E308 至 -4.94065645841247E-324；正数从 4.94065645841247E-324 至 1.79769313486232E308 CDecDecim

8、al零变比数值，即无小数位数值，为+/- 79,228,162,514,264,337,593,543,950,335。对于 28 位小数的数值，范围 则为+/-7.9228162514264337593543950335；最小的可能非零值是 0.0000000000000000000000000001。 CIntInteger-32,768 至 32,767，小数部分四舍五入 CLngLong-2,147,483,648 至 2,147,483,647，小数部分四舍五入 CSngSingle负数为 -3.402823E38 至 -1.401298E-45；正数为 1.401298E-45 至

9、 3.402823E38 CStrString依据 expression 参数返回 Cstr CVarVariant若为数值，则范围与 Double 相同；若不为数值，则范围与 String 相同 除上面的转换函数外，还有一个用于识别数据类型的函数：TypeName。 功能：返回一个 String，提供有关变量的信息。 语法：TypeName (varname) 如判断一个数据或者变量是何数据类型，可以用以下语句： Msgbox Typename(10)返回“Integer” ，VBA 自动分配的类型 Msgbox Typename(65536) 返回“Long” Msgbox Typenam

10、e(a1) 返回“Range” Msgbox Typename(Mystr) 在未对变量赋值的状态下返回“Empty” 如果利用表 5-2 中的函数对变量进行转换，那么 Typename 返回值会相应变化， 变量本身的值也可能变化。 Sub 类型转换()Dim funds As Doublefunds = 80.42454MsgBox “类型：“ 1;2;3;4;5*7+1,2,3,4,5,6,7-1),“,DATE(y,m,1)- (WEEKDAY(DATE(y,m,1)-1)+0;1;2;3;4;5*7+1,2,3,4,5,6,7-1),“d“)“将公式中的辅助字符替换.Replace W

11、hat:=“,m,“, Replacement:=“,MONTH(“ *.bmp;*.png;*.gif),*.jpg;*.bmp;*.png;*.gif“)让用户选择待插入的文件，只能单选filennames = Application.GetOpenFilename(filefilter1, , “请选一个图片文件“, , MultiSelect:=False)插入该图片，然后设置其上边距、左边距、宽度与高度皆与选区一致With ActiveSheet.Pictures.Insert(filennames).Top = ActiveCell.Top.ShapeRange.LockAspec

12、tRatio = msoFalse.Width = Selection.Width.Left = ActiveCell.Left.Height = Selection.HeightEnd With End Sub 以上过程中使用了 GetOpenFilename 来获取图片文件名，然后根据图片文件全 名插入该图片工作表，而不是使用插入图片的对话框 Application.Dialogs(342)。这是 因为插入图片的对话框无法限制用户一次只能插入一张图片，而且插入后的图片不 便于控制。GetOpenFilename 方法的优点在于随心所欲地定制文件格式，且插入后扔 的图片也可以随心所欲控制它。

13、 （3）返回工作表，选择 B2:E5 区域，然后使用快捷键【Alt+F8】打开“宏”对 话框，选择并执行“插入图片且等于选区大小” ，程序将弹出一个“请选择一个图片 文件”的对话框，它支持 png、jpg、gif 和 bmp 四种格式的图片文件，见图 11.56 所 示。 从打开的对话框中任意选择一张图片，然后程序会将该图片插入到当前表，当 其上边距、左边距以及高度、宽度都是与选区一致。图 11.57 是选择的区域，而图 11.58 是插入图片后的效果，它刚好适应选区。如果用户选择的是一个单元格，那么 图片也会刚好嵌入该单元格中。图 11.56 选择图片文件图 11.57 选择单元格 图 11

14、.58 插入图片覆盖选区 语法补充： （1）Range.Top 属性返回或设置单元格的上边距（以磅为单位） 。而 Range.Left 属性返回或设置单元格的左边距（以磅为单位） 。 （2）Range.Width 属性返回一个区域的宽度（以磅为单位） 。Range.Height 属性 返回一个区域的高度（以磅为单位） 。 （3）Application.GetOpenFilename 方法可以显示一个标准的“打开”对话框， 并获取用户文件名。它的语法如下： Application.GetOpenFilename(FileFilter, FilterIndex, Title, ButtonText

15、, MultiSelect) 其中五个参数的含义如下：表 11-27 Application.GetOpenFilename方法含义详解名称描述 FileFilter一个指定文件筛选条件的字符串FilterIndex指定默认文件筛选条件的索引号，取值范围为 1 到由 FileFilter 所指定的筛 选条件数目。如果省略该参数，或者该参数的值大于可用筛选条件数，则使 用第一个文件筛选条件 Title指定对话框的标题。如果省略该参数，则标题为“打开” ButtonText仅限 MacintoshMultiSelect如果为True，则允许选择多个文件名。如果为False，则只允许选择一个文件 名

16、。默认值为False Application.GetOpenFilename 的一个极大的优点是可以任意筛选文件格式，也可 以任何组合多种格式的文件。本例中默认显示四种格式的图片，如果需要分开显示， 即一次筛选一种格式的图片，那么可以按以下方式设置筛选条件： filefilter1 = “位图 (*.bmp),*.bmp,jpg 图片 (*.jpg),*.jpg,png 图片 (*.png),9.png,Gif 动画 (*.gif),*.gif“ 设置效果如下，显示为四种格式，默认显示第一种，而对话框中显示的图片也 根据用户选择的文件类型而变化。图 11.59 显示四种格式的图片 如果需要既显示多个选项，其中某个选项又可以包含多种格式，那么需要对包 含多种格式的选项利用分号连接。而且同时可以修改 Application.GetOpenFilename 的第二参数设置默认选项为 2。例如以下代码可以产生图 11.60 中的效果： fi