VBA常用技巧代码解析

bibisin

俺要拿板凳,坐着学习,谢谢LZ

nicholas_he

多谢楼主，已经下载

入孝出悌

真是太好了，期待整个文章全部完成后的word发布！或者pdf最好，如果能授权可以打印那就帅呆了！

fdd

强烈支持分享！感谢楼主的辛勤劳动！

sean536255

好东西,谢谢楼主的分享,受益匪浅!

zengdg1972

期待版主的新作！

yuanzhuping

技巧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 方法所要求的参数依有效性检验的类型而定，如表格所示。

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列单元格的内容而变化，使之达到动态数据有效性的效果，如图所示。


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），请使用如表格所列的代码来表示相应的键，表中的每个代码表示键盘上的一个键。

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





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

hbcbob

Thanks for sharing

yuanzhuping

技巧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属性来获得公式的返回结果，将得到类型不匹配的错误信息，如图所示。

       通过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中公式结果是否为错误，如果为错误则显示该错误类型，否则显示公式的结果，如图所示。

       第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过程结果如图所示。

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 编辑 ]

zhangmi

我就等全集了