《Excel VBA程序開發(fā)自學(xué)寶典(第2版)》.doc

Excel VBA程序開發(fā)自學(xué) （第2版）第06章編寫SUB過程及開發(fā)函數(shù)VBA的主體結(jié)構(gòu)就是過程。VBA包括子過程、函數(shù)過程和屬性過程三種，本書主要介紹子過程（也稱Sub過程）和函數(shù)過程（也稱Function過程）。本章要點u 認識過程u Sub過程u Function過程u 關(guān)于過程參數(shù)u 開發(fā)自定義函數(shù)u 編寫函數(shù)幫助6.1認識過程VBA中每一個程序都包含過程。錄制的宏是一個過程，一個自定義函數(shù)也是一個過程。掌握好單個過程的編寫思路，就可以組合成一個大中型插件或者專業(yè)程序。6.1.1過程的分類與調(diào)用方式過程主要分為三類：子過程、函數(shù)過程和屬性過程。這三類過程的格式如下。Sub 子過程()End SubFunction 函數(shù)過程(rng As Range)End FunctionProperty Get 屬性過程() As VariantEnd Property本書主要講述Sub子過程和Function函數(shù)過程的開發(fā)。Sub過程是VBA中應(yīng)用最廣的過程，錄制宏所產(chǎn)生的過程就是Sub過程。Sub過程的執(zhí)行方式包括五種。1.【Alt+F8】執(zhí)行如果在工作表命令窗口、ThisWorkBook命令窗口或者標準模塊窗口中存在Sub過程，那么在工作表界面可以通過快捷鍵【Alt+F8】來執(zhí)行該過程。假設(shè)在VBE界面中的Sheet1代碼窗口中有一個Sub過程“匯總”，在模塊1中有一個名為“新建菜單”的Sub過程，那么通過快捷鍵【Alt+F8】打開“宏”對話框后，將在對話框中產(chǎn)生兩個可執(zhí)行程序名，其中工作表命令窗口的Sub過程會連同工作表名一起出現(xiàn)在宏名列表中，而模塊中的過程則僅僅列出過程名。用戶選擇目標程序并單擊【執(zhí)行】按鈕即可啟動Sub子過程。2.快捷鍵執(zhí)行Sub過程可以與某個快捷鍵進行關(guān)聯(lián)，在后續(xù)的使用中就可以利用這個快捷鍵來調(diào)用對應(yīng)的過程。設(shè)置Sub過程的快捷鍵主要有兩種方式：利用宏對話框設(shè)置及用VBA代碼指定。后者在本書其他章節(jié)將會講述，在此演示一下“宏”對話框設(shè)置宏的快捷鍵的方法：假設(shè)VB工程中有兩個名為“匯總”和“新建菜單”的Sub過程，在工作表界面中按下快捷鍵【Alt+F8】調(diào)出圖6.1所示對話框，然后選擇“新建菜單”，并單擊“選項”按鈕，在彈出的“宏選項”對話框中指定快捷鍵，如圖6.2所示。圖中設(shè)計宏程序“新建菜單”的快捷鍵是【Ctrl+q】。 圖6.1 “宏”對話框 圖6.2 設(shè)置Sub過程的快捷鍵3.按鈕執(zhí)行在工作表中建一個按鈕，并將按鈕與Sub過程關(guān)聯(lián)，從而實現(xiàn)單擊按鈕執(zhí)行程序。將按鈕關(guān)聯(lián)到Sub過程的步驟為：1.單擊菜單【開發(fā)工具】【表單控件】【按鈕】；2.在工作表中按下左鍵并向右下方拖動，從而繪制一個控件按鈕；3.在彈出的對話框中選擇“新建菜單”，如圖6.3所示；4.返回工作表后即可單擊名為“按鈕1”的按鈕來執(zhí)行程序“新建菜單”。圖6.3 關(guān)聯(lián)過程與按鈕4.菜單調(diào)用最常見的是編寫一個自定義菜單或者工具條來調(diào)用Sub過程。菜單與工具條的設(shè)計方法參見本書第19章及第20章。5.事件引發(fā)對于部分需要自啟動的程序，通常利用事件引發(fā)，不需要人工干預(yù)。例如工作簿開啟時就自動執(zhí)行某程序，或者關(guān)掉窗體、鼠標移過窗體時執(zhí)行某程序?qū)τ谑录^程的運用參見本書第8章。6.工作表中使用公式調(diào)用Function過程即自定義函數(shù)，可以像使用內(nèi)置的工作表函數(shù)一樣在公式中使用。調(diào)用Function過程的步驟如下：1.單擊菜單【插入】【模塊】；2.在模塊中錄入以下代碼：Function 成績(rng)成績 = IIF(rng = 60, 及格, 不及格)End Function3.返回工作表中，在A1輸入數(shù)值50，在B1輸入公式：=成績(A1)可以發(fā)現(xiàn)公式可以像內(nèi)置函數(shù)一樣運行，它返回“不及格”，正是期望的結(jié)果。6.1.2插入過程的方式編寫過程時可以手工錄入代碼，也可以讓利用VBA提供的列表自動產(chǎn)生程序外殼。一個Sub過程分為程序外殼部分和主體部分。如圖6.4所示。 圖6.4 Sub過程的外殼與主體部分示意圖其中外殼部分可以手工錄入，也可以利用VBE提供的方式完成。1.非事件過程對于非事件的Sub 過程，VBA提供了一個專用窗體來選擇性錄入過程的外殼。具體步驟如下：1.在VBE界面中單擊菜單【插入】【模塊】；2.單擊菜單【插入】【過程】打開“添加過程”對話框；3.在“名稱”框中錄入“匯總”，并將“類型”選擇“子過程”，將“范圍”設(shè)為“私有的”，如圖6.5所示。然后單擊“確定”按鈕。圖6.5 添加Sub過程外殼圖執(zhí)行以上程序后在模塊中可以看到產(chǎn)生的代碼為：Private Sub 匯總()End Sub如果是Function函數(shù)過程，也可以按照上述方法錄入過程的外殼。2.事件類過程VBA支持很多類事件，大部分事件的代碼都需要參數(shù)。而這些參數(shù)是很難記憶的，包括所有VBA專業(yè)程序員。為了快速且準確地錄入事件類過程，可以通過VBE提供的對象與過程窗口的下拉列表完成。例如輸入工作表SelectionChange事件的過程，方法如下：（1）使用快捷鍵【Alt+F11】進入VBE界面，并用快捷鍵【Ctrl+g】打開工程資源管理器窗口；（2）雙擊Sheet1或者其他需要錄入工作表事件的工作表名；（3）從對象窗口的下拉框中選擇“Worksheet”，代碼窗口默認產(chǎn)生以下代碼：Private Sub Worksheet_SelectionChange(ByVal Target As Range)End Sub 圖6.6 從下拉列表選擇對象因為VBA默認狀態(tài)下就是彈出“Worksheet_SelectionChange”事件的代碼，所以當(dāng)選擇對象為“Worksheet”后就產(chǎn)生了需要的代碼。如果需要錄入“Worksheet_Change”事件的代碼，則需要在選擇對象“Worksheet”后，再選擇過程“Change”，然后將產(chǎn)生的“Worksheet_SelectionChange”事件的代碼刪除，僅保留以下代碼：Private Sub Worksheet_Change(ByVal Target As Range)End Sub對于此類包含參數(shù)的事件過程，應(yīng)該盡量選擇通過對象與過程窗口的下拉列表產(chǎn)生代碼的方式，手工錄入很容易產(chǎn)生誤差。在用戶窗體中很多事件也支持參數(shù)，而且有多個參數(shù)，通常也需要從列表中選擇對象與過程的方式來錄入代碼。例如在窗體中錄入鼠標移過事件的過程代碼，步驟如下：1.單擊菜單【插入】【用戶窗體】；2.使用快捷鍵【Ctrl+g】顯示工程資源管理器，并在UserForm1（或者別的名稱）上單擊右鍵，選擇菜單【查看代碼】； 3.從對象窗口選擇“UserForm1”，此時默認產(chǎn)生“UserForm_Click”事件的代碼；再從過程窗口選擇“MouseMove”，代碼窗口中將產(chǎn)生以下代碼：Private Sub UserForm_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)End Sub4.刪除“UserForm_Click”事件的代碼。6.1.3過程的命名規(guī)則過程的命名與變量的命名規(guī)則一致。但需要補充的一點是：過程名可以和本過程的私有變量同名，但卻不能和公有變量同名。例如：Sub 身份證() Dim 身份證 As String 身份證 = a1.TextEnd Sub以上代碼中過程與變量同名，但這是允許的。 Dim 身份證 As StringSub 身份證() 身份證 = a1.TextEnd Sub這段代碼卻是非法的，只要運行程序就會彈出編譯錯誤。為了避免錯誤及便于識別，需要使本過程的私有變量也盡量保持與過程名不相同。6.2編寫Sub過程本節(jié)開始了解關(guān)于Sub過程的基本概念，以及編寫簡單的Sub過程。6.2.1 Sub過程的語法解析Sub過程即利用Sub語句聲明的子過程。所有宏錄制器產(chǎn)生的過程全是Sub過程，無法通過錄制宏產(chǎn)生Function過程或者屬性過程。Sub語句聲明過程的語法如下：Private | Public | Friend Static Sub name (arglist) statements Exit Sub statementsEnd Sub其中各參數(shù)的詳細功能如表6-1所示。 表6-1Sub語句參數(shù)詳解參數(shù)部分功能解釋Public可選的。表示所有模塊的所有其他過程都可訪問這個 Sub 過程。 如果在包含 Option Private 的模塊中使用，則這個過程在該工程外是不可使用的Private可選的。表示只有在包含其聲明的模塊中的其他過程可以訪問該 Sub 過程Friend可選的。只能在類模塊中使用。表示該 Sub 過程在整個工程中都是可見的，但對對象實例的控制者是不可見的Static可選的。表示在調(diào)用之間保留 Sub 過程的局部變量的值。Static 屬性對在 Sub 外聲明的變量不會產(chǎn)生影響，即使過程中也使用了這些變量name必需的。Sub 的名稱；遵循標準的變量命名約定arglist可選的。代表在調(diào)用時要傳遞給 Sub 過程的參數(shù)的變量列表。多個變量則用逗號隔開statements可選的。Sub 過程中所執(zhí)行的任何語句組Sub過程與所有變量一樣，也區(qū)分公有和私有，而在說法上稍有區(qū)別。過程分模塊級過程和工程級過程。1.模塊級過程模塊級過程即只能在當(dāng)前模塊調(diào)用的過程，它的特征有三個：（1）聲明Sub過程前使用Private；（2）只有當(dāng)前模塊可以調(diào)用，例如在“模塊1”中有以下代碼：Private Sub 過程一() MsgBox 123End SubPrivate Sub 過程二() Call 過程一End Sub執(zhí)行過程二時可以調(diào)用過程一，但如果過程二存放于“模塊2”中，則將彈出“子過程未定義”的錯誤提示。（3）不出現(xiàn)在“宏”對話框中，即使用快捷鍵【Alt+F8】所打開的對話框中無法查看到當(dāng)前過程的名稱列表。如果是Function過程，則無法在函數(shù)向?qū)е胁榭吹胶瘮?shù)名。提示：所有事件的代碼都是過程級的，默認狀態(tài)下只能在當(dāng)前過程可以調(diào)用。2.工程級過程工程級過程是指在當(dāng)前工程中任意地方都可以隨意調(diào)用的過程。它的特征剛好與模塊級過程相反：在“Sub”語句前置標識符“Public”、非當(dāng)前過程可以調(diào)用，可以出現(xiàn)在“宏”對話框中。如果一個過程沒有使用“Public”和“Private”標識，則默認為公有過程，任何模塊或者窗體中都可以調(diào)用。Sub過程也支持參數(shù)，其參數(shù)的用法與Function過程的參數(shù)用法一致，本小節(jié)不詳述，請參閱本書6.4節(jié)。3.Exit sub與End的作用與區(qū)別Sub過程可以在程序執(zhí)行期間隨時退出程序，通常是設(shè)定若干個條件。當(dāng)滿足條件時使用“Exit Sub”語句來終止程序?！癊xit Sub”語句后面的代碼不再執(zhí)行。也可以使用“End”語句來退出程序?！癊nd”和“Exit Sub”在使用中有相同處，也有明顯的差別。相同處是都可以中途終止運行程序，不同處則有以下兩點。（1）是否釋放公有變量從以下三段代碼可以體現(xiàn)“End”和“Exit Sub”的差異：Dim x As Long Sub A() x = 888 Exit SubEnd SubSub B() x = 888 EndEnd SubSub C() MsgBox xEnd Sub代碼中X是公有變量，當(dāng)執(zhí)行過程A后執(zhí)行過程C，那么變量X的值為888，表示X變量的值在過程中并沒有釋放，“Exit Sub”僅僅退出程序執(zhí)行，公有變量的值保持不變。如果執(zhí)行過程B再執(zhí)行過程C，那么X的值則為0，說明在過程B中的“End”語句已經(jīng)釋放變量X的值。（2）是否終止所有程序仍然用三個過程來演示“End”和“Exit Sub”的差異：Sub A() Call B MsgBox 終止End SubSub B() Exit Sub End SubSub C() EndEnd Sub執(zhí)行程序A的結(jié)果是彈出對話框“終止”，而將過程A中的“Call B”修改為“Call C”，那么什么反應(yīng)也沒有。也就是“Exit Sub”是退出它所在的程序，而“End”則中止所有程序，包括調(diào)用它的程序。如果在窗體代碼中，“Exit Sub”僅僅退出事件，而“End”則退出事件后關(guān)掉窗體，窗體中聲明的所有變量全部釋放。6.2.2 Sub過程的執(zhí)行流程如果錄制宏并執(zhí)行宏，可以看出宏代碼的執(zhí)行流程永遠是從上到下?？梢允褂谜{(diào)試功能來查看流程。例如執(zhí)行以下代碼：Sub 設(shè)置A1單元格() Range(A1).Select Range(A1) = 中華人民共和國 Range(A1).Interior.Color = 65535 Range(A1).Font.ColorIndex = 3 Range(A1).Borders.LineStyle = xlContinuous Range(A1).Font.Name = 黑體 Range(A1).Font.Size = 20 Range(A1).EntireColumn.AutoFitEnd Sub將VBE窗口縮小，使自己能同時看到代碼及A1單元格的情況下再按下快捷鍵【F8】，從而進入逐句調(diào)試階段。注意：在VBE中使用【F8】鍵表示調(diào)試代碼語句，每按一次【F8】鍵即執(zhí)行一句，忽略變量與常量的聲明語句，直到“Exit Sub”或者“End”、“End Sub”為止。在編寫代碼時非常有用，可以借助它檢查代碼的準確性，同時也可以查看程序間的跳轉(zhuǎn)是否正常（當(dāng)有標簽設(shè)置和嵌套調(diào)用的時候）。當(dāng)按下調(diào)試鍵【F8】時，當(dāng)前執(zhí)行的語句呈黃色顯示，再次按下【F8】鍵時，則下一句呈黃色顯示，而操作對象A1則對應(yīng)產(chǎn)生變化。圖6.7中已執(zhí)行到第四句，所以A2單元格同步后的狀態(tài)就是已錄入“中華人民共和國”，且并設(shè)置了黃色背景色。 圖6.7 逐步執(zhí)行代碼當(dāng)繼續(xù)通過【F8】鍵執(zhí)行完成的代碼后，可以得出結(jié)論：所有錄制的宏和未特別指定程序跳轉(zhuǎn)的VBA代碼總是按照從上至下的流程逐句執(zhí)行。那么是否有例外呢？通常在以下三種情況會有例外。1.使用冒號實現(xiàn)一行執(zhí)行多句代碼VBA中允許借助冒號將多句代碼寫在同一行執(zhí)行。對同行中的代碼按從左向右的順序執(zhí)行。例如：Sub 設(shè)置A1單元格() Range(A1) = 中華人民共和國: Range(A1).Interior.Color = 65535 Range(A1).Font.ColorIndex = 3: Range(A1).Font.Size = 20 Range(A1).EntireColumn.AutoFitEnd Sub以上代碼在借助冒號將前四行代碼縮至兩行，但執(zhí)行過程仍然為四步。對于同行中有多句代碼時，按從左向右的順序執(zhí)行。那么讀者一定可以想到，使用冒號和不使用冒號的執(zhí)行結(jié)果豈不是完全一致？僅僅改變了行數(shù)？答案是“有時一致，有時不一致”。如果以上的代碼按如下方式編寫，那么通過冒號改變行數(shù)后執(zhí)行結(jié)果完全一致：Sub 設(shè)置A1單元格() Range(A1) = 中華人民共和國 Range(A1).Interior.Color = 65535 Range(A1).Font.ColorIndex = 3 Range(A1).Font.Size = 20 Range(A1).EntireColumn.AutoFitEnd Sub而在下面的情況中，使用冒號后卻可以得到完全不同的結(jié)果：Sub 判斷是否及格1() IF B2 = 60 Then C3 = 及格: Exit Sub IF B3 = 60 Then C3 = 及格End SubSub 判斷是否及格2() IF B2 = 60 Then C3 = 及格 Exit Sub IF B3 = 60 Then C3 = 及格End Sub假設(shè)工作表中有圖6.8所示數(shù)據(jù)，執(zhí)行過程“判斷是否及格1”時，C3單元格將出現(xiàn)“及格”；而執(zhí)行過程“判斷是否及格2”時則無任何反應(yīng)。也就是說“Exit Sub”語句與IF同行時，只有單元格B2的值大于或等于60，“Exit Sub”語句才會執(zhí)行。在本例中不符合條件，那么沒有退出程序，可以繼續(xù)執(zhí)行其后的代碼。而“Exit Sub”語句單獨占據(jù)一行時，不管單元格B2是否符合條件，“Exit Sub”都會執(zhí)行，從而退出程序，不再對B3的值進行判斷。圖6.8 數(shù)據(jù)2.使用標簽改變執(zhí)行流程VBA可以在代碼中設(shè)置一個或者多個標簽，然后讓程序在滿足某條件時跳轉(zhuǎn)到標簽處，從而改變過程執(zhí)行流程。標簽的規(guī)則是：u 可以是標點符號以外的字符組合u 以冒號（:）結(jié)尾u 與大小寫無關(guān)u 必須位于一行的最左端u 配合GoTo使用例如，建立一個名為“總表”的工作表，代碼如下：Sub 新建總表() For i = 1 To Sheets.Count IF Sheets(i).Name = 總表 Then GoTo err Next i Sheets.Add ActiveSheet.Name = 總表 Enderr: MsgBox 已經(jīng)存在總表End Sub以上代碼首先利用For循環(huán)逐一檢查工作表的名字，如果某個工作表的名字等于“總表”則執(zhí)行標簽“Err”之后的代碼，否則繼續(xù)執(zhí)行For循環(huán)，直到循環(huán)完成并新建一個工作表且命名為“總表”。使用標簽完成當(dāng)前程序間的跳轉(zhuǎn)時需要注意兩點：（1）標簽名后面必須帶有冒號。（2）在標簽之前根據(jù)需要，及時退出程序。在本例中，按照設(shè)計意圖，只要工作簿中存在“總表”則執(zhí)行標簽“Err”之后的語句，反之不執(zhí)行。所以標簽之前必須加入“End”或者“Exit Sub”來退出程序，否則任何情況下Err后的語句都會被執(zhí)行。在一個過程中還可以定義多個標簽。例如：Sub 新建總表() MsgBox ActiveWorkbook.ProtectWindows IF ActiveWorkbook.ProtectWindows = true Then GoTo 已加密 For i = 1 To Sheets.Count IF Sheets(i).Name = 總表 Then GoTo 已存在 Next i Sheets.Add ActiveSheet.Name = 總表 End已存在: MsgBox 已經(jīng)存在總表 End已加密: MsgBox 當(dāng)前工作簿窗口已鎖定，無法建立新表End Sub在此過程中，首先判斷當(dāng)前工作表的窗口是否鎖定，如果鎖定則執(zhí)行“已加密”標簽后的語句；然后再檢查是否存在“總表”，當(dāng)有“總表”時執(zhí)行“已存在”標簽后的語句。本例中兩個標簽沒有順序上的差異，誰前誰后不影響代碼的結(jié)果。3.Sub過程的嵌套調(diào)用方式過程與過程之間是可以相互調(diào)用的，從而使代碼的執(zhí)行流程改變。通過VBA代碼調(diào)用Sub子過程主要有兩種方式。u Call語句Call語句的功能是將一個過程的控制權(quán)轉(zhuǎn)移到另一個過程。它的語法為：Call name argumentlist，即Call 過程名 參數(shù)。其中Call是可選的，即在其他過程調(diào)用過程一時可以有以下兩種形式：Sub 過程一() MsgBox 你好！End SubPrivate Sub 過程二() 過程一End SubPrivate Sub 過程三() Call 過程一End Sub過程二和過程三都是合法的過程調(diào)用。u Run方法Run方法可以運行一個宏或者調(diào)用一個函數(shù)。該方法可用于運行一個用 Visual Basic 或 Excel 宏語言編寫的宏或者運行DLL或XLL中的函數(shù)。實例如下：Sub 過程四() Application.Run 過程一End Sub其中“Application.Run”也可以簡寫為“Run”。6.2.3 過程的遞歸所有過程都是可以遞歸的，即可以調(diào)用自己來完成任務(wù)。實際工作中需要調(diào)用過程本身的實例極少，通常進入遞歸都是編碼有問題而誤入遞歸狀態(tài)，結(jié)果耗盡系統(tǒng)資源。在某些情況下也可以故意調(diào)用自己來完成任務(wù)。例如下面2例。1.按條件新建工作表Sub 建立10個表() IF Sheets.Count = 10 Then Exit Sub Sheets.Add , Sheets(Sheets.Count), 1 Call 建立10個表End Sub以上代碼中，首先利用IF查找并檢測當(dāng)前工作簿的工作表數(shù)量，如果大于或等于10則退出程序，否則在最后位置新建一個工作表，最后再調(diào)用自身繼續(xù)執(zhí)行，直到滿足條件“大于或等于10”為止。因代碼中人為設(shè)置了退出遞歸的條件，所以這類遞歸不會造成程序崩潰，資源耗盡。如果將代碼中的“IF Sheets.Count = 10 Then Exit Sub”刪除，那么程序循環(huán)執(zhí)行的結(jié)果就是電腦死機，除非中途人工中斷程序執(zhí)行：使用快捷鍵【Ctrl+Break】。2.設(shè)計時鐘Sub 時間() a1 = WorksheetFunction.Text(Now(), hh:mm:ss) Application.OnTime Now() + TimeValue(00:00:01), 時間End SubSub 終止() Application.OnTime Now() + TimeValue(00:00:01), 時間, , falseEnd Sub以上代碼實現(xiàn)的效果是在單元格顯示當(dāng)前時間，包括時、分、秒，且每秒鐘更新一次。通過遞歸方式讓程序每秒鐘執(zhí)行一次實現(xiàn)時鐘的效果，同時再利用另一個過程隨時退出遞歸。當(dāng)然也可以用快捷鍵【Ctrl+Break】。6.2.4 Sub過程實例演示為了更好地理解Sub過程，通過兩個實例來展示。1.統(tǒng)計選區(qū)信息：不帶參數(shù)的Sub過程要求：對任意選區(qū)統(tǒng)計單元格個數(shù)、數(shù)值個數(shù)、非空單元格個數(shù)、空白單元格個數(shù)及選區(qū)之和。代碼如下：Sub 選區(qū)統(tǒng)計() Dim msg As String msg = 單元格個數(shù)： & Selection.Count & Chr(10) msg = msg & 數(shù)字個數(shù)： & WorksheetFunction.Count(Selection) & Chr(10) msg = msg & 非空單元格： & WorksheetFunction.CountA(Selection) & Chr(10) msg = msg & 空白單元格個數(shù)： & WorksheetFunction.CountBlank(Selection) & Chr(10) msg = msg & 選區(qū)之和： & WorksheetFunction.Sum(Selection) MsgBox msg, 64, 選區(qū)統(tǒng)計End Sub假設(shè)工作表中存在圖6.9所示數(shù)據(jù)，選擇A1:D9區(qū)域后利用快捷鍵【Alt+F8】執(zhí)行“選區(qū)統(tǒng)計”過程，其統(tǒng)計結(jié)果如圖6.10所示。 圖6.9工作表數(shù)據(jù) 圖6.10選區(qū)統(tǒng)計結(jié)果2.將單元格數(shù)據(jù)轉(zhuǎn)換為首字母大寫：帶有參數(shù)的Sub過程要求：在工作表中選擇任意一個帶英文的單元格時，將其轉(zhuǎn)換為每個單詞首字母大寫。（1）插入模塊1，并錄入以下代碼：Sub 轉(zhuǎn)換(Target)Selection(1) = StrConv(Target, vbProperCase)End Sub（2）雙擊工程資源管理器中的“Sheet1”，進入工作表代碼窗口后錄入代碼：Private Sub Worksheet_SelectionChange(ByVal Target As Range)Call 轉(zhuǎn)換(Target(1)End Sub（3）返回工作表“Sheet1”，單擊任意單元格，如果存在英文單詞，則每個單詞首字母大寫，否則保持不變。如單元格中有句子“You are on it”，那么單擊該單元格后將被轉(zhuǎn)換為“You Are On It”。 6.3認識Function過程Function過程即自定義函數(shù)，在插件中應(yīng)用極廣。本節(jié)介紹關(guān)于Function過程的語法及調(diào)用方法。6.3.1Function過程的特點Function過程的功能較Sub過程的應(yīng)用范圍稍小，F(xiàn)unction過程僅僅用于返回一個值或者多個數(shù)的組合，即數(shù)組，而Sub過程既可以返回值，還可以對引用的對象進行修改。例如，引用單元格A1的值后對單元格A1設(shè)置新的格式，或者修改工作表名稱等。Function可以獲取工作表名稱，但無法修改工作表的名稱。Function過程可以不使用參數(shù)，類似于工作表函數(shù)Rand和Now等，但絕大部分函數(shù)是需要一個參數(shù)或者多個參數(shù)的，最多時可達255個參數(shù)。6.3.2Function的語法解析Function的語法如下：Public | Private | Friend Static Function name (arglist) As type statements name = expression Exit Function statements name = expressionEnd FunctionFunction語句的各參數(shù)詳解如表6-2所示。表6-2Function語句參數(shù)詳解參數(shù)部分功能解釋Public可選的。表示所有模塊的所有其他過程都可以訪問這個 Function 過程。如果是在包含 Option Private 的模塊中使用，則這個過程在該工程外是不可使用的Private可選的。表示只有包含其聲明的模塊的其他過程可以訪問該 Function 過程Friend可選的。只能在類模塊中使用。表示該 Function 過程在整個過程中都是可見的，但對于對象實例的控制者是不可見的Static可選的。表示在調(diào)用之間將保留 Function 過程的局部變量值。Static 屬性對在該 Function 外聲明的變量不會產(chǎn)生影響，即使過程中也使用了這些變量name必需的。Function 的名稱，遵循標準的變量命名約定arglist可選的。代表在調(diào)用時要傳遞給 Function 過程的參數(shù)變量列表。多個變量應(yīng)用逗號隔開type可選的。Function 過程的返回值的數(shù)據(jù)類型，可以是 Byte、 Boolean 、Integer、Long、Currency、Single、Double、Decimal（目前尚不支持）、Date、String（除定長）、Object、Variant或任何用戶定義類型statements可選的。在 Function 過程中執(zhí)行的任何語句組expression可選的。Function 的返回值和Sub過程一樣，F(xiàn)unction過程也有模塊級過程和工程級過程之分。Function前置“Public”即為工程級，前置“Private”則為模塊級。Function名稱在聲明時需要遵循與Sub過程一樣的規(guī)則。如果自定義的Function名稱與VBA內(nèi)部名稱一致，仍然可以正常執(zhí)行，只是在代碼中調(diào)用Excel內(nèi)部函數(shù)時必須聲明其對象庫。例如：Function sqr(AA) sqr = AA (1 / 3)End FunctionSub test() MsgBox VBA.SQR: & VBA.sqr(27) & Chr(10) & SQR: & sqr(27)End Sub執(zhí)行test過程時的結(jié)果如圖6.11所示。圖6.11自定義SQR和內(nèi)置SQR的分別從結(jié)果可以得知，在代碼使用“VBA.SQR”可以調(diào)用VBA自帶的SQR功能，而直接使用SQR則調(diào)用自定義的SQR函數(shù)的功能。雖然定義函數(shù)時允許與內(nèi)部函數(shù)一致，但卻不允許與定義的變量或者常量一致，不管這個變量或者常量是本過程私有的還是模塊中公有的，否則將產(chǎn)生“發(fā)現(xiàn)二義性的名稱”的編譯錯誤。6.3.3調(diào)用Function過程Function過程通常以三種方式調(diào)用。（1）在工作表中通過公式調(diào)用：像內(nèi)部函數(shù)一樣在工作表中使用，也可以與其他函數(shù)嵌套。（2）在VBA代碼中被其他過程調(diào)用：就像圖6.9對應(yīng)的那段代碼一樣在Sub過程調(diào)用函數(shù)。（3）遞歸：Function過程和Sub一樣可以實現(xiàn)遞歸。如果不是刻意地、有計劃地進入遞歸狀態(tài)，可能會造成資源耗盡或者溢出堆棧空間。例如下面函數(shù)的調(diào)用：Function 遞歸(參數(shù)) 遞歸 = 遞歸(參數(shù))End FunctionSub 測試() MsgBox 遞歸(1000)End Sub將代碼錄入到模塊中后，執(zhí)行過程“測試”，立即彈出錯誤提示“溢出堆?？臻g”。為了避免遞歸造成的錯誤，甚至程序崩潰，盡量不要調(diào)用自身，開發(fā)函數(shù)、插件時多方面查核是否可能造成循環(huán)引用、遞歸現(xiàn)象。當(dāng)然，有目的、有條件的遞歸是可以給工作帶來便利的。另外，談到函數(shù)就不能不說它的“刷新”性能，即在工作表中使用函數(shù)時，當(dāng)在其他區(qū)域的數(shù)據(jù)更新時，當(dāng)前單元格的函數(shù)是否重新運算，專業(yè)術(shù)語稱之為“易失性”。用戶定義的函數(shù)是否有易失性可以使用以下語句來控制：Application.Volatile 該語句的作用是無論何時在工作表的任意單元格中進行計算，函數(shù)都必須重新進行計算。即工作表刷新時調(diào)用函數(shù)再運算一次，從而實現(xiàn)數(shù)據(jù)更新，使公式結(jié)果同步。6.4關(guān)于過程的參數(shù)Sub過程和Function過程都可以使用參數(shù)。有參數(shù)的過程相對于無參數(shù)的過程更具靈活性，相當(dāng)于給了用戶更多自定義的空間。6.4.1Sub過程的參數(shù)及應(yīng)用Sub過程的語法是：Private | Public | Friend Static Sub name (arglist) statements Exit Sub statementsEnd Sub其中“(arglist)”即表示它支持可選的參數(shù)，可以不用參數(shù)，也可以使用參數(shù)；可以使用一個參數(shù)，也可以使用多個參數(shù)。其中參數(shù)(arglist)的具體語法如下：Optional ByVal | ByRef ParamArray varname( ) As type = defaultvalue表6-3 Sub過程參數(shù)詳解部分功能詳解Optional可選的。表示參數(shù)不是必需的關(guān)鍵字。如果使用了該選項，則 arglist 中的后續(xù)參數(shù)都必須是可選的，而且必須都使用 Optional 關(guān)鍵字聲明。如果使用了 ParamArray，則任何參數(shù)都不能使用 OptionalByVal可選的。表示該參數(shù)按值傳遞ByRef可選的。表示該參數(shù)按地址傳遞。ByRef 是 Visual Basic 的默認選項ParamArray可選的。只用于 arglist 的最后一個參數(shù)，指明最后這個參數(shù)是一個 Variant 元素的 Optional 數(shù)組。使用 ParamArray 關(guān)鍵字可以提供任意數(shù)目的參數(shù)。ParamArray 關(guān)鍵字不能與 ByVal、ByRef或 Optional 一起使用varname必需的。代表參數(shù)的變量的名稱，遵循標準的變量命名約定type可選的。傳遞給該過程的參數(shù)的數(shù)據(jù)類型，如果沒有選擇參數(shù) Optional，則可以指定用戶定義類型，或?qū)ο箢愋蚫efaultvalue可選的。任何常數(shù)或常數(shù)表達式。只對 Optional 參數(shù)合法。如果類型為 Object，則顯式的默認值只能是 Nothing從表中可以看出，如果需要給Sub過程設(shè)置一個可選參數(shù)，則可以使用關(guān)鍵字Optional來聲明，如果需要設(shè)置多個可選參數(shù)，則可以使用關(guān)鍵字ParamArray來聲明參數(shù)。下例即為使用一個參數(shù)的Sub過程：Sub 過程一(msg As String)IF Len(msg) 0 Then MsgBox msg, 64, 友情提示End SubPrivate Sub 過程二() Call 過程一(你好)End Sub 如果執(zhí)行過程二，將彈出圖6.12所示對話框。圖6.12 提示信息可能看到以上代碼時有讀者會有疑問，直接在過程二中執(zhí)行Msgbox不是更簡單嗎？例如改成以下代碼：Private Sub 過程二() MsgBox 你好, 64, 友情提示End Sub在本例中確實二合一后更簡單，但當(dāng)有很多過程需要執(zhí)行類似操作時，則對一個過程進行判斷比每個過程都判斷一次更簡單。例如：Sub 姓名(name As String) Dim i As Byte, rng As Range For i = 1 To Sheets.Count IF ThisWorkbook.Sheets(i).name = 許可人員列表 Then: GoTo OK Next i MsgBox 不存在“許可人員列表”, 64 Exit SubOK: IF Len(name) 4 Then MsgBox 長度只能2到4,請重新錄入, 64: Exit Sub Set rng = ThisWorkbook.Sheets(許可人員列表).Range(a1:a10).Find(name) IF rng Is Nothing Then MsgBox 你無操作權(quán)限 Else MsgBox 你具有操作權(quán)限End SubSub 確認權(quán)限一() 手工指定姓名 Call 姓名(Application.InputBox(請輸入您的姓名, 確認權(quán)限, , , , , , 2)End SubSub 確認權(quán)限二() 以當(dāng)前表A1的值進行判斷 Call 姓名(ActiveSheet.Range(A1)End SubSub 確認權(quán)限三() 以O(shè)ffice安裝用戶名進行判斷 Call 姓名(Application.UserName)End Sub以上代碼用于判斷指定的用戶名是否具有操作權(quán)限。在工作簿中有一個工作表名為“許可人員列表”，該表中A1:A10存放10個允許操作的人員名單。程序會將用戶輸入或者指定方式獲取的姓名與表A1:A10中允許的姓名進行比較，如果與任何一個一致則提示“你具有操作權(quán)限”，否則提示“你無操作權(quán)限”。在過程“確認權(quán)限一”、“確認權(quán)限二”和“確認權(quán)限三”中都可以調(diào)用過程“姓名”，只是參數(shù)不同。如果不使用過程“姓名(Name)”作過渡的話，那么過程“姓名(Name)”中的所有代碼需要在后面三個過程中出現(xiàn)三次，每一個過程都需要對參數(shù)進行多次判斷及循環(huán)，從而使整個工程的代碼偏長。提示：本例文件參見光盤：. 第六章確認權(quán)限.xlsm下例再演示具有兩個參數(shù)但第二個參數(shù)是可選參數(shù)的Sub過程：Sub 改名(Sht_Name As String, Optional i As Byte = 1) Dim j As Byte For j = 1 To Sheets.Count IF Sheets(j).name = Sht_Name Then MsgBox 已存在： & Sht_Name, 64: End Next j IF i = 1 And i = 60 Then MsgBox 及格 Else MsgBox 不及格End SubSub Test() 成績 (59)End Sub執(zhí)行“Test”過程可以正確判斷成績59分是否及格。但若改用Function過程則一定出錯：Function 成績(成績)IF 成績 = 60 Then 成績 = 及格 Else 成績 = 不及格End FunctionSub Test()MsgBox 成績(59)End Sub執(zhí)行過程“Test”后將彈出“當(dāng)前范圍內(nèi)的聲明重復(fù)”的編譯錯誤。即使再修改為以下方式仍然報錯：Function 成績(成績) IF 成績 = 60 Then MsgBox 及格 Else MsgBox 不及格End FunctionSub Test()Call 成績 (59)End Sub正確的方式是：Function 成績(分數(shù)) IF 分數(shù) = 60 Then 成績 = 及格 Else 成績 = 不及格End FunctionSub Test() MsgBox 成績(59)End Sub6.5 開發(fā)自定義函數(shù)6.4節(jié)對自定義函數(shù)的基礎(chǔ)知識做了詳解，本節(jié)則進行實例