小苏子
小苏子PDF在线图书

疑难千寻千解丛书 Excel 2013 VBA编程与实践 内容简介

疑难千寻千解丛书 Excel 2013 VBA编程与实践 内容简介

疑难千寻千解丛书 Excel 2013 VBA编程与实践 目录

疑难千寻千解丛书 Excel 2013 VBA编程与实践 精彩文摘

《Excel 2013 VBA编程与实践》为读者展示Excel VBA编程的实战技巧,包括工作中最常用的查询、定位、格式转换、报表拆分与合并、开发自定义函数、处理文件与文件夹、功能区设计,以及插件开发实战与原理分析。本书侧重于解决问题和展示解题思路,案例包含诸多常见疑难的解决方案。阅读本书后,读者可以解决工作中的诸多疑难杂症,大大提高工作效率,且有助于提升编程能力,拓展思路,将理论向实战迈进。好的代码应该同时具备准确、纠错、兼容和效率四个特性,本书所有的案例都在准确性与高效性的基础上提供完善的错误处理措施与思路讲解。《Excel 2013 VBA编程与实践》包括205个实用案例和一个大型综合应用――开发送货单套打程序系统。书中的每个案例采取疑难描述、解决方案、操作方法、原理分析和知识扩展五个步骤进行讲解,力图在解决问题的同时让读者可以通晓其思路和原理。第1章 基础理论 11.1 变量、常量与数据类型 1疑难1 正确地定义变量和数据类型有何优势 1数据类型与对象类型 5疑难2 公共变量和静态变量都有何用处 61.2 程序防错要点 8疑难3 常见的代码错误由哪些原因造成 8疑难4 如何侦测代码出错,并将运行代码的错误原因发给作者 12疑难5 如何开发完善的程序 141.3 练习与思考 18第2章 数据查找技巧 192.1 快速查找 19疑难6 能否按范围批量查找数值 19疑难7 能否将符合多条件之一的所有数据提取到新表中 22判断工作表是否存在的方法 23疑难8 可否按格式查找单元格,然后替换其格式 25FindFormat的使用技巧 26疑难9 如何找出A线的不达标人员信息 27单列多条件与多列多条件筛选的区别 29疑难10 如何查找所有的“#”并标识为上标 29定位单元格任意字符的方法 30疑难11 如何找出还款时间超过一年及未还款的客户信息 31日期函数Datedif的特性 32疑难12 可以将查找到的所有数据串连并写入剪贴板中吗 33疑难13 可以创建一个工具栏来方便查找吗 35如何区分精确匹配与模糊匹配 37疑难14 能否按相似度查找所有的数据 37利用Array向区域中一次性写入多个常量 39疑难15 如何在具有合并单元格的区域中多条件逐步查找 40通过“MergeArea”属性返回合并区域 41疑难16 如何查找成绩并分批发送邮件 42VBA中邮件正文的换行符表示法 43疑难17 如何在输入时逐步查找 44通过KeyUp事件自动执行查询 462.2 跨表查找内容 47疑难18 能否将所有表中的完成目标者汇总到“总表” 47利用SpecialCells定位实现快速查找 48疑难19 查找每月产量冠军名单,在窗体中罗列显示 49不采用循环,一次性找出最大值所在行 50疑难20 如何找出工作簿中所有的外部链接且将它们转换成值 51如何获取工作簿中的外部链接 52疑难21 可否模糊查找所有部门的电话信息 52Target与Activecell的区别 54疑难22 如何实现将所有未收货款者在状态栏随机显示 54利用OnTime定时执行程序 56疑难23 可否在单元格中创建多级下拉菜单 56ActionControl对象的功能与限制 60疑难24 可否在文件夹的所有文件中查找特定信息并汇总到新表 60疑难25 如何统计文件夹中所有的成绩工作簿中不及格人数 64调用工作表函数时应如何书写区域引用型参数 652.3 文件查找与转换 66疑难26 如何判断指定的文件是否存在 66判断文件是否存在的函数 67疑难27 如何进行深度查找且创建文件目录 68疑难28 可否在工作表中罗列出所有大于5MB的文件 70疑难29 如何将所有的Word文件转换成PDF文件 72疑难30 如何将xlsm和xlsx格式的所有文件转换成xls格式 76疑难31 如何在网上邻居的共享盘中查找并打开“单价表” 78疑难32 如何实现全盘查找且播放音乐文件 79疑难33 能否在文件夹的所有工作簿中执行批量替换 81疑难34 能否根据成绩表和模板生成Word成绩通知单 842.4 图片查找与引用 87疑难35 如何瞬间删除当前表中艺术字和图表等以外的图片 87DrawingObjects与Shapes对象的区别 89疑难36 可以将签名图片复制到表中所有的签名处吗 89复制图形对象与复制数据的区别 90Selection代表什么 90疑难37 如何像vlookup引用数据一样引用图片 91循环弹出对话框直接到用户正确操作为止的编程思路 93疑难38 能否对材料表分页且调用材料图片 942.5 练习与思考 97第3章 数据处理 983.1 按条件定位的技巧 98疑难39 如何一次性定位产量大于1000的所有单元格 98使用定位技术减少循环语句的循环次数 99疑难40 可否全选至少三科不及格的学生姓名 100通过SpecialCells定位错误值 103疑难41 工作表中所有的错误值是否可隐藏起来 102疑难42 能否定位数值区域并转换成以“万”为单位 104VBA中如何处理双引号 105疑难43 如何定位当前表的最大值或最小值 105调用工作表函数并配合Find方法查找最大值 107疑难44 能否定位并标识高于平均值的单元格 107VBA中数字、汉字的大小关系 109疑难45 如何反向选择工作表区域 109辅助区的重要性 1103.2 数据处理及格式转换 111疑难46 能否一键对选区横向、纵向汇总 111“R1C1”引用方式的优点 112疑难47 如何将单词在大写、小写与首字母大写间任意切换 113利用StrConv函数对英文进行大写、小写和首字母大写转换 114疑难48 如何将二维的材料表转换成一维表 115使用数组函数Array简化代码 116疑难49 如何开发一个通用的一维表转二维表的工具 117对数组和区域中的值去除重复值时的区别 120疑难50 如何开发一个通用的二维表转一维表的工具 121疑难51 可否将字符串中的字母、数字和汉字分离到多单元格中 124利用Like运算符区分汉字、数字和字母 125疑难52 能否一键转换表达式为计算结果 126通过Evaluate方法转换表达式 127疑难53 数据有效性可以设置为关联的三级下拉选单吗 127疑难54 能否将职工信息按自定义序列排序 130新旧版本中Sort的差异 131疑难55 可以不打开工作簿而提取其数据吗 132在VBA中调用公式实现从未打开的工作表中取值 134疑难56 可否一键保护所有的公式 134切换Locked 属性实现公式保护 135疑难57 如何实现发票金额分解 136MID取文本的特点 137疑难58 VBA可以破解工作表密码吗 138表的分类 139疑难59 如何将不规范的时间统一为“hh:mm:ss.00”格式 139Format与Text函数的相同点和不同点 141疑难60 可以生成指定范围的不重复随机数吗 141Collection对象的优势 142疑难61 如何对工作簿减肥 143文件虚胖的常见原因 144疑难62 可否让数字在文本与数值之间快速切换 145在文本与数值间切换的VBA思路 146疑难63 如何将“/”分隔的数据进行汇总 147以“/”为分隔符取其左右字符的思路优化 148疑难64 可否一键删除工作表中所有的空白行 148SpecialCells方法的限制 150疑难65 能实现粘贴数据时跳过隐藏区吗 150如何确定单元格是否被隐藏 153疑难66 可否让单元格的值真正地四舍五入 153疑难67 如何对相同值进行标识着色 155ColorIndex属性的限制对VBA代码的影响 157疑难68 如何根据工资计算零钞数量 158获取选区第一列及已用区域的交集 160疑难69 可否将职工资料表一键转换成打印格式 160如何计算图片所在单元格的地址 1623.3 单元格合并技巧 162疑难70 能否一键合并相同且相邻的所有单元格 162利用DisplayAlerts属性关闭合并单元格时的提示加快代码执行速度 164疑难71 如何实现按产品合并产量数据表 164利用变量暂存数据,代替辅助区 166疑难72 能否改进“跨越合并”,使其居中保留所有的数据 166Merge方法的真正功能 167疑难73 可以合并同类项且分类汇总吗 168通过选择性粘贴格式实现合并单元格 170疑难74 如何实现合并时保留所有的数据,而拆分时还原数据 171看不见的特殊字符的应用 173疑难75 如何一键选择所有合并的单元格 174定位合并单元格 175查找设置对下一次查找结果有何影响 175疑难76 如何取消所有的合并区域,并对所有的单元格填充数据 175合并区域的赋值方式 176疑难77 能否实现撤销合并后能还原所有数据的多单元格合并 177合并单元格后再取消合并不丢失数据的思路 178疑难78 如何将指定的单元格合并到一个选区 178将指定单元格链接到一个选区 180疑难79 可以让合并单元格自动换行吗 180工作表簿件代码与普通过程的区别 1823.4 报表合并与拆分 183疑难80 如何合并所有工作表的数据到一个表中 183在复制数据时既去除公式,又不影响数值的显示状态 185疑难81 如何实现多表合并汇总 185利用相对引用公式批量合并数据,避免使用循环 187疑难82 如何将多个工作簿中的所有工作表合并到一个工作表中 187通过禁用工作簿重算提升代码效率 190疑难83 如何将多个工作簿数据合并到一个工作簿 190利用变量作为辅助进行条件判断 194疑难84 如何实现按条件将单个工作表拆分成多个工作表 194Range.AutoFilter方法的特殊性 198疑难85 如何将工作簿中的每个工作表转换为独立工作表 198在不同的Excel版本中如何选择文件格式 2003.5 单元格颜色的综合应用 201疑难86 不同版本的Excel在颜色处理方面有区别吗 201不同的版本中颜色差异对程序的影响 202疑难87 可以在Excel 2013中按颜色筛选再做扩展吗 203CurrentRegion与Usedrange对程序的影响 206疑难88 如何实现将颜色排序 207借用辅助区和调用老版本的Sort功能提升程序的通用性 208疑难89 可以按颜色对选区的数据分类汇总吗 209修改颜色不触发公式重算和任何VBA事件 211疑难90 如何用函数对单元格的背景和字体按颜色汇总 212如何声明可选参数 2143.6 重复数据处理 214疑难91 可否清空重复值所在单元格并以背景色标示 214Countif函数的限制 216疑难92 可用红圈标示重复出现的数据吗 217疑难93 如何提取两列数据中的相同项与不同项 219利用Transpose函数实现区域转数组 220疑难94 可否一键删除重复行 221Range.RemoveDuplicates方法中Columns参数的限制 2223.6 练习与思考 223第4章 报表打印 2244.1 打印设置 224疑难95 如何一次性设置“总表”以外工作表的页脚 224选择工作表数量对页脚的影响 225疑难96 可否将所有工作表的打印区域设置为有数据的区域 225更新打印区域设置的限制 227疑难97 可否将订单表转换成每10行打印一页 228全自动批量插入分页符 230疑难98 如何让跨页的合并单元格在打印后能完整显示 229判断合并单元格是否跨页 2324.2 特殊打印格式设计 231疑难99 如何制作工资条 232疑难100 如何打印工资卡 234疑难101 VBA可以实现对工作表分页小计吗 237利用宏表函数Get.Document(50)获取工作表页数 242疑难102 如何同时打印顶端标题和底端标题 241计算第一个分页符所在的行号 249疑难103 可以借用图片实现底端标题打印吗 248Export与API方式将区域转换成图片的差异 251疑难104 可否将订单表转换成适合针式多联打印的报表格式 251修改CopyObjectsWithCells属性实现复制数据时忽略图形对象 254疑难105 如何实现双面打印 254疑难106 可以只打印活动单元格所在页吗 255如何计算分页符位置和当前页的序号 2574.3 思考与练习 257第5章 借用事件让程序自动化 2585.1 工作表事件 258疑难107 录入M2和M3时可自动将2或3显示为上标吗 258Target与ActiveCell的相同与不同点 259疑难108 可否录入产品规格时自动在右边显示表达式的值 260修改EnableEvents属性避免事件的连锁反应 261疑难109 可否实时记录指定区域的修改记录 262让批注框自动调整大小的必要步骤 264疑难110 能否双击首行或首列时弹出工作表目录 264工作表事件与工作簿事件的分别 2655.2 工作簿事件 266疑难 111 能否在启动工作簿时自动创建工作表目录 266Workbook_Open事件与Auto_open宏的相同点和不同点 267疑难112 进入包含“货款”的工作表时可语音提示未收款客户名称吗 268DateDiff函数与DateDif函数的相同和不同点 269疑难113 能否自动记录工作簿的已打印次数 270BeforePrint事件的缺陷 271疑难114 输入重复的工号时能否提示“已重复” 271创建让用户指定执行方式的对话框的三种方法 273疑难115 能否在状态栏显示选区中的最大值和最小值地址 273工作表函数Counta的参数应如何对待Range对象的默认属性值 2755.3 应用程序事件 275疑难116 可否让新建的工作簿包含“进库”、“出库”和“异常统计”三个工作表 275借助类实现应用程序级的事件 277安装加载宏的两种方法 278疑难117 可否新建图表时默认显示为圆角加阴影效果 279工作簿级与应用程序级的图表事件差异 280疑难118 可否选择单元格时整行与整列自动着色 280引用活动窗口的可见区域 2825.4 思考与练习 283第6章 开发自定义函数 2846.1 自定义函数基础 284疑难119 如何开发自定义函数 284如何跨工作簿调用自定义函数 288疑难120 自定义函数在不同的版本中有何差异 288颜色对自定义函数的影响 289函数参数的数量上限 291疑难121 如何对自定义函数添加功能描述与参数说明 291通过Application.MacroOptions方法为自定义函数添加参数说明 2926.2 开发自定义函数 293疑难122 如何对区域内混杂字符串中的数字求和 293Excel公式对“+”的处理方式 294疑难123 如何分离字符串中的数值、英文和汉字 294正则表达式在字符处理中的优势 295疑难124 如何将文本混杂字符串及表达式转换为值 296正则表达式中如何表示数字、字母和汉字范围 297疑难125 如何按时间段汇总金额 298根据计算对象的规范性决定函数参数的个数 300疑难126 如何对具有分隔符的数据分类汇总 300Split函数产生的数组的特点 302疑难127 超过15位的数字如何求和 302Excel对数据计算的长度限制 304疑难128 如何根据身份证号码获取出生日期、年龄和性别 304疑难129 可否将发票格式的数字金额合并且转换为大写 307疑难130 如何突破Rank函数排名的限制 308去除重复值的常用方法 310疑难131 如何实现按数据出现次数排序 310Collection与字典的区别 312疑难132 可以用函数改变引用区域的值吗 312利用自定义函数修改引用区域的值的两种方法 314疑难133 如何对两个以逗号分隔的乱序字符串比较异同 315将Split函数嵌套应用从字符串中分别获取品名与数量 3166.3 开发具有可选参数的自定义函数 317疑难134 可以扩展Vlookup函数实现返回所有符合条件的值吗 317利用Find替代工作表函数Vlookup实现多个数据查找 318疑难135 可以用一个函数将人民币大小写相互转换吗 319通过拆分法理解代码 321疑难136 可以让函数的两个参数全是可选参数吗 322将单个参数声明为可选参数的方法 323疑难137 可以用函数连接内存数据和区域中所有的文本吗 324利用ParamArray声明不确定个数的函数参数 3256.4 思考与练习 326第7章 文件与文件夹管理 3277.1 文件管理 327疑难138 能否在打开文件时自动备份文件 327将过程命名为“Auto_Open”使其自动执行 328疑难139 如何让文件打开一次后就自我销毁 328文件“自杀”的条件 329疑难140 如何一键删除3年前创建的所有文件 329DATEDIF与DATEDIFF的区别 330疑难141 可以删除18个月没有打开过的文件吗 331用DATEDIF计算文件闲置时间的技巧 331疑难142 可以在收藏夹中对当前工作簿创建快捷方式吗 332获取收藏夹地址的方法 3337.2 文件夹管理 334疑难143 如何瞬间删除D盘中所有的空文件夹 334将代表磁盘的字符串转换成磁盘对象的方法 334疑难144 如何获取指定目录下的文件列表 335Dir函数查找子文件夹的注意事项 337疑难145 安装多个版本的Excel时可以随心所欲地选择打开方式吗 338将Excel快捷方式导入“Sendto”文件夹 339疑难146 如何批量创建以本月每日的日期命名的文件夹 341利用DateSerial的纠错功能计算本月天数 3427.3 思考与练习 342第8章 VBA操作图表 3438.1 利用VBA批量修改图表格式 343疑难147 如何批量修改图表标签 343操作图表标签的条件 347疑难148 如何批量移位标签 347手工移位图表标签的限制 350疑难149 如何对图表系列设置条件格式 350填充图表系列的两种方法 354疑难150 可否一键统一图表大小并对齐 354图表名称的特点 3568.2 思考与练习 356第9章 VBA操作图形对象 3579.1 图形对象的批量操作 357疑难151 如何删除E列存放的图表以外的图形对象 357通过TopLeftCell和BottomRightCell定位图片位置 358疑难152 如何让所有的图形对齐所在单元格左边框 359通过统一Left属性对齐图片 359疑难153 如何一键导入工作簿所在路径下的家具图片 360录制“批量插入图片”的宏的缺陷 3619.2 批注的高级应用 362疑难154 如何将指定列的数据批量追加到批注中 362疑难155 如何批量导入图片到单元格的批注中 365在“打开”对话框中筛选文件格式的方法 366疑难156 可以对批注进行替换吗 367Unload Me、End和Exit Sub的区别 370疑难157 如何在所有的批注末尾追加日期 370通过设置AutoSize属性使批注框自动调整大小 371疑难158 如何批量修改批注的外观 372声明正确的变量类型及防错 3749.3 图形对象综合应用 374疑难159 如何创建图片目录与批量插入图片 374单元格的行高与列宽限制 378疑难160 如何实现输入品名时查看当前路径下的同名图片 378通过修改图像控件的Picture属性更新图片 379疑难161 如何实现批量导入图片且自动排版打印 380疑难162 可否以模糊匹配方式批量导入图片 384借用通配符“*”实现模糊匹配文件名称 387疑难163 如何开发批量删除指定类型的图形对象的工具 387如何遍历控件集合 3909.4 练习与思考 390第10章 窗体控件应用 39110.1 ActiveX控件应用 391疑难164 可以利用列表框强化数据有效性吗 391列表框相较数据有效性的灵活性 393疑难165 如何利用复合框引用区域的唯一值 394疑难166 能否利用复合框和列表框打造二级选单 396MouseMove事件获取组合框的值的技巧 39910.2 窗体设计技巧 400疑难167 如何用一个窗体展现多项不同的内容 400分页显示的两种方法 403疑难168 如何实现利用快捷键启用和关闭窗体 403借用命令按钮的Cancel属性快捷关闭窗体 405疑难169 如何用一个按钮执行多个功能 406利用PasswordChar属性隐藏敏感字符 408疑难170 如何让日期输入器窗体紧随活动单元格 409影响窗体Left、Top属性的因素 411疑难171 VBA可以设计彩蛋吗 412MouseUp事件中的Button与Shift参数的作用 414疑难172 如何通过窗体控制实现工作表隔行插入行 415通过Shift参数控制Insert方法的单元格移动方向 418疑难173 如何在窗体中开发鼠标右键快捷菜单 419创建自定义菜单的基本思路 420知识扩展 420疑难174 在窗体Show状态下可以调整控件大小吗 421通过鼠标指针的坐标变化同步更新控件的宽度和边距 423疑难175 在窗体Show状态下如何调整控件的位置 423MouseMove事件中Button参数的应用技巧 425疑难176 如何检测窗体中文本框的数据有效性 426对文本框设置数据有效性检验的优势 427疑难177 能否通过代码生成窗体及控件和代码 428用代码操作代码的必要条件 43010.3 窗体与工作表之数据交互 431疑难178 可否将多工作表中符合条件的值显示在窗体中 431列表框的ColumnHeads属性的特点 434疑难179 如何实现通过窗体向多工作表中录入数据 434疑难180 如何开发日历工具箱 438类的主要应用领域 442Excel对日期计算的限制 442疑难181 如何实现多条件电话查询 442TextBox1_Change事件与TextBox1_Exit事件的异同 444疑难182 如何开发复选框批量管理工具 445区分三种复选框控件 447疑难182 如何让窗体控件自适应工作表数据变化 448用代码创建新控件 450疑难184 可以让拆分工作簿的列表框具有拖放功能吗 451控件拖放相关的事件 454疑难185 如何设计对文件批量重命名的工具箱 45410.4 练习与思考 460第11章 功能区菜单与backstage视图设计 46111.1 创建功能区菜单 461疑难186 如何创建新选项卡及子菜单 461创建功能区菜单的语法与代码结构 463疑难187 如何在“开始”选项卡中插入弹出式菜单 466创建弹出式菜单的语法 467疑难188 如何创建对话框启动器 469在标签控件中让多段文字换行的技巧 471疑难189 如何在“视图”选项卡中插入“阅读模式”的切换按钮 472切换按钮与命令按钮的区别 474疑难190 如何将常用却又未显示在功能区中的按钮显示在“插入”选项卡中 475如何查看Excel内置命令的id 476疑难191 如何制作功能区代码模板 477模板的功能与制作技巧 47911.2 backstage视图设计 481疑难192 如何在“文件”菜单中添加命令按钮 481如何调用外部程序 483疑难193 能否在“文件”菜单中创建多组命令按钮 48311.3 思考与练习 486第12章 用VBA访问网络资源 48712.1 导入网页列表 487疑难194 如何一键导入最近三个月人民币与美元的历史汇率 487通过QueryTables.Add方法和QueryTable.Refresh方法导入网页数据 488疑难195 可否录入城市名称后自动列出火车票信息 489让Worksheet_Change事件仅作用于固定区域的技巧 49112.2 导入不规范的网页数据 491疑难196 能否批量计算手机号码的归属地、区号与卡类型 491分析网页源代码的基本思路 493疑难197 可否读取网页的天气预报数据 496查看网页源代码的方法 49712.3 练习与思考 499第13章 设计Excel通用工具 50013.1 开发Excel插件 500疑难198 如何开发定位工具箱扩展Excel定位的功能 500Excel定位的限制 506疑难199 可以利用插件一键录入常用VBA代码吗 507计算当前代码的行号 511疑难200 如何开发带功能区菜单的中英文互译插件 512用公式实现中英互译 51413.2 封装代码 515疑难201 如何将已编好的两段代码封装为COM加载项 515安装COM加载项的三种方法 522疑难202 如何封装包含工作簿事件的代码 522封装事件过程的基本思路 525疑难203 如何将自定义函数封装为COM加载项 525VBA中调用COM加载项中的函数的方法 52813.3 设计安装程序 529疑难204 如何将COM加载项封装为EXE格式的安装程序 529InnoSetup软件的参数说明 533疑难205 可否开发具有撤销功能且可单击卸载的插件 533使用回调函数动态更新菜单的指定属性 53713.4 练习与思考 538第14章 送货单套打程序的系统开发 53914.1 罗列需求 53914.2 设计表格 54014.2.1 工作表职能分配 54014.2.2 设计“配置”工作表 54014.2.3 设计“打印界面”工作表 54114.2.4 设计“明细表”工作表 54314.3 编写代码 54314.3.1 为品名、品牌和型号创建下拉列表 54314.3.2 开发小写金额转大写的函数 54614.3.3 生成送货单号 54614.3.4 打印并保存数据 54714.3.5 批量打印历史数据 54814.3.6 保存并清空数据 55014.3.7 创建数据透视表 55014.3.8 功能说明 55114.4 设计菜单 55114.5 测试功能 55214.5.1 测试“生成送货单号” 55314.5.2 测试品名、品牌与型号的录入方式 55314.5.3 测试“打印当前货单” 55414.5.4 测试“打印历史记录” 55514.5.5 测试“创建透视表” 55614.5.6 测试对话框启动器 55714.5.7 总结 557疑难5 如何开发完善的程序编写代码解决一个工作问题是很简单的,但是如何让程序完善,可以适应所有的环境,且通用、兼容、可防错,这是一门相当复杂的学问。那么如何开发一个完善的程序?如何及时地防止过程中的所有错误呢?解决方案通过常规思路开发一段程序完成基本需求,然后查找存在的问题并进行完善;再对新的过程审核是否有新问题,继续完善,直到代码可以应对一切外部环境。本节以“让用户从对话框输入一个值,并对该值开平方后写入活动单元格”为例,展示利用VBA解决此问题且逐步完善程序的过程。操作方法步骤1 按组合键打开VBE窗口,然后插入模块,并在模块中录入以下代码:Sub 获取平方根1() ‘第一次编写的代码,直接解决问题Dim Value As Long ‘声明一个Long型变量’让用户录入一个值,将该值赋予变量ValueValue = InputBox(”请输入数值:”, “待开方之数值”, 0)ActiveCell.Value = Sqr(Value) ‘对变量计算平方根End Sub步骤2 按键执行以上过程,并输入100或者789、123.455等数据进行测试,可以发现当前代码已实现需求的功能。然而,如果用户在对话框中单击“取消”按钮,那么程序会出错。用户很难通过出错提示了解出错的原因,而且假设后面还有其他代码,程序将不再执行。为了解决以上问题,修改代码为:Sub 获取平方根2() ‘解问按“取消”键问题Dim Value As VariantValue = InputBox(”请输入数值:”, “待开方之数值”, 0)If Len(Value) = 0 Then Exit Sub ‘如果变量的值长度为0,那么结束过程,不弹 ‘出错误提示ActiveCell.Value = Sqr(Value)End Sub步骤3 再次执行程序,单击“取消”按钮后会发现程序自动退出,不弹出错误提示,具有防错功能。也可以将“Exit sub”语句修改为其他代码,从而实现单击“取消”按钮后程序得以继续执行。不过当输入一个负数时,程序仍然会出错,且自动中断,因此需要继续改进代码。改进后的代码如下:Sub 获取平方根3() ‘解决负数问题Dim ValueValue = InputBox(”请输入数值:”, “待开方之数值”, 0)If Len(Value) = 0 Then Exit Sub ‘如果变量的值长度为0,那么结束过程,不弹 ‘出错误提示’若变量Value的值大于或等于0,则对变量开平方,且将结果存放在活动单元格,否则提示用户If Value >= 0 Then ActiveCell.Value = Sqr(Value) Else MsgBox “不能小于0″End Sub步骤4 当输入负数后,程序会提示用户且自动结束过程,但如果用户输入文本,那么程序仍然会出错,所以再次对代码做优化:Sub 获取平方根4() ‘解决文本问题Dim ValueValue = InputBox(”请输入数值:”, “待开方之数值”, 0)If Len(Value) = 0 Then Exit Sub ‘如果变量的值长度为0,那么结束过程,不’弹出错误提示If IsNumeric(Value) Then ‘如果变量Value的值是数值’若变量Value的值大于或等于0,则对变量开平方,且将结果存放在活动单元格,否则提示用户If Value <= 0 Then ActiveCell.Value = Sqr(Value) Else MsgBox "不能小于0"Else '否则,提示不能输入文本MsgBox "不能输入文本", 64, "提示"End IfEnd Sub步骤5 如果输入文本,程序具有了识别并警告用户的功能。然而,活动表是图表时,执行程序时仍然会出错。完善的程序需要处理所有的意外,那么程序可以做如下改进:Sub 获取平方根5() '解决图表问题Dim Value'如果活动表是图表,那么提示用户,而且结束过程If TypeName(ActiveSheet) = "Chart" Then MsgBox "不要选择图表": Exit SubValue = InputBox("请输入数值:", "待开方之数值", 0)If Len(Value) = 0 Then Exit Sub '如果变量的值长度为0,那么结束过程,不'弹出错误提示If IsNumeric(Value) Then '如果变量Value的值是数值'若变量Value的值大于或等于0,则对变量开平方,且将结果存放在活动单元格,否则提示用户If Value <= 0 Then ActiveCell.Value = Sqr(Value) Else MsgBox "不能小于0"Else '否则,提示不能输入文本MsgBox "不能输入文本", 64, "提示"End IfEnd Sub步骤6 如果工作表在被保护状态下执行以上程序仍然会出错,继续完善代码:Sub 获取平方根6() '解决工作表保护问题Dim Value'如果活动表是图表,那么提示用户,而且结束过程If TypeName(ActiveSheet) = "Chart" Then MsgBox "不要选择图表": Exit Sub'如果活动表处于保护状态,那么提示用户,然后结束过程If ActiveSheet.ProtectContents Then MsgBox "工作表已保护": Exit SubValue = InputBox("请输入数值:", "待开方之数值", 0)If Len(Value) = 0 Then Exit Sub '如果变量的值长度为0,那么结束过程,不'弹出错误提示If IsNumeric(Value) Then '如果变量Value的值是数值'若变量Value的值大于或等于0,那么对变量开平方,且将结果存放在活动单元格,否则提示用户If Value <= 0 Then ActiveCell.Value = Sqr(Value) Else MsgBox "不能小于0"Else '否则,提示不能输入文本MsgBox "不能输入文本", 64, "提示"End IfEnd Sub步骤7 如果活动单元格处于数组区域之间,程序仍然会产生错误,所以最后将代码优化为:Sub 获取平方根7() '解决数组区域问题Dim Value'如果活动表是图表,那么提示用户,而且结束过程If TypeName(ActiveSheet) = "Chart" Then MsgBox "不要选择图表": Exit Sub'如果活动表处于保护状态,那么提示用户,然后结束过程If ActiveSheet.ProtectContents Then MsgBox "工作表已保护": Exit SubValue = InputBox("请输入数值:", "待开方之数值", 0)If Len(Value) = 0 Then Exit Sub '如果变量的值长度为0,那么结束过程,不'弹出错误提示If IsNumeric(Value) Then '如果变量Value的值是数值On Error Resume Next '如果代码出错,继续执行下一步Debug.Print ActiveCell.CurrentArray '将活动单元格的当前数据区域地址输'出到立即窗口'如果没有错误(表示处于数组区域中),那么提示用户,然后结束过程If Err = 0 Then MsgBox "请不要选择数组区域": Exit Sub'若变量Value的值大于或等于0,则对变量开平方,且将结果存放在活动单元格,否则提示用户If Value <= 0 Then ActiveCell.Value = Sqr(Value) Else MsgBox "不能小于0"Else '否则,提示不能输入文本MsgBox "不能输入文本", 64, "提示"End IfEnd Sub原理分析编程的基本条件是准确性。然而程序除了准确以外,还必须具备防错和通用的特性,否则代码在当前状态下能正确执行,环境稍加变化就出现错误,将会增加维护成本。一个好的程序应该尽量通用于所有的状况,而本例正是通过一个典范来展示程序的完善过程,让读者了解程序可能出现的错误,并提供所有错误的解决之道。在实际工作中,都应该严格按此思路编写代码,提升程序的准确性、纠错性,同时也减少维护成本。知识扩展数组区域对VBA程序的影响 ?(1)区域数组公式是同时存在于多个连续单元格中带有“{}”标志的公式,将它输入到工作表后会占据一个区域的空间,而非单个单元格,该区域即为数组区域。它的特性是不能单独修改区域中任意一个单元格,如果代码修改其中一个单元格,程序会因出错而中断。(2)本例其实也可以利用“On Error Resume Next”语句一次性解决所有的问题,其代码如下。不过如果执行程序后得不到结果,就无法知道出错的原因。Sub 获取平方根8() '解决所有的问题On Error Resume NextActiveCell.Value = Sqr(Application.InputBox("请输入数值:", "开平方", 0, , , , , 1))End Sub注意本书中编程的主题是准确性、效率、防错性和兼容性,代码一定要对所有的错误进行防范,确保程序通用。然而为了节约篇幅,让书中展现更多的内容,我们尽量减少重复代码,对工作表是否保护、当前表是否为图表,以及活动单元格是否处于数组区域之间就不再对每个案例都进行判断了,只对其他出错的可能性进行防错。但读者在实际工作中应该全面防错。练习与思考1.在VBA编程过程中为什么要声明变量的数据类型及定义数据类型?2.变量与常量的区别是什么?3.执行代码时出错,都是因为代码书写有误造成的?4.使用什么语句可以使代码在执行过程中出错后仍然可以继续执行下去?5.执行代码过程中出现“下标越界”错误,通常是什么原因造成的?

赞(0)
未经允许不得转载:小苏子图书 » 疑难千寻千解丛书 Excel 2013 VBA编程与实践 内容简介