Excel使用技巧

Excel使用技巧 第1章 VBA技巧 1.1 删除工作簿中的工作 Sheets(1).delete  or  sheets(“sheets”).delete 如果想在删除中不提示告警信息，则使用如下语句： Application.DisplayAlerts = False Sheets("数据源").Delete Application.DisplayAlerts = True 1.2 去除某一列的公式链接 Columns("E:F").Select    ‘选择两列 Range("E3").Activate    ‘激活选中的两列中的第一列的第一个非合并单元格 Selection.Copy        ‘复制选择的两列 Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False ‘选择性粘贴（只数值） 以上举例可利用录制宏的获得 1.3 将一个工作簿中的n个表复制到另一个工作簿中 Sheets(Array("数据源", "封面", "中国电信报价汇总")).Copy 注意：Array中的参数，也可以使用sheets(1).name表示，但容易出错，因为有时sheets对应不起来。 Worksheet.Copy 方法   将工作表复制到工作簿的另一位置。 语法 表达式.Copy(Before, After) 表达式   一个代表 Worksheet 对象的变量。 参数 名称 必选/可选 数据类型 描述 Before 可选 Variant 将要在其之前放置所复制工作表的工作表。如果指定了 After，则不能指定 Before。 After 可选 Variant 将要在其之后放置所复制工作表的工作表。如果指定了 Before，则不能指定 After。         说明 如果既不指定 Before 也不指定 After，则 Microsoft Excel 将新建一个工作簿，其中包含复制的工作表。 示例 此示例复制工作表 Sheet1，并将其放置在工作表 Sheet3 之后。 1.4 更改工作簿中的表的名字 Sheets(1).Name=”工作表名” 1.5 移动/复制工作 Sheets(2).copy before:=Sheets(1) Sheets(1).copy after:=Sheets(2) 1.6 在excel的VBA中动态创建文件保存的文件夹 MKDIR “aaa”        ‘说明：在当前工作簿所在目录下创建新文件夹aaa ActiveWorkbook.saveas Filename:=ThisWorkBook.path & “\aaa\excel1.xls” ‘将当前工作簿另存在aaa文件夹中。 1.7 动态修改工作表名 1.7.1 方法一：宏 创建下拉菜单窗体，关联问题为D2，指定以下宏： Sub Macro1() Sheets(1).Name = Sheets(1).Cells(2, 4) Sheet2.Name = Sheet1.Cells(2, 4) & "asf" End Sub 1.7.2 方法二：表中过程 在当前表中增加如下过程： Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo err1 If Target.Address = "$B$1" Then Me.Name = Target End If Exit Sub err1: Range("B1") = Me.Name End Sub 例如： 1.8 序列和窗体下拉菜单的区别 序列中的数据源，必须为“=sheet1.$a$1:$a$4”格式 窗体下拉菜单的数据源，为“sheet1.$a$1:$a$4”格式 因此，可以在序列中使用=indirect(C1),C1=区域名称，而在窗体下拉菜单中直接使用C1或区域名称，如data，data=$a$1:$a10 问题：窗体下拉菜单中只有数据源为列数据时才能正常显示，否则只能显示第一个单元数据 1.9 复制/粘贴 应用于 Worksheet 对象。 本示例将 Sheet1 上单元格区域 C1:C5 中的数据复制到单元格区域 D1:D5 中。 Worksheets("Sheet1").Range("C1:C5").Copy ActiveSheet.Paste Destination:=Worksheets("Sheet1").Range("D1:D5") ActiveWorkbook.Sheets(2).Cells(i - 3, 1).Select ActiveSheet.Paste 或 Sheets(2).Paste 或 Sheets("sheet1").Paste Destination:=Worksheets("Sheet1").Cells(i - 3, 1) With Worksheets("Sheet1") .Range("C1:C5").Copy .Range("D1:D5").PasteSpecial _ Operation:=xlPasteSpecialOperationAdd End With 1.10 其他 定制模块行为 (1) Option Explicit '强制对模块内所有变量进行声明 Option Private Module '标记模块为私有，仅对同一工程中其它模块有用，在宏对话框中不显示 Option Compare Text '字符串不区分大小写 Option Base 1 '指定数组的第一个下标为1 (2) On Error Resume Next '忽略错误继续执行VBA代码,避免出现错误消息 (3) On Error GoTo ErrorHandler '当错误发生时跳转到过程中的某个位置 (4) On Error GoTo 0 '恢复正常的错误提示 (5) Application.DisplayAlerts=False '在程序执行过程中使出现的警告框不显示 (6) Application.ScreenUpdating=False '关闭屏幕刷新 Application.ScreenUpdating=True '打开屏幕刷新 (7) Application.Enable.CancelKey=xlDisabled '禁用Ctrl+Break中止宏运行的功能 工作簿 (8) Workbooks.Add() '创建一个新的工作簿 (9) Workbooks(“book1.xls”).Activate '激活名为book1的工作簿 (10) ThisWorkbook.Save '保存工作簿 (11) ThisWorkbook.close '关闭当前工作簿 (12) ActiveWorkbook.Sheets.Count '获取活动工作薄中工作表数 (13) ActiveWorkbook.name '返回活动工作薄的名称 (14) ThisWorkbook.Name ‘返回当前工作簿名称 ThisWorkbook.FullName ‘返回当前工作簿路径和名称 (15) ActiveWindow.EnableResize=False ‘禁止调整活动工作簿的大小 (16) Application.Window.Arrange xlArrangeStyleTiled ‘将工作簿以平铺方式排列 (17) ActiveWorkbook.WindowState=xlMaximized ‘将当前工作簿最大化 工作表 (18) ActiveSheet.UsedRange.Rows.Count ‘当前工作表中已使用的行数 (19) Rows.Count ‘获取工作表的行数(注：考虑向前兼容性) (20) Sheets(Sheet1).Name= “Sum” '将Sheet1命名为Sum (21) ThisWorkbook.Sheets.Add Before:=Worksheets(1) '添加一个新工作表在第一工作表前 (22) ActiveSheet.Move After:=ActiveWorkbook. _ Sheets(ActiveWorkbook.Sheets.Count) '将当前工作表移至工作表的最后 (23) Worksheets(Array(“sheet1”,”sheet2”)).Select '同时选择工作表1和工作表2 (24) Sheets(“sheet1”).Delete或 Sheets(1).Delete '删除工作表1 (25) ActiveWorkbook.Sheets(i).Name '获取工作表i的名称 (26) ActiveWindow.DisplayGridlines=Not ActiveWindow.DisplayGridlines '切换工作表中的网格线显示，这种方法也可以用在其它方面进行相互切换，即相当于开关按钮 (27) ActiveWindow.DisplayHeadings=Not ActiveWindow.DisplayHeadings ‘切换工作表中的行列边框显示 (28) ActiveSheet.UsedRange.FormatConditions.Delete ‘删除当前工作表中所有的条件格式 (29) Cells.Hyperlinks.Delete ‘取消当前工作表所有超链接 (30) ActiveSheet.PageSetup.Orientation=xlLandscape 或ActiveSheet.PageSetup.Orientation=2 '将页面设置更改为横向 1.11 获取“另存为文件名”及有选择的将sheet保存为另一个工作簿 Private Sub RackExport_Click() Dim MyFileName MyFileName = Application.GetSaveAsFilename & "xlsx" Worksheets(Array("机架图", "机架表")).Copy ActiveWorkbook.SaveAs (MyFileName) ActiveWorkbook.Close End Sub 1.12 删除VBA模块!! 2007-10-25 22:28 237人阅读 评论(0) 收藏 举报 application.VBE.VBProjects(x).VBComponents(x).CodeModule.DeleteLines 1, x 必须先引用:Microsoft Visual Basic for Applications Extensibility 5.3(我的是office 03) Application.VBE.ActiveVBProject.VBComponents.Remove Application.VBE.ActiveVBProject.VBComponents("模块1")  而且要在宏安全性里勾选"信任对于"Visual Basic项目"的访问(V)". Excel文档VBA代码自动删除 有时候，不想让自己编写的代码让别人长期使用或换地方使用，又不想让文档自杀，就可以设计限期清除代码，或其他限制条件下清除。具体限制条件可参考作者《Excel文档自杀程序设计》Workbook_Open模块那一部分。代码清除后，与代码有关的程序就不能运行了。 如设置2011年12月31日后打开文档就清除代码，可以这样设计： Private Sub Workbook_Open()   ’这个程序一定要在Thisworkbook模块中 On Error Resume Next