VBA在限定Excel工作表用戶按鈕中的應用

1、VBA在限定Excel工作表用戶按鈕中的應用摘要 通過研究VBE及其下層對象的訪問方法，本文詳細地探討了按鈕過程的代碼控制技術，從而實現(xiàn)了Excel測試軟件中工作表用戶按鈕的執(zhí)行限定。關鍵詞 按鈕過程 VBE 下層對象 代碼限定1 引言作為一個優(yōu)秀的表格處理軟件和系統(tǒng)開發(fā)平臺，Excel擁有許多無可替代的優(yōu)勢。基于Excel環(huán)境開發(fā)的管理信息系統(tǒng)，具有操作直觀性強、開發(fā)周期短的特點，因此形成了熟悉Excel操作的廣大用戶。為了保證開發(fā)系統(tǒng)的正常渠道流通及著作者利益，必須保護系統(tǒng)的使用權限，本文通過VBA的開發(fā)應用，提出了一種Excel工作表用戶按鈕的限定方法，以此來完善其系統(tǒng)測試軟件的功能。2

2、 限定按鈕執(zhí)行及其對應過程代碼限定工作表用戶按鈕的執(zhí)行包括兩個方面的含義，即限定其按鈕對象的顯示及其指定宏過程代碼的有效性，兩者結合在一起加以實現(xiàn)，才能起到既展現(xiàn)軟件功能，又真正限制其過分執(zhí)行操作的作用，從而體現(xiàn)對用戶軟件的測試目的。2.1 查找按鈕執(zhí)行過程代碼限定按鈕對象及其執(zhí)行代碼，首要的問題便是獲得其對應的執(zhí)行代碼過程名，并在當前工程的所有代碼模塊中查找其代碼位置。實踐表明，按鈕對象與圖形圖像和藝術字體一樣，都屬于Shape形狀對象，它們都有其對應的OnAction屬性，代表其被指定的宏過程代碼名稱。需要指出的是窗體類型按鈕對應的宏過程代碼一般位于工程的標準模塊中，而ActiveX按鈕過

3、程代碼則位于工作表代碼模塊中，且其對應的正確執(zhí)行過程名為其按鈕名與相應的事件名的連接串。為了查找某個按鈕對應的過程代碼，需要對VBE（Visual Basic 編輯器）的下層對象VBComponents進行搜索。VBComponents對象代表VBE編輯器下的各個代碼模塊VBComponent。通過對某一VBComponent的下一層對象CodeModule的相關屬性的訪問，可以獲得諸如模塊代碼總行數(shù)等信息，也可以通過其Find方法查找指定內容的代碼行。下面即為查找按鈕過程、處理按鈕過程代碼的算法代碼。SheetsCount = ActiveWorkbook.Worksheets.Count&

4、#39;對所有工作表中的所有按鈕對象循環(huán)For i = 1 To SheetsCountSet MySheet = Worksheets(i)MySheet.ActivateFor Each sh In MySheet.Shapes sh.Visible = True sh.Select '取得選定對象所對應的過程名 MyProname = sh.OnAction '對于ActiveX按鈕,則只取得其onClick事件過程名 If sh.Type = msoOLEControlObject Then MyProname = sh.Name & "_Cl

5、ick" 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.CodeNa

6、me Then GoTo Label4 End If'本代碼模塊的代碼總行數(shù) 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(S

7、tartLine, 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

8、_Proc) = MyProname Then '若還沒有插入規(guī)定的代碼 If Trim(ch.CodeModule.Lines(StartLine + 1, 1) <> "'隱藏過程代碼" Then '代碼1,在按鈕過程代碼的首部加入過程調用代碼 '代碼2,調整本代碼行以后的已經插入的代碼行調用參數(shù) Endif End IfLabel4:NextNextNext iActiveWorkbook.Save2.2 插入過程調用代碼經過查找按鈕的執(zhí)行過程代碼的正確位置，就能夠限定其執(zhí)行的有效性。本文的處理方法是，在按鈕

9、的對應過程代碼的首部加入兩行代碼，如：'隱藏過程代碼Call 模塊5.隱藏過程代碼(256,73,”Sheet2”,2,3)可以看出第一行代碼是一注釋行，第二行代碼是一過程調用。正是這一過程調用，在按鈕點擊時首先被執(zhí)行，從而實現(xiàn)了對按鈕執(zhí)行的準確計數(shù)，并在按鈕執(zhí)行到達規(guī)定次數(shù)時，阻止其執(zhí)行。下面的代碼通過對找到的按鈕過程所在代碼模塊VBComponent的下層對象CodeModule采用InsertLines方法，首先在按鈕對應過程的首部插入兩行臨時代碼，然后通過該對象的ReplaceLine方法，將它們分別替換為正確的上述兩行代碼。這樣先插入代碼行、后替換代碼行的目的是，在插入代碼行

10、后便于獲得準確的過程起始代碼行號，從而為下面的替換代碼行語句準備正確的參數(shù)。下面即是上述查找按鈕過程代碼中“代碼1”位置處的算法代碼，其功能是在查找到的按鈕過程代碼的首部加入上述的兩行代碼：ModuleName = Application.VBE.SelectedVBComponent.Nam' 獲得過程所處的代碼模塊名 CodeName = ch.Name ' 插入兩行臨時代碼行 ch.CodeModule.InsertLines StartLine + 1, "'插入代碼行1" ch.CodeModule.InsertLines StartLin

11、e + 2, "'插入代碼行2" ' 獲得模塊代碼總行數(shù)、過程起始行號、過程代碼總行數(shù) LinesCount = ch.CodeModule.CountOfLines' 過程代碼起始行號 ProcStartline = ch.CodeModule.ProcStartline(MyProname, vbext_pk_Proc)' 過程代碼總行數(shù) ProcCountLines = ch.CodeModule.ProcCountLines(MyProname, vbext_pk_Proc)' 替換為兩行正確的代碼行 ch.CodeModul

12、e.ReplaceLine StartLine + 1, "'隱藏過程代碼" ch.CodeModule.ReplaceLine StartLine + 2, "Call " & ModuleName & ".隱藏過程代碼(" & ProcStartline & "," & ProcCountLines & "," & """" &

13、;amp; CodeName & """" & "," & i & "," & sh.ZOrderPosition & ")"此外，當按鈕被點擊執(zhí)行并進入到插入的調用過程內部時，還需要確定哪個按鈕被執(zhí)行、執(zhí)行的次數(shù)情況、以及執(zhí)行按鈕的對應過程代碼的行范圍等情況，以便準確地實施對執(zhí)行按鈕及其過程代碼的控制，所以在上述為按鈕過程替換為正確的過程調用代碼的同時，需要添入正確的下列五個過程調用參數(shù)：（1）按鈕

14、的對應過程頭部所在代碼模塊中的代碼起始行號；（2）按鈕過程的對應代碼行數(shù)；（3）按鈕過程所處的代碼模塊名；（4）按鈕所在的工作表序號；（5）按鈕在所在工作表上的Shape形狀對象集合的序號。從上面的插入代碼中可以看出獲得這些參數(shù)的方法。針對上例的過程調用語句：Call 模塊5.隱藏過程代碼(256,73,”Sheet2”,2,3)，其參數(shù)含義是：當?shù)?個工作表中的第3個Shape對象（按鈕）執(zhí)行次數(shù)到達規(guī)定次數(shù)時，則將名稱為“Sheet2”的代碼模塊中從256行起的73行代碼設為無效，并將該按鈕進行隱藏。值得注意的是，由于某些按鈕的對應過程代碼加入了上述的過程調用代碼，必然導致與之處于同一代碼

15、模塊的其他按鈕過程、并已經添入的上述過程調用代碼中的參數(shù)值出現(xiàn)偏差，因此需要對其中的過程代碼起始行參數(shù)值作修改。下面的代碼就是起這個作用，此代碼須插入于前述查找按鈕過程代碼的“代碼2”位置。' 調整代碼查找起始位置Line1 = StartLine + 3Col1 = 1Endline = LinesCountDo If Not ch.CodeModule.Find("'隱藏過程代碼", Line1, Col1, Endline, 1, False, False) Then Exit Do End If' 如果查找的代碼不符合插入的代碼格式，則繼續(xù)查

16、找 If Col1 > 1 Then GoTo Label3 End If' 調整代碼調用參數(shù) 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

17、, InStr(Str1, "(") & Str2 & Mid(Str1, InStr(Str1, ",") ch.CodeModule.ReplaceLine Line1 + 1, Str1 End IfLabel3: Line1 = Line1 + 1 Col1 = 1 Endline = LinesCountLoop2.3 限定按鈕對象及其執(zhí)行過程代碼限定按鈕對象本身，之前我們可以為之添加一個名為“按鈕運行次數(shù)記錄表”的工作表，以便使用其第i行j列的單元格來記錄當前工程第i個工作表上第j個形狀對象的運行次數(shù)。此外，為

18、了實現(xiàn)對按鈕執(zhí)行的準確計數(shù)，也需要在工程打開時清除其內容，為了防止工作表數(shù)據(jù)意外修改，最好將其隱藏。這些均可以通過創(chuàng)建自動宏來加以實現(xiàn)。下面的代碼即是按鈕執(zhí)行時首先被調用的過程，其作用為對按鈕執(zhí)行進行計數(shù)，在按鈕執(zhí)行到達規(guī)定次數(shù)（這里暫定為5次）時，隱藏該按鈕，并將其執(zhí)行過程代碼設為無效。這里將代碼行設為無效的方式是將其改成注釋，方法仍然是通過訪問指定的VBComponent下CodeModule對象的Lines屬性，并采用ReplaceLine方法來實現(xiàn)。改成的注釋行的格式為：'隱藏行*:原代碼行，其中*號代表其在本代碼模塊中的行號。下面的代碼需要與前述的查找按鈕過程代碼位于同一代碼

19、模塊。Public Sub 隱藏過程代碼(ByVal Beginline As Integer, ByVal LinesCount As Integer, ByVal CodeName As String, ByVal SheetIndex As Integer, ByVal ButtonIndex As Long) Sheets("按鈕運行次數(shù)記錄表").Cells(SheetIndex, ButtonIndex).Value = Sheets("按鈕運行次數(shù)記錄表").Cells(SheetIndex, ButtonIndex).Value + 1

20、If Sheets("按鈕運行次數(shù)記錄表").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 '將參數(shù)規(guī)定范圍的代碼改為注釋For k = Beginline To Beginline + LinesCount -

21、 1 Str1 = ch.CodeModule.Lines(k, 1) Str1 = "'隱藏行" & k - Beginline + 1 & ":" & Str1 ch.CodeModule.ReplaceLine k, Str1 Next k '隱藏執(zhí)行的按鈕 ActiveWorkbook.Sheets(SheetIndex).Shapes(ButtonIndex).Visible = FalseLabel5: Next End IfEnd Sub3 支撐對象與軟件恢復提供對VBE及其下

22、層對象的訪問，需要創(chuàng)建對其支撐對象的引用，方法是進入VBE編輯環(huán)境，單擊“工具”菜單的“引用”命令，然后加入對“Microsoft Visual Basic for Application Extensibility 5.3”的引用。此外，軟件運行不應該影響其本來面目，所以在其被打開時需要將其本身提供的界面恢復初態(tài)，在工程保存時將已經變?yōu)樽⑨屝械拇a恢復原狀，下面通過編寫當前工程的自動宏AUTO_OPEN和“ThisWorkbook”模塊的Workbook_BeforeSave事件過程去分別實現(xiàn)這兩個軟件恢復功能：Public Sub AUTO_OPEN()'查找輔助工作表Sheets

23、Count = Application.ActiveWorkbook.Worksheets.CountFor i = 1 To SheetsCount If ActiveWorkbook.Sheets(i).Name = "按鈕運行次數(shù)記錄表" Then FoundSheet = True End IfNext i'添加或清除輔助工作表內容If Not FoundSheet Then ActiveWorkbook.Unprotect Worksheets.add.Move After:=Worksheets(SheetsCount) ActiveSheet.Name

24、 = "按鈕運行次數(shù)記錄表" ActiveSheet.Visible = FalseElse Sheets("按鈕運行次數(shù)記錄表").Cells.Clear Sheets("按鈕運行次數(shù)記錄表").Visible = FalseEnd If'將工作表中按鈕恢復為顯示狀態(tài)SheetsCount = ActiveWorkbook.Worksheets.CountFor i = 1 To SheetsCount Set MySheet = Worksheets(i) For Each sh In MySheet.Shapes If sh.Visible = False Then sh.Visible = True NextNext iEnd SubPrivate Sub Wor