VBA常用技巧代码解析

yuanzhuping

技巧190         优化代码
190-2        使用工作表函数
       在VBA中使用工作表函数比仅仅使用VBA代码的运行时间要快得多，如下面的代码所示。

1. #001  Sub ShFunction()
   
2. #002      Dim i As Integer
   
3. #003      Dim t As Date
   
4. #004      Dim t1 As String
   
5. #005      Dim t2 As String
   
6. #006      Range("B1:B2").ClearContents
   
7. #007      Application.ScreenUpdating = False
   
8. #008      t = Timer
   
9. #009      For i = 1 To 30000
   
10. #010          Cells(1, 2) = Cells(1, 2) + Cells(i, 1)
    
11. #011      Next
    
12. #012      t1 = Timer - t
    
13. #013      t = Timer
    
14. #014      Cells(2, 2) = Application.WorksheetFunction.Sum(Range("A1:A30000"))
    
15. #015      t2 = Timer - t
    
16. #016      Application.ScreenUpdating = True
    
17. #017      MsgBox "第一次运行时间:" & Format(t1, "0.00000") & "秒" _
    
18. #018           & Chr(13) & "第二次运行时间:" & Format(t2, "0.00000") & "秒"
    
19. #019  End Sub

复制代码

代码解析：
       ShFunction过程分别使用VBA代码和调用工作表Sum函数对单元格区域进行求和计算，最后使用消息显示运行时间。
       第9行到第11行代码使用VBA的累加方法计算单元格A1：A30000的和。
       第14行代码调用工作表Sum函数计算单元格A1：A30000的和。VBA中调用工作表函数请参阅技巧153 。
       运行ShFunction过程，消息框显示两种方法的运行时间，可以看出调用工作表函数进行计算的运行时间要远远小于使用累加方法运行的时间，如图所示。
      

胡剑0227

哇，太丰富了，感动ing

yuanzhuping

技巧190         优化代码
190-3        使用更快的单元格操作方法
       在对单元格区域进行操作时，使用Find、Replace、SpecialCells等方法可以比使用VBA代码获得更快的速度，如下面的代码所示。

1. #001  Sub Methods()
   
2. #002      Dim arr As Variant
   
3. #003      Dim i As Long
   
4. #004      Dim t As Date
   
5. #005      Dim t1 As String
   
6. #006      Dim t2 As String
   
7. #007      With Range("A1:A20000")
   
8. #008          arr = .Value
   
9. #009          t = Timer
   
10. #010          For i = 20000 To 1 Step -1
    
11. #011              If Cells(i, 1) = "Excel" Then
    
12. #012                  Cells(i, 1).EntireRow.Delete
    
13. #013              End If
    
14. #014          Next
    
15. #015          t1 = Timer - t
    
16. #016          .Value = arr
    
17. #017          t = Timer
    
18. #018          .Replace "Excel", ""
    
19. #019          .SpecialCells(4).EntireRow.Delete
    
20. #020      End With
    
21. #021      t2 = Timer - t
    
22. #022      MsgBox "第一次运行时间:" & Format(t1, "0.00000") & "秒" _
    
23. #023           & Chr(13) & "第二次运行时间:" & Format(t2, "0.00000") & "秒"
    
24. #024  End Sub

复制代码

Methods过程分别使用VBA代码和使用Replace、SpecialCells方法删除工作表A列内容为“Excel”的单元格所在的行，最后使用消息显示运行时间。
       第8行代码将单元格数据保存在数组arr中，因为在运行第2种方法前需要恢复单元格数据。
       第10行到第14行代码，采用遍历单元格的方法删除内容为“Excel”的单元格所在的行。
       第16行代码恢复单元格原有的数据。
       第18行代码使用Replace方法将内容为“Excel”的单元格替换成空白单元格。
       第19行代码使用SpecialCells方法定位到空白单元格后一次性删除其所在的行。
       关于Replace方法和SpecialCells方法请参阅技巧33 。
       运行Methods过程，消息框显示两种方法的运行时间，可以看出使用Replace方法和SpecialCells方法的运行时间要远远小于使用VBA代码运行的时间，如图所示。
      

ykx042907

袁版主：您好！
    非常感谢您一起以来辛苦地为我们大家作贡献！

xiworld

非常非常感谢！

yuanzhuping

原帖由 ykx042907 于 2009-6-24 11:55 发表
袁版主：您好！
    非常感谢您一起以来辛苦地为我们大家作贡献！

多谢ykx042907，PDF要使用压缩软件压缩后上传，体积超过2000KB需要分卷压缩后上传。建议再等等，计划到200个技巧结束，到时上传完整的吧。

yuanzhuping

技巧190         优化代码
190-4        使用With语句引用对象
       在需要重复引用同一个对象时可以使用With语句来获得较快的运行速度，如下面的代码所示。

1. #001  Sub WithSta()
   
2. #002      Dim i As Integer
   
3. #003      Dim t As Date
   
4. #004      Dim t1 As String
   
5. #005      Dim t2 As String
   
6. #006      t = Timer
   
7. #007      For i = 1 To 5000
   
8. #008          Sheets("Sheet1").Cells(1, 1) = 10
   
9. #009          Sheets("Sheet1").Cells(1, 2) = 10
   
10. #010          Sheets("Sheet1").Cells(1, 3) = 10
    
11. #011          Sheets("Sheet1").Cells(1, 4) = 10
    
12. #012          Sheets("Sheet1").Cells(1, 5) = 10
    
13. #013      Next
    
14. #014      t1 = Timer - t
    
15. #015      t = Timer
    
16. #016      With Sheets("Sheet1")
    
17. #017          For i = 1 To 5000
    
18. #018              .Cells(1, 1) = 10
    
19. #019              .Cells(1, 2) = 10
    
20. #020              .Cells(1, 3) = 10
    
21. #021              .Cells(1, 4) = 10
    
22. #022              .Cells(1, 5) = 10
    
23. #023          Next
    
24. #024      End With
    
25. #025      t2 = Timer - t
    
26. #026      MsgBox "第一次运行时间:" & Format(t1, "0.00000") & "秒" _
    
27. #027           & Chr(13) & "第二次运行时间:" & Format(t2, "0.00000") & "秒"
    
28. #028  End Sub

复制代码

代码解析：
      WithSta过程在单元格填充时使用With语句来引用工作表对象从而获得较快的运行速度。
      With语句在一个单一对象或一个用户定义类型上执行一系列的语句，语法如下：
      With Object
              [statements]
       End With
       参数object是必需的，一个对象或用户自定义类型的名称。
       参数statements是可选的，要执行的一条或多条语句。
       With语句可以对某个对象执行一系列的语句，而不用重复指出对象的名称。在运行时只需引用对象一次而不是在每个属性赋值时都要引用，从而获得较快的运行速度。
       运行WithSta过程，消息框显示两种方法的运行时间，可以看出使用With语句来引用工作表对象的运行速度较快，如图所示。
      

yuanzhuping

技巧190         优化代码
190-5        少用激活或选择语句
       在学习VBA的过程中我们经常通过录制新宏的方法来获得所需的代码，但是在录制宏的过程中会记录所有的动作，代码中有大量的Select和Activate语句，而这些代码往往是不必要而且会影响代码的运行速度。所以通过录制新宏的方法获得的代码在使用时需要进行修改以加快运行速度，如下面的代码所示。

1. #001  Sub Sta()
   
2. #002      Dim i As Integer
   
3. #003      Dim t As Date
   
4. #004      Dim t1 As String
   
5. #005      Dim t2 As String
   
6. #006      t = Timer
   
7. #007      For i = 1 To 5000
   
8. #008          Sheets("Sheet2").Select
   
9. #009          Range("A1").Select
   
10. #010          ActiveCell.FormulaR1C1 = "1"
    
11. #011      Next
    
12. #012      t1 = Timer - t
    
13. #013      t = Timer
    
14. #014      For i = 1 To 5000
    
15. #015          Sheets("Sheet2").Range("A1") = 1
    
16. #016      Next
    
17. #017      t2 = Timer - t
    
18. #018      MsgBox "第一次运行时间:" & Format(t1, "0.00000") & "秒" _
    
19. #019           & Chr(13) & "第二次运行时间:" & Format(t2, "0.00000") & "秒"
    
20. #020  End Sub

复制代码

代码解析：
       Sta过程分别使用录制宏所得的代码和修改后的代码给单元格填充，最后使用消息显示运行时间。
       第8行代码到第10行代码是录制宏所得的代码，其中有两次使用Select方法，第15行代码是修改后的代码，在代码量不大的情况下运行速度区别不大，但是在循环5000次后运行速度就会差别很大。
       运行Sta过程，消息框显示两种方法的运行时间，可以看出后一种方法的运行时间要远远小于录制宏所得的代码的运行时间，如图所示。
      

[ 本帖最后由 yuanzhuping 于 2009-6-24 17:24 编辑 ]

yuanzhuping

技巧191         取得文件的基本名称
       技巧77-2中介绍了如何使用Excel内置的“打开”对话框来获得选定文件的文件名称，此名称包含文件路径及文件扩展名，有时在操作时只需要文件的基本名称，此时可以使用GetBaseName方法，如下面的代码所示。

1. #001  Sub GetName()
   
2. #002      Dim MyFile As Object
   
3. #003      Dim Filename As Variant
   
4. #004      Set MyFile = CreateObject("Scripting.FileSystemObject")
   
5. #005      Filename = Application.GetOpenFilename
   
6. #006      If Filename <> False Then
   
7. #007          MsgBox MyFile.GetBaseName(Filename)
   
8. #008      End If
   
9. #009  End Sub

复制代码

代码解析：
       GetName过程取得用户选定文件的基本文件名称。
       第4行代码使用CreateObject函数创建FileSystemObject对象并将该对象赋给变量MyFile，请参阅技巧180 。
       第5行代码使用GetOpenFilename方法显示标准的内置“打开”对话框，请参阅技巧77-2。
       第6行到第8行代码，如果用户选定了文件，使用消息框显示选定文件的基本名称。应用于FileSystemObject对象的GetBaseName方法返回一个包含路径中最后部件的基本名字（去掉任何文件扩展名）的字符串，语法如下：
       object.GetBaseName(path)
       参数object是必需的，FileSystemObject对象的名称。
       参数path是必需的，要返回其基本名字的部件的路径说明。
       注意   GetBaseName方法只对参数path提供的字符串起作用，既不试图去辨认路径，也不检查指定路径是否存在。
       运行GetName过程，在打开对话框中选定示例文件后结果如图所示。
      

rerere6

顶你没商量！