VBA常用技巧

VBA常用技巧

vba常用技巧解析

目录

第1章range（单元格）对象--------------------------------------------------------------------10

技巧1单元格的引用方法----------------------------------------------------------------------10

1-1采用range属性-----------------------------------------------------------------------101-2采用cells属性------------------------------------------------------------------------111-3采用便捷记号--------------------------------------------------------------------------111-4采用offset属性-----------------------------------------------------------------------121-5采用resize属性-----------------------------------------------------------------------131-6采用union方法-----------------------------------------------------------------------141-7采用usedrange属性----------------------------------------------------------------141-8采用currentregion属性------------------------------------------------------------15技巧2选取单元格区域的方法----------------------------------------------------------------15

2-1使用select方法-----------------------------------------------------------------------152-2使用activate方法--------------------------------------------------------------------162-3使用goto方法-------------------------------------------------------------------------17技巧3获得指定行、列中的最后一个非空单元格--------------------------------------17技巧4定位单元格-------------------------------------------------------------------------------20技巧5查找单元格-------------------------------------------------------------------------------21

5-1采用find方法-------------------------------------------------------------------------215-2采用like运算符----------------------------------------------------------------------25技巧6替代单元格内字符串-------------------------------------------------------------------26技巧7激活单元格区域-------------------------------------------------------------------------27技巧8仅激活数值至另一区域----------------------------------------------------------------30

8-1使用选择性粘贴-----------------------------------------------------------------------308-2直接赋值的方法---------------------------------------------

--------------------------31技巧9单元格自动进入编辑状态------------------------------------------------------------32技巧10禁用单元格拖放功能-----------------------------------------------------------------32技巧11单元格格式操作-----------------------------------------------------------------------33

11-1单元格字体格式设置---------------------------------------------------------------3311-2设置单元格内部格式---------------------------------------------------------------3511-3为单元格区域嵌入边框------------------------------------------------------------3611-4有效率设置单元格的行高列阔------------------------------------------------------38技巧12单元格中的数据有效性--------------------------------------------------------------39

12-1在单元格中建立数据有效性------------------------------------------------------3912-2判断单元格是否存在数据有效性------------------------------------------------4112-3动态的数据有效性------------------------------------------------------------------4112-4自动展开数据有效性下拉列表---------------------------------------------------43技巧13单元格中的公式-----------------------------------------------------------------------44

1

vba常用技巧解析

13-1在单元格中载入公式---------------------------------------------------------------4413-2检查单元格与否所含公式---------------------------------------------------------4513-3推论单元格公式与否存有错误---------------------------------------------------4613-4获得单元格中公式的提及单元格------------------------------------------------4713-5将单元格中的公式切换为数值---------------------------------------------------48技巧14单元格中的注释-----------------------------------------------------------------------49

14-1判断单元格是否存在批注---------------------------------------------------------4914-2为单元格添加批注------------------------------------------------------------------5014-3删除单元格中的批注---------------------------------------------------------------51技巧15合并单元格操作-----------------------------------------------------------------------52

15-1推论单元格区域与否存有分拆单元格-----------------------------------------5215-2分拆单元格时相连接每个单元格的文本-----------------------------------------5315-3分拆内容相同的已连续单元格------------------------------------------------------5415-4中止分拆单元格时在每个单元格中留存内容--------------------------------56技巧16高亮表明单元格区域---------------------------------------

--------------------------57技巧17双击被维护单元格时不表明提示信息消息侧边----------------------------------------58技巧18再次排序工作表选定区域-----------------------------------------------------------60技巧19打印数据后单元格自动维护--------------------------------------------------------60技巧20工作表事件target参数的采用方法----------------------------------------------62

20-1使用单元格的address属性------------------------------------------------------6220-2使用column属性和row属性--------------------------------------------------6320-3使用intersect方法-------------------------------------------------------------------63

第2章worksheet（工作表）对象---------------------------------------------------------------65

技巧21引用工作表的方式--------------------------------------------------------------------65

21-1采用工作表的名称------------------------------------------------------------------6521-2采用工作表的索引号---------------------------------------------------------------6521-3采用工作表的代码名称------------------------------------------------------------6621-4采用activesheet属性提及活动工作表----------------------------------------66技巧22挑选工作表的方法--------------------------------------------------------------------67技巧23结点工作表的方法--------------------------------------------------------------------68

23-1使用语句----------------------------------------------------------------6823-2使用语句---------------------------------------------------------70技巧24在工作表中上下翻页-----------------------------------------------------------------71技巧25工作表的添加与删除-----------------------------------------------------------------72技巧26禁止删除指定工作表-----------------------------------------------------------------76技巧27自动建立工作表目录-----------------------------------------------------------------78技巧28工作表的深度隐藏--------------------------------------------------------------------80技巧29防止更改工作表的名称--------------------------------------------------------------82技巧30工作表中一次插入多行--------------------------------------------------------------83技巧31删除工作表中的空行-----------------------------------------------------------------84技巧32删除工作表的重复行-----------------------------------------------------------------86技巧33定位删除特定内容所在的行--------------------------------------------------------88技巧34判断是否选中整行---------------------------------------------------

-----------------89技巧35限制工作表的滚动区域--------------------------------------------------------------90

2

vba常用技巧解析

技巧36激活自动甄选后的数据区域--------------------------------------------------------91技巧37采用高级甄选赢得不重复记录-----------------------------------------------------93技巧38工作表的维护与中止维护-----------------------------------------------------------94技巧39奇偶页列印------------------------------------------------------------------------------97第3章wordbook（工作簿）对象---------------------------------------------------------------99

技巧40工作簿的引用方法--------------------------------------------------------------------99

40-1采用工作簿的名称------------------------------------------------------------------9940-2采用工作簿的索引号---------------------------------------------------------------9940-3采用thisworkbook----------------------------------------------------------------10040-4采用activeworkbook-------------------------------------------------------------101技巧41新建工作簿文件---------------------------------------------------------------------101技巧42关上选定的工作簿------------------------------------------------------------------103技巧43推论选定工作簿与否关上---------------------------------------------------------106

43-1遍历workbooks集合方法-------------------------------------------------------10643-2错误处理方法-----------------------------------------------------------------------106技巧44禁用宏则关闭工作簿---------------------------------------------------------------107技巧45关闭工作簿不显示保存对话框---------------------------------------------------111

45-1采用close方法停用工作簿-----------------------------------------------------11145-2单击工作簿关闭按钮停用工作簿----------------------------------------------113技巧46停止使用工作簿的关闭按钮------------------------------------------------------------113技巧47留存工作簿的方法------------------------------------------------------------------115

47-1使用save方法---------------------------------------------------------------------11547-2直接保存为另一文件名----------------------------------------------------------11547-3保存工作簿副本--------------------------------------

-----------------------------115技巧48保存指定工作表为工作簿文件---------------------------------------------------116技巧49打印预览时不触发事件------------------------------------------------------------118技巧50设置工作簿文档属性信息---------------------------------------------------------120技巧51不打开工作簿取得其他工作簿数据--------------------------------------------121

51-1采用公式-----------------------------------------------------------------------------12151-2采用getobject函数--------------------------------------------------------------12251-3暗藏application对象-------------------------------------------------------------12351-4采用executeexcel4macro方法------------------------------------------------12451-5采用sql相连接---------------------------------------------------------------------125技巧52回到窗口的对齐区域地址---------------------------------------------------------126第4章shape（图形）、chart（图表）对象------------------------------------------------128

技巧53在工作表中添加图形---------------------------------------------------------------128技巧54导出工作表中的图片---------------------------------------------------------------133技巧55在工作表中添加艺术字------------------------------------------------------------135技巧56遍历工作表中的图形---------------------------------------------------------------137技巧57移动、旋转图片---------------------------------------------------------------------139技巧58工作表中自动插入图片------------------------------------------------------------140技巧59固定工作表中图形的位置---------------------------------------------------------143技巧60使用vba自动生成图表----------------------------------------------------------145技巧61使用独立窗口显示图表------------------------------------------------------------149

3

vba常用技巧解析

技巧62求出工作表中的图表---------------------------------------------------------------150技巧63多图表制作----------------------------------------------------------------------------151第5章application对象--------------------------------------------------------------------------155

技巧64取得excel版本信息----------------------------------------------------------------155技巧65取得当前用户名称------------------------------------------------------------------156技巧66excel中的“定时器”--------------------------------------------------------------156技巧67设置活动打印机的名称------------------------------------------------------------158技巧68屏蔽、改变组合

键的功能---------------------------------------------------------159技巧69设置excel窗口标题栏-------------------------------------------------------------160技巧70自定义excel状态栏----------------------------------------------------------------161技巧71灵活退出excel-----------------------------------------------------------------------162技巧72隐藏excel主窗口-------------------------------------------------------------------163

72-1设置application对象的visible属性-----------------------------------------16372-2将窗口抽走屏幕-------------------------------------------------------------------16472-3设置工作簿做为读取宏运转----------------------------------------------------165

第6章使用对话框--------------------------------------------------------------------------------167

技巧73采用msgbox函数-------------------------------------------------------------------167

73-1显示简单的提示信息-------------------------------------------------------------16773-2定制个性化的消息框-------------------------------------------------------------16873-3获得消息框的返回值-------------------------------------------------------------16973-4在消息框中排版-------------------------------------------------------------------17173-5对齐消息框中显示的信息-------------------------------------------------------172技巧74自动关闭的消息框------------------------------------------------------------------174

74-1采用方法表明消息侧边---------------------------------------17474-2采用api函数表明消息侧边-------------------------------------------------------175技巧75采用inputbox函数-----------------------------------------------------------------176

75-1简单的数据输入-------------------------------------------------------------------17675-2使用对话框输入密码-------------------------------------------------------------178技巧76使用inputbox方法-----------------------------------------------------------------180

76-1输出选定类型的数据-------------------------------------------------------------18076-2赢得单元格区域地址-------------------------------------------------------------182技巧77内置对话框----------------------------------------------------------------------------183

77-1调用内置的对话框----------------------------------------------------------------18377-2获取选定文件的文件名----------------------------------------------------------18677-3使用“另存为”对话框----------------------------------------------------------188技巧78调用操作系统“关于”对话框------------------------------------------------------190第7章菜单和工具栏-----------------------------------------------------------------------------192

技巧79在菜单中嵌入菜单项---------------------------------------------------------------192技巧80在菜单栏选定边线嵌入菜单------------------------------------------------------195技巧81屏蔽和删掉工作表菜单------------------------------------------------------------197技巧82发生改变系统菜单的操作方式---------------------------------------------------------------198技巧83订制自己的系统菜单---------------------------------------------------------------199技巧84发生改变菜单按钮图标------------------------------------------------------------------205技巧85右键便捷菜单减少菜单项---------------------------------------------------------206

4

vba常用技巧解析

技巧86自定义右键快捷菜单---------------------------------------------------------------207技巧87使用右键菜单制作数据有效性---------------------------------------------------210技巧88禁用工作表右键菜单---------------------------------------------------------------212技巧89创建自定义工具栏------------------------------------------------------------------213技巧90自定义工具栏按钮图标------------------------------------------------------------216技巧91自定义工作簿图标------------------------------------------------------------------217技巧92移除工作表的最小最大化和关闭按钮-----------------------------------------218技巧93在工具栏上添加下拉列表框------------------------------------------------------219技巧94屏蔽工作表的复制功能------------------------------------------------------------221技巧95禁用工具栏的自定义---------------------------------------------------------------222技巧96屏蔽所有的命令栏------------------------------------------------------------------225技巧97恢复excel的命令栏----------------------------------------------------------------226第8章控件与用户窗体--------------------------------------------------------------------------228

技巧98管制文本框的输出------------------------------------------------------------------228技巧99文本框嵌入右键便捷菜单---------------------------------------------------------230技巧100文本框contrary自动输出------------------

-------------------------------------------234技巧101自动挑选文本框内容-------------------------------------------------------------235技巧102设置文本框数据格式-------------------------------------------------------------236技巧103管制文本框的输出长度----------------------------------------------------------238技巧104将光标回到文本框中-------------------------------------------------------------239技巧105文本框的自动下划线-----------------------------------------------------------------241技巧106多个文本框数据相乘-------------------------------------------------------------243技巧107控件追随活动单元格-------------------------------------------------------------244技巧108高亮表明按钮-----------------------------------------------------------------------245技巧109女团侧边和列表框嵌入列表项的方法-------------------------------------------247

109-1使用rowsource属性添加列表项--------------------------------------------247109-2使用list属性添加列表项-----------------------------------------------------248109-3使用additem方法添加列表项-----------------------------------------------249技巧110去除列表框数据源的重复值和空格-------------------------------------------251技巧111移动列表框条目--------------------------------------------------------------------253技巧112允许多项选择的列表框-----------------------------------------------------------256技巧113多列组合框和列表框的设置----------------------------------------------------259

113-1多列女团侧边和列表框嵌入列表项--------------------------------------------259113-2多列列表框载入工作表---------------------------------------------------------261技巧114输出时逐步提示信息--------------------------------------------------------------263技巧115二级女团侧边--------------------------------------------------------------------------270技巧116采用dtp控件输出日期---------------------------------------------------------272技巧117采用refedit控件挑选区域-----------------------------------------------------275技巧118如何登记注册控件-----------------------------------------------------------------------276技巧119结点控件的方法--------------------------------------------------------------------279

119-1使用名称中的变量遍历控件--------------------------------------------------279119-2使用对象类型遍历控件---------------------------------------------------------281119-3使用程序标识符遍历控件------------------------------------------------------282119-4使用名称中的变量遍历图形--------------------------------------------------283

5