編寫Function過程開發函數

概述：function過程即自訂函數，在外掛程式中應用極廣。應用範圍小，過程僅僅能返回一個只或者多個數的組合，而sub過程既可以傳回值，還可以對引用的對象進行修改，例如，引入儲存格A1的值，對A1設定心的格式。Function可以擷取到工作名稱，但無法修改工作表的名稱，funcion過程可以不實用參數，類似於工作表函數Rand和Now等。但絕大部分是需要一個參數或者多個。最大達到255個參數。

一、Function文法解析

[public | private | friend ] [static] function name [(arglist)][as type]

         [statements]

         [Name=expression]

[Exit function]

[statements]

         [name=expression]

End function

Public 可選，表示所有模組的所有其他過程都可以訪問這個function過程，如果是在包含option private的模組中使用，則這個過程在該工程外是不可使用的

Private 可選的，表示只有包含其聲明的模組的其他過程可以訪問該function

Friend  可選的，智能在類別模組中使用，表示該function過程在整個過程中都是可見的，但對於對象執行個體的控制者是不可見的

Static        可選，表示在調用之間將保留function過程的局部變數值，static屬性對在該function外聲明的變數不會影響，即使過程中也使用了這些變數

Name      必須的。Function的名稱

Arglist      參數

Expression        可選的，function的傳回值，函數名=值，將返回這個值

        

         調用方式：在工作表中通過公式調用，想內建函式一樣在工作表中使用，也可以嵌套

         被其他程序呼叫。VBA.函數名(參數)

         跟sub過程一樣都可以實現遞迴

二、過程的參數

a)        Sub過程的參數及應用

                        i.             參數文法：

[Optional] [ByVal | ByRef] [ParamArray] varname[()] [astype][=defaultvalue]

Opntional 可選，表示參數不是必須的關鍵字，如果使用了該選擇，則後續參數都必須是可選的，而且必須都使用optional關鍵字聲明，如果使用了paramarray 則任何參數都不能使用optional

ByVal 可選的  表示該參數按值傳遞

ByRef        可選表示該參數按地址傳遞，ByRef是預設的

ParamArray     可選，只用於arglist的最後一個參數，致命最後一個參數一個variant元素optional數組，使用paramArray關鍵字可以提供任何資料的參數。ParamArray關鍵字不能寫ByVal ByRef或optional一起使用

Defaultvalue 可選的，任何常數或常數運算式，只對optional參數合法，如何類型為Object，則顯示的預設值只能是nothing

驗證操作許可權案例

Sub 姓名(name AsString)

Dim i AsByte, rng As Range

For i = 1To Sheets.Count

    If ThisWorkbook.Sheets(i).name = "許可人員列表"Then: GoTo OK

    Next i

    MsgBox "不存在許可人員列表", 64

    Exit Sub

OK:

    If Len(name) < 2 Or Len(name) > 10Then MsgBox "長度智能是2到4，請重新錄入", 64: Exit Sub

    Set rng = ThisWorkbook.Sheets("許可人員列表").Range("a1:a10").Find(name)

    If rng Is Nothing Then MsgBox "你無權操作"Else MsgBox "你具有操作許可權"

End Sub

Sub 確認許可權一() '手工指定姓名

Call 姓名(Application.InputBox("請輸入姓名","確認許可權", "", , , , , 2))

End Sub

Sub 確認許可權二() '以當前表A1的值進行判斷

    Call 姓名(ActiveSheet.Range("A1"))

End Sub

Sub 確認許可權三()

    MsgBox Application.UserName

    Call 姓名(Application.UserName)

End Sub

                      ii.             按值傳遞和按地址傳遞。

Byval 變數 as long

Byval 表示參數按值傳遞。不能改變主程式傳過來的實參

ByRef 表示參數按地址傳遞   可以改變主程式傳過來的實參  預設

b)        Function過程的參數

Function只能傳過來來實參的引用，不能更改傳的實參的屬性和值，可以返回對應的值

c)        開發自訂函數

                        i.             開發不帶參數的function過程

1.        擷取本機IP地址

Function IP() '擷取IP地址

    Dim item

    '通過WMI技術擷取網卡當前設定的IP地址

    For Each item InGetObject("winmgmts:\\" & "." &"root\cimv2").ExecQuery("select * fromWin32_NetworkAdapterConfiguration")

    If TypeName(item.IPAddress) <>"null" Then IP = item.IPAddress(0)

    Next

End Function

2.        返回有公式的儲存格地址

FunctionFunAdd()

Dim rngAs Range, cell As Range

For Eachrng In ActiveSheet.UsedRange    '遍曆當前表的已用地區

Ifrng.HasFormula Then '如果有儲存格公式

Ifrng.Address <> Application.ThisCell.Address Then '如果變數rng的地址不等於當前公式所在的儲存格的地址

If cellIs Nothing Then '如果變數cell為初始化

    Set cell = rng

    Else

    Set cell = Appcation.Union(cell, rng) '將變數cell與rng所代表的兩個儲存格對象合并為一個對象，賦值給cell

    End If

End If

End If

Next rng

 

If cellIs Nothing Then FunAdd = "" Else FunAdd = cell.Address(0, 0)  '返回結果

 

End Function

                      ii.             開發帶有一個參數的function過程

1.        將人民幣金額轉換成大寫

Function 大寫(cell As String)

    Dim rmbs As String

    If cell = "" Or NotIsNumeric(cell) Then 大寫 = "": Exit Function '如果參數為空白，或者不是數值就返回空退出過程

    If cell = 0 Then 大寫 = "零元整":Exit Function '如果參數為0則返回字串並退出

    '將數值轉換成中文大些，並將點替換成元，將負號替換成負

    rmbs = Replace(Replace(Application.Text(Round(cell,2), "[DBnum2]"), ".", "元"),"-", "負")

    '加入角與分，同事將最後的"零"替換成元整

    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 '返回

   

End Function

2.        建立工作表目錄

Function 工作表(Optional 序號) As String '聲明函數，有一個參數為選擇性參數

Application.Volatile'聲明為易失性函數

'如果未輸入參數，則賦予變數序號為當前表的地址

If IsMissing(序號) Then 序號 =ActiveSheet.Index

If 序號 >Sheets.Count Then '如果參數大於工作表數量

工作表 = ""

Else

工作表 = Sheets(序號).name

End If

End Function

公式：

=HYPERLINK("#" & 工作表(ROW(A2))& "!A1",工作表(ROW(A1)))

3.        關機函數

Function 關機(Optional Close_Time As Byte = 10)

關機 = Close_Time

Shell"shutdown -s -t" & Close_Time '在指定的時間內關閉計算機，調用dos命令

End Function

                            方法收穫：

                                     IsNumeric 
喲關於判斷參數是否是數字

                                     Replace    
是用於替換的函數，但它與工作表函數replace有極大不同，與substitute函數極其相近

                                     IsMissing
用於判斷函數的選擇性參數是否已經傳遞給過程。

                                     Index             
屬性則是值工作表在所有工作表中的序號左---右

                                     Shell   操作Dos命令，傳遞dos命令即可

 

                     iii.             開發帶有兩個參數的function過程

1.        對系統匯出的資料分列，有兩個參數，第二個參數為選擇性參數

'第一個參數為儲存格參照，第二參數表示取分列後的第幾列

Function Breakdown(rng As Range, Optional style As Byte = 1) AsString

Application.Volatile

On ErrorResume Next '防錯

Dim i AsInteger, str As String

'將儲存格的值以逗號為分隔字元轉換成一位元組，再從數組中取字串賦值給變數str 取值的位置取決於第二個參數

str = Split(rng.Text, ",")(WorksheetFunction.RoundUp(style/ 3, 0) - 1)

If styleMod 3 = 1 Then '如果第二個參數除以3餘數是1

For i = 1To Len(str) '遍曆str的每一個字元

    If IsNumeric(Mid(str, i, 1)) Then ExitFunction '如果遇到數字，結束過程

    Breakdown = Breakdown & Mid(str, i, 1)'將取出的所有字串串連起來左右傳回值

    Next i

ElseIfstyle Mod 3 = 2 Then '如果參數除以3餘數是2

    For i = 1 To Len(str)

    '如果遇到數字或者小數點，就取出並串聯起來作為傳回值

    If VBA.IsNumeric(Mid(str, i, 1)) Or Mid(str,i, 1) = "." Then Breakdown = Breakdown & Mid(str, i, 1)

    Next i

ElseIfstyle Mod 3 = 0 Then

    For i = 1 To Len(str) Step -1 '遍曆str的每一個字元， 從右向左

    If IsNumeric(Mid(str, i, 1)) Then ExitFunction '如果遇到數字就結束過程

    Breakdown = Mid(str, i, 1) & Breakdown

    Next i

End If

If Err<> 0 Then Breakdown = "" '如果有錯誤就返空

End Function

調用：=Breakdown($A3,COLUMN(A1))

2.        中國式排名

Function 排名(地區, 成績) '聲明函數，有兩個參數

Application.Volatile

Dim dicAs Object, rng, i As Integer '聲明變數，包括字典對象

Set dic =CreateObject("scripting.dictionary") '聲明字典物件變數

For Eachrng In 地區 '遍曆地區

'如果變數rng等於成績則為變數i 賦值1，如果變數rng大於成績，則將rng的值追加到字典中

If rng = 成績 Then i = 1Else If rng > 成績 Then dic(rng * 1) = 1

Next

'如果變數i大於0，則地區中有資料等於成績，那麼排名結果等於字典中的數量+1 (字典對象是忽略重複值)

If i >0 Then 排名 = dic.Count + 1 Else 排名 = "超出範圍" '如果成績與地區中任何資料都不想等則返回超出範圍

End Function

CreateObject(“scripting.dictionary”)用於建立一個字典對象，它的特點是成員不重複，而中國式排名，是需要忽略重複值的，即四人中第一人100分算第一名，兩個99分並列第二名。

函數的兩個參數都支援手動錄入參數，而非僅僅限於儲存格參照

Split   是一個數組函數，它可以將一個字串按某個字元為分隔字元轉換成一個數組。

                     iv.             開發兩個帶有選擇性參數的function過程

聲明方式：function col(optional rng as range,optional style as string=”A”)  聲明函數名稱，有兩個選擇性參數   後面=“A” 方便於判斷使用者是否輸入值

收穫：

函數中費物件變數被忽略時，可以用isMissing來判斷，如果是儲存格對象，只能用nothing來判斷。If rng is nothing

Address屬性的兩個參數使用0時可以將地址轉換成相對參照，這有利於擷取列標

日期類型的變數。不能用nothing來判斷。判斷是否等於0

                      v.             開發帶有不確定參數的function過程

聲明方式  functionConnect(ParamArray  rng() as Variant)  有多個參數，包括1-255個，在處理的時候使用for迴圈遍曆。這個數組的大小使用UBound函數擷取

收穫：

UBound  返回一個
Long 型資料，其值為指定的數組維可用的最大下標

For i=0 To UBound(rng) 因為是變體量，for迴圈時預設下標應該為0

聲明不確定參數規則：

1.      所聲明的參數必須位於最後位置

2.      聲明的參數必須是Variant資料類型

3.      Application的Intersect的作用是讓函數只計算資料區域與參數所有代碼地區的重疊去，防止整列，整行或者整個工作表作為參數造成死機，但它同事也帶來了一個缺點參數智能引用本工作表的地區，一用其他工作表或者活頁簿地區時，將忽略

                     vi.             開發帶具有三個參數切第三個為可選的function過程

聲明方式：function 三餐(條件區 as range,顏色儲存格 as range,optional 統計區)

收穫：

計算儲存格背景色必須使用Color，不能用ColorIndex。在2003中可以使用。但在2010中不能使用

Range.Resize(num1,num2)屬性用於調整指定地區的大小。 參數1表示新地區的行數，新地區中的列數，如果忽略。則不變

ReDim Preserve array(1 To 2)沒找到一個目標就需要重設數組的大小，且重設時需要保留原數組的值，所以迴圈中必須加入 ReDim Preserve 來聲明數組

Transpose(數組)  將橫向數組專置為縱向數組

Vlookup 函數  只能返回一個合格目標值

三、編寫函數的協助

當你在模組或者其他地方編寫了函數，需要讓使用者使用的時候，使用者雙擊進去，協助資訊空白。那麼給使用者帶來了很大的不便，所以需要編寫函數的協助資訊

Application.MacroOptions方法編寫協助資訊

Application.MacroOptions(macro,Description,HasMenu,MenuText,HasShortcutKey,ShortcutKey,Category,StatusBar,HelpCountextId,HelpFile)

Macro      宏的名稱或使用者定義的函數的名稱

Description      宏的描述

hasMenu          忽略該參數

MenuText         忽略該參數

HasShortcutKey       如果為true，則為宏指定一個快速鍵，必須指定shortcutKey。如果該參數為false，則不為宏指定快速鍵。如果宏已經有快速鍵，如果指定false就失效

ShortcutKey     如果上面參數為true，必須指定快速鍵

Category           一個指定現有的宏函數類別的整數，如果提供了一個字串，它將作為類別名稱顯示在插入函數的對話方塊中。如果此類別名稱未使用過，則用該名稱定義一個新的類別，如果使用的類別名稱與某個內建名稱相同，將把使用者定義的函數映射到該內建類別

statusBar    宏的狀態列文本

HelpCountextId        一個指定分配給宏說明主題上下文id的整數

HelFIie               包含HelpContextId定義的說明主題的協助檔案名稱

ArgumentDescriptions             函數參數 對話方塊中顯示的參數的描述

 

四、總結

Function過程即自訂函數，根據工作需要可以開發自己專用的函數

 

對於函數的運算速度，內部的工作表函數一定快於使用者自訂的函數。

 

開發自訂函數的時候，應該盡量給使用者一些可選項，使函數運算更簡單，公式也更簡短。參數可選的概念

 

對於某些運算結果，如果可以返回多個結果，應儘可能將所有格式全部羅列出來，讓使用者選擇。例如本次的日期函數

 

對於有多個結果的函數，應將函式宣告為數組，將所有結果顯示給使用者，但為了體現靈活性，同事需要指定一種預設顯示的值。例如自訂函數VlookupCol。

 

為了讓使用者自訂的函數在任何活頁簿都可以使用，應將活頁簿為載入宏檔案。並對齊載入或者至於自開機檔案夾。