Excel 在测试中的应用(二)--添加功能键
来源:互联网 发布:整型变量的数据范围 编辑:程序博客网 时间:2024/04/30 18:29
下面我们看下如何添加功能键。
如果看不到开发选项,请在文件--》开发者选项中勾选显示开发项就行。
直接选择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
Sub ShowTestCases()Dim TestCases As RangeSet 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
Sub ShowDifferentLevel()Dim TestCases As RangeSet 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 IfEnd 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:="XXX@sysnew.com", _
- Subject:=BankName + "(机构代码" + BankNum + ")" + "测试运行报告" + "_" + MyDate, _
- ShowMessage:=True, _
- IncludeAttachment:=True
- End Sub
Sub SendMail()Dim BankNum As StringDim BankName As StringDim MyDate As StringDim mDim nDim Address1, Address2, ResultAddress, ResultAddress2n = 0MyDate = DateBankNum = Sheet1.Range("BankNum").ValueBankName = Sheet1.Range("BankName").ValueSheet2.Range("A3").Value = "$D$4"Sheet2.Range("D4:D200").EntireRow.DeleteFor 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 IfNextActiveSheet.Outline.ShowLevels RowLevels:=2Range("RangeTestCases").EntireColumn.Hidden = TrueApplication.ThisWorkbook.SaveMsgBox "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:="XXX@sysnew.com", _ Subject:=BankName + "(机构代码" + BankNum + ")" + "测试运行报告" + "_" + MyDate, _ ShowMessage:=True, _ IncludeAttachment:=TrueEnd Sub
上面也就是发送邮件功能,中间应该加上application.updatescreen=false 防止在发送过程中弹出对话框,还可做另外一点优化就是只发送测试统计结果而不发送测试案例。简单的设置一下应该就可以完成。
ActiveWorkbook.SendForReview _
Recipients:="XXX@sysnew.com", _
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
Sub ResetTestResult()Dim iDim Msg, Style, Title, Help, Ctxt, Response, MyStringMsg = "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 IfNextSheet1.Range("BankNum").Value = ""Sheet1.Range("BankName").Value = ""Sheet1.Range("N7:P954").ClearContentsSheet2.Range("D4:D200").EntireRow.DeleteMsgBox "All results cleared"End IfEnd Sub
最后一步即清除运行记录结果,考虑到需要重复使用,所以加入此功能。
其实还可以将运行失败的案例单独提出另存一个sheet。也便于测试跟踪和汇报。
- Excel 在测试中的应用(二)--添加功能键
- 第二讲:Excel 在测试中的应用
- VBA在Excel中的应用(二)
- VBA在Excel中的应用(二)
- 第一讲:Excel在测试活动中的应用
- excel在sap中的应用
- BurnInTest在测试中的应用
- UML在测试中的应用
- AspectJ在测试中的应用
- echarts功能键如何添加
- VBA在Excel中的应用(一)
- VBA在Excel中的应用(一)
- VBA在Excel中的应用(四)
- 公式在Excel报表中的应用
- VBA在Excel中的应用(一)
- VBA在Excel中的应用(三)
- VBA在Excel中的应用(四)
- SQL在Excel中的应用方法
- jquery实现页面滚动到最下方自动按分页的形式加载内容效果
- 笔记通过查询创建表
- 多线程编程2 - NSOperation
- Eclipse使用过程中出现java.lang.NoClassDefFoundError的解决方案
- JMS调用IBM MQ监听模式
- Excel 在测试中的应用(二)--添加功能键
- perl-opengl几何变换函数
- 有效的沟通非常重要
- 对象和函数
- Uva 540 Team Queue
- AWS 上 Apache httpd 服务器性能调优
- 数学杂记
- 什么是java
- Codeforces #165 div2 B Multithreading