2 編寫Sub過程
本節(jié)開始了解關(guān)于Sub過程的基本概念，以及編寫簡(jiǎn)單的Sub過程。
2.1 Sub過程的語(yǔ)法解析
Sub過程即利用Sub語(yǔ)句聲明的過程,。所以宏錄制器產(chǎn)生的過程全是Sub過程，無(wú)法通過錄制宏產(chǎn)生Function過程或者屬性過程,。
Sub語(yǔ)句聲明過程的語(yǔ)法如下：
Private | Public | Friend] [Static] Sub name [(arglist)]
[statements]
[Exit Sub]
[statements]
End Sub
其中各參數(shù)的詳細(xì)功能如表1所示,。
表1 Sub語(yǔ)句參數(shù)詳解
參數(shù)部分 功能解釋
Public 可選的。表示所有模塊的所有其他過程都可訪問這個(gè) Sub 過程,。 如果在包含 Option Private 的模塊中使用,，則這個(gè)過程在該工程外是不可使用的
Private 可選的。表示只有在包含其聲明的模塊中的其他過程可以訪問該 Sub 過程
Friend 可選的,。只能在類模塊中使用,。表示該 Sub 過程在整個(gè)工程中都是可見的，但對(duì)對(duì)象實(shí)例的控制者是不可見的
Static 可選的,。表示在調(diào)用之間保留 Sub 過程的局部變量的值,。Static 屬性對(duì)在 Sub 外聲明的變量不會(huì)產(chǎn)生影響，即使過程中也使用了這些變量
name 必需的,。Sub 的名稱,；遵循標(biāo)準(zhǔn)的變量命名約定
arglist 可選的,。代表在調(diào)用時(shí)要傳遞給 Sub 過程的參數(shù)的變量列表。多個(gè)變量則用逗號(hào)隔開
statements 可選的,。Sub 過程中所執(zhí)行的任何語(yǔ)句組
Sub過程與所有變量一樣,，也區(qū)分公有和私有，而在說法上稍有區(qū)別,。過程分模塊級(jí)過程和工程級(jí)過程,。
1. 模塊級(jí)過程
模塊級(jí)過程即只能在當(dāng)前模塊調(diào)用的過程，它的特征有三個(gè)：
（1）聲明Sub過程前使用Private,；
（2）只有當(dāng)前過程可以調(diào)用,，例如在“模塊1”中有以下代碼：
Private Sub 過程一()
MsgBox 123
End Sub
Private Sub 過程二()
Call 過程一
End Sub
執(zhí)行過程二時(shí)可以調(diào)用過程一，但如果過程二存放于“模塊2”中,，則將彈出“子過程未定義”的錯(cuò)誤提示,。
（3）不出現(xiàn)在“宏”對(duì)話框中，即使用快捷鍵【Alt+F8】所打開的對(duì)話框中無(wú)法查看到當(dāng)前過程的名稱列表,。如果是Function過程,，則無(wú)法在函數(shù)向?qū)е胁榭吹胶瘮?shù)名。
提示:所有事件的代碼都是過程級(jí)的,，默認(rèn)狀態(tài)下只能在當(dāng)前過程可以調(diào)用,。
2. 工程級(jí)過程
工程級(jí)過程是指在當(dāng)前工程中任意地方都可以隨意調(diào)用的過程。它的特征剛好與模塊級(jí)過程相反：在“Sub”語(yǔ)句前置標(biāo)識(shí)符“Public”,、非當(dāng)前過程可以調(diào)用,，可以出現(xiàn)在“宏”對(duì)話框中。
如果一個(gè)過程沒有使用“Public”和“Private”標(biāo)識(shí),，則默認(rèn)為公有過程,，任何模塊或者窗體中都可以調(diào)用。
Sub過程也支持參數(shù),，其參數(shù)的用法與Function過程的參數(shù)用法一致,，本小節(jié)不詳述，請(qǐng)參閱第4節(jié),。
3. 中途退出程序的多種方法與分別
Sub過程可以在程序中間任意位置退出程序,，通常是設(shè)定若干個(gè)條件。當(dāng)滿足條件時(shí)使用“Exit Sub”來(lái)退出程序,。當(dāng)程序退出后,，后面的代碼不再執(zhí)行。
也可以使用“End”來(lái)退出程序,?！癊nd”和“Exit Sub”在使用中有相同處，也有明顯的差別。相同處是都可以中途終止程序的運(yùn)行,，不同處則有以下兩點(diǎn),。
（1）是否釋放公有變量
從以下三段代碼可以體現(xiàn)“End”和“Exit Sub”的差異：
Dim x As Long
Sub A()
x = 888
Exit Sub
End Sub
Sub B()
x = 888
End
End Sub
Sub C()
MsgBox x
End Sub
代碼中X是公有變量，當(dāng)執(zhí)行過程A后執(zhí)行過程C,，那么變量X的值為888,，表示X變量的值在過程中并沒有釋放，“Exit Sub”僅僅退出程序執(zhí)行,，公有變量的值保持不變,。
如果執(zhí)行過程Ｂ再執(zhí)行過程Ｃ，那么Ｘ的值則為０,，說明在過程Ｂ中的“End”已經(jīng)釋放變量X的值,。
（2）是否終止所有程序
仍然用三個(gè)過程來(lái)演示“End”和“Exit Sub”的差異：
Sub A()
Call B
MsgBox "終止"
End Sub
Sub B()
Exit Sub
End Sub
Sub C()
End
End Sub
執(zhí)行程序A的結(jié)果是彈出對(duì)話框“終止”，而將過程A中的“Call B”修改為“Call C”,，那么什么反應(yīng)也沒有,。也就是“Exit Sub”是退出它所在的程序，而“End”則中止所有程序,，包括調(diào)用它的程序,。如果在窗體代碼中，“Exit Sub”僅僅退出事件,，而“End”則退出事件后關(guān)掉窗體,，窗體中聲明的所有變量全部釋放。
2.2 Sub過程的執(zhí)行流程
如果錄用宏并執(zhí)行宏,，可以看出宏代碼的執(zhí)行流程永遠(yuǎn)是從上到下,。可以使用調(diào)試功能來(lái)查看流程,。例如執(zhí)行以下代碼：
Sub 設(shè)置A1單元格()
Range("A1").Select
Range("A1") = "中華人民共和國(guó)"
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.AutoFit
End Sub
將VBE窗口縮小,，使自己能同時(shí)看到代碼及A1單元格的情況下再按下快捷鍵【F8】，從而進(jìn)入逐句調(diào)試階段,。
注意:在VBE中使用【F8】鍵表示調(diào)試代碼語(yǔ)句,，每按一次【F8】鍵即執(zhí)行一句，忽略變量與常量的聲明語(yǔ)句,，直到“Exit Sub”或者“End”、“End Sub”為止,。在編寫代碼時(shí)非常有用,，可以借助它檢查代碼的準(zhǔn)確性,，同時(shí)也可以查看程序間的跳轉(zhuǎn)是否正常（當(dāng)有標(biāo)簽設(shè)置和嵌套調(diào)用的時(shí)候）。
當(dāng)按下調(diào)試鍵【F8】時(shí),，當(dāng)前執(zhí)行的語(yǔ)法呈黃色顯示,，再次按下【F8】鍵時(shí)，則下一句呈黃色顯示,，而操作對(duì)象A1則對(duì)應(yīng)產(chǎn)生變化,。圖7中已執(zhí)行到第四句，所以A2單元格同步后的狀態(tài)就是錄入“中華人民共和國(guó)”后并設(shè)置了背景色為黃色,。
圖7 逐步執(zhí)行代碼
當(dāng)繼續(xù)通過【F8】鍵執(zhí)行完成的代碼后,，可以得出結(jié)論：所有錄制的宏和未特別指定程序跳轉(zhuǎn)的VBA代碼總是按照從上至下的流程逐句執(zhí)行。
那么是否有例外呢,？通常在以下三種情況會(huì)有例外,。
1. 使用冒號(hào)使一行執(zhí)行多句代碼
VBA中允許借助冒號(hào)將多句代碼寫在同一行執(zhí)行。對(duì)同行中的代碼按從左向右的順序執(zhí)行,。例如：
Sub 設(shè)置A1單元格()
Range("A1") = "中華人民共和國(guó)": Range("A1").Interior.Color = 65535
Range("A1").Font.ColorIndex = 3: Range("A1").Font.Size = 20
Range("A1").EntireColumn.AutoFit
End Sub
以上代碼在借助冒號(hào)將四行代碼縮至兩行,，但執(zhí)行過程仍然為四步。對(duì)于同行中有多句代碼時(shí),，按從左向右的順序執(zhí)行,。
那么讀者一定可以想到，使用冒號(hào)和不使用冒號(hào)的執(zhí)行結(jié)果豈不是完全一致,？?jī)H僅改變了行數(shù),？
答案是“有時(shí)一致，有時(shí)不一致”,。如果以上的代碼按如下方式編寫,，那么結(jié)果完全一致：
Sub 設(shè)置A1單元格()
Range("A1") = "中華人民共和國(guó)"
Range("A1").Interior.Color = 65535
Range("A1").Font.ColorIndex = 3
Range("A1").Font.Size = 20
Range("A1").EntireColumn.AutoFit
End Sub
而在下面的代碼中，使用冒號(hào)后卻可以得到完全不同的結(jié)果：
Sub 判斷是否及格1()
IF [B2] >= 60 Then [C3] = "及格": Exit Sub
IF [B3] >= 60 Then [C3] = "及格"
End Sub
Sub 判斷是否及格2()
IF [B2] >= 60 Then [C3] = "及格"
Exit Sub
IF [B3] >= 60 Then [C3] = "及格"
End Sub
假設(shè)工作表中有圖8所示數(shù)據(jù),，執(zhí)行過程“判斷是否及格1”時(shí),，C3單元格將出現(xiàn)“及格”；而執(zhí)行過程“判斷是否及格2”時(shí)則無(wú)任何反應(yīng),。也就是說“Exit Sub”語(yǔ)句與IF同行時(shí),，只有單元格B2的值大于或等于60，“Exit Sub”語(yǔ)句才會(huì)執(zhí)行,。在本例中不符合條件,，那么沒有退出程序,，可以繼續(xù)執(zhí)行其后的代碼。而“Exit Sub”語(yǔ)句單獨(dú)占據(jù)一行時(shí),，不管單元格B2是否符合條件,，“Exit Sub”都會(huì)執(zhí)行，從而退出程序,，不再對(duì)B3的值進(jìn)行判斷,。
圖8 數(shù)據(jù)
2. 使用標(biāo)簽改變執(zhí)行流程
VBA可以在代碼中設(shè)置一個(gè)或者多個(gè)標(biāo)簽，然后讓程序在滿足某條件時(shí)跳轉(zhuǎn)到標(biāo)簽處,，從而改變過程執(zhí)行流程,。
標(biāo)簽的規(guī)則是：
*可以是標(biāo)點(diǎn)符號(hào)以外的字符組合
* 以冒號(hào)（:）結(jié)尾
*與大小寫無(wú)關(guān)
*必須位于一行的最左端
*配合GoTo使用
例如，建立一個(gè)名為“總表”的工作表,，代碼如下：
Sub 新建總表()
For i = 1 To Sheets.Count
IF Sheets(i).Name = "總表" Then GoTo err
Next i
Sheets.Add
ActiveSheet.Name = "總表"
End
err:
MsgBox "已經(jīng)存在總表"
End Sub
以上代碼首先利用For循環(huán)逐一檢查工作表的名字,，如果某個(gè)工作表的名字等于“總表”則執(zhí)行標(biāo)簽“Err”之后的代碼，否則繼續(xù)執(zhí)行For循環(huán),，直到循環(huán)完成并新建一個(gè)工作表,、命名為“總表”。
使用標(biāo)簽完成當(dāng)前程序間的跳轉(zhuǎn)時(shí)需要注意兩點(diǎn)：
（1）標(biāo)簽名后面必須帶有冒號(hào),。
（2）在標(biāo)簽之前根據(jù)需要，及時(shí)退出程序,。
在本例中,，按照設(shè)計(jì)意圖，只要工作簿中存在“總表”則執(zhí)行標(biāo)簽“Err”之后的語(yǔ)句,，反之不執(zhí)行,。所以標(biāo)簽之前必須加入“End”或者“Exit Sub”來(lái)退出程序，否則任何情況下Err后的語(yǔ)句都會(huì)被執(zhí)行,。
在一個(gè)過程中還可以定義多個(gè)標(biāo)簽,。例如：
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)前工作簿窗口已鎖定，無(wú)法建立新表"
End Sub
在此過程中,，首先判斷當(dāng)前工作表的窗口是否鎖定,，如果鎖定則執(zhí)行“已加密”標(biāo)簽后的語(yǔ)句；然后再檢查是否存在“總表”,，當(dāng)有“總表”時(shí)執(zhí)行“已存在”標(biāo)簽后的語(yǔ)句,。本例中兩個(gè)標(biāo)簽沒有順序上的差異，誰(shuí)前誰(shuí)后不影響代碼的結(jié)果,。
3. Sub過程的嵌套調(diào)用方式
過程與過程之間是可以相互調(diào)用的,，從而使代碼的執(zhí)行流程改變。通過VBA代碼調(diào)用Sub子過程主要有兩種方式,。
**Call語(yǔ)句**
Call語(yǔ)句的功能是將一個(gè)過程的控制權(quán)轉(zhuǎn)移到另一個(gè)過程,。
它的語(yǔ)法為：[Call] name [argumentlist]，即Call 過程名 參數(shù)。
其中Call是可選的,，即在其他過程調(diào)用過程一時(shí)可以有以下兩種形式：
Sub 過程一()
MsgBox "你好,！"
End Sub
Private Sub 過程二()
過程一
End Sub
Private Sub 過程三()
Call 過程一
End Sub
過程二和過程三都是合法的過程調(diào)用。
**Run方法**
Run方法可以運(yùn)行一個(gè)宏或者調(diào)用一個(gè)函數(shù),。該方法可用于運(yùn)行一個(gè)用 Visual Basic 或 Excel 宏語(yǔ)言編寫的宏或者運(yùn)行DLL或XLL中的函數(shù),。實(shí)例如下：
Sub 過程四()
Application.Run "過程一"
End Sub
其中“Application.Run”也可以簡(jiǎn)寫為“Run”。
2.3 過程的遞歸
所有過程都是可以遞歸的,，即可以調(diào)用自己來(lái)完成任務(wù),。
實(shí)際工作中需要調(diào)用過程本身的實(shí)例極少，通常進(jìn)入遞歸都是編碼有問題而誤入遞歸狀態(tài),，結(jié)果耗盡系統(tǒng)資源,。
在某些情況下也可以故意調(diào)用自己來(lái)完成任務(wù)。例如下面2例,。
1. 按條件新建工作表
Sub 建立10個(gè)表()
IF Sheets.Count >= 10 Then Exit Sub
Sheets.Add , Sheets(Sheets.Count), 1
Call 建立10個(gè)表
End Sub
以上代碼中,，首先利用IF查找并檢測(cè)當(dāng)前工作簿的工作表數(shù)量，如果大于或等于10則退出程序,，否則在最后位置新建一個(gè)工作表,，最后再調(diào)用自身繼續(xù)執(zhí)行，直到滿足條件“大于或等于10”為止,。
因代碼中人為設(shè)置了退出遞歸的條件,，所以這類遞歸不會(huì)造成程序崩潰，資源耗盡,。如果將代碼中的“IF Sheets.Count >= 10 Then Exit Sub”刪除,，那么程序循環(huán)執(zhí)行的結(jié)果就是電腦死機(jī)，除非中途人工中斷程序執(zhí)行：使用快捷鍵【Ctrl+Break】,。
2. 設(shè)計(jì)時(shí)鐘
Sub 時(shí)間()
[a1] = WorksheetFunction.Text(Now(), "hh:mm:ss")
Application.OnTime Now() + TimeValue("00:00:01"), "時(shí)間"
End Sub
Sub 終止()
Application.OnTime Now() + TimeValue("00:00:01"), "時(shí)間", , false
End Sub
以上代碼實(shí)現(xiàn)的效果是在單元格顯示當(dāng)前時(shí)間,，包括時(shí)、分,、秒,，且每秒鐘更新一次。通過遞歸方式讓程序每秒鐘執(zhí)行一次實(shí)現(xiàn)時(shí)鐘的效果,，同時(shí)再利用另一個(gè)過程來(lái)隨時(shí)退出遞歸,。當(dāng)然也可以用快捷鍵【Ctrl+Break】。
2.4 Sub過程實(shí)例演示
為了更好地理解Sub過程,，通過兩個(gè)示例來(lái)展示,。
1. 統(tǒng)計(jì)選區(qū)信息：不帶參數(shù)的Sub過程
要求：對(duì)任意選區(qū)進(jìn)行單元格個(gè)數(shù)、數(shù)值個(gè)數(shù),、非空單元格個(gè)數(shù),、空白單元格個(gè)數(shù)及選區(qū)之和統(tǒng)計(jì),。
代碼如下：
Sub 選區(qū)統(tǒng)計(jì)()
Dim msg As String
msg = "單元格個(gè)數(shù)：" & Selection.Count & Chr(10)
msg = msg & "數(shù)字個(gè)數(shù)：" & WorksheetFunction.Count(Selection) & Chr(10)
msg = msg & "非空單元格：" & WorksheetFunction.CountA(Selection) & Chr(10)
msg = msg & "空白單元格個(gè)數(shù)：" & WorksheetFunction.CountBlank(Selection) & Chr(10)
msg = msg & "選區(qū)之和：" & WorksheetFunction.Sum(Selection)
MsgBox msg, 64, "選區(qū)統(tǒng)計(jì)"
End Sub
假設(shè)工作表中存在圖9所示數(shù)據(jù)，選擇A1:D9區(qū)域后利用快捷鍵【Alt+F8】執(zhí)行“選區(qū)統(tǒng)計(jì)”過程,，其統(tǒng)計(jì)結(jié)果如圖10所示,。
圖9 工作表數(shù)據(jù)
圖10 選區(qū)統(tǒng)計(jì)結(jié)果
2. 將單元格數(shù)據(jù)轉(zhuǎn)換為首字母大寫：帶有參數(shù)的Sub過程
要求：在工作表中選擇任意一個(gè)帶英文的單元格時(shí)，將其轉(zhuǎn)換為每個(gè)單詞首字母大寫,。
（1）插入模塊1,，并錄入以下代碼：
Sub 轉(zhuǎn)換(Target)
Selection(1) = StrConv(Target, vbProperCase)
End Sub
（2）雙擊工程資源管理器中的“Sheet1”，進(jìn)入工作表代碼窗口后錄入代碼：
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Call 轉(zhuǎn)換(Target(1))
End Sub
（3）返回工作表“Sheet1”,，單擊任意單元格,，如果存在英文單詞，則每個(gè)單詞首字母大寫,，否則保持為變,。如單元格中有句子“You are do it”，那么單擊該單元格后將被轉(zhuǎn)換為“You Are Do It”,。 |
|
|
|
|
|
|
|
3 認(rèn)識(shí)Function過程
Function過程即自定義函數(shù),，在插件中應(yīng)用極廣。本節(jié)介紹關(guān)于Function過程的語(yǔ)法及調(diào)用方法,。
3.1 Function過程的特點(diǎn)
Function過程的功能較Sub過程的應(yīng)用范圍稍小,，Function過程僅僅用于返回一個(gè)值或者多個(gè)數(shù)的組合即數(shù)組，而Sub過程可以返回值,，還可以對(duì)引用的對(duì)象進(jìn)行修改,。例如，引用單元格A1的值后對(duì)單元格A1設(shè)置新的格式,，或者修改工作表名稱等。Function可以獲取工作表名稱,，但無(wú)法修改工作表的名稱,。
Function過程可以不使用參數(shù)，類似于工作表函數(shù)Rand和Now等,，但絕大部分函數(shù)是需要一個(gè)參數(shù)或者多個(gè)參數(shù)的,，最多時(shí)可達(dá)255個(gè)參數(shù)。
3.2 Function的語(yǔ)法解析
Function的語(yǔ)法如下：
[Public | Private | Friend] [Static] Function name [(arglist)] [As type]
[statements]
[name = expression]
[Exit Function]
[statements]
[name = expression]
……
End Function
Function語(yǔ)句的各參數(shù)詳解如表2所示,。
表2 Function語(yǔ)句參數(shù)詳解
|
參數(shù)部分
|
功能解釋
|
Public
|
可選的,。表示所有模塊的所有其他過程都可以訪問這個(gè) Function 過程。如果是在包含 Option Private 的模塊中使用,，則這個(gè)過程在該工程外是不可使用的
|
Private
|
可選的,。表示只有包含其聲明的模塊的其他過程可以訪問該 Function 過程
|
Friend
|
可選的。只能在類模塊中使用,。表示該 Function 過程在整個(gè)過程中都是可見的，但對(duì)于對(duì)象實(shí)例的控制者是不可見的
|
Static
|
可選的。表示在調(diào)用之間將保留 Function 過程的局部變量值,。Static 屬性對(duì)在該 Function 外聲明的變量不會(huì)產(chǎn)生影響,，即使過程中也使用了這些變量
|
name
|
必需的,。Function 的名稱，遵循標(biāo)準(zhǔn)的變量命名約定
|
arglist
|
可選的,。代表在調(diào)用時(shí)要傳遞給 Function 過程的參數(shù)變量列表,。多個(gè)變量應(yīng)用逗號(hào)隔開
|
type
|
可選的,。Function 過程的返回值的數(shù)據(jù)類型,，可以是 Byte,、 Boolean ,、Integer,、Long,、Currency、Single,、Double,、Decimal（目前尚不支持）、Date,、String（除定長(zhǎng)）,、Object、Variant或任何用戶定義類型
|
statements
|
可選的,。在 Function 過程中執(zhí)行的任何語(yǔ)句組
|
expression
|
可選的,。Function 的返回值
|
和Sub過程一樣,，Function過程也有模塊級(jí)過程和工程級(jí)過程之分,。Function前置“Public”即為工程級(jí)，前置“Private”則為模塊級(jí),。
Function名稱在聲明時(shí)需要遵循與Sub過程一樣的規(guī)則,。
如果自定義的Function名稱與VBA內(nèi)部名稱一致,，仍然可以正常執(zhí)行，只是在代碼中調(diào)用Excel內(nèi)部函數(shù)時(shí)必須聲明其對(duì)象庫(kù),。例如：
Function sqr(AA)
sqr = AA ^ (1 / 3)
End Function
Sub test()
MsgBox "VBA.SQR:" & VBA.sqr(27) & Chr(10) & "SQR:" & sqr(27)
End Sub
圖11 自定義SQR和內(nèi)置SQR的分別
執(zhí)行test過程時(shí)的結(jié)果如圖11所示,。
從結(jié)果可以得知，在代碼使用“VBA.SQR”可以調(diào)用VBA自帶的SQR功能,，而直接使用SQR則調(diào)用自定義的SQR函數(shù)的功能,。
雖然定義函數(shù)時(shí)允許與內(nèi)部函數(shù)一致，但卻不允許與定義的變量或者常量一致,，不管這個(gè)變量或者常量是本過程私有的還是模塊中公有的,，否則將產(chǎn)生“發(fā)現(xiàn)二義性的名稱”的編譯錯(cuò)誤。
3.3 調(diào)用Function過程
Function過程通常以三種方式調(diào)用,。
（1）在工作表中通過公式調(diào)用：像內(nèi)部函數(shù)一樣在工作表中使用,，也可以與其他函數(shù)嵌套。
（2）在VBA代碼中被其他過程調(diào)用：就像圖9對(duì)應(yīng)的那段代碼一樣在Sub過程調(diào)用函數(shù),。
（3）遞歸：Function過程和Sub一樣可以實(shí)現(xiàn)遞歸,。如果不是刻意地、有計(jì)劃地進(jìn)入遞歸狀態(tài)，可能會(huì)造成資源耗盡或者溢出堆?？臻g,。例如下面函數(shù)的調(diào)用：
Function 遞歸(參數(shù))
遞歸 = 遞歸(參數(shù))
End Function
Sub 測(cè)試()
MsgBox 遞歸(1000)
End Sub
將代碼錄入到模塊中后，執(zhí)行過程“測(cè)試”,，立即彈出錯(cuò)誤提示“溢出堆?？臻g”。
為了避免遞歸造成的錯(cuò)誤,，甚至程序崩潰,，盡量不要調(diào)用自身，開發(fā)函數(shù),、插件時(shí)多方面查核是否可能造成循環(huán)引用,、遞歸現(xiàn)象。當(dāng)然,，有目的,、有條件的遞歸是可以給工作帶來(lái)便利的。
另外,，談到函數(shù)就不能不說它的“刷新”性能,，即在工作表中使用函數(shù)時(shí)，當(dāng)在其他區(qū)域的數(shù)據(jù)更新時(shí),，當(dāng)前單元格的函數(shù)是否重新運(yùn)算,，專業(yè)術(shù)語(yǔ)稱之為“易失性”。
用戶定義的函數(shù)是否有易失性可以使用以下語(yǔ)句來(lái)控制：
Application.Volatile
該語(yǔ)句的作用是無(wú)論何時(shí)在工作表的任意單元格中進(jìn)行計(jì)算,，函數(shù)都必須重新進(jìn)行計(jì)算,。即工作表刷新時(shí)調(diào)用函數(shù)再運(yùn)算一次，從而實(shí)現(xiàn)數(shù)據(jù)更新,，使公式結(jié)果同步,。 |
|
|
|
|
|
|
|
4 關(guān)于過程的參數(shù)
Sub過程和Function過程都可以使用參數(shù)。有參數(shù)的過程相對(duì)于無(wú)參數(shù)的過程更具靈活性,，相當(dāng)于給了用戶更多自定義的空間,。
4.1 Sub過程的參數(shù)及應(yīng)用
Sub過程的語(yǔ)法是：
[Private | Public | Friend] [Static] Sub name [(arglist)]
[statements]
[Exit Sub]
[statements]
……
End Sub
其中“(arglist)”即表示它支持可選的參數(shù)，可以不用參數(shù),，也可以使用參數(shù),；可以使用一個(gè)參數(shù)，也可以使用多個(gè)參數(shù),。
其中參數(shù)(arglist)的具體語(yǔ)法如下：
[Optional] [ByVal | ByRef] [ParamArray] varname[( )] [As type] [= defaultvalue]
表3 Sub過程參數(shù)詳解
|
部分
|
功能詳解
|
Optional
|
可選的,。表示參數(shù)不是必需的關(guān)鍵字。如果使用了該選項(xiàng),，則 arglist 中的后續(xù)參數(shù)都必須是可選的,，而且必須都使用 Optional 關(guān)鍵字聲明,。如果使用了 ParamArray，則任何參數(shù)都不能使用 Optional
|
ByVal
|
可選的,。表示該參數(shù)按值傳遞
|
ByRef
|
可選的,。表示該參數(shù)按地址傳遞。ByRef 是 Visual Basic 的默認(rèn)選項(xiàng)
|
ParamArray
|
可選的,。只用于 arglist 的最后一個(gè)參數(shù)，指明最后這個(gè)參數(shù)是一個(gè) Variant 元素的 Optional 數(shù)組,。使用 ParamArray 關(guān)鍵字可以提供任意數(shù)目的參數(shù),。ParamArray 關(guān)鍵字不能與 ByVal、ByRef或 Optional 一起使用
|
varname
|
必需的,。代表參數(shù)的變量的名稱,，遵循標(biāo)準(zhǔn)的變量命名約定
|
type
|
可選的。傳遞給該過程的參數(shù)的數(shù)據(jù)類型,，如果沒有選擇參數(shù) Optional,，則可以指定用戶定義類型，或?qū)ο箢愋?/font>
|
defaultvalue
|
可選的,。任何常數(shù)或常數(shù)表達(dá)式,。只對(duì) Optional 參數(shù)合法。如果類型為 Object,，則顯式的默認(rèn)值只能是 Nothing
| 從表中可以看出,，如果需要給Sub過程設(shè)置一個(gè)可選參數(shù)，則可以使用關(guān)鍵字Optional來(lái)聲明,，如果需要設(shè)置多個(gè)可選參數(shù),，則可以使用關(guān)鍵字ParamArray來(lái)聲明參數(shù)。
下例即為使用一個(gè)參數(shù)的Sub過程：
Sub 過程一(msg As String)
IF Len(msg) <> 0 Then MsgBox msg, 64, "友情提示"
End Sub
Private Sub 過程二()
Call 過程一("你好")
End Sub
如果執(zhí)行過程二,，將彈出圖12所示對(duì)話框,。
圖12 提示信息
可能看到以上代碼時(shí)有讀者會(huì)有疑問，直接在過程二中執(zhí)行Msgbox不是更簡(jiǎn)單嗎,？例如改成以下代碼：
Private Sub 過程二()
MsgBox "你好", 64, "友情提示"
End Sub
在本例中確實(shí)二合一后更簡(jiǎn)單,，但當(dāng)有很多過程需要執(zhí)行類似操作時(shí)，則對(duì)一個(gè)過程進(jìn)行判斷比每個(gè)過程都判斷一次更簡(jiǎn)單,。例如：
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 Sub
OK:
IF Len(name) < 2 Or Len(name) > 4 Then MsgBox "長(zhǎng)度只能2到4,請(qǐng)重新錄入", 64: Exit Sub
Set rng = ThisWorkbook.Sheets("許可人員列表").Range("a1:a10").Find(name)
IF rng Is Nothing Then MsgBox "你無(wú)操作權(quán)限" Else MsgBox "你具有操作權(quán)限"
End Sub
Sub 確認(rèn)權(quán)限一() ''手工指定姓名
Call 姓名(Application.InputBox("請(qǐng)輸入您的姓名", "確認(rèn)權(quán)限", "", , , , , 2))
End Sub
Sub 確認(rèn)權(quán)限二() ''以當(dāng)前表A1的值進(jìn)行判斷
Call 姓名(ActiveSheet.Range("A1"))
End Sub
Sub 確認(rèn)權(quán)限三() ''以Office安裝用戶名進(jìn)行判斷
Call 姓名(Application.UserName)
End Sub
以上代碼用于判斷指定的用戶名是否具有操作權(quán)限,。在工作簿中有一個(gè)工作表名為“許可人員列表”，該表中A1:A10存放10個(gè)允許操作的人員名單,。程序會(huì)將用戶輸入或者指定方式獲取的姓名與表A1:A10中允許的姓名進(jìn)行比較,，如果與任何一個(gè)一致則提示“你具有操作權(quán)限”，否則提示“你無(wú)操作權(quán)限”,。
在過程“確認(rèn)權(quán)限一”,、“確認(rèn)權(quán)限二”和“確認(rèn)權(quán)限三”中都可以調(diào)用過程“姓名”,，只是參數(shù)不同。如果不使用過程“姓名(Name)”作過渡的話,，那么過程“姓名(Name)”中的所有代碼需要在后面三個(gè)過程中出現(xiàn)三次,，每一個(gè)過程都需要對(duì)參數(shù)進(jìn)行多次判斷及循環(huán)，從而使整個(gè)工程的代碼偏長(zhǎng),。
下例再演示具有兩個(gè)參數(shù)但第二個(gè)參數(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 <= Sheets.Count Then Sheets(i).name = Sht_Name
End Sub
Private Sub 過程二()
Call 改名("總表", 12)
End Sub
Private Sub 過程三()
Call 改名("匯總表")
End Sub
以上過程用于工作表改名,，根據(jù)指定的工作表新名稱與工作表序號(hào)對(duì)工作表重命名。
在以上代碼中,，過程“改名”具有兩個(gè)參數(shù),，第一參數(shù)用于指定工作表新名稱，第二參數(shù)用于指定工作表序號(hào),。如果忽略第二參數(shù),，則當(dāng)作1處理。
將三段代碼復(fù)制到模塊中,，執(zhí)行“過程二”,。因其第一參數(shù)為“總表”，第二參數(shù)為2,，那么執(zhí)行結(jié)果即工作簿中第二個(gè)工作表重命名為“總表”,。
而執(zhí)行“過程三”后，因忽略了第二參數(shù),，默認(rèn)當(dāng)作1處理,，所以結(jié)果為第一個(gè)工作表重命名為“匯總表”。
4.2 Function過程的參數(shù)
Function過程的參數(shù)與Sub過程的參數(shù)在語(yǔ)法上完全一致,，可以使用相同的參數(shù),。
但是Function過程只能返回引用對(duì)象的某個(gè)屬性值或者運(yùn)算結(jié)果，無(wú)法改變對(duì)象的屬性,、格式等,，所以部分帶有參數(shù)的Sub過程可以直接改用Function實(shí)現(xiàn)，而部分卻無(wú)法實(shí)現(xiàn),。
Function和Sub一樣可以使用一個(gè)或者多個(gè)參數(shù),，也可以使用可選參數(shù)。但不同的是Function過程只能返回值,，所以在聲明Function過程時(shí),，其所有參數(shù)可以指定數(shù)據(jù)類型，Function過程本身也可以指定數(shù)據(jù)類型,。例如：
Function Str(rng as range) as string
Function與Sub過程的另一個(gè)區(qū)別是Sub過程的參數(shù)允許與Sub過程名一致,，而Function的參數(shù)絕不能與Function過程名一致。例如：
Sub 成績(jī)(成績(jī))
IF 成績(jī) >= 60 Then MsgBox "及格" Else MsgBox "不及格"
End Sub
Sub Test()
成績(jī) (59)
End Sub
執(zhí)行“Test”過程可以正確判斷成績(jī)59分是否及格,。但若改用Function過程則一定出錯(cuò)：
Function 成績(jī)(成績(jī))
IF 成績(jī) >= 60 Then 成績(jī) = "及格" Else 成績(jī) = "不及格"
End Function
Sub Test()
MsgBox 成績(jī)(59)
End Sub
執(zhí)行過程“Test”后將彈出“當(dāng)前范圍內(nèi)的聲明重復(fù)”的編譯錯(cuò)誤,。即使再修改為以下方式仍然報(bào)錯(cuò)：
Function 成績(jī)(成績(jī))
IF 成績(jī) >= 60 Then MsgBox "及格" Else MsgBox "不及格"
End Function
Sub Test()
Call 成績(jī) (59)
End Sub
正確的方式是：
Function 成績(jī)(分?jǐn)?shù))
IF 分?jǐn)?shù) >= 60 Then 成績(jī) = "及格" Else 成績(jī) = "不及格"
End Function
Sub Test()
MsgBox 成績(jī)(59)
End Sub |
|
|
|
|
|
|
|
5開發(fā)自定義函數(shù)
第4節(jié)對(duì)自定義函數(shù)的基礎(chǔ)知識(shí)做了詳解,，本節(jié)則進(jìn)行實(shí)例演示，通過帶有不同參數(shù)的函數(shù)定義過程來(lái)增進(jìn)讀者的理解與編寫功底,。
5.1 開發(fā)不帶參數(shù)的Function過程
1. 不隨時(shí)間變化的時(shí)間函數(shù)
【要求】：函數(shù)需要獲取當(dāng)前系統(tǒng)時(shí)間,，但卻不能隨其他單元格的值的改變而更改時(shí)間值。
【代碼】：
Function Nows()''聲明函數(shù)
Dim Tim As String''聲明一個(gè)變量
Tim = Format(Now, "yyyy-mm-dd hh:mm:ss")''獲取當(dāng)前時(shí)間,并轉(zhuǎn)換成文本
Nows = Tim''將文本日期賦予函數(shù)
End Function
【測(cè)試】：
將以上代碼錄入模塊中,，然后返回工作表界面,。
在工作表中，A列用于存放倉(cāng)庫(kù)的進(jìn)庫(kù)數(shù)量,，而B列用于登記進(jìn)庫(kù)時(shí)間?，F(xiàn)需求的是只要A列錄入數(shù)據(jù)，B列則自動(dòng)產(chǎn)生當(dāng)前時(shí)間,，而且這個(gè)時(shí)間不會(huì)因?yàn)槠渌麛?shù)據(jù)的修改而變化,。
在B2單元格錄入公式：=IF(A2="","",Nows())
將公式向下填充到A100,，然后返回A2單元格錄入進(jìn)庫(kù)數(shù)量500,，B2則自動(dòng)出現(xiàn)錄入進(jìn)庫(kù)數(shù)量的時(shí)間。過半小時(shí)再在A3錄入第二次進(jìn)庫(kù)的數(shù)量800,，B3單元格則自動(dòng)產(chǎn)生第二次進(jìn)庫(kù)的時(shí)間,，且第一次進(jìn)庫(kù)時(shí)間保持不變……具體效果如圖13所示。
提示
代碼中的Now是VBA函數(shù),，不是工作表函數(shù)NOW（）,，所以不需要帶括號(hào)，但它們功能相同,。
點(diǎn)評(píng)
相對(duì)于系統(tǒng)自帶的工作表函數(shù)NOW,，Nows函數(shù)具有不隨時(shí)間變化的優(yōu)點(diǎn)，對(duì)于記錄進(jìn)庫(kù)時(shí)間這類工作的應(yīng)用極廣,。此外,，如果僅僅需要不變的日期，忽略時(shí)間,，可以不改變代碼,，而直接在公式中套用Text函數(shù)即可，例如：
=TEXT(Nows(),"yyyy-mm-dd")
2. 取當(dāng)前工作簿名
【要求】：利用函數(shù)獲取當(dāng)前工作薄名稱,，不管工作簿是否保存,。
【代碼】：
Function 工作簿名()''獲取當(dāng)前工作簿名稱
工作簿名 = ActiveWorkbook.Name''ActiveWorkbook即表示當(dāng)前工作簿
End Function
【測(cè)試】：
進(jìn)入工作表，在單元格錄入以下公式即可獲取當(dāng)前工作簿名,，如圖6.14所示,。
=工作簿名()
圖13 測(cè)試不隨時(shí)間變化的時(shí)間函數(shù)
圖14 獲取工作簿名
提示
未保存的工作簿也具有Name屬性，若需要取得路徑名,，則需要使用FullName屬性,，不過它需要在保存工作簿后才能取得,。
點(diǎn)評(píng)
Excel沒有獲取工作簿名稱的函數(shù)，利用Cell函數(shù)勉強(qiáng)可以完成,，不過它有兩個(gè)缺點(diǎn)：工作簿名包含路徑,，若工作簿未保存則無(wú)法獲取名稱。本自定義函數(shù)不管工作簿是否保存都可以順利地獲取工作簿名,，不過未保存的工作簿就沒有后綴名,。
5.2 開發(fā)帶有一個(gè)參數(shù)的Function過程
1. 將人民幣金額轉(zhuǎn)換為大寫
【要求】：對(duì)于財(cái)務(wù)報(bào)表，金額默認(rèn)為阿拉伯?dāng)?shù)字,，現(xiàn)需將其轉(zhuǎn)換**民幣大寫形式,。
【代碼】：
Function 大寫(CELL As String) As String ''聲明函數(shù)名,有一個(gè)參數(shù)
Dim RMBS As String
IF CELL = "" Or Not IsNumeric(CELL) Then 大寫 = "": Exit Function ''如果參數(shù)為空或者非數(shù)值則返回空白
IF CELL = 0 Then 大寫 = "零元整": Exit Function ''如果參數(shù)為0則返回"零元整"
''將數(shù)值轉(zhuǎn)換成中文大寫,并將點(diǎn)替換成"元",將負(fù)號(hào)替換成"負(fù)"
RMBS = Replace(Replace(Application.Text(Round(CELL, 2), "[DBnum2]"), ".", "元"), "-", "負(fù)")
''加入角與分,同時(shí)將最后的"零"替換成"元整"
RMBS = IIF(Left(Right(RMBS, 3), 1) = "元", Left(RMBS, Len(RMBS) - 1) & "角" & Right(RMBS, 1) & "分", IIF(Left(Right(RMBS, 2), 1) = "元", RMBS & "角", IIF(RMBS = "零", "", RMBS & "元整")))
''將"零元"和"零角"替換成空
RMBS = Replace(Replace(RMBS, "零元", ""), "零角", "")
大寫 = RMBS ''將變量的值賦予函數(shù)
End Function
提示
（1）IsNumeric用于判斷參數(shù)是否是數(shù)字，非數(shù)字是無(wú)法轉(zhuǎn)換**民幣大寫的,；
（2）Replace是用于替換的函數(shù),，但它和工作表函數(shù)Replace有極大的不同，與SUBSTITUTE函數(shù)極其相近,。
【測(cè)試】：
如圖15所示工作表中的員工工資需要匯總后以大寫金額顯示,，那么在單元格B7錄入以下公式：
=大寫(SUM(B2:B6))
點(diǎn)評(píng)
Excel自帶的Text函數(shù)可以實(shí)現(xiàn)數(shù)字轉(zhuǎn)中文大寫，但無(wú)法實(shí)現(xiàn)人民幣大寫,。借用本函數(shù)可以大幅提升財(cái)務(wù)人員的工作效率,。大寫函數(shù)與Text函數(shù)在大寫方面的差異見圖6-16。
圖15 匯總并轉(zhuǎn)換成大寫
圖16 Text與大寫函數(shù)之差異
2. 建立工作表目錄
【要求】：在工作表中建立當(dāng)前工作簿的目錄,，且當(dāng)單擊目錄中的工作表名可以跳轉(zhuǎn)到該工作表中,。
【代碼】：
Function 工作表(Optional 序號(hào)) As String''聲明函數(shù),有一個(gè)參數(shù)可選參數(shù)
Application.Volatile ''聲明為易失性函數(shù)
''如果未輸入?yún)?shù),則賦予變量序號(hào)為當(dāng)前表的地址
IF IsMissing(序號(hào)) Then 序號(hào) = ActiveSheet.Index
IF 序號(hào) > Sheets.Count Then ''如果參數(shù)大于工作表數(shù)量
工作表 = ""''返回空
Else''否則
工作表 = Sheets(序號(hào)).Name ''取表名
End IF
End Function
提示
（1）IsMissing用于判斷函數(shù)的可選參數(shù)是否已經(jīng)傳遞給過程。在本例中如果不指定函數(shù)的參數(shù),，則默認(rèn)返回當(dāng)前工作表的表名,；
（2）Index屬性則是指工作表在所有工作表中的序號(hào)（從左向右數(shù)）。
【測(cè)試】：
在工作表的A2中錄入以下公式,，并向下填充,，即可完成工作表目錄的創(chuàng)建。當(dāng)鼠標(biāo)單擊任意單元格A3時(shí),，將打開名為“工作簿名”的工作表,。
=HYPERLINK("#"&工作表(ROW(A2))&"!A1",工作表(ROW(A1)))
圖17 創(chuàng)建工作表目錄
點(diǎn)評(píng)
Excel本身沒有獲取工作表的函數(shù)，雖然依靠調(diào)用宏表函數(shù)并借助名稱可以完成,，但公式較長(zhǎng),，且必須要借助名稱，公式無(wú)法在單元格中直接套用,。本自定義函數(shù)以“Row(a1)”作為參數(shù)可以逐一提取工作表名,，再配合“HYPERLINK”即可建立鏈接。
3.關(guān)機(jī)函數(shù)
【要求】：利用函數(shù)在指定時(shí)間內(nèi)關(guān)閉計(jì)算機(jī),。
【代碼】：
Function 關(guān)機(jī)(Optional Close_Time As Byte = 10)''聲明函數(shù)名稱,有一個(gè)可選參數(shù)
關(guān)機(jī) = Close_Time''在單元格顯示時(shí)間
Shell "shutdown -s -t " & Close_Time''在指定的時(shí)間內(nèi)關(guān)閉計(jì)算機(jī),調(diào)用DOS命令
End Function
提示
（1）關(guān)機(jī)函數(shù)的參數(shù)使用了Byte數(shù)據(jù)類型,，所以這個(gè)時(shí)間只能是在0到255秒之間,。如果需要更長(zhǎng)的時(shí)間，可以改用Integer,；
（2）Shutdown是一個(gè)DOS下的程序,，可以用Shell函數(shù)來(lái)執(zhí)行。
【測(cè)試】：
在工作表任意單元格錄入以下公式,，那么10秒鐘后可以關(guān)閉計(jì)算機(jī),。如果將參數(shù)設(shè)定為3，則3秒鐘后關(guān)閉計(jì)算機(jī),。
=關(guān)機(jī)()
點(diǎn)評(píng)
Excel本身是不具備系統(tǒng)控制能力的,，但DOS下很多工具具有系統(tǒng)權(quán)限，而VBA的Shell函數(shù)恰好可以調(diào)用DOS下的所有程序,，所以VBA也就獲得了對(duì)操作系統(tǒng)的部分控制功能,。如果需要重啟電腦，可以將Shutdown的參數(shù)“-S”改為“-R”,。
注意：測(cè)試此函數(shù)會(huì)關(guān)掉計(jì)算機(jī),，請(qǐng)?jiān)诒４嫠匈Y料后再行測(cè)試。
5.3 開發(fā)帶有兩個(gè)參數(shù)的Function過程
1. 對(duì)帶“/”的數(shù)據(jù)進(jìn)行合計(jì)
【要求】：盤點(diǎn)產(chǎn)品時(shí),，部分產(chǎn)品以“雙”為單位,，部分產(chǎn)品無(wú)法配雙,，則以“左/右”形式出現(xiàn),，現(xiàn)需對(duì)這種數(shù)據(jù)進(jìn)行匯總，且按需求有時(shí)會(huì)按“只”計(jì)算,，有時(shí)按“雙”計(jì)算,，公式必須具備通用性及可選性。
【代碼】：
Function hesum(rng As Range, Optional 單雙 As Byte = 1) ''聲明函數(shù),有兩個(gè)參數(shù),第二個(gè)是可選參數(shù)
Application.Volatile ''聲明為易失性函數(shù)
Dim cell As Range, Sum1, Sum2
For Each cell In rng
IF InStr(cell, "/") > 0 Then''如果有“/”
左 = CLng(Left(cell, InStr(cell, "/") - 1))''提取/左邊的數(shù)據(jù)
右 = CLng(Replace(cell, 左 & "/", ""))''提取/右邊的數(shù)據(jù)
Sum1 = Sum1 + (左 + 右) ''將左右相加
Else
sum2 = Sum2 + cell * 2 ''沒有“/”則直接乘以2
End IF
Next
hesum = (Sum2 + Sum1) / 單雙 ''匯總后除以第二參數(shù)
End Function
提示
（1）InStr函數(shù)用于在盤點(diǎn)表中查找“/”,，如果查找結(jié)果大于0,，則分別取“/”左、右的數(shù)值相加,，否則直接取數(shù)值本身,。
（2）函數(shù)的第二參數(shù)為可選函數(shù)，如果忽略參數(shù),，則按“只”為單位計(jì)算,，即當(dāng)作1處理。
圖18 匯總表盤表數(shù)據(jù)
【測(cè)試】：在如圖18所示的工作表中,，在B10單元格錄入以下公式可以對(duì)盤點(diǎn)數(shù)進(jìn)行匯總,，合計(jì)數(shù)以“只”為單位：
=hesum(B3:B9)
如果需要匯總結(jié)果以雙為單位，則需要將公式改為：
=hesum(B3:B9,2)
【點(diǎn)評(píng)】：針對(duì)帶有“/”的盤點(diǎn)數(shù)據(jù),，Excel本身不存在可以直接計(jì)算的公式,，如果用多函數(shù)嵌套也可以計(jì)算出正確結(jié)果,，但公式極長(zhǎng)，而Hesum函數(shù)卻簡(jiǎn)短易懂,。內(nèi)置公式附后：
=SUM(IF(ISNUMBER(FIND("/",B3:B9)),(LEFT(B3:B9,FIND("/",B3:B9)-1)+RIGHT(B3:B9,LEN(B3:B9)-FIND("/",B3:B9)))/2,B3:B9))*2
2. 中國(guó)式排名
【要求】：對(duì)學(xué)生成績(jī)按班級(jí)排名,、按性別排名，且需按中國(guó)式排名,。
【代碼】：
Function 排名(區(qū)域, 成績(jī)) ''聲明函數(shù),有兩個(gè)參數(shù)
Application.Volatile ''聲明為易失性函數(shù)
Dim Dic As Object, rng, i As Integer ''聲明變量,，包括一個(gè)字典對(duì)象
Set Dic = CreateObject("scripting.dictionary") ''聲明字典對(duì)象變量
For Each rng In 區(qū)域''遍歷區(qū)域
''如果變量rng等于成績(jī)則為變量i賦值1,如果變量rng大于成績(jī),則將rng的值追加到字典中
IF rng = 成績(jī) Then i = 1 Else IF rng > 成績(jī) Then Dic(rng * 1) = 1
Next
''如果變量i大于0,即區(qū)域中有數(shù)據(jù)等于成績(jī),那么排名結(jié)果等于字典中的數(shù)量加1(字典對(duì)象是忽略重復(fù)值的)
IF i > 0 Then 排名 = Dic.Count + 1 Else 排名 = "超出范圍" ''如果成績(jī)與區(qū)域中任何數(shù)據(jù)都不相等則返回"超出范圍"
End Function
提示
（1）CreateObject("scripting.dictionary")用于創(chuàng)建一個(gè)字典對(duì)象，它的特點(diǎn)是成員不重復(fù),。而中國(guó)式排名,，是需要忽略重復(fù)值的。即四人中第一人100分算第一名,，兩個(gè)99分并列第二名,，而98分者按第三名計(jì)算，而非美式排名中的第四名,。所以設(shè)計(jì)排名函數(shù)時(shí)需要借助字典這個(gè)特性來(lái)實(shí)現(xiàn)中國(guó)式排名,。
（2）函數(shù)的兩個(gè)參數(shù)都支持手動(dòng)錄入?yún)?shù)，而非僅僅限于單元格引用,。
圖19 按條件排名
【測(cè)試】：
工作表中有如圖6.19所示數(shù)據(jù),，在E2單元錄入以下公式：
=排名(IF(B$2:B$10=B2,D$2:D$10),D2)
再在F2單元格錄入以下公式：
=排名(IF(C$2:C$10=C2,D$2:D$10),D2)
選擇E2:F2單元格，雙擊單元格的填充柄,，將公式填充到最末尾即可完成排名計(jì)算,。
點(diǎn)評(píng)
Excel有一個(gè)內(nèi)置函數(shù)Rank用于對(duì)成績(jī)排名，但它是美式排名法,。而更重要的是它無(wú)法實(shí)現(xiàn)按條件排序,，它的第一參數(shù)必須是單元格，這限制了它的功能發(fā)揮,，例如以下公式Rank是無(wú)法運(yùn)算的：
=rank(3,{1,2,3,4,5})
而本自定義函數(shù)是可以使用內(nèi)存數(shù)組作為參數(shù)的：
=排名({1,2,3,4,5},3)
5.4開發(fā)帶有兩個(gè)可選參數(shù)的Function過程
1. 獲取可控制大小寫的英文列標(biāo)
【要求】：返回指定單元格的英文列標(biāo),，且可以控制列標(biāo)的大小寫狀態(tài)。如果不指定大小寫則默認(rèn)為大寫,，如果不指定單元格,，則默認(rèn)計(jì)算公式所在單元格的列標(biāo)。
【代碼】：
Function col(Optional rng As Range, Optional style As String = "A")''聲明函數(shù)名稱,有兩個(gè)可選參數(shù)
Application.Volatile''聲明為易失性函數(shù)
''如果第二參數(shù)錄入A和a以外的任意字符則返回空白
IF style <> "A" And style <> "a" Then col = "": Exit Function
IF rng Is Nothing Then Set rng = Application.ThisCell''如果忽略第一參數(shù)則默認(rèn)取公式所在單元格
''函數(shù)結(jié)果等于Cells(1, rng)的地址去除1之后所對(duì)應(yīng)的字母,。然后根據(jù)第二參數(shù)進(jìn)行大小寫控制
col = StrConv(Replace(Cells(1, rng.Column).Address(0, 0), 1, ""), IIF(style = "A", vbUpperCase, vbLowerCase))
End Function
提示
（1）函數(shù)中非對(duì)象變量被忽略時(shí),，可以用IsMissing來(lái)判斷，但本例中第一參數(shù)是單元格對(duì)象,，所以只對(duì)用Nothing來(lái)判斷,，且在賦值時(shí)必須用Set語(yǔ)句。
（2）Address屬性的兩個(gè)參數(shù)使用0時(shí)可以將地址轉(zhuǎn)換成相對(duì)引用，這有利于獲取列標(biāo),。
【測(cè)試】：
在工作表中錄入以下公式測(cè)試Col函數(shù)：
=Col(D2,"A")——結(jié)果為D,，第二參數(shù)大寫則結(jié)果也大寫
=Col(D2) ——結(jié)果仍為D，若忽略第二參數(shù)則默認(rèn)按大寫處理
=Col(D2,"a")——結(jié)果為d,，第二參數(shù)小寫則結(jié)果也小寫
=Col(,)——如果在C10輸入公式則結(jié)果為C,，兩個(gè)參數(shù)都忽略時(shí)獲取當(dāng)前單元格的大寫列標(biāo)
=Col()——如果在F2輸入公式則結(jié)果為F，兩個(gè)參數(shù)都忽略時(shí)獲取公式所在單元格的大寫列標(biāo)
如果需要產(chǎn)生升序的大寫字母序列,，可以采用以下公式并向右填充：
=col(A1)
點(diǎn)評(píng)
Excel自帶的Column函數(shù)可以獲取指定單元格的數(shù)字列標(biāo),，無(wú)法獲取英文列標(biāo)，本函數(shù)與Column可以做互補(bǔ),。
2. 計(jì)算多樣式星期
【要求】：對(duì)指定日期計(jì)算星期,，有四種格式可選，包括“一”,、“星期一”,、“Mon”和“Monday”四種。如果未指定日期則以今天為基準(zhǔn),，如果未指定格式則以“星期一”這種中文長(zhǎng)寫為基準(zhǔn),。
【代碼】：
Function 星期(Optional dates As Date, Optional style As Byte = 2) ''聲明函數(shù)名稱,具有兩個(gè)可選參數(shù)
IF dates = 0 Then dates = Date ''如果忽略第一參數(shù),則以當(dāng)日計(jì)算
''如果僅一個(gè)參數(shù),則參數(shù)在1到4之間,則將參數(shù)值賦予第二參數(shù),而將當(dāng)前日期賦予第一參數(shù)
IF dates < 5 And dates > 1 Then style = dates: dates = Date
Select Case style ''根據(jù)第二參數(shù)值選擇星期的格式
Case 1 ''第二參數(shù)為1
星期 = WorksheetFunction.Text(dates, "aaa") ''短寫中文
Case 2
星期 = WorksheetFunction.Text(dates, "aaaa") ''長(zhǎng)寫中文
Case 3
星期 = WorksheetFunction.Text(dates, "ddd") ''短寫英文
Case 4
星期 = WorksheetFunction.Text(dates, "dddd") ''長(zhǎng)寫英文
End Select
End Function
提示
（1）第一參數(shù)聲明為日期類型，那么當(dāng)忽略第一參數(shù)時(shí),，不能用IsMissing來(lái)判斷,，只能判斷它是否等于0。而當(dāng)日期參數(shù)聲明為可選參數(shù)時(shí)不能像第二參數(shù)一樣直接賦予一個(gè)默認(rèn)值：Date或者Now,，因?yàn)槁暶髯兞繒r(shí)只能用常數(shù),。為了解決這個(gè)問題，只能在代碼中間根據(jù)其特征判斷用戶在錄入公式時(shí)是否已經(jīng)忽略該參數(shù),。
（2）本函數(shù)實(shí)例實(shí)現(xiàn)了自動(dòng)判斷所忽略的是哪一個(gè)參數(shù)的功能,，即當(dāng)忽略兩個(gè)可選參數(shù)中的一個(gè)時(shí),，函數(shù)會(huì)判斷用戶忽略的是哪一個(gè),。如果唯一的參數(shù)值在1到4之間，則將其賦予第二參數(shù),，將當(dāng)前日期賦予第一參數(shù),。否則將唯一的參數(shù)當(dāng)作第一參數(shù)計(jì)算，而第二參數(shù)以默認(rèn)值2參與計(jì)算,。
【測(cè)試】：
在工作表中錄入以下公式測(cè)試星期函數(shù)：
=星期()——假設(shè)今天是2009-4-28,，則結(jié)果為“星期二”，中文長(zhǎng)寫格式
=星期(,3) ——假設(shè)今天是2009-4-28,，則結(jié)果為“Tue”
=星期("2000-2-29",1)——結(jié)果等于“二”
=星期(A1,4) ——如果A1為“1998-12-20”,，則結(jié)果等于“Sunday”
=星期(4) ——假設(shè)今天是2009-4-28，則結(jié)果為“Tuesday”
點(diǎn)評(píng)
Excel自帶函數(shù)TEXT可以實(shí)現(xiàn)四種星期格式的運(yùn)算,，但其參數(shù)對(duì)于新手來(lái)說不方便記憶,。開發(fā)自定義函數(shù)時(shí)需要突破這種屏障，盡量用最簡(jiǎn)單的參數(shù)表示出來(lái),；另一個(gè)值得學(xué)習(xí)的是本函數(shù)所有參數(shù)全是可選的,，為用戶提供最大的便利。
5.5開發(fā)帶有不確定參數(shù)的Function過程
1. 串聯(lián)內(nèi)存數(shù)組及選區(qū)
【要求】：按要求將內(nèi)存數(shù)組中每個(gè)元素串聯(lián)成一個(gè)字符串,，同時(shí)對(duì)選定區(qū)域也進(jìn)行串聯(lián),。
【代碼】：
Function Connect(ParamArray Rng() As Variant) ''聲明函數(shù)名稱,有多個(gè)可選參數(shù),包括1到255個(gè)
Dim cell As Range, celll As Range, i As Integer, cellv As Variant ''聲明變量
Connect = "" ''將函數(shù)初始化
''遍歷參數(shù)所有代碼的對(duì)象集合(可能是字符串,可能是區(qū)域,也可能是數(shù)組)
For i = 0 To UBound(Rng)
IF Not IsMissing(Rng(i)) Then ''如果有參數(shù)
Select Case TypeName(Rng(i)) ''根據(jù)參數(shù)的類型決定計(jì)算方式
Case "Range"''如果是單元格
''如果參數(shù)設(shè)置過大,僅僅對(duì)參數(shù)與已用區(qū)域的重疊部分進(jìn)行計(jì)算
Set celll = Application.Intersect(Rng(i), ActiveSheet.UsedRange)
For Each cell In celll''遍歷單元格區(qū)域
Connect = Connect & cell''串聯(lián)所有單元格字符
Next cell
Case "Variant()"''如果是數(shù)組(包括內(nèi)存數(shù)組)
For Each cellv In Rng(i)''遍歷數(shù)組
''跳過false,將數(shù)組中其他元素串聯(lián)
IF cellv <> false Then Connect = Connect & cellv
Next cellv
Case Else''否則
Connect = Connect & Rng(i)''直接連接(指直接在參數(shù)中輸入的字符串)
End Select
End IF
Next i
End Function
提示
不確定參數(shù)的函數(shù)必須使用ParamArray進(jìn)行聲明參數(shù)，使用ParamArray時(shí)需要遵循三個(gè)規(guī)則：
（1）ParamArray所聲明的參數(shù)必須位于最后位置,，即除了ParamArray聲明的參數(shù)外還有其他參數(shù)時(shí),，該參數(shù)必須位于ParamArray聲明的參數(shù)的左方。
（2）ParamArray所聲明的參數(shù)必須用Variant數(shù)據(jù)類型,。
（3）Intersect的作用是讓函數(shù)只計(jì)算數(shù)據(jù)區(qū)域與參數(shù)所有代碼區(qū)域的重疊區(qū),，防止整列、整行或者整個(gè)工作表作為參數(shù)造成死機(jī),。但它同時(shí)也帶來(lái)了一個(gè)缺點(diǎn)：參數(shù)只能引用本工作表的區(qū)域,，引用其他工作表或者工作簿的區(qū)域時(shí)，將會(huì)忽略,。
本函數(shù)在Excel 2007中具有0到255個(gè)參數(shù),，而在Excel 2003中則只有0到30個(gè)參數(shù)。每個(gè)函數(shù)都是可選的,。
【測(cè)試】：
工作表中有如圖6.20所示數(shù)據(jù),，為了將工號(hào)大于2000的員工的姓名串聯(lián)成一個(gè)字符串，在單元格D2中錄入以下數(shù)組公式：
= Connect(IF(B2:B10*1>2000,A2:A10))
公式必須用【Ctrl+Shift+Enter】三鍵組合錄入才能得到正確結(jié)果,。
圖20 串聯(lián)內(nèi)存數(shù)組
如果需要串聯(lián)A列所有姓名,，那么可以使用以下公式：
=Connect(A2:A10)
如果需要對(duì)常量數(shù)組進(jìn)行連接，也可以使用以下公式：
=Connect({"A","DD","S"},{1,7,100})
點(diǎn)評(píng)
Excel自帶兩個(gè)連接文件的函數(shù)：CONCATENATE和&,。然而它們共同的缺點(diǎn)都是不能對(duì)區(qū)域進(jìn)行批量操作,，也不能對(duì)數(shù)組進(jìn)行串聯(lián)，這使兩個(gè)函數(shù)在工作中受到極大的限制,。而自定義函數(shù)可以突破這兩個(gè)限制,，完成更復(fù)雜的工作，這也是本函數(shù)的亮點(diǎn),。
2. 統(tǒng)計(jì)多區(qū)域公式個(gè)數(shù)
【要求】：對(duì)多個(gè)區(qū)域計(jì)算含有公式的單元格的個(gè)數(shù),。
【代碼】：
Function Functions(ParamArray rng() As Variant)‘聲明函數(shù)名稱,有多個(gè)可選參數(shù),包括1到255個(gè)
Dim cell, Fun_count As Long, i As Byte, celll As Range ''聲明變量
IF UBound(rng) = -1 Then Functions = 0: Exit Function ''如果無(wú)參數(shù)則結(jié)果為0
For i = 0 To UBound(rng)''遍歷每個(gè)參數(shù)
IF Not IsMissing(rng(i)) Then''如果有參數(shù)
Set celll = Application.Intersect(rng(i), ActiveSheet.UsedRange)
For Each cell In celll''遍歷區(qū)域中每個(gè)元素
IF cell.HasFormula Then Fun_tion = Fun_tion + 1 ''如果有公式則累加變量
Next cell
End IF
Next i
Functions = Fun_tion ''統(tǒng)計(jì)結(jié)果
End Function
提示
（1）在本函數(shù)的參數(shù)中，Rng是變體型,，作為數(shù)組處理,。那么進(jìn)入For循環(huán)時(shí)默認(rèn)下標(biāo)為0，不能使用For I = 1 to UBound(Rng)。如果參數(shù)聲明為Range對(duì)象,，那么其下標(biāo)才是1,。
（2）同前一個(gè)函數(shù)一樣，只能對(duì)當(dāng)前表區(qū)域統(tǒng)計(jì)公式個(gè)數(shù),。
【測(cè)試】：
在圖6.21所示工作表中,，為了統(tǒng)計(jì)C列和F列具有多個(gè)公式，可以使用以下公式進(jìn)行計(jì)算：
=Functions(C:C,F:F)
公式可以使用1到255個(gè)參數(shù),，還可以累加區(qū)域,。但是參數(shù)引用的區(qū)域不可以包含公式所在單元格。
圖21 統(tǒng)計(jì)區(qū)域中的公式個(gè)數(shù)
點(diǎn)評(píng)
工作表函數(shù)可以統(tǒng)計(jì)空單元格個(gè)數(shù),、數(shù)字個(gè)數(shù),、文本個(gè)數(shù)、大于或小于某值的個(gè)數(shù)等,。本公式用于計(jì)算區(qū)域中的公式個(gè)數(shù),，算是對(duì)函數(shù)功能的補(bǔ)充。
5.6開發(fā)具有三個(gè)參數(shù)其中第三個(gè)為可選的Function過程
1. 按單元格背景顏色進(jìn)行條件平均
【要求】：按條件對(duì)與條件區(qū)域同等大小的統(tǒng)計(jì)區(qū)域計(jì)算平均,，如果不指定統(tǒng)計(jì)區(qū)域則以條件區(qū)域進(jìn)行計(jì)算,。
【代碼】：
Function AverageIFcol(條件區(qū) As Range, 顏色單元格 As Range, Optional 統(tǒng)計(jì)區(qū)) ''聲明函數(shù)名稱,有三個(gè)參數(shù),第三個(gè)是可選參數(shù)
Dim i As Integer, Counts As Integer, rng As Range, sum As Double''聲明變量
Application.Volatile''聲明為易失性函數(shù)
IF IsMissing(統(tǒng)計(jì)區(qū)) Then Set rng = 條件區(qū)''如果第三參數(shù)被忽略,則將條件區(qū)賦予rng變量
''如果未被忽略,那么以統(tǒng)計(jì)區(qū)第一個(gè)單元格為基準(zhǔn),向下擴(kuò)充到條件區(qū)同等大小的區(qū)域賦予變量Rng
IF Not IsMissing(統(tǒng)計(jì)區(qū)) Then Set rng = 統(tǒng)計(jì)區(qū)(1).Resize(條件區(qū).Rows.Count, 條件區(qū).Columns.Count)
For i = 1 To 條件區(qū).Count ''遍歷條件區(qū)
''如果條件區(qū)中某個(gè)單元格背景色與顏色單元格區(qū)域(參照區(qū))顏色一致,那么
IF 條件區(qū)(i).Interior.Color = 顏色單元格(1).Interior.Color Then
sum = sum + rng(i).Value''累加符合條件的數(shù)據(jù)
Counts = Counts + 1''統(tǒng)計(jì)符合條件的個(gè)數(shù)
End IF
Next i
AverageIFcol = sum / Counts''最后結(jié)果等于總和除以個(gè)數(shù)
End Function
提示
（1）Rng是一個(gè)中間變量，用它來(lái)替代實(shí)際統(tǒng)計(jì)區(qū),。當(dāng)有第三參數(shù)時(shí)則等于第三參數(shù),，但參照條件區(qū)的大小,；當(dāng)忽略第三參數(shù)時(shí)則等于第一參數(shù),。
（2）為了體現(xiàn)通用性，計(jì)算單元格的背景色時(shí)必須使用Color,，而不能用ColorIndex,，否則在Excel 2003中可以使用，在Excel 2007中卻無(wú)法正常使用,。
【測(cè)試】：
對(duì)于圖6.22中的數(shù)據(jù),，對(duì)背景是黃色的學(xué)生的成績(jī)計(jì)算平均?？捎靡韵鹿剑?/font>
=AVERAGEifcol(K9:K17,K10,L9)
如果條件區(qū)和實(shí)際統(tǒng)計(jì)區(qū)是一個(gè)區(qū)域,，可以忽略第三參數(shù)，如圖6.23所示,。
圖22 按顏色條件對(duì)統(tǒng)計(jì)區(qū)求平均
圖23 按顏色條件對(duì)條件區(qū)求平均
點(diǎn)評(píng)
Excel本身有條件求和函數(shù)——SUMIF，但無(wú)法與單元格顏色作為參照,。本函數(shù)可以作為SUMIF函數(shù)的補(bǔ)充,，它與SUMIF函數(shù)的用法一致。
2. 按顏色從左向向右查找所有數(shù)據(jù)
【要求】：根據(jù)參照顏色對(duì)查找區(qū)域最左列查找同顏色的單元格，然后返回其右邊若干列的數(shù)據(jù),。如果找到多個(gè)符合條件的數(shù)據(jù),，需要全部羅列出來(lái)。
【代碼】：
''聲明函數(shù)名稱,有三個(gè)參數(shù),第三個(gè)是可選參數(shù),函數(shù)的結(jié)果是數(shù)組
Function VlookupCol(查找值 As Range, 查找區(qū)域 As Range, Optional 列數(shù) As Byte = 2) As Variant
Dim Col As Long, cell As Range, arr(), i As Byte ''聲明變量
Application.Volatile''聲明為易失性函數(shù)
Col = 查找值.Interior.Color''獲取參照單元格的背景色
''遍歷查找區(qū)域的最左邊一列
For Each cell In 查找區(qū)域(1).Resize(查找區(qū)域.Rows.Count, 1)
IF cell.Interior.Color = Col Then''如果與參照顏色一致
i = i + 1''累加變量
ReDim Preserve arr(1 To i)''重新聲明數(shù)據(jù)大小,且保持?jǐn)?shù)組原數(shù)據(jù)
arr(i) = cell.Offset(0, 列數(shù) - 1)''將找到的單元格右邊對(duì)應(yīng)的數(shù)值賦予數(shù)組
End IF
Next cell
VlookupCol = WorksheetFunction.Transpose(arr)''將數(shù)組的結(jié)果賦予函數(shù)
End Function
提示
（1）Resize屬性用于調(diào)整指定區(qū)域的大小,。在本例中因需要取得查找區(qū)域的最左邊一列,，所以需要借助Resize來(lái)重置區(qū)域，將行限定為原區(qū)域行數(shù),，將列限定為1,。
（2）因每找到一個(gè)目標(biāo)就需要重置數(shù)組Arr的大小，且重置時(shí)需要保留原數(shù)組的值,，所以循環(huán)中必須加入“ReDim Preserve”來(lái)聲明數(shù)組,。
（3）Arr數(shù)組是橫向數(shù)組，本例中利用工作表函數(shù)Transpose將它轉(zhuǎn)置為縱向數(shù)組,，再賦予函數(shù),。
（4）函數(shù)的結(jié)果是數(shù)組，如果以普通公式錄入可以取得第一個(gè)查到的目標(biāo),；以區(qū)域數(shù)組形式錄入也可以返回所有查到的結(jié)果,，假設(shè)存在多個(gè)符合條件的目標(biāo)值的話。
【測(cè)試】：
在圖24所示工作表中,，A列的姓名以不同背景顏色進(jìn)行區(qū)分,，在E1單元格有需要查找的參照顏色，在E2單元格錄入以下普通公式可以返回第一個(gè)查到的目標(biāo)數(shù)值41：
=VlookupCol(E1,A2:B11,2)
如果需要將符合條件的所有數(shù)據(jù)全部羅列出來(lái),，則需要使用區(qū)域數(shù)組公式,。選擇E2:E11區(qū)域并錄入以下數(shù)組公式：
=VlookupCol(E1,A2:B11,2)
必須按【Ctrl+Shift+Enter】三鍵結(jié)束才可以得到正確結(jié)果，如圖6.25所示,。
圖24 按顏色獲取第一個(gè)目標(biāo)值
圖25 按顏色獲取所有目標(biāo)值
因?yàn)闊o(wú)法確定有多少個(gè)符合條件的值,，那么使用區(qū)域數(shù)組公式時(shí)無(wú)法把握好區(qū)域大小，即既要將所有結(jié)果顯示出來(lái),，又不能出現(xiàn)錯(cuò)誤值“#N/A”,，那么可以套用Index來(lái)完成。
=IFERROR(INDEX(VlookupCol(E$1,A$2:B$11,2),ROW(A1)),"")
在F1錄入公式后,，將公式向下填充即可,。 |
|
|
|
|
|
|
|
6 編寫函數(shù)幫助
用戶自定義的函數(shù)不管自己使用還是給其他用戶使用，都有必要對(duì)函數(shù)的功能和參數(shù)添加一個(gè)說明,，使用戶在使用上更加方便,。不僅如此，還有必要對(duì)函數(shù)進(jìn)行分類,，例如大寫函數(shù)應(yīng)該劃入財(cái)務(wù)函數(shù)類,，那么通過插入函數(shù)的向?qū)Э梢詮呢?cái)務(wù)函數(shù)下拉列表中找到該函數(shù),。
如果打開插入函數(shù)向?qū)В梢园l(fā)現(xiàn)所有自定義函數(shù)默認(rèn)存在于“用戶定義”類別中,，如圖27所示,。
而雙擊進(jìn)入“函數(shù)參數(shù)”對(duì)話框后可以發(fā)現(xiàn)，函數(shù)的功能沒有相應(yīng)的說明,，每個(gè)參數(shù)的說明全是空白,。如圖28所示。
圖27 用戶定義類別中的自定義函數(shù)列表
圖28 函數(shù)參數(shù)對(duì)話框
為了解決這兩個(gè)問題,，需要簡(jiǎn)單的定義函數(shù)的幫助信息,。
VBA中用于指定函數(shù)說明的是Application.MacroOptions方法。
Application.MacroOptions方法的基本語(yǔ)法是：
Application.MacroOptions(Macro, Description, HasMenu, MenuText, HasShortcutKey, ShortcutKey, Category, StatusBar, HelpContextID, HelpFile)
各參數(shù)的函數(shù)如表4所示,。
表4 MacroOptions參數(shù)說明
|
名 稱
|
必選/可選
|
描 述
|
Macro
|
可選
|
用戶定義函數(shù) (UDF) 的名稱
|
Description
|
可選
|
函數(shù)的描述
|
HasMenu
|
可選
|
忽略該參數(shù)
|
MenuText
|
可選
|
忽略該參數(shù)
|
HasShortcutKey
|
可選
|
如果為 true,，則為宏指定一個(gè)快捷鍵；如果該參數(shù)為 false,，則不為宏指定快捷鍵
|
ShortcutKey
|
可選
|
如果參數(shù)為true,，則該參數(shù)為必選參數(shù)；否則忽略該參數(shù)快捷鍵
|
Category
|
可選
|
一個(gè)指定現(xiàn)有的宏函數(shù)類別的整數(shù),，以確定映射為內(nèi)置類別的整數(shù),，還可指定自定義類別的字符串。如果提供了一個(gè)字符串,，它將作為類別名稱顯示在“插入函數(shù)”對(duì)話框中,，如果此類別名稱從未使用過，則將用該名稱定義一個(gè)新的類別,，如果使用的類別名稱與某個(gè)內(nèi)置名稱相同,，則 Excel 會(huì)將用戶定義的函數(shù)映射為此內(nèi)置類別
|
StatusBar
|
可選
|
宏的狀態(tài)欄文本
|
HelpContextID
|
可選
|
一個(gè)指定分配給宏的幫助主題上下文 ID 的整數(shù)
|
HelpFile
|
可選
|
包含 HelpContextId 定義的幫助主題的幫助文件名
| 其中Category表示函數(shù)的類型。用戶可以將自定義函數(shù)添加到屬于自己的獨(dú)有類型中,，例如：
Application.MacroOptions Macro:="大寫", Description:="將阿拉伯?dāng)?shù)字轉(zhuǎn)為人民幣金額大寫", Category:="andy專用"
以上代碼用于將“大寫”函數(shù)加入到名為“andy專用”的新類別中,，如圖6.29所示。
圖29 將函數(shù)加入新類別中
當(dāng)然也可以使用內(nèi)置的函數(shù)類別,，例如“財(cái)務(wù)”,、“文本”、“邏輯”等,。在代碼中,，可以直接使用類別名，也可以使用其常數(shù)值,。值與類別的對(duì)應(yīng)關(guān)系如表5所示,。
表5 內(nèi)置函數(shù)類別
|
值
|
類 別
|
值
|
類 別
|
1
|
財(cái)務(wù)
|
8
|
邏輯
|
2
|
日期與時(shí)間
|
9
|
信息
|
3
|
數(shù)學(xué)與三角函數(shù)
|
10
|
命令
|
4
|
統(tǒng)計(jì)
|
11
|
自定義
|
5
|
查找與引用
|
12
|
宏控件
|
6
|
數(shù)據(jù)庫(kù)
|
13
|
DDE/外部
|
7
|
文本
|
14
|
用戶定義
|
根據(jù)上表的分析，如需要將自定義函數(shù)加入“文本”類,，可以用以下語(yǔ)句：
Application.MacroOptions Macro:="大寫", Description:="將阿拉伯?dāng)?shù)字轉(zhuǎn)為人民幣金額大寫", Category:=7
Application.MacroOptions Macro:="大寫", Description:="將阿拉伯?dāng)?shù)字轉(zhuǎn)為人民幣金額大寫", Category:="文本"
以上兩句代碼的效果一致,，都可以將“大寫”函數(shù)加入到內(nèi)置的函數(shù)類別“文本”中,。
MacroOptions方法僅對(duì)自定義函數(shù)生效,，內(nèi)置函數(shù)的任何說明性文字和分類都無(wú)法修改,。
使用MacroOptions方法添加函數(shù)的幫助分為兩類：普通工作簿和加載宏?；谄胀üぷ鞑九c加載工作簿的特性不同,，在設(shè)置函數(shù)說明時(shí)需要區(qū)別對(duì)待。
1. 普通工作簿
當(dāng)普通工作簿文件中有自定義函數(shù)時(shí),，例如“大寫”函數(shù),，利用以下一個(gè)自啟動(dòng)的程序添加函數(shù)說明，即每次加載工作簿時(shí)執(zhí)行,。
（1） 如果當(dāng)前工程中有“大寫”自定義函數(shù),，那么在VBE界面是單擊【插入】\【模塊】；
（2）在模塊中錄入以下自啟動(dòng)程序代碼,，該過程可以在工作簿每次開啟時(shí)全自動(dòng)執(zhí)行：
Sub auto_open()
Application.MacroOptions Macro:="大寫", Description:="將阿拉伯?dāng)?shù)字轉(zhuǎn)為人民幣金額大寫：僅需要一個(gè)參數(shù),，單元格引用。" + Chr(10) + "例如：“=大寫(a1)”", Category:="財(cái)務(wù)"
End Sub
（3）保存代碼后退出Excel,，再重新打開工作簿,；
（4）打開“插入函數(shù)”向?qū)В凇盎蜻x擇類型”下拉列表中選擇“財(cái)務(wù)”,，“大寫”函數(shù)即位于該類別中,，如圖30所示。
（5）雙擊“大寫”函數(shù)打開“函數(shù)參數(shù)”對(duì)話框 ,，在對(duì)話框中有關(guān)于“大寫”函數(shù)的功能,、參數(shù)說明及用法，如圖31所示,。
圖30 財(cái)務(wù)函數(shù)中的“大寫”函數(shù)
圖31 自定義函數(shù)的參數(shù)說明
以后不管任何時(shí)間打開該工作簿,，都可以在插入函數(shù)的向?qū)е锌吹疥P(guān)于“大寫”函數(shù)的信息，方便用戶使用,。
圖32 加載宏中添加函數(shù)說明時(shí)的錯(cuò)誤提示
| 2. 加載宏工作簿
前一種方法如果用在加載宏（xla格式或者xlam格式）文件中,，在開啟該加載宏時(shí)將會(huì)產(chǎn)生如圖6.32所示的錯(cuò)誤提示。
這是由VBA的規(guī)則決定的：在加載宏文件（加載宏的工作簿處于隱藏狀態(tài)）中,，不可以利用代碼編輯宏,。而控制文件是否具有加載宏屬性的方法是改變其IsAddin屬性。
所以方法一的代碼需要修改為：
Sub auto_open() ''每次開啟工作簿時(shí)執(zhí)行
With Application
.ScreenUpdating = false''關(guān)閉屏幕更新,防止閃屏
ThisWorkbook.IsAddin = false ''顯示加載宏工作簿
''添加函數(shù)說明
.MacroOptions Macro:="大寫", Description:="將阿拉伯?dāng)?shù)字轉(zhuǎn)為人民幣金額大寫:僅需要一個(gè)參數(shù),單元格引用,。" + Chr(10) + "例如："=大寫(a1)"", Category:="財(cái)務(wù)"
ThisWorkbook.IsAddin = true ''還原為加載宏
End With
End Sub
圖33 退出Excel程序時(shí)彈出的對(duì)話框
|
修改后的代碼中,，利用“IsAddin = false”，使加載宏工作簿由隱藏狀態(tài)還原為顯示狀態(tài),，然后再借用MacroOptions方法設(shè)置函數(shù)的說明信息,，最后恢復(fù)其隱藏狀態(tài),。
然而利用IsAddin控制其狀態(tài)切換將帶來(lái)兩個(gè)負(fù)面作用：
（1）在對(duì)工作簿顯示狀態(tài)進(jìn)行切換時(shí)，將會(huì)閃屏一次,，給用戶不舒服的感覺,。所以代碼中的“ScreenUpdating = false”則用于解決這個(gè)問題；
（2）因MacroOptions方法修改了工作簿中宏的屬性,，在退出Excel程序時(shí),，每次都會(huì)彈出一個(gè)如圖6.33所示對(duì)話框。程序會(huì)詢問用戶是否保存加載宏,，這是一個(gè)令人極其無(wú)法接受的事,。
為了杜絕這個(gè)問題，必須在工作簿的關(guān)閉事件中加入以下代碼：
Private Sub Workbook_BeforeClose(Cancel As Boolean)
ThisWorkbook.Close (false)
End Sub
代碼的含義是每次關(guān)閉當(dāng)前工作簿時(shí)都不保存,，從而屏蔽提示框,。
雖然改為不用關(guān)閉工作簿，而直接在“auto_open”過程加入一句保存工作簿的代碼也可以解決這個(gè)問題,，但它將浪費(fèi)一些不必要的時(shí)間,。
除以上兩種方法外，還有一種更簡(jiǎn)單的方式——在立即窗口執(zhí)行MacroOptions方法,。
使用立即窗口是基于一個(gè)前提條件：Excel會(huì)記錄并保存MacroOptions的操作,。也就是說只要第一次利用MacroOptions方法設(shè)計(jì)好后，函數(shù)中的說明就永遠(yuǎn)存在,，從而避免每次啟動(dòng)都調(diào)用代碼,。
具體的步驟如下：
打開帶有函數(shù)的工作簿，使用快捷鍵【Alt+F11】進(jìn)入VBE界面,；
雙擊Thisworkbook打開工作簿事件代碼窗口,，使用快捷鍵【Ctrl+G】顯示立即窗口；
在立即窗口中輸入以下代碼：
application.MacroOptions Macro:="大寫", Description:="將阿拉伯?dāng)?shù)字轉(zhuǎn)為人民幣金額大寫:僅需要一個(gè)參數(shù),單元格引用,。" + Chr(10) + "例如："=大寫(a1)"", Category:="財(cái)務(wù)"
在該行代碼最右邊單擊回車鍵,，表示執(zhí)行語(yǔ)句；
保存工作簿并刪除立即窗口中的代碼即可,。重新啟動(dòng)后在插入函數(shù)向?qū)е锌梢钥吹皆撜Z(yǔ)句產(chǎn)生的函數(shù)說明,，且永久生效。
如果工作簿中有多個(gè)自定義函數(shù),，那么可以錄入多句代碼并復(fù)制到立即窗口中,，然后分別執(zhí)行。分別執(zhí)行,，記住這一點(diǎn)很重要,，立即窗口一次只能執(zhí)行一行代碼，所以復(fù)制三行代碼到立即窗口后,，需要將光標(biāo)定位于每句的末尾單擊一次回車鍵,，以此類推,。
3. 如何讓函數(shù)說明通用于Excel 2003和Excel 2007
在編寫函數(shù)的說明時(shí)，有必要了解Excel 2003和Excel 2007中的一些差異,，從而在編寫代碼時(shí)盡量做到代碼通用,。
對(duì)于函數(shù)的處理Excel 2003和Excel 2007存在兩方面的差異。
（1）說明文字的長(zhǎng)度不同,。在Excel 2003和Excel 2007中,，利用MacroOptions方法添加的說明字符串長(zhǎng)度是不相同的,。Excel 2003的函數(shù)參數(shù)對(duì)話框僅僅能容下150個(gè)字符以內(nèi)的說明信息,。Excel 2007雖然可以達(dá)到200個(gè)字符以上，但為了兼容性,，盡量在150個(gè)字符之內(nèi)將函數(shù)的功能,、參數(shù)、用法完全說明白,，否則用戶在Excel 2003中使用時(shí)會(huì)看不到部分字符,；
（2）最大參數(shù)不同。當(dāng)函數(shù)使用了ParamArray聲明不確定參數(shù)時(shí),，其參數(shù)個(gè)數(shù)的上限是不同的,。在Excel 2003中，最多可以使用30個(gè)參數(shù),，而在Excel 2007中最多可以使用255個(gè)參數(shù),。所以為了讓函數(shù)的說明能夠適用于Excel 2003和Excel 2007，需要使用一些技巧,。
解決辦法是：利用Version屬性判斷Excel程序的版本,，如果不是Excel 2007則使用30，否則使用255,。代碼如下：
Sub 加入函數(shù)提示()
Dim counts As Byte
IF Application.Version * 1 <= 11 Then counts = 30 Else counts = 255
ThisWorkbook.IsAddin = false
Application.MacroOptions Macro:="我的自定義函數(shù)", Description:="本函數(shù)具有" & counts & "個(gè)參數(shù),其中......", Category:="財(cái)務(wù)"
ThisWorkbook.IsAddin = true
End Sub
補(bǔ)充：Excel 2010中,，Application.MacroOptions 方法增加了個(gè)可選參數(shù)ArgumentDescriptions，它表示“函數(shù)參數(shù)”對(duì)話框中顯示的參數(shù)的描述,。 換一種說法即通過它添加自定義函數(shù)的參數(shù)說明,。
例如：
Function A(B, C) As String
A = B & C
End Function
Sub 創(chuàng)建參數(shù)說明()
Application.MacroOptions Macro:="a", ArgumentDescriptions:=Array("第一參數(shù)", "第二參數(shù)")
End Sub
執(zhí)行過程“創(chuàng)建參數(shù)說明”后，自定義函數(shù)的參數(shù)B和C將出現(xiàn)參數(shù)說明,。如下圖所示：
圖34 參數(shù)說明
總結(jié)
Function過程即自定義函數(shù),，根據(jù)工作需要可以開發(fā)自己專用的函數(shù)。
對(duì)于函數(shù)的運(yùn)算速度,，內(nèi)部集成的工作表函數(shù)一定快于用戶自己定義的函數(shù),，在內(nèi)部函數(shù)能夠完成的情況下盡量使用工作表函數(shù)；如果工作表函數(shù)無(wú)法完成,，或者需要非常長(zhǎng)的數(shù)組公式才能完成的情況下,，可以開發(fā)專用的自定義函數(shù),。
開發(fā)自定義函數(shù)時(shí)，應(yīng)該盡量給用戶一些可選項(xiàng),，使函數(shù)運(yùn)用更簡(jiǎn)單,，公式也更簡(jiǎn)短。例如內(nèi)部函數(shù)Left,，取左邊第一個(gè)字符時(shí)可以忽略第二參數(shù),。
對(duì)于某些運(yùn)算結(jié)果，如果工作中常見格式較多,，應(yīng)盡可能將所有格式全部羅列出來(lái),，讓用戶選擇。例如本章定義的星期函數(shù),，支持四種顯示方式,。
對(duì)于有多個(gè)結(jié)果的函數(shù)，應(yīng)將函數(shù)聲明為數(shù)組,，將所有結(jié)果顯示給用戶,。但為了體現(xiàn)靈活性，同時(shí)需要指定一種默認(rèn)顯示的值,。例如自定義函數(shù)VlookupCol,，相對(duì)內(nèi)部函數(shù)Vlookup在某些方面具有獨(dú)特的優(yōu)越性。
為了讓用戶自定義的函數(shù)在任何工作簿中都可以應(yīng)用,，應(yīng)將工作簿保存為加載宏文件,，并對(duì)其加載或者置于自啟動(dòng)文件夾。對(duì)于加載宏文件的生成方法參見本書第31章,。
最后,，為了讓用戶能更快掌握開發(fā)者定義函數(shù)的功能，應(yīng)對(duì)函數(shù)編寫說明,，且對(duì)其歸類,。
|
|
|
