VBA常用技巧代码解析

Snow--dancer · 发表于 2009-2-17 11:23

呵呵非常感谢楼主

cxf641219 · 发表于 2009-2-17 15:00

很好！很好！很好！

yuanzhuping · 发表于 2009-2-17 16:07

技巧35 限制工作表的滚动区域
如果希望限制工作表中滚动的区域，可以通过设置WorkSheet对象的ScrollArea属性来实现。ScrollArea属性使用以A1样式的区域引用形式（字符串类型）返回或设置工作表允许滚动的区域。当设置了工作表滚动区域之后，用户不能选定滚动区域之外的单元格，但仍然可以选定区域之外的其他对象（例如图形、按钮等），同时工作表的一些相应功能可能被禁止（例如工作表全选、选中整行或整列等）。
在VBE中的工程管理窗口选择相应工作表对象，然后在其属性窗口中设置ScrollArea属性，即可限制工作表中滚动的区域，如图所示。

但是Excel不会记忆该项设置，当再次打开该工作簿时，ScrollArea属性将被重置，用户必须重新设置ScrollArea属性才能限制工作表中的滚动区域，解决方法是使用代码在工作簿打开时对ScrollArea属性进行设置，如下面的代码所示。

#001 Private Sub Workbook_Open()
#002 Sheet1.ScrollArea = "B4:H12"
#003 End Sub

复制代码

代码解析：
工作簿的Open事件，在打开该工作簿时设置Sheet1工作表的滚动区域为“B4:H12”单元格区域。
如果需要取消滚动区域的限制，可以将ScrollArea属性值设置为空，如下面的代码所示。
Sheet1.ScrollArea = ""

chury11 · 发表于 2009-2-17 17:20

版主努力啊

alphy · 发表于 2009-2-17 20:48

真是太好了
楼主辛苦！

yuanzhuping · 发表于 2009-2-17 21:37

技巧36 复制自动筛选后的数据区域
用户在对如图所示的数据列表进行自动筛选后，往往希望将自动筛选的结果复制到其它地方。

这时可以通过获取该列表区域中可见单元格的方法得到筛选结果的单元格区域，并复制到工作表Sheet2中，如下面的代码所示。

#001 Sub CopyFilter()
#002 Sheet2.Cells.Clear
#003 With Sheet1
#004 If .FilterMode Then
#005 .AutoFilter.Range.SpecialCells(12).Copy Sheet2.Cells(1, 1)
#006 End If
#007 End With
#008 End Sub

复制代码

代码解析：
   CopyFilter过程将Sheet1表中的筛选结果复制到工作表Sheet2中。
   第2行代码清除Sheet2表中数据。
   第4行代码判断Sheet1表是否处于自动筛选状态。FilterMode属性返回工作表是否处于筛选模式，如果指定工作表中包含已筛选序列且该序列中含有隐藏行，则该值为True。
   第5行代码通过AutoFilter对象的Range属性返回工作表的自动筛选列表区域，再使用SpecialCells方法获取该列表区域中可见单元格（SpecialCells方法请参阅技巧4 ），得到筛选结果的单元格区域，然后使用Copy方法将结果区域复制到工作表Sheet2中，应用于Range对象的Copy方法将单元格区域复制到指定的区域或剪贴板中，语法如下：
expression.Copy(Destination)
         参数expression是必需的，该表达式返回一个Range对象。
   参数Destination是可选的，指定区域要复制到的目标区域。如果省略该参数，则将该区域复制到剪贴板中。
   运行CopyFilter过程工作表Sheet2如图所示。

yuanzhuping · 发表于 2009-2-17 21:59

技巧37 使用高级筛选获得不重复记录
在如图所示的数据列表中，如果要将其中不重复的记录复制到另一工作表中，则可以通过高级筛选功能实现。

示例代码如下：

#001 Sub Filter()
#002 Sheet1.Range("A1").CurrentRegion.AdvancedFilter _
#003 Action:=xlFilterCopy, Unique:=True, _
#004 CopyToRange:=Sheet2.Range("A1")
#005 End Sub

复制代码

代码解析：
Filter过程使用AdvancedFilter方法对单元格A1的当前区域筛选不重复的记录，并将筛选结果复制到工作表Sheet2中。应用于Range集合的AdvancedFilter方法语法如下：
AdvancedFilter(Action, CriteriaRange, CopyToRange, Unique)
参数Action是必需的，可以为表格所列的 XlFilterAction常量之一。

参数CriteriaRange指定高级筛选操作的条件区域，缺省时表示没有条件限制。
参数CopyToRange表示指定被复制行的目标区域，仅当Action为xlFilterCopy时有效，否则忽略本参数。
参数Unique指示是否选择不重复的记录，如果其值为True，则重复出现的记录仅保留一条；如果其值为 False（默认值），则筛选出所有符合条件的记录。
运行FilterUnique过程，结果如图所示。

一_一 · 发表于 2009-2-17 23:46

yuan版您辛苦了,
每天必学,感觉进步好大
祝您一切顺利!!

nicholas_he · 发表于 2009-2-18 20:05

waiting for the next.....

yuanzhuping · 发表于 2009-2-18 20:46

技巧38       工作表的保护与解除保护
   在实际应用中，Excel编制的报表、表格、程序等，往往在单元格中设置了公式、函数、自定义格式等，为了防止在使用过程中修改或无意中修改这些设置，一般使用Excel的工作表保护功能来保护这些设置。
   但是程序中可能会使用代码对受保护的工作表进行操作，此时如果没有解除工作表保护，运行出现错误，如图所示。

解决方法是在运行操作工作表的代码前先使用代码解除工作表保护，待操作完毕后再保护工作表，如下面的代码所示。

#001 Sub ShProtect()
#002 With Sheet1
#003 .Unprotect Password:="12345"
#004 .Cells(1, 1) = 100
#005 .Protect Password:="12345"
#006 End With
#007 End Sub

复制代码

代码解析：
   ShProtect过程在受保护的工作表中对单元格进行操作，其中第3行代码使用Unprotect方法解除工作表的保护。应用于Worksheet 对象的Unprotect方法解除工作表的保护，如果工作表不是受保护的，则此方法不起作用，所以在解除之前无需判断工作表是否受保护，其语法如下：
expression.Unprotect(Password)
   参数expression是必需的，该表达式返回一个Worksheet 对象。
   参数Password是可选的，指定用于解除工作表的保护的密码，此密码是区分大小写的。
   第4、5行代码在单元格录入数据后使用Protect方法重新保护工作表。应用于Worksheet对象的Protect方法保护工作表使其不至被修改，语法如下：
expression.Protect(Password, DrawingObjects, Contents, Scenarios, UserInterfaceOnly, AllowFormattingCells, AllowFormattingColumns, AllowFormattingRows, AllowInsertingColumns, AllowInsertingRows, AllowInsertingHyperlinks, AllowDeletingColumns, AllowDeletingRows, AllowSorting, AllowFiltering, AllowUsingPivotTables)
   其中参数expression是必需的，该表达式返回一个Worksheet对象。
   参数Password是可选的，为一个字符串，该字符串为工作表指定区分大小写的密码。
   其他参数都是可选参数，其功能等同于如图所示的工作表保护对话框中的各项选项，具体请参阅VBA帮助。

如果一个Excel文件使用时间过长，忘记了工作表保护时设置的密码，那么也可以使用VBA解除工作表的保护，代码如下：

#001 Sub RemoveShProtect()
#002 Dim i1 As Integer, i2 As Integer, i3 As Integer
#003 Dim i4 As Integer, i5 As Integer, i6 As Integer
#004 Dim i7 As Integer, i8 As Integer, i9 As Integer
#005 Dim i10 As Integer, i11 As Integer, i12 As Integer
#006 On Error Resume Next
#007 If ActiveSheet.ProtectContents = False Then
#008 MsgBox "该工作表没有保护密码！"
#009 Exit Sub
#010 End If
#011 For i1 = 65 To 66: For i2 = 65 To 66: For i3 = 65 To 66
#012 For i4 = 65 To 66: For i5 = 65 To 66: For i6 = 65 To 66
#013 For i7 = 65 To 66: For i8 = 65 To 66: For i9 = 65 To 66
#014 For i10 = 65 To 66: For i11 = 65 To 66: For i12 = 32 To 126
#015 ActiveSheet.Unprotect Chr(i1) & Chr(i2) & Chr(i3) & Chr(i4) & Chr(i5) _
#016 & Chr(i6) & Chr(i7) & Chr(i8) & Chr(i9) & Chr(i10) & Chr(i11) & Chr(i12)
#017 If ActiveSheet.ProtectContents = False Then
#018 MsgBox "已经解除了工作表保护！"
#019 Exit Sub
#020 End If
#021 Next: Next: Next: Next: Next: Next
#022 Next: Next: Next: Next: Next: Next
#023 End Sub

复制代码

代码解析：
   RemoveShProtect过程解除工作表的保护。
   其中第7行到第10行代码判断工作表是否受保护，ProtectContents属性返回工作表的保护状态，如果工作表的内容处于保护状态，则该值为True。
   第11行到第22行代码使用For...Next 语句和Chr函数来返回指定字符码所代表的字符串组合不断地尝试解除工作表保护，一旦工作表的ProtectContents属性返回False说明已经解除工作表保护。

[ 本帖最后由 yuanzhuping 于 2009-2-19 07:52 编辑 ]

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

VBA常用技巧代码解析

第2部分Worksheet（工作表）对象

第2部分Worksheet（工作表）对象

第2部分Worksheet（工作表）对象

第2部分Worksheet（工作表）对象