VBA在限定Excel工作表用户按钮中的应用

摘　要 通过研究VBE及其下层对象的访问方法，本文详细地探讨了按钮过程的代码控制技术，从而实现了Excel测试软件中工作表用户按钮的执行限定。

关键词  按钮过程  VBE  下层对象   代码限定

1  引言
作为一个优秀的表格处理软件和系统开发平台，Excel拥有许多无可替代的优势。基于Excel环境开发的管理信息系统，具有操作直观性强、开发周期短的特点，因此形成了熟悉Excel操作的广大用户。为了保证开发系统的正常渠道流通及著作者利益，必须保护系统的使用权限，本文通过VBA的开发应用，提出了一种Excel工作表用户按钮的限定方法，以此来完善其系统测试软件的功能。
2  限定按钮执行及其对应过程代码
限定工作表用户按钮的执行包括两个方面的含义，即限定其按钮对象的显示及其指定宏过程代码的有效性，两者结合在一起加以实现，才能起到既展现软件功能，又真正限制其过分执行操作的作用，从而体现对用户软件的测试目的。
2.1 查找按钮执行过程代码
限定按钮对象及其执行代码，首要的问题便是获得其对应的执行代码过程名，并在当前工程的所有代码模块中查找其代码位置。
实践表明，按钮对象与图形图像和字体一样，都属于Shape形状对象，它们都有其对应的OnAction属性，代表其被指定的宏过程代码名称。需要指出的是窗体类型按钮对应的宏过程代码一般位于工程的标准模块中，而ActiveX按钮过程代码则位于工作表代码模块中，且其对应的正确执行过程名为其按钮名与相应的事件名的连接串。为了查找某个按钮对应的过程代码，需要对VBE（Visual Basic 编辑器）的下层对象VBComponents进行搜索。VBComponents对象代表VBE编辑器下的各个代码模块VBComponent。通过对某一VBComponent的下一层对象CodeModule的相关属性的访问，可以获得诸如模块代码总行数等信息，也可以通过其Find方法查找指定内容的代码行。下面即为查找按钮过程、处理按钮过程代码的算法代码。
SheetsCount = ActiveWorkbook.Worksheets.Count
'对所有工作表中的所有按钮对象循环
For i = 1 To SheetsCount
 Set MySheet = Worksheets(i)
 MySheet.Activate
 For Each sh In MySheet.Shapes
  sh.Visible = True
  sh.Select
  '取得选定对象所对应的过程名
  MyProname = sh.OnAction
  '对于ActiveX按钮,则只取得其onClick事件过程名
  If sh.Type = msoOLEControlObject Then
    MyProname = sh.Name & "_Click"
  End If
  ……
  ' StartLine、StartCol为设置查找按钮过程名的开始行、列序号，并在代码查找成功时' 返回所在的代码行、列序号
  StartLine = 1
  StartCol = 1
  Set MyCoponent = Application.VBE.ActiveVBProject.VBComponents
  '在各代码模块中查找按钮过程
  For Each ch In MyCoponent
    If sh.Type = msoOLEControlObject And ch.Name <> sh.Parent.CodeName Then
      GoTo Label4
  End If
'本代码模块的代码总行数
    LinesCount = ch.CodeModule.CountOfLines
    Endline = LinesCount
    '忽略注释行，查找正确的过程头部位置
    Do While ch.CodeModule.Find("Sub " & MyProname & "()", StartLine, StartCol, Endline, 1, False, False) And Left(Trim(ch.CodeModule.Lines(StartLine, 1)), 1) = "'"
        StartLine = StartLine + 1
        StartCol = 1
        Endline = LinesCount
    Loop
    '找到了过程头部位置
    If ch.CodeModule.Find("Sub " & MyProname & "()", StartLine, StartCol, LinesCount, 1, False, False) And ch.CodeModule.ProcOfLine(StartLine, vbext_pk_Proc) = MyProname Then
    '若还没有插入规定的代码
    If Trim(ch.CodeModule.Lines(StartLine + 1, 1)) <> "'隐藏过程代码" Then
    '代码1……,在按钮过程代码的首部加入过程调用代码
    '代码2……,调整本代码行以后的已经插入的代码行调用参数
    Endif
   End If
Label4:
Next
Next
Next i
ActiveWorkbook.Save
2.2 插入过程调用代码
经过查找按钮的执行过程代码的正确位置，就能够限定其执行的有效性。本文的处理方法是，在按钮的对应过程代码的首部加入两行代码，如：
'隐藏过程代码
Call 模块5.隐藏过程代码(256,73,”Sheet2”,2,3)
可以看出第一行代码是一注释行，第二行代码是一过程调用。正是这一过程调用，在按钮点击时首先被执行，从而实现了对按钮执行的准确计数，并在按钮执行到达规定次数时，阻止其执行。
下面的代码通过对找到的按钮过程所在代码模块VBComponent的下层对象CodeModule采用InsertLines方法，首先在按钮对应过程的首部插入两行临时代码，然后通过该对象的ReplaceLine方法，将它们分别替换为正确的上述两行代码。这样先插入代码行、后替换代码行的目的是，在插入代码行后便于获得准确的过程起始代码行号，从而为下面的替换代码行语句准备正确的参数。下面即是上述查找按钮过程代码中“代码1”位置处的算法代码，其功能是在查找到的按钮过程代码的首部加入上述的两行代码：
ModuleName = Application.VBE.SelectedVBComponent.Nam' 获得过程所处的代码模块名
  CodeName = ch.Name
  ' 插入两行临时代码行
  ch.CodeModule.InsertLines StartLine + 1, "'插入代码行1"
  ch.CodeModule.InsertLines StartLine + 2, "'插入代码行2"
  ' 获得模块代码总行数、过程起始行号、过程代码总行数
  LinesCount = ch.CodeModule.CountOfLines
' 过程代码起始行号
  ProcStartline = ch.CodeModule.ProcStartline(MyProname, vbext_pk_Proc)
' 过程代码总行数
  ProcCountLines = ch.CodeModule.ProcCountLines(MyProname, vbext_pk_Proc)
' 替换为两行正确的代码行
  ch.CodeModule.ReplaceLine StartLine + 1, "'隐藏过程代码"
  ch.CodeModule.ReplaceLine StartLine + 2, "Call " & ModuleName & ".隐藏过程代码(" & ProcStartline & "," & ProcCountLines & "," & """" & CodeName & """" & "," & i & "," & sh.ZOrderPosition & ")"
此外，当按钮被点击执行并进入到插入的调用过程内部时，还需要确定哪个按钮被执行、执行的次数情况、以及执行按钮的对应过程代码的行范围等情况，以便准确地实施对执行按钮及其过程代码的控制，所以在上述为按钮过程替换为正确的过程调用代码的同时，需要添入正确的下列五个过程调用参数：
（1）按钮的对应过程头部所在代码模块中的代码起始行号；
（2）按钮过程的对应代码行数；
（3）按钮过程所处的代码模块名；
（4）按钮所在的工作表序号；
（5）按钮在所在工作表上的Shape形状对象集合的序号。
从上面的插入代码中可以看出获得这些参数的方法。针对上例的过程调用语句：Call 模块5.隐藏过程代码(256,73,”Sheet2”,2,3)，其参数含义是：当第2个工作表中的第3个Shape对象（按钮）执行次数到达规定次数时，则将名称为“Sheet2”的代码模块中从256行起的73行代码设为无效，并将该按钮进行隐藏。
值得注意的是，由于某些按钮的对应过程代码加入了上述的过程调用代码，必然导致与之处于同一代码模块的其他按钮过程、并已经添入的上述过程调用代码中的参数值出现偏差，因此需要对其中的过程代码起始行参数值作修改。下面的代码就是起这个作用，此代码须插入于前述查找按钮过程代码的“代码2”位置。
' 调整代码查找起始位置
 Line1 = StartLine + 3
 Col1 = 1
 Endline = LinesCount
 Do
  If Not ch.CodeModule.Find("'隐藏过程代码", Line1, Col1, Endline, 1, False, False) Then
       Exit Do
  End If
' 如果查找的代码不符合插入的代码格式，则继续查找
  If Col1 > 1 Then
     GoTo Label3
  End If
' 调整代码调用参数
  Str1 = ch.CodeModule.Lines(Line1 + 1, 1)
  If InStr(Str1, "Call") Then
   Str2 = Mid(Str1, InStr(Str1, "(") + 1, InStr(Str1, ",") - InStr(Str1, "(") + 1)
   Str2 = Trim(Str(Val(Trim(Str2)) + 2))
   Str1 = Left(Str1, InStr(Str1, "(")) & Str2 & Mid(Str1, InStr(Str1, ","))
   ch.CodeModule.ReplaceLine Line1 + 1, Str1
  End If
Label3:
  Line1 = Line1 + 1
  Col1 = 1
  Endline = LinesCount
 Loop
2.3 限定按钮对象及其执行过程代码
限定按钮对象本身，之前我们可以为之添加一个名为“按钮运行次数记录表”的工作表，以便使用其第i行j列的单元格来记录当前工程第i个工作表上第j个形状对象的运行次数。此外，为了实现对按钮执行的准确计数，也需要在工程打开时清除其内容，为了防止工作表数据意外修改，最好将其隐藏。这些均可以通过创建自动宏来加以实现。
下面的代码即是按钮执行时首先被调用的过程，其作用为对按钮执行进行计数，在按钮执行到达规定次数（这里暂定为5次）时，隐藏该按钮，并将其执行过程代码设为无效。这里将代码行设为无效的方式是将其改成注释，方法仍然是通过访问指定的VBComponent下CodeModule对象的Lines属性，并采用ReplaceLine方法来实现。改成的注释行的格式为：'隐藏行*:原代码行，其中*号代表其在本代码模块中的行号。下面的代码需要与前述的查找按钮过程代码位于同一代码模块。
Public Sub 隐藏过程代码(ByVal Beginline As Integer, ByVal LinesCount As Integer, ByVal CodeName As String, ByVal SheetIndex As Integer, ByVal ButtonIndex As Long)
……
      Sheets("按钮运行次数记录表").Cells(SheetIndex, ButtonIndex).Value = Sheets("按钮运行次数记录表").Cells(SheetIndex, ButtonIndex).Value + 1
      If Sheets("按钮运行次数记录表").Cells(SheetIndex, ButtonIndex).Value >= 5 Then
        Set MyCoponent = Application.VBE.ActiveVBProject.VBComponents
        For Each ch In MyCoponent
          If ch.Name <> CodeName Then
            GoTo Label5
          End If
          '将参数规定范围的代码改为注释
For k = Beginline To Beginline + LinesCount - 1
            Str1 = ch.CodeModule.Lines(k, 1)
            Str1 = "'隐藏行" & k - Beginline + 1 & ":" & Str1
            ch.CodeModule.ReplaceLine k, Str1
          Next k
          '隐藏执行的按钮
          ActiveWorkbook.Sheets(SheetIndex).Shapes(ButtonIndex).Visible = False
Label5:
        Next
……
      End If
End Sub

3  支撑对象与软件恢复
提供对VBE及其下层对象的访问，需要创建对其支撑对象的引用，方法是进入VBE编辑环境，单击“工具”菜单的“引用”命令，然后加入对“Microsoft Visual Basic for Application Extensibility 5.3”的引用。
此外，软件运行不应该影响其本来面目，所以在其被打开时需要将其本身提供的界面恢复初态，在工程保存时将已经变为注释行的代码恢复原状，下面通过编写当前工程的自动宏AUTO_OPEN和“ThisWorkbook”模块的Workbook_BeforeSave事件过程去分别实现这两个软件恢复功能：
Public Sub AUTO_OPEN()
'查找辅助工作表
 SheetsCount = Application.ActiveWorkbook.Worksheets.Count
 For i = 1 To SheetsCount
   If ActiveWorkbook.Sheets(i).Name = "按钮运行次数记录表" Then
      FoundSheet = True
   End If
 Next i
 '添加或清除辅助工作表内容
 If Not FoundSheet Then
   ActiveWorkbook.Unprotect
   Worksheets.add.Move After:=Worksheets(SheetsCount)
   ActiveSheet.Name = "按钮运行次数记录表"
   ActiveSheet.Visible = False
 Else
   Sheets("按钮运行次数记录表").Cells.Clear
   Sheets("按钮运行次数记录表").Visible = False
 End If
 '将工作表中按钮恢复为显示状态
 SheetsCount = ActiveWorkbook.Worksheets.Count
 For i = 1 To SheetsCount
   Set MySheet = Worksheets(i)
   For Each sh In MySheet.Shapes
    If sh.Visible = False Then sh.Visible = True
   Next
 Next i
End Sub
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
'恢复注释行为原来的正式代码行
 Set MyCoponent = Application.VBE.ActiveVBProject.VBComponents
  For Each ch In MyCoponent
    StartLine = 1
    StartCol = 1
    LinesCount = ch.CodeModule.CountOfLines
    Endline = LinesCount
    Do While ch.CodeModule.Find("'隐藏行", StartLine, StartCol, Endline, 1, False, False)
      If StartCol <> 1 Then GoTo Label7
      Str1 = ch.CodeModule.Lines(StartLine, 1)
      Str1 = Mid(Str1, InStr(Str1, ":") + 1)
      ch.CodeModule.ReplaceLine StartLine, Str1
Label7:
      StartLine = StartLine + 1
      StartCol = 1
      Endline = LinesCount
    Loop
  Next
End Sub

4  结束语
综上所述，针对基于Excel VBA测试软件的使用权限设定，本文提出的通过执行过程代码的控制方法实现工作表用户按钮的限定技术，操作起来尽管存在一定的麻烦，但仍然具有相当的实用性。

[1]　云舟工作室著，中文版Excel 2000 VBA一册通，人民邮电出版社，2000.1
[2]　
Microsoft Visual Basic for Application帮助