VBA常用技巧代码解析

yuanzhuping · 发表于 2009-6-24 09:05

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

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

复制代码

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

胡剑0227 · 发表于 2009-6-24 10:47

哇，太丰富了，感动ing

yuanzhuping · 发表于 2009-6-24 11:42

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

#001 Sub Methods()
#002 Dim arr As Variant
#003 Dim i As Long
#004 Dim t As Date
#005 Dim t1 As String
#006 Dim t2 As String
#007 With Range("A1:A20000")
#008 arr = .Value
#009 t = Timer
#010 For i = 20000 To 1 Step -1
#011 If Cells(i, 1) = "Excel" Then
#012 Cells(i, 1).EntireRow.Delete
#013 End If
#014 Next
#015 t1 = Timer - t
#016 .Value = arr
#017 t = Timer
#018 .Replace "Excel", ""
#019 .SpecialCells(4).EntireRow.Delete
#020 End With
#021 t2 = Timer - t
#022 MsgBox "第一次运行时间:" & Format(t1, "0.00000") & "秒" _
#023 & Chr(13) & "第二次运行时间:" & Format(t2, "0.00000") & "秒"
#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 · 发表于 2009-6-24 11:55

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

xiworld · 发表于 2009-6-24 16:19

非常非常感谢！

yuanzhuping · 发表于 2009-6-24 16:52

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

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

yuanzhuping · 发表于 2009-6-24 16:54

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

#001 Sub WithSta()
#002 Dim i As Integer
#003 Dim t As Date
#004 Dim t1 As String
#005 Dim t2 As String
#006 t = Timer
#007 For i = 1 To 5000
#008 Sheets("Sheet1").Cells(1, 1) = 10
#009 Sheets("Sheet1").Cells(1, 2) = 10
#010 Sheets("Sheet1").Cells(1, 3) = 10
#011 Sheets("Sheet1").Cells(1, 4) = 10
#012 Sheets("Sheet1").Cells(1, 5) = 10
#013 Next
#014 t1 = Timer - t
#015 t = Timer
#016 With Sheets("Sheet1")
#017 For i = 1 To 5000
#018 .Cells(1, 1) = 10
#019 .Cells(1, 2) = 10
#020 .Cells(1, 3) = 10
#021 .Cells(1, 4) = 10
#022 .Cells(1, 5) = 10
#023 Next
#024 End With
#025 t2 = Timer - t
#026 MsgBox "第一次运行时间:" & Format(t1, "0.00000") & "秒" _
#027 & Chr(13) & "第二次运行时间:" & Format(t2, "0.00000") & "秒"
#028 End Sub

复制代码

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

yuanzhuping · 发表于 2009-6-24 17:23

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

#001 Sub Sta()
#002 Dim i As Integer
#003 Dim t As Date
#004 Dim t1 As String
#005 Dim t2 As String
#006 t = Timer
#007 For i = 1 To 5000
#008 Sheets("Sheet2").Select
#009 Range("A1").Select
#010 ActiveCell.FormulaR1C1 = "1"
#011 Next
#012 t1 = Timer - t
#013 t = Timer
#014 For i = 1 To 5000
#015 Sheets("Sheet2").Range("A1") = 1
#016 Next
#017 t2 = Timer - t
#018 MsgBox "第一次运行时间:" & Format(t1, "0.00000") & "秒" _
#019 & Chr(13) & "第二次运行时间:" & Format(t2, "0.00000") & "秒"
#020 End Sub

复制代码

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

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

yuanzhuping · 发表于 2009-6-25 10:08

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

#001 Sub GetName()
#002 Dim MyFile As Object
#003 Dim Filename As Variant
#004 Set MyFile = CreateObject("Scripting.FileSystemObject")
#005 Filename = Application.GetOpenFilename
#006 If Filename <> False Then
#007 MsgBox MyFile.GetBaseName(Filename)
#008 End If
#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 · 发表于 2009-6-25 11:01

顶你没商量！

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

VBA常用技巧代码解析

第11部分其他应用

第11部分其他应用

VBA常用技巧(1-10)之pdf版

非常非常好

第11部分其他应用

第11部分其他应用

第11部分其他应用

VBA常用技巧代码解析

第11部分 其他应用

第11部分 其他应用

VBA常用技巧(1-10)之pdf版

非常非常好

第11部分 其他应用

第11部分 其他应用

第11部分 其他应用

第11部分其他应用

第11部分其他应用

第11部分其他应用

第11部分其他应用

第11部分其他应用