第二讲:Excel 在测试中的应用

下面我们看下如何添加功能键。

如果看不到开发选项，请在文件--》开发者选项中勾选显示开发项就行。

直接选择button放置对应位置。

 

先来看第一个show/hide Test cases按钮用来显示或者隐藏详细案例

Sub ShowTestCases()
Dim TestCases As Range
Set TestCases = Range("RangeTestCases")
If TestCases.EntireColumn.Hidden = True Then
   TestCases.EntireColumn.Hidden = False
   ActiveSheet.Outline.ShowLevels RowLevels:=5
   Else
   TestCases.EntireColumn.Hidden = True
   ActiveSheet.Outline.ShowLevels RowLevels:=2
   End If
   
End Sub

 设置好要隐藏或者显示区域即range。

Sub ShowDifferentLevel()
Dim TestCases As Range
Set TestCases = Range("RangeTestCases")
If Range("B4").Value = 0 Then
   Range("B4").Value = 1
   TestCases.EntireColumn.Hidden = True
   ActiveSheet.Outline.ShowLevels RowLevels:=3
   Else
   TestCases.EntireColumn.Hidden = True
   ActiveSheet.Outline.ShowLevels RowLevels:=2
   Range("B4").Value = 0
   End If
End Sub

 

这里对应的就是outline.show levels。也就是在界面上点1 2 3等不同的级别，可通过上面代码设置

Sub SendMail()
Dim BankNum As String
Dim BankName As String
Dim MyDate As String
Dim m
Dim n
Dim Address1, Address2, ResultAddress, ResultAddress2
n = 0
MyDate = Date
BankNum = Sheet1.Range("BankNum").Value
BankName = Sheet1.Range("BankName").Value
Sheet2.Range("A3").Value = "$D$4"
Sheet2.Range("D4:D200").EntireRow.Delete
For m = 10 To 953
 If Sheet1.Cells(m, 13).Value = "失败" Then
 Sheet2.Range("A1").Value = Split(Sheet1.Cells(m, 13).MergeArea.Address, ":")(1)
 Sheet2.Range("A2").Value = Sheet1.Cells(m, 5).Address
    Address1 = Sheet2.Range("A1").Value
    Address2 = Sheet2.Range("A2").Value
    ResultAddress = Address1 + ":" + Address2
    ResultAddress2 = Sheet2.Range("A3").Value
    Sheet1.Range(ResultAddress).Copy
    ActiveSheet.Paste Destination:=Sheet2.Range(ResultAddress2).Offset(1, 0)
    Sheet2.Range("A3").Value = Split(Sheet2.Range(ResultAddress2).Offset(1, 0).MergeArea.Address, ":")(1)
    ResultAddress2 = Sheet2.Range("A3").Value
    n = n + 1
    End If
Next
ActiveSheet.Outline.ShowLevels RowLevels:=2
Range("RangeTestCases").EntireColumn.Hidden = True
Application.ThisWorkbook.Save
MsgBox "please select  'No'  when Microsoft Excel dialog box asks if you want to share this workbook and changes have been saved automatically"
    ActiveWorkbook.SendForReview _
        Recipients:="[e-mail]", _
        Subject:=BankName + "(机构代码" + BankNum + ")" + "测试运行报告" + "_" + MyDate, _
        ShowMessage:=True, _
        IncludeAttachment:=True

End Sub

上面也就是发送邮件功能，中间应该加上application.updatescreen=false 防止在发送过程中弹出对话框，还可做另外一点优化就是只发送测试统计结果而不发送测试案例。简单的设置一下应该就可以完成。

 

   ActiveWorkbook.SendForReview _
        Recipients:="[e-mail]", _
        Subject:=BankName + "(机构代码" + BankNum + ")" + "测试运行报告" + "_" + MyDate, _
        ShowMessage:=True, _
        IncludeAttachment:=True

 
分别设置邮件主题、收件人、是否包括附件、显示信息等。

Sub ResetTestResult()
Dim i
Dim Msg, Style, Title, Help, Ctxt, Response, MyString
Msg = "Are you sure you want to clean record results? (You can do this unless you want to test a new orgination or aband previous test results)"    ' Define message.
Style = vbYesNo   ' Define buttons.
Title = "Clean Result"    ' Define title.   ' Define Help file.
Response = MsgBox(Msg, Style, Title)
If Response = vbYes Then    ' User chose Yes.
   For i = 10 To 950
 If Sheet1.Cells(i, 13).Value <> "" Then
    Sheet1.Cells(i, 13).Value = "未测试"
 End If
Next
Sheet1.Range("BankNum").Value = ""
Sheet1.Range("BankName").Value = ""
Sheet1.Range("N7:P954").ClearContents
Sheet2.Range("D4:D200").EntireRow.Delete
MsgBox "All results cleared"
End If


End Sub

 

最后一步即清除运行记录结果，考虑到需要重复使用，所以加入此功能。

其实还可以将运行失败的案例单独提出另存一个sheet。也便于测试跟踪和汇报。