ExcelHome技术论坛

 找回密码
 免费注册
QQ登录 只需一步,快速开始
   
高效办公必会的Office99uu优优 永久免费,网表让Excel秒变数据库 Excel服务器-会Excel,做管理系统 Excel Home精品图文教程库
Excel不给力? 何不试试FoxTable! 国内首部Excel函数公式学习大典 职场充电黑科技, Office微99uu优优 免费下载Excel行业应用视频
300集Office 2010微99uu优优 Tableau-数据可视化工具 突破Excel限制,用活字格提高效率 12门Excel免费公开课任你学
你的Excel 201099uu优优学习锦囊 欲罢不能, 过目难忘的 Office 新界面 免费的Excel考勤计算系统
楼主: yuanzhuping
打印 上一主题 下一主题

VBA常用技巧代码解析

    [复制链接]
TA的精华主题TA的得分主题
51
发表于 2009-2-10 22:27 | 只看该作者
本帖已被收录到知识树中,索引项:开发帮助和教程
俺要拿板凳,坐着学习,谢谢LZ
TA的精华主题TA的得分主题
52
发表于 2009-2-10 22:35 | 只看该作者
TA的精华主题TA的得分主题
53
发表于 2009-2-10 23:21 | 只看该作者
真是太好了,期待整个文章全部完成后的word发布!或者pdf最好,如果能授权可以打印那就帅呆了!
TA的精华主题TA的得分主题
54
发表于 2009-2-10 23:31 | 只看该作者
TA的精华主题TA的得分主题
55
发表于 2009-2-11 00:12 | 只看该作者
好东西,谢谢楼主的分享,受益匪浅!
TA的精华主题TA的得分主题
56
发表于 2009-2-11 00:31 | 只看该作者
TA的精华主题TA的得分主题
57
 楼主| 发表于 2009-2-11 00:38 | 只看该作者 |楼主

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

技巧12         单元格中的数据有效性
12-1        在单元格中建立数据有效性
       在单元格中建立数据有效性可以使用Add方法,如下面的代码所示。
  • #001  Sub Validation()
  • #002      With Range("A1:A10").Validation
  • #003         .Delete
  • #004          .Add Type:=xlValidateList, _
  • #005              AlertStyle:=xlValidAlertStop, _
  • #006              Operator:=xlBetween, _
  • #007              Formula1:="1,2,3,4,5,6,7,8"
  • #008      End With
  • #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 方法所要求的参数依有效性检验的类型而定,如表格所示。

    12-2        判断单元格是否存在数据有效性
           在VBA中没有专门的属性判断单元格是否存在数据有效性设置,可以使用Validation对象的有效性类型和错误陷阱来判断,如下面的代码所示。
  • #001  Sub Validation()
  • #002      On Error GoTo Line
  • #003      If Range("A2").Validation.Type >= 0 Then
  • #004          MsgBox "单元格有数据有效性!"
  • #005          Exit Sub
  • #006      End If
  • #007  Line:
  • #008      MsgBox "单元格没有数据有效性!"
  • #009  End Sub
  • 复制代码
    代码解析:
           Validation过程使用Validation对象的有效性类型和错误陷阱来判断A2单元格中是否存在数据有效性。
           第6行代码,如果A2单元格中存在数据有效性,Type参数值就会大于等于0,否则就会发生错误,使用On Error GoTo捕捉到错误后转移到第8行代码,显示一个消息框。
    12-3        动态的数据有效性
           利用VBA可以在单元格中建立动态的数据有效性,如下面的代码所示。
  • #001  Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  • #002      If Target.Column = 1 And Target.Count = 1 And Target.Row > 1 Then
  • #003          With Target.Validation
  • #004              .Delete
  • #005              .Add Type:=xlValidateList, _
  • #006                  AlertStyle:=xlValidAlertStop, _
  • #007                  Operator:=xlBetween, _
  • #008                  Formula1:="主机,显示器"
  • #009          End With
  • #010      End If
  • #011  End Sub
  • #012  Private Sub Worksheet_Change(ByVal Target As Range)
  • #013      If Target.Column = 1 And Target.Row > 1 And Target.Count = 1 Then
  • #014          With Target.Offset(0, 1).Validation
  • #015              .Delete
  • #016              Select Case Target
  • #017                  Case "主机"
  • #018                      .Add Type:=xlValidateList, _
  • #019                          AlertStyle:=xlValidAlertStop, _
  • #020                          Operator:=xlBetween, _
  • #021                          Formula1:="Z286,Z386,Z486,Z586"
  • #022                  Case "显示器"
  • #023                      .Add Type:=xlValidateList, _
  • #024                          AlertStyle:=xlValidAlertStop, _
  • #025                          Operator:=xlBetween, _
  • #026                          Formula1:="三星17,飞利浦15,三星15,飞利浦17"
  • #027              End Select
  • #028          End With
  • #029      End If
  • #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列单元格的内容而变化,使之达到动态数据有效性的效果,如图所示。


    12-4        自动展开数据有效性下拉列表
           选择工作表单元格时自动展开数据有效性的下拉列表,如下面的代码所示。
  • #001  Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  • #002      If Target.Column = 5 Then Application.SendKeys "%{down}"
  • #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),请使用如表格所列的代码来表示相应的键,表中的每个代码表示键盘上的一个键。

    当选择工作表中的E列单元格时将自动展开数据有效性的下拉列表,如所示。





    [ 本帖最后由 yuanzhuping 于 2009-2-16 22:19 编辑 ]
    技巧12 单元格中的数据有效性.rar 25.35 KB, 下载次数: 2992
    TA的精华主题TA的得分主题
    58
    发表于 2009-2-11 07:00 | 只看该作者
    TA的精华主题TA的得分主题
    59
     楼主| 发表于 2009-2-11 07:31 | 只看该作者 |楼主

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

    技巧13         单元格中的公式
    13-1        在单元格中写入公式
           使用Range对象的Formula属性可以在单元格区域中写入公式,如下面的代码所示。
  • #001  Sub rngFormula()
  • #002      Sheet1.Range("C1:C10").Formula = "=SUM(A1+B1)"
  • #003  End Sub
  • 复制代码
    代码解析:
           应用于Range对象的Formula属性返回或设置A1样式表示的Range对象的公式,语法如下:
    expression.Formula
           参数expression是必需的,返回一个Range对象。
           还可以使用FormulaR1C1属性返回或设置以R1C1-样式符号表示的公式,如下面的代码所示。
  • #001  Sub rngFormulaRC()
  • #002      Sheet2.Range("C1:C10").FormulaR1C1 = "=SUM(RC[-2]+RC[-1])"
  • #003  End Sub
  • 复制代码
    如果需要在单元格中写入数组公式则使用Range对象的FormulaArray属性。如下面的代码所示。
  • #001  Sub RngFormulaArray()
  • #002      Sheet3.Range("C1").FormulaArray = "=A1:A2*B1:B2"
  • #003  End Sub
  • 复制代码
    Range对象的FormulaArray属性返回或设置单元格区域的数组公式。
    13-2        检查单元格是否含有公式
           使用单元格的HasFormula属性检查单元格是否含有公式,如下面的代码所示。
  • #001  Private Sub CommandButton1_Click()
  • #002      Select Case Selection.HasFormula
  • #003          Case True
  • #004              MsgBox "公式单元格!"
  • #005          Case False
  • #006              MsgBox "非公式单元格!"
  • #007          Case Else
  • #008          MsgBox "公式区域:" & Selection.SpecialCells(xlCellTypeFormulas, 23).Address(0, 0)
  • #009      End Select
  • #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属性来获得公式的返回结果,将得到类型不匹配的错误信息,如图所示。

           通过Range对象的Value属性的返回结果是否为错误类型,来判断公式是否存在错误,如下面的代码所示。
  • #001  Sub FormulaIsError()
  • #002      If VBA.IsError(Range("A1").Value) = True Then
  • #003          MsgBox "A1单元格错误类型为:" & Range("A1").Text
  • #004      Else
  • #005          MsgBox "A1单元格公式结果为" & Range("A1").Value
  • #006      End If
  • #007  End Sub
  • 复制代码
    代码解析:
           FormulaIsError过程代码判断单元格A1中公式结果是否为错误,如果为错误则显示该错误类型,否则显示公式的结果,如图所示。

           第2行代码使用IsError函数返回Boolean值,指出表达式是否为一个错误值,如果表达式表示一个错误,则IsError函数返回True,否则返回False。
    13-4        取得单元格中公式的引用单元格
           如果需要取得单元格中公式的引用单元格对象,可以使用Range对象的Precedents属性,如下面的代码所示。
  • #001  Sub RngPrecedent()
  • #002      Dim rng As Range
  • #003      Set rng = Sheet1.Range("C1").Precedents
  • #004      MsgBox "公式所引用的单元格有:" & rng.Address
  • #005      Set rng = Nothing
  • #006  End Sub
  • 复制代码
    代码解析:
           在工作表的C1单元格中写有公式“SUM(“A1:B1”)”,RngPrecedent过程使用Range对象的Precedents属性取得其引用的单元格A1:B1。
           Precedents属性返回一个Range对象,该对象代表单元格的所有引用单元格。如果有若干引用单元格,那么该区域可能是多个的选定区域(Range 对象的联合)。
           运行RngPrecedent过程结果如图所示。

    13-5        将单元格中的公式转换为数值
           工作表中如果存在过多的公式将影响操作速度,将单元格中的函数与公式的结果转换为数值,可以提高工作表运算效率,有下面几种方法可以实现。
           使用选择性粘贴的方法可以将函数与公式的结果转换为数值,如下面的代码所示。
  • #001  Sub SpecialPaste()
  • #002      With Range("A1:A10")
  • #003          .Copy
  • #004          .PasteSpecial Paste:=xlPasteValues
  • #005      End With
  • #006      Application.CutCopyMode = False
  • #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属性可以将函数与公式的结果转换为数值,如下面的代码所示。
  • #001  Sub UseValue()
  • #002      Range("A1:A10").Value = Range("A1:A10").Value
  • #003  End Sub
  • 复制代码
    代码解析:
           UseValue过程使用Value属性将函数与公式的结果转换为数值。
           使用Formula属性可以将函数与公式的结果转换为数值,如下面的代码所示。
  • #001  Sub UseFormula()
  • #002      Range("A1").Formula = Range("A1").Value
  • #003  End Sub
  • 复制代码
    代码解析:
           UseFormula过程Formula属性将函数与公式的结果转换为数值。当Formula属性值为非公式时,返回的结果与Value属性一致。

    [ 本帖最后由 yuanzhuping 于 2009-2-16 23:06 编辑 ]
    技巧13 单元格中的公式.rar 36.05 KB, 下载次数: 2645

    评分

    参与人数 3鲜花 +6 收起 理由
    征婚启事 + 2 最高。
    飞花四月 + 2 优秀作品
    seki_100 + 2 值得肯定
    查看全部评分
    TA的精华主题TA的得分主题
    60
    发表于 2009-2-11 08:43 | 只看该作者
    您需要登录后才可以回帖 登录 | 免费注册
    本版积分规则
    关闭

    最新热点上一条 /1 下一条

    关注官方微信,每天坐享新鲜教程
    手机版|关于我们|联系我们|ExcelHome    GMT+8, 2018-1-18 17:35 , Processed in 1.084418 second(s), 22 queries , Gzip On.
    Powered by Discuz! X3.3 © 2001-2017 Wooffice Inc.
        沪公网安备 31011702000001号 沪ICP备11019229号 本论坛言论纯属发表者个人意见,任何违反国家相关法律的言论,本站将协助国家相关部门追究发言者责任!     本站特聘法律顾问:徐怀玉律师 李志群律师
    快速回复 返回顶部 返回列表
    99uu优优