VBA常用技巧代码解析

yuanzhuping · 发表于 2009-2-25 07:58

技巧48 保存指定工作表为工作簿文件
如果需要将工作簿中的工作表单独保存为一个工作簿文件，可以使用Worksheet对象的Copy方法，将指定的工作表复制到一个新建的工作簿，如下面的代码所示。

#001 Sub SheetCopy()
#002 On Error GoTo line
#003 ActiveSheet.Copy
#004 ActiveWorkbook.Close SaveChanges:=True, Filename:=ThisWorkbook.Path & "\SheetCopy.xls"
#005 Exit Sub
#006 line:
#007 ActiveWorkbook.Close False
#008 End Sub

复制代码

代码解析：
SheetCopy过程将活动工作表单独保存为一个工作簿文件。
第2行代码错误处理语句。备份过程中，如果已存在同名工作簿，会出现如图所示的提示，如果选择了“否”或“取消”，此时新工作簿已经建立，在执行4行代码时发生错误，使程序中断，所以使用GoTo语句执行第7行代码，关闭新建立的工作簿并且不保存。

   第3行代码使用Copy方法新建一个工作簿，新工作簿中包含复制的工作表。应用于Worksheet对象的Copy方法将指定工作表复制到工作簿的另一位置，语法如下：
Copy (Before, After)
   其中，参数Before是可选的，用来指定工作表，复制的工作表将置于此工作表之前。参数After是可选的，用来指定工作表，复制的工作表将置于此工作表之后。
   不能同时指定Before参数和After参数。当Copy方法省略参数时，应用程序将新建一个空工作簿（新建工作簿将成为活动窗口），并将Copy方法引用的工作表复制到该空工作簿中。
   第4行代码使用Workbook对象的Close方法关闭新建的工作簿。应用于Workbooks集合和Workbook对象的Close方法请参阅技巧45-1。
   如果需要将工作簿中的几个工作表单独保存为一个工作簿文件时，可以以数组的形式指定要复制的工作表，如下面的代码所示。

#001 Sub ArrSheetCopy()
#002 On Error GoTo line
#003 Worksheets(Array("Sheet1", "Sheet2")).Copy
#004 ActiveWorkbook.SaveAs Filename:=ThisWorkbook.Path & "\ArrSheetCopy.xls"
#005 ActiveWorkbook.Close SaveChanges:=True
#006 Exit Sub
#007 line:
#008 ActiveWorkbook.Close False
#009 End Sub

复制代码

代码解析：
ArrSheetCopy过程将“Sheet1”和“Sheet2”工作表单独保存为一个工作簿文件。
第4行代码使用SaveAs方法保存活动工作簿，关于SaveAs方法请参阅技巧47-2。

[ 本帖最后由 yuanzhuping 于 2009-3-13 17:17 编辑 ]

asan06 · 发表于 2009-2-25 12:28

天啊，太博大精深了，光看标题就另我头晕眼花的了

love_5000 · 发表于 2009-2-25 13:15

gusuke · 发表于 2009-2-25 13:45

VBA资料正好现在学习，谢谢班主

yuanzhuping · 发表于 2009-2-25 15:25

技巧49 打印预览时不触发事件
在工作表打印之前或进行打印预览时，会触发工作簿的BeforePrint事件。在某些情况下希望在打印预览时能禁止触发该事件，例如如图所示的工作表中，用户在打印时使用下面的代码将流水号的数值自动加1。

#001 Private Sub Workbook_BeforePrint(Cancel As Boolean)
#002 Sheet1.Range("J1") = Sheet1.Range("J1") + 1
#003 End Sub

复制代码

但是在打印预览时并不希望流水号的数值自动加1，此时，需要修改系统的打印预览功能，如下面的代码所示。

#001 Private Sub Workbook_Open()
#002 Dim CmdCtrls As CommandBarControls
#003 Dim Cmd As CommandBarControl
#004 Set CmdCtrls = Application.CommandBars.FindControls(ID:=109)
#005 For Each Cmd In CmdCtrls
#006 Cmd.OnAction = "ThisWorkbook.MyPrint"
#007 Next
#008 End Sub

复制代码

代码解析：
   工作簿的Open事件过程，在打开工作簿时，修改系统中所有打印预览命令按钮和菜单项的动作，指定其OnAction属性为ThisWorkbook代码窗口中的公用过程MyPrint。
   第4行代码使用FindControls方法将所有打印预览命令按钮和菜单项赋给变量CmdCtrls，FindControls方法返回符合指定条件的CommandBarControls集合，语法如下：
expression.FindControls(Type, Id, Tag, Visible)
   其中参数expression是必需的，该表达式返回一个CommandBars集合。
   参数Id是可选的，要查找控件的标识符。打印预览命令控件的标识符为109。
   第5行到第7行代码遍历所有打印预览命令控件，指定其OnAction属性为ThisWorkbook代码窗口中的公用过程MyPrint。OnAction属性返回或设置一个Visual Basic 的过程名，该过程在用户单击或更改某命令栏控件的值时运行。
MyPrint过程代码如下：

#001 Public Sub MyPrint()
#002 With Application
#003 .EnableEvents = False
#004 .ActiveSheet.PrintPreview EnableChanges:=False
#005 .EnableEvents = True
#006 End With
#007 End Sub

复制代码

代码解析：
   MyPrint过程通过禁止对象事件，使工作表打印预览时不触发工作簿的BeforePrint事件。
   第3行代码将Application对象的EnableEvents属性设置为False，禁用事件，使事件不能触发。
   第4行代码使用PrintPreview方法对工作表执行打印预览。PrintPreview方法以打印效果显示指定的对象，该方法只有一个参数EnableChanges，用来指定是否可以修改页面设置，当其值为False时，禁止在打印预览时修改页面设置，默认值为True。
   第5行代码将Application对象的EnableEvents属性设置为True，启用事件。
   为了在工作簿时恢复默认的打印预览设置，在ThisWorkbook代码窗口写入以下代码：

#001 Private Sub Workbook_BeforeClose(Cancel As Boolean)
#002 Dim CmdCtrls As CommandBarControls
#003 Dim Cmd As CommandBarControl
#004 Set CmdCtrls = Application.CommandBars.FindControls(ID:=109)
#005 For Each Cmd In CmdCtrls
#006 Cmd.OnAction = ""
#007 Next
#008 End Sub

复制代码

代码解析：
工作簿的BeforeClose事件过程，关闭工作簿时将所有打印预览命令按钮和菜单项的OnAction属性恢复为默认的动作。
经过以上设置，工作表只有在进行打印时“流水号”数值才自动加1。

[ 本帖最后由 yuanzhuping 于 2009-2-25 17:27 编辑 ]

yuanzhuping · 发表于 2009-2-25 20:34

技巧50 设置工作簿文档属性信息
使用DocumentProperties集合对象的BuiltinDocumentProperties属性可以设置文档的属性信息，如下面的代码所示。

#001 Sub WbBuiltin()
#002 With ThisWorkbook
#003 .BuiltinDocumentProperties("Title") = "Wordbook（工作簿）对象"
#004 .BuiltinDocumentProperties("Subject") = "设置工作簿的文档属性信息"
#005 .BuiltinDocumentProperties("Author") = "yuanzhuping"
#006 .BuiltinDocumentProperties("Company") = "tzzls"
#007 .BuiltinDocumentProperties("Comments") = "工作簿文档属性信息"
#008 .BuiltinDocumentProperties("Keywords") = "Excel VBA"
#009 End With
#010 MsgBox "工作簿文档属性信息设置完毕！"
#011 End Sub

复制代码

代码解析：
WbBuiltin过程设置代码所在工作簿的属性信息，应用于Workbook对象的BuiltinDocumentProperties属性返回一个DocumentProperties集合，该集合代表指定工作簿的所有内置文档属性，本属性返回的是内置文档属性的整个集合。通过指定属性的名称或集合中的索引号返回集合中的单个成员（一个DocumentProperty对象）。
第3行代码设置标题，第4行代码设置主题，第5行代码设置作者，第6行代码设置公司，第7行代码设置备注，第8行代码设置关键字。

menghuazhang · 发表于 2009-2-25 21:11

各位老师，技巧46 禁用工作簿的关闭按钮。在sheet1中，那个关闭按钮是怎么做出来的？
入门学习时最先学做的是Command Button1 ，触发事件利用单击按钮来触发代码执行程序，但这个例子好像没有Command Button?
请教。。。。。。

menghuazhang · 发表于 2009-2-25 21:21

谢谢236楼的一_一，非常感谢你的分享，能否将代码或是附件传上，以便我这个菜鸟拿回研习？

期待你的恢复-------关于删除所有Sheet1 中的空行。还有，如果我要是再增加一条需求：除了删除整个空行，如果在某一行中在规定列（B列单元格不为空，但该行的其他单元格为空则删除该行）

期待您的回复，等待你的关注。。。。。。。

谢谢。。。。。。

hongjiny · 发表于 2009-2-25 21:51

謝謝版主的無私奉獻

yumoym · 发表于 2009-2-26 11:37

给我最尊重的版主挑个小瑕疵
版主说：“此方法（Address引用法）只适用于单个单元格或者加上OR运算符可以适用于几个单元格，多则不方便。”，可我发现address属性也可以返回区域名，如A1:B2之类的呀！

		自动登录	找回密码
密码			免费注册

VBA常用技巧代码解析

第3部分 Wordbook（工作簿）对象

评分

评分

第3部分 Wordbook（工作簿）对象

点评

评分

第3部分 Wordbook（工作簿）对象

来自菜鸟的问题---禁用工作簿的关闭按钮的请教

236楼的一_一请赐教代码

回复 95楼 yuanzhuping 的帖子

VBA常用技巧代码解析

第3部分 Wordbook（工作簿）对象

评分

评分

第3部分 Wordbook（工作簿）对象

点评

评分

第3部分 Wordbook（工作簿）对象

来自菜鸟的问题---禁用工作簿的关闭按钮的请教

236楼的一_一 请赐教代码

回复 95楼 yuanzhuping 的帖子

236楼的一_一请赐教代码