VBA在限定Excel工作表用戶按鈕中的應(yīng)用

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

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

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

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

5、ick" End If ' StartLine、StartCol為設(shè)置查找按鈕過程名的開始行、列序號(hào)，并在代碼查找成功時(shí)' 返回所在的代碼行、列序號(hào) 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,在按鈕過程代碼的首部加入過程調(diào)用代碼 '代碼2,調(diào)整本代碼行以后的已經(jīng)插入的代碼行調(diào)用參數(shù) Endif End IfLabel4:NextNextNext iActiveWorkbook.Save2.2 插入過程調(diào)用代碼經(jīng)過查找按鈕的執(zhí)行過程代碼的正確位置，就能夠限定其執(zhí)行的有效性。本文的處理方法是，在按鈕

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

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

11、e + 2, "'插入代碼行2" ' 獲得模塊代碼總行數(shù)、過程起始行號(hào)、過程代碼總行數(shù) LinesCount = ch.CodeModule.CountOfLines' 過程代碼起始行號(hào) 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 & ")"此外，當(dāng)按鈕被點(diǎn)擊執(zhí)行并進(jìn)入到插入的調(diào)用過程內(nèi)部時(shí)，還需要確定哪個(gè)按鈕被執(zhí)行、執(zhí)行的次數(shù)情況、以及執(zhí)行按鈕的對(duì)應(yīng)過程代碼的行范圍等情況，以便準(zhǔn)確地實(shí)施對(duì)執(zhí)行按鈕及其過程代碼的控制，所以在上述為按鈕過程替換為正確的過程調(diào)用代碼的同時(shí)，需要添入正確的下列五個(gè)過程調(diào)用參數(shù)：（1）按鈕

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

15、模塊的其他按鈕過程、并已經(jīng)添入的上述過程調(diào)用代碼中的參數(shù)值出現(xiàn)偏差，因此需要對(duì)其中的過程代碼起始行參數(shù)值作修改。下面的代碼就是起這個(gè)作用，此代碼須插入于前述查找按鈕過程代碼的“代碼2”位置。' 調(diào)整代碼查找起始位置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' 調(diào)整代碼調(diào)用參數(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 限定按鈕對(duì)象及其執(zhí)行過程代碼限定按鈕對(duì)象本身，之前我們可以為之添加一個(gè)名為“按鈕運(yùn)行次數(shù)記錄表”的工作表，以便使用其第i行j列的單元格來記錄當(dāng)前工程第i個(gè)工作表上第j個(gè)形狀對(duì)象的運(yùn)行次數(shù)。此外，為

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

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

20、If Sheets("按鈕運(yùn)行次數(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 支撐對(duì)象與軟件恢復(fù)提供對(duì)VBE及其下

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

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

24、 = "按鈕運(yùn)行次數(shù)記錄表" ActiveSheet.Visible = FalseElse Sheets("按鈕運(yùn)行次數(shù)記錄表").Cells.Clear Sheets("按鈕運(yùn)行次數(shù)記錄表").Visible = FalseEnd If'將工作表中按鈕恢復(fù)為顯示狀態(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