ExcelHome技术论坛

 找回密码
 免费注册

QQ登录

只需一步,快速开始

快捷登录

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

VBA常用技巧代码解析

    [复制链接]

TA的精华主题

TA的得分主题

发表于 2009-2-10 22:27 | 显示全部楼层
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件       ★免费下载 ★       ★ 使用帮助
本帖已被收录到知识树中,索引项:开发帮助和教程
俺要拿板凳,坐着学习,谢谢LZ

TA的精华主题

TA的得分主题

发表于 2009-2-10 22:35 | 显示全部楼层

TA的精华主题

TA的得分主题

发表于 2009-2-10 23:21 | 显示全部楼层
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件       ★免费下载 ★       ★ 使用帮助
真是太好了,期待整个文章全部完成后的word发布!或者pdf最好,如果能授权可以打印那就帅呆了!

TA的精华主题

TA的得分主题

发表于 2009-2-10 23:31 | 显示全部楼层

TA的精华主题

TA的得分主题

发表于 2009-2-11 00:12 | 显示全部楼层
好东西,谢谢楼主的分享,受益匪浅!

TA的精华主题

TA的得分主题

发表于 2009-2-11 00:31 | 显示全部楼层

TA的精华主题

TA的得分主题

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

第1部分Range(单元格)对象

技巧12         单元格中的数据有效性
12-1        在单元格中建立数据有效性
       在单元格中建立数据有效性可以使用Add方法,如下面的代码所示。
  1. #001  Sub Validation()
  2. #002      With Range("A1:A10").Validation
  3. #003         .Delete
  4. #004          .Add Type:=xlValidateList, _
  5. #005              AlertStyle:=xlValidAlertStop, _
  6. #006              Operator:=xlBetween, _
  7. #007              Formula1:="1,2,3,4,5,6,7,8"
  8. #008      End With
  9. #009  End Sub
复制代码
代码解析:
       Validation过程使用Add方法在A1:A10单元格中建立数据有效性。
       第3行代码删除已建立的数据有效性,防止代码运行出错。
       第4行到第7行代码使用Add方法建立数据有效性。应用于Validation对象的Add方法的语法如下:
expression.Add(Type, AlertStyle, Operator, Formula1, Formula2)
       参数expression是必需的,返回一个Validation对象。
       参数Type是必需的,数据有效性类型。
       参数AlertStyl是可选的,有效性检验警告样式。
       参数Operator是可选的,数据有效性运算符。
       参数Formula1是可选的,数据有效性公式的第一部分。
       参数Formula2是可选的,当Operator为xlBetween或xlNotBetween时,数据有效性公式的第二部分(其他情况下,此参数被忽略)。
       Add 方法所要求的参数依有效性检验的类型而定,如表格所示。
数据有效性类型.jpg
12-2        判断单元格是否存在数据有效性
       在VBA中没有专门的属性判断单元格是否存在数据有效性设置,可以使用Validation对象的有效性类型和错误陷阱来判断,如下面的代码所示。
  1. #001  Sub Validation()
  2. #002      On Error GoTo Line
  3. #003      If Range("A2").Validation.Type >= 0 Then
  4. #004          MsgBox "单元格有数据有效性!"
  5. #005          Exit Sub
  6. #006      End If
  7. #007  Line:
  8. #008      MsgBox "单元格没有数据有效性!"
  9. #009  End Sub
复制代码
代码解析:
       Validation过程使用Validation对象的有效性类型和错误陷阱来判断A2单元格中是否存在数据有效性。
       第6行代码,如果A2单元格中存在数据有效性,Type参数值就会大于等于0,否则就会发生错误,使用On Error GoTo捕捉到错误后转移到第8行代码,显示一个消息框。
12-3        动态的数据有效性
       利用VBA可以在单元格中建立动态的数据有效性,如下面的代码所示。
  1. #001  Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  2. #002      If Target.Column = 1 And Target.Count = 1 And Target.Row > 1 Then
  3. #003          With Target.Validation
  4. #004              .Delete
  5. #005              .Add Type:=xlValidateList, _
  6. #006                  AlertStyle:=xlValidAlertStop, _
  7. #007                  Operator:=xlBetween, _
  8. #008                  Formula1:="主机,显示器"
  9. #009          End With
  10. #010      End If
  11. #011  End Sub
  12. #012  Private Sub Worksheet_Change(ByVal Target As Range)
  13. #013      If Target.Column = 1 And Target.Row > 1 And Target.Count = 1 Then
  14. #014          With Target.Offset(0, 1).Validation
  15. #015              .Delete
  16. #016              Select Case Target
  17. #017                  Case "主机"
  18. #018                      .Add Type:=xlValidateList, _
  19. #019                          AlertStyle:=xlValidAlertStop, _
  20. #020                          Operator:=xlBetween, _
  21. #021                          Formula1:="Z286,Z386,Z486,Z586"
  22. #022                  Case "显示器"
  23. #023                      .Add Type:=xlValidateList, _
  24. #024                          AlertStyle:=xlValidAlertStop, _
  25. #025                          Operator:=xlBetween, _
  26. #026                          Formula1:="三星17,飞利浦15,三星15,飞利浦17"
  27. #027              End Select
  28. #028          End With
  29. #029      End If
  30. #030  End Sub
复制代码
代码解析:
       第1行到第11行代码,工作表的SelectionChange事件,当选择工作表的A列单元格时,在A2以下的单元格中建立动态的数据有效性。
       其中第2行代码,利用SelectionChange事件的Target参数来限制事件的触发条件。
       第3行到第9行代码使用Add方法在A列单元格中建立数据有效性。应用于Validation对象的Add方法请参阅技巧12-1。
       第12行到第30行代码,工作表的Change事件,当工作表A列单元格内容改变时,在B列单元格中建立动态的数据有效性。
       其中第16行到第27行代码,根据A列单元格的内容在B列对应的单元格中建立数据有效性,其Formula1参数的值根据A列单元格的内容而变化,使之达到动态数据有效性的效果,如图所示。
动态数据有效性1.jpg
动态数据有效性2.jpg
12-4        自动展开数据有效性下拉列表
       选择工作表单元格时自动展开数据有效性的下拉列表,如下面的代码所示。
  1. #001  Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  2. #002      If Target.Column = 5 Then Application.SendKeys "%{down}"
  3. #003  End Sub
复制代码
代码解析:
       当选择工作表的E列中有数据有效性的单元格时使用SendKeys方法发送Alt+向下键,打开数据有效性的下拉列表。
       应用于Application对象的SendKeys方法将击键发送给活动应用程序,语法如下:
expression.SendKeys(Keys, Wait)
       参数expression是可选的,该表达式返回一个Application对象。
       参数Keys是必需的,要发送的键或者组合键,以文本方式表示。
       Keys参数可以指定任何单个键或与Alt、Ctrl 或Shift的组合键(或者这些键的组合)。每个键可用一个或多个字符表示。例如,"a" 表示字符 a,或者 "{ENTER}" 表示 Enter。
       若要指定在按相应键时不会显示的字符(例如,Enter 或 Tab),请使用如表格所列的代码来表示相应的键,表中的每个代码表示键盘上的一个键。
按键代码.JPG
当选择工作表中的E列单元格时将自动展开数据有效性的下拉列表,如所示。
Snap19.jpg




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

技巧12 单元格中的数据有效性.rar

25.35 KB, 下载次数: 3575

TA的精华主题

TA的得分主题

发表于 2009-2-11 07:00 | 显示全部楼层

TA的精华主题

TA的得分主题

 楼主| 发表于 2009-2-11 07:31 | 显示全部楼层

第1部分Range(单元格)对象

技巧13         单元格中的公式
13-1        在单元格中写入公式
       使用Range对象的Formula属性可以在单元格区域中写入公式,如下面的代码所示。
  1. #001  Sub rngFormula()
  2. #002      Sheet1.Range("C1:C10").Formula = "=SUM(A1+B1)"
  3. #003  End Sub
复制代码
代码解析:
       应用于Range对象的Formula属性返回或设置A1样式表示的Range对象的公式,语法如下:
expression.Formula
       参数expression是必需的,返回一个Range对象。
       还可以使用FormulaR1C1属性返回或设置以R1C1-样式符号表示的公式,如下面的代码所示。
  1. #001  Sub rngFormulaRC()
  2. #002      Sheet2.Range("C1:C10").FormulaR1C1 = "=SUM(RC[-2]+RC[-1])"
  3. #003  End Sub
复制代码
如果需要在单元格中写入数组公式则使用Range对象的FormulaArray属性。如下面的代码所示。
  1. #001  Sub RngFormulaArray()
  2. #002      Sheet3.Range("C1").FormulaArray = "=A1:A2*B1:B2"
  3. #003  End Sub
复制代码
Range对象的FormulaArray属性返回或设置单元格区域的数组公式。
13-2        检查单元格是否含有公式
       使用单元格的HasFormula属性检查单元格是否含有公式,如下面的代码所示。
  1. #001  Private Sub CommandButton1_Click()
  2. #002      Select Case Selection.HasFormula
  3. #003          Case True
  4. #004              MsgBox "公式单元格!"
  5. #005          Case False
  6. #006              MsgBox "非公式单元格!"
  7. #007          Case Else
  8. #008          MsgBox "公式区域:" & Selection.SpecialCells(xlCellTypeFormulas, 23).Address(0, 0)
  9. #009      End Select
  10. #010  End Sub
复制代码
代码解析:
       工作表中按钮的单击过程,检查所选择的单元格区域是否含有公式。
       第2行代码返回所选择单元格区域的HasFormula属性值。如果区域中所有单元格均包含公式,则该值为True;如果所有单元格均不包含公式,则该值为False。
       第3、4行代码,如果返回True,说明区域中所有单元格均包含公式。
       第5、6行代码,如果返回False,说明区域中所有单元格均不包含公式。
       第7、8行代码,如果是混合区域,则显示包含公式的单元格地址。
13-3        判断单元格公式是否存在错误
       当需要获取的单元格的值由公式返回时,公式返回的结果可能是一个错误文本,包含#NULL!、#DIV/0!、#VALUE!、#REF!、#NAME?、#NUM!、#N/A等。此时,当单元格公式返回结果为错误文本时,如果试图通过Value属性来获得公式的返回结果,将得到类型不匹配的错误信息,如图所示。
Snap1.jpg
       通过Range对象的Value属性的返回结果是否为错误类型,来判断公式是否存在错误,如下面的代码所示。
  1. #001  Sub FormulaIsError()
  2. #002      If VBA.IsError(Range("A1").Value) = True Then
  3. #003          MsgBox "A1单元格错误类型为:" & Range("A1").Text
  4. #004      Else
  5. #005          MsgBox "A1单元格公式结果为" & Range("A1").Value
  6. #006      End If
  7. #007  End Sub
复制代码
代码解析:
       FormulaIsError过程代码判断单元格A1中公式结果是否为错误,如果为错误则显示该错误类型,否则显示公式的结果,如图所示。
Snap2.jpg
       第2行代码使用IsError函数返回Boolean值,指出表达式是否为一个错误值,如果表达式表示一个错误,则IsError函数返回True,否则返回False。
13-4        取得单元格中公式的引用单元格
       如果需要取得单元格中公式的引用单元格对象,可以使用Range对象的Precedents属性,如下面的代码所示。
  1. #001  Sub RngPrecedent()
  2. #002      Dim rng As Range
  3. #003      Set rng = Sheet1.Range("C1").Precedents
  4. #004      MsgBox "公式所引用的单元格有:" & rng.Address
  5. #005      Set rng = Nothing
  6. #006  End Sub
复制代码
代码解析:
       在工作表的C1单元格中写有公式“SUM(“A1:B1”)”,RngPrecedent过程使用Range对象的Precedents属性取得其引用的单元格A1:B1。
       Precedents属性返回一个Range对象,该对象代表单元格的所有引用单元格。如果有若干引用单元格,那么该区域可能是多个的选定区域(Range 对象的联合)。
       运行RngPrecedent过程结果如图所示。
Snap3.jpg
13-5        将单元格中的公式转换为数值
       工作表中如果存在过多的公式将影响操作速度,将单元格中的函数与公式的结果转换为数值,可以提高工作表运算效率,有下面几种方法可以实现。
       使用选择性粘贴的方法可以将函数与公式的结果转换为数值,如下面的代码所示。
  1. #001  Sub SpecialPaste()
  2. #002      With Range("A1:A10")
  3. #003          .Copy
  4. #004          .PasteSpecial Paste:=xlPasteValues
  5. #005      End With
  6. #006      Application.CutCopyMode = False
  7. #007  End Sub
复制代码
代码解析:
       SpecialPaste过程使用选择性粘贴方法将单元格区域的公式转换为数值。
       第3行代码将单元格区域复制到剪贴板中。
       应用于Range对象的Copy方法将单元格区域复制到指定的区域或剪贴板中,语法如下:
expression.Copy(Destination)参数expression是必需的,该表达式返回一个Range对象。
       参数Destination是可选的,指定区域要复制到的目标区域。如果省略该参数,Microsoft Excel 将把该区域复制到剪贴板中。
       第4行代码将剪贴板中的Range对象仅复制值到单元格区域中。
       应用于Range对象的PasteSpecial方法将剪贴板中的Range对象粘贴到指定区域中,语法如下:
expression.PasteSpecial(Paste, Operation, SkipBlanks, Transpose)
       参数expression是必需的,该表达式返回一个Range对象。
       参数Paste是可选的,指定要粘贴的区域部分。在本例中设置为xlPasteValues,仅复制值到单元格区域中。
       使用Value属性可以将函数与公式的结果转换为数值,如下面的代码所示。
  1. #001  Sub UseValue()
  2. #002      Range("A1:A10").Value = Range("A1:A10").Value
  3. #003  End Sub
复制代码
代码解析:
       UseValue过程使用Value属性将函数与公式的结果转换为数值。
       使用Formula属性可以将函数与公式的结果转换为数值,如下面的代码所示。
  1. #001  Sub UseFormula()
  2. #002      Range("A1").Formula = Range("A1").Value
  3. #003  End Sub
复制代码
代码解析:
       UseFormula过程Formula属性将函数与公式的结果转换为数值。当Formula属性值为非公式时,返回的结果与Value属性一致。

[ 本帖最后由 yuanzhuping 于 2009-2-16 23:06 编辑 ]

技巧13 单元格中的公式.rar

36.05 KB, 下载次数: 3223

评分

3

查看全部评分

TA的精华主题

TA的得分主题

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

本版积分规则

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

GMT+8, 2024-3-29 14:44 , Processed in 0.045007 second(s), 7 queries , Gzip On, Redis On.

Powered by Discuz! X3.4

© 1999-2023 Wooffice Inc.

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

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

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