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

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ì)。基于Excel環(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限定按鈕執(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按鈕過程代碼則位于工作表代碼模塊中，且其對(duì)應(yīng)的正確執(zhí)行過程名為其按鈕名與相應(yīng)的事件名的連接串。為了查找某個(gè)按鈕對(duì)應(yīng)的過程代碼，需要對(duì)VBE（VisualBasic編輯器）的下層對(duì)象VBComponents進(jìn)行搜索。VBComponents對(duì)象代表VBE編輯器下的各個(gè)代碼模塊VBComponent。通過對(duì)某一VBComponent的下一層對(duì)象CodeModule的相關(guān)屬性的訪問，可以獲得諸如模塊代碼總行數(shù)等信息，也可以通過其Find方法查找指定內(nèi)容的代碼行。下面即為查找按鈕過程、處理按鈕過程代碼的算法代碼。SheetsCount=ActiveWorkbook.Worksheets.Count'對(duì)所有工作表中的所有按鈕對(duì)象循環(huán)Fori=1ToSheetsCountSetMySheet=Worksheets(i)MySheet.ActivateForEachshInMySheet.Shapessh.Visible=Truesh.Select'取得選定對(duì)象所對(duì)應(yīng)的過程名MyProname=sh.OnAction'對(duì)于ActiveX按鈕，則只取得其onClick事件過程名Ifsh.Type=msoOLEControlObjectThen

MyProname=sh.Name&"_Click"EndIf……'StartLine、StartCol為設(shè)置查找按鈕過程名的開始行、列序號(hào)，并在代碼查找成功時(shí)'返回所在的代碼行、列序號(hào)StartLine=1StartCol=1SetMyCoponent=Application.VBE.ActiveVBProject.VBComponents'在各代碼模塊中查找按鈕過程ForEachchInMyCoponent

Ifsh.Type=msoOLEControlObjectAndch.Name<>sh.Parent.CodeNameThen

GoToLabel4EndIf'本代碼模塊的代碼總行數(shù)

LinesCount=ch.CodeModule.CountOfLines

Endline=LinesCount

'忽略注釋行，查找正確的過程頭部位置

DoWhilech.CodeModule.Find("Sub"&MyProname&"()"，StartLine，StartCol，Endline，1，F(xiàn)alse，F(xiàn)alse)AndLeft(Trim(ch.CodeModule.Lines(StartLine，1))，1)="'"StartLine=StartLine+1StartCol=1Endline=LinesCount

Loop

'找到了過程頭部位置

Ifch.CodeModule.Find("Sub"&MyProname&"()"，StartLine，StartCol，LinesCount，1，F(xiàn)alse，F(xiàn)alse)Andch.CodeModule.ProcOfLine(StartLine，vbext_pk_Proc)=MyPronameThen

'若還沒有插入規(guī)定的代碼

IfTrim(ch.CodeModule.Lines(StartLine+1，1))<>"'隱藏過程代碼"Then

'代碼1……，在按鈕過程代碼的首部加入過程調(diào)用代碼

'代碼2……，調(diào)整本代碼行以后的已經(jīng)插入的代碼行調(diào)用參數(shù)

EndifEndIfLabel4:NextNextNextiActiveWorkbook.Save2.2插入過程調(diào)用代碼經(jīng)過查找按鈕的執(zhí)行過程代碼的正確位置，就能夠限定其執(zhí)行的有效性。本文的處理方法是，在按鈕的對(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方法，將它們分別替換為正確的上述兩行代碼。這樣先插入代碼行、后替換代碼行的目的是，在插入代碼行后便于獲得準(zhǔn)確的過程起始代碼行號(hào)，從而為下面的替換代碼行語句準(zhǔn)備正確的參數(shù)。下面即是上述查找按鈕過程代碼中“代碼1”位置處的算法代碼，其功能是在查找到的按鈕過程代碼的首部加入上述的兩行代碼：ModuleName=Application.VBE.SelectedVBComponent.Nam'獲得過程所處的代碼模塊名CodeName=ch.Name'插入兩行臨時(shí)代碼行ch.CodeModule.InsertLinesStartLine+1，"'插入代碼行1"ch.CodeModule.InsertLinesStartLine+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.CodeModule.ReplaceLineStartLine+1，"'隱藏過程代碼"ch.CodeModule.ReplaceLineStartLine+2，"Call"&ModuleName&".隱藏過程代碼("&ProcStartline&"，"&ProcCountLines&"，"&""""&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）按鈕的對(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)致與之處于同一代碼模塊的其他按鈕過程、并已經(jīng)添入的上述過程調(diào)用代碼中的參數(shù)值出現(xiàn)偏差，因此需要對(duì)其中的過程代碼起始行參數(shù)值作修改。下面的代碼就是起這個(gè)作用，此代碼須插入于前述查找按鈕過程代碼的“代碼2”位置。'調(diào)整代碼查找起始位置Line1=StartLine+3Col1=1Endline=LinesCountDoIfNotch.CodeModule.Find("'隱藏過程代碼"，Line1，Col1，Endline，1，F(xiàn)alse，F(xiàn)alse)Then

ExitDoEndIf'如果查找的代碼不符合插入的代碼格式，則繼續(xù)查找IfCol1>1Then

GoToLabel3EndIf'調(diào)整代碼調(diào)用參數(shù)Str1=ch.CodeModule.Lines(Line1+1，1)IfInStr(Str1，"Call")ThenStr2=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.ReplaceLineLine1+1，Str1EndIfLabel3:Line1=Line1+1Col1=1Endline=LinesCountLoop2.3限定按鈕對(duì)象及其執(zhí)行過程代碼限定按鈕對(duì)象本身，之前我們可以為之添加一個(gè)名為“按鈕運(yùn)行次數(shù)記錄表”的工作表，以便使用其第i行j列的單元格來記錄當(dāng)前工程第i個(gè)工作表上第j個(gè)形狀對(duì)象的運(yùn)行次數(shù)。此外，為了實(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)。下面的代碼需要與前述的查找按鈕過程代碼位于同一代碼模塊。PublicSub隱藏過程代碼(ByValBeginlineAsInteger，ByValLinesCountAsInteger，ByValCodeNameAsString，ByValSheetIndexAsInteger，ByValButtonIndexAsLong)……

Sheets("按鈕運(yùn)行次數(shù)記錄表").Cells(SheetIndex，ButtonIndex).Value=Sheets("按鈕運(yùn)行次數(shù)記錄表").Cells(SheetIndex，ButtonIndex).Value+1

IfSheets("按鈕運(yùn)行次數(shù)記錄表").Cells(SheetIndex，ButtonIndex).Value>=5ThenSetMyCoponent=Application.VBE.ActiveVBProject.VBComponentsForEachchInMyCoponentIfch.Name<>CodeNameThen

GoToLabel5EndIf'將參數(shù)規(guī)定范圍的代碼改為注釋Fork=BeginlineToBeginline+LinesCount-1

Str1=ch.CodeModule.Lines(k，1)

Str1="'隱藏行"&k-Beginline+1&":"&Str1

ch.CodeModule.ReplaceLinek，Str1Nextk'隱藏執(zhí)行的按鈕ActiveWorkbook.Sheets(SheetIndex).Shapes(ButtonIndex).Visible=FalseLabel5:Next……

EndIfEndSub3支撐對(duì)象與軟件恢復(fù)提供對(duì)VBE及其下層對(duì)象的訪問，需要?jiǎng)?chuàng)建對(duì)其支撐對(duì)象的引用，方法是進(jìn)入VBE編輯環(huán)境，單擊“工具”菜單的“引用”命令，然后加入對(duì)“MicrosoftVisualBasicforApplicationExtensibility5.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ù)功能：PublicSubAUTO_OPEN()'查找輔助工作表SheetsCount=Application.ActiveWorkbook.Worksheets.CountFori=1ToSheetsCountIfActiveWorkbook.Sheets(i).Name="按鈕運(yùn)行次數(shù)記錄表"Then

FoundSheet=TrueEndIfNexti'添加或清除輔助工作表內(nèi)容IfNotFoundSheetThenActiveWorkbook.UnprotectWorksheets.add.MoveAfter:=Worksheets(SheetsCount)ActiveSheet.Name="按鈕運(yùn)行次數(shù)記錄表"ActiveSheet.Visible=FalseElseSheets("按鈕運(yùn)行次數(shù)記錄表").Cells.ClearSheets("按鈕運(yùn)行次數(shù)記錄表").Visible=FalseEndIf'將工作表中按鈕恢復(fù)為顯示狀態(tài)SheetsCount=ActiveWorkbook.Worksheets.CountFori=1ToSheetsCountSetMySheet=Worksheets(i)ForEachshInMySheet.Shapes

Ifsh.Visible=FalseThensh.Visible=TrueNex