ExcelHome技术论坛

 找回密码
 免费注册

QQ登录

只需一步,快速开始

快捷登录

搜索
EH技术汇-专业的职场技能充电站 妙哉!函数段子手趣味讲函数 Excel服务器-会Excel,做管理系统 效率神器,一键搞定繁琐工作
HR薪酬管理数字化实战 Excel 2021函数公式学习大典 Excel数据透视表实战秘技 打造核心竞争力的职场宝典
让更多数据处理,一键完成 数据工作者的案头书 免费直播课集锦 ExcelHome出品 - VBA代码宝免费下载
用ChatGPT与VBA一键搞定Excel WPS表格从入门到精通 Excel VBA经典代码实践指南
楼主: yuanzhuping

VBA常用技巧代码解析

    [复制链接]

TA的精华主题

TA的得分主题

 楼主| 发表于 2009-6-24 09:05 | 显示全部楼层

第11部分 其他应用

本帖已被收录到知识树中,索引项:开发帮助和教程
技巧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过程,消息框显示两种方法的运行时间,可以看出调用工作表函数进行计算的运行时间要远远小于使用累加方法运行的时间,如图所示。
       Snap2.jpg

190-2 使用工作表函数.rar

165.98 KB, 下载次数: 1750

TA的精华主题

TA的得分主题

发表于 2009-6-24 10:47 | 显示全部楼层
哇,太丰富了,感动ing

TA的精华主题

TA的得分主题

 楼主| 发表于 2009-6-24 11:42 | 显示全部楼层

第11部分 其他应用

技巧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代码运行的时间,如图所示。
       Snap1.jpg

190-3 使用更快的单元格操作方法.rar

85.12 KB, 下载次数: 1247

TA的精华主题

TA的得分主题

发表于 2009-6-24 11:55 | 显示全部楼层
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件       ★免费下载 ★       ★ 使用帮助

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

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

TA的精华主题

TA的得分主题

发表于 2009-6-24 16:19 | 显示全部楼层

TA的精华主题

TA的得分主题

 楼主| 发表于 2009-6-24 16:52 | 显示全部楼层
原帖由 ykx042907 于 2009-6-24 11:55 发表
袁版主:您好!
    非常感谢您一起以来辛苦地为我们大家作贡献!

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

TA的精华主题

TA的得分主题

 楼主| 发表于 2009-6-24 16:54 | 显示全部楼层

第11部分 其他应用

技巧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语句来引用工作表对象的运行速度较快,如图所示。
       Snap1.jpg

190-4 使用With语句引用对象.rar

7.75 KB, 下载次数: 992

TA的精华主题

TA的得分主题

 楼主| 发表于 2009-6-24 17:23 | 显示全部楼层

第11部分 其他应用

技巧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过程,消息框显示两种方法的运行时间,可以看出后一种方法的运行时间要远远小于录制宏所得的代码的运行时间,如图所示。
       Snap1.jpg

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

190-5 少用激活或选择语句.rar

8.23 KB, 下载次数: 992

TA的精华主题

TA的得分主题

 楼主| 发表于 2009-6-25 10:08 | 显示全部楼层

第11部分 其他应用

技巧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过程,在打开对话框中选定示例文件后结果如图所示。
       Snap1.jpg

技巧191 取得文件的基本名称.rar

6.19 KB, 下载次数: 1051

TA的精华主题

TA的得分主题

发表于 2009-6-25 11:01 | 显示全部楼层
您需要登录后才可以回帖 登录 | 免费注册

本版积分规则

手机版|关于我们|联系我们|ExcelHome

GMT+8, 2024-11-6 07:37 , Processed in 0.046523 second(s), 8 queries , Gzip On, MemCache On.

Powered by Discuz! X3.4

© 1999-2023 Wooffice Inc.

沪公网安备 31011702000001号 沪ICP备11019229号-2

本论坛言论纯属发表者个人意见,任何违反国家相关法律的言论,本站将协助国家相关部门追究发言者责任!     本站特聘法律顾问:李志群律师

快速回复 返回顶部 返回列表