這裡介紹 Excel VBA 的函數（Function）與子程序（Sub）使用方法，並且提供許多實用的參考範例。

一般的程式語言都會有自訂函數的功能，讓程式設計者可以將會重複使用的程式碼編寫成函數，方便未來使用。VBA 的自訂函數有分為兩種：

  • 函數（Function）：VBA 的 Function 就像一般程式語言的函數，可傳入各種參數，進行自訂的運算，並將計算結果傳回。
  • 子程序（Sub）：VBA 的 Sub 與 Function 類似，可傳入各種參數並進行運算，但是沒有傳回值（沒有辦法傳回計算結果）。

以下介紹函數（Function）與子程序（Sub）的語法。

子程序（Sub）

事實上我們在一開始學習 VBA 的程式設計時，就已經使用過子程序了，以下是一個最簡單的 Hello World 子程序範例程式碼：

Sub Hello()
  MsgBox ("Hello, world!")
End Sub

子程序是以 Sub 這個關鍵字加上一個子程序名稱開始的，子程序名稱後方會接著一對小括號，小括號內部會放置傳入的參數（這個例子中沒有任何傳入參數），而最後面的 End Sub 就是子程序的結尾，中間的部分就是子程序的程式內容。

這我們裡定義了一個名稱為 Hello 的子程序，其內容就是一行 MsgBox 輸出訊息指令。當我們呼叫這個 Hello 子程序時，就會執行裡面的程式碼，在前面的 VBA 教學中，我們都是透過這種沒有任何輸入參數的子程序來執行自己的 VBA 巨集程式。

輸入參數

我們可以自己定義可接受各種輸入參數的子程序：

' 自行定義的子程序
Sub mySub(x As Integer, y As Integer)
  MsgBox ("x + y = " & x + y)
End Sub

這個 mySub 子程序可以接受兩個整數參數，在計算這兩個數的總和之後，再用 MsgBox 輸出結果。

其他類型的參數用法也都類似，例如：Double、String 等，請依據資料特性自行選擇適合的參數類型。

定義好這個子程序之後，就可以在程式的其他地方使用它，呼叫子程序時只要直接輸入子程序，再依序加上逗點分隔的輸入參數即可：

Sub hello()
  ' 呼叫 mySub 子程序
  mySub 1, 2
End Sub

在執行時，我們會先呼叫 hello 這個子程序，接著它會呼叫 mySub 子程序，並將要計算的兩個數值資料也傳遞進去，進行運算後再將結果顯示出來。

VBA 子程序

這是執行的結果：

執行結果

預設參數值

在定義子程序（Sub）的參數時，我們可以透過 Optional 這個關鍵字將某些參數設定為選擇性的，並且加上參數的預設值：

' 自行定義的子程序
Sub mySub2(Optional x As Integer = 3, Optional y As Integer = 4)
  MsgBox ("x + y = " & x + y)
End Sub

這樣的話在呼叫這個子程序時就可以省略這些選擇性的參數，讓它自動使用預設值，只在需要更動預設值的時候才加上該參數：

Sub hello()
  ' 計算 3 + 4
  mySub2

  ' 計算 1 + 4
  mySub2 1

  ' 計算 1 + 2
  mySub2 1, 2
End Sub

傳值與傳參考呼叫

在預設的情況下，如果我們在子程序改變的傳入參數的值，原來的值也會跟著改變，請看以下的範例：

' 自行定義的子程序
Sub mySub3(x As Integer)
  x = x + 1
End Sub

這裡我們在自行定義的子程序中將傳入的 x 值加上 1，然後我們在呼叫這個子程序之後，檢查一下原來的數值：

Sub Hello()
  Dim val As Integer
  val = 5
  mySub3 val
  MsgBox val
End Sub

執行之後，會發現到原來的 val 變數值也跟著加上 1 了。

執行結果

會有這樣的結果是因為 VBA 子程序預設的呼叫方式是傳參考呼叫（call by reference），簡單來說就好像把這裡的 val 變數直接拿進 mySub3 子程序中使用，所以 mySub3 中的 x 其實就是 val，而改變了 x 的值就等於是改變了 val 變數。

如果不想要讓子程序去改變原本的變數值，可以改用傳值呼叫（call by value）的方式來定義子程序的參數：

' 傳值呼叫的子程序
Sub mySub3(ByVal x As Integer)
  x = x + 1
End Sub

使用方式都一樣，只不過使用傳值呼叫的參數就不會影響到原來的變數：

執行結果

傳值呼叫就好像把原來的 val 複製一份，再放進 mySub3 中的 x，所以這時候不管 x 怎麼改變，val 都不會有影響。

如果不指定參數使用何種傳遞方式，VBA 預設會使用傳參考的方式傳涮，而如果想要讓程式碼更清楚，可以使用傳參考呼叫的標準寫法：

' 傳參考呼叫的子程序
Sub mySub3(ByRef x As Integer)
  x = x + 1
End Sub

接下來要介紹 VBA 函數（Function）的用法，請繼續閱讀下一頁。

函數（Function）

VBA 的函數（Function）跟子程序（Sub）類似，比較不一樣的地方是函數在執行完之後會有一個傳回值，而子程序則沒有，以下是一個簡單的函數範例：

' 自行定義的函數
Function myFun(x As Integer, y As Integer) As Integer
  myFun = x + y
End Function

函數的定義方式與子程序類似，其以 Function 開頭，接著是函數名稱與傳入參數，而最後還要加上一個函數傳回值的型態，以這個例子來說就是最後一個 As Integer。最後一行 End Function 就是函數的結尾。

在函數的程式碼內容上，比較要注意的就是傳回值的寫法，VBA 的函數中會有一個與函數同名稱的變數，以這個例子來說就是 myFun，函數執行完成後，就會把這個變數的內容當成函數的傳回值，傳回呼叫此函數的位置。

這裡定義的 myFun 函數會接受 x 與 y 兩個整數，傳回兩個整數加起來之後的總和，以下是呼叫這個函數的範例程式碼：

Sub Hello()
  Dim a As Integer
  a = myFun(3, 4)
  MsgBox a
End Sub

執行結果

Excel 使用 VBA 函數

VBA 的函數定義好之後，除了可以在一般的 VBA 程式碼中呼叫之外，也可以直接在 Excel 中使用，其使用方式就跟一般的 Excel 函數一樣，在儲存格中輸入等於，再加上自訂的函數名稱以及輸入的資料：

Excel 使用自訂函數

正確輸入函數名稱與傳入的資料之後，Excel 就會將計算結果顯示在該儲存格之中，這裡我是將 A1 與 B1 儲存格的值傳入 myFun 函數，計算它們的總和。

Excel 使用自訂函數

當然如果只是單純讓兩個數值相加的話，直接用普通的 Excel 公式就好了，這個範例是要示範如何將 Excel 的資料傳入 VBA 函數中進行運算，在將結果傳回來，熟悉這個流程之後，我們就可以設計各式各樣的 VBA 函數來自動處理 Excel 表格中的資料了。

預設參數值

函數的預設參數值用法與子程序相同，同樣都是使用 Optional，以下是一個簡單的範例：

' 自行定義的函數
Function myFun2(Optional x As Integer = 3, Optional y As Integer = 4) As Integer
  myFun = x + y
End Function

以下是呼叫此函數的範例：

' 呼叫自行定義的函數
Sub Hello()
  Dim a As Integer

  ' 計算 3 + 4
  a = myFun2()
  MsgBox a

  ' 計算 20 + 4
  a = myFun2(20)
  MsgBox a

  ' 計算 20 + 40
  a = myFun2(20, 40)
  MsgBox a
End Sub

傳值與傳參考呼叫

VBA 函數的傳值與傳參考呼叫原則與子程序相同，以下是函數的傳值與傳參考呼叫範例：

' 傳參考函數
Function myFun3(ByRef x As Integer) As Integer
  x = x + 1
  myFun3 = x
End Function

' 傳值函數
Function myFun4(ByVal x As Integer) As Integer
  x = x + 1
  myFun4 = x
End Function

以下是呼叫 myFun3 與 myFun4 兩個函數的範例：

Sub Hello()
  Dim a As Integer, b As Integer

  a = 5
  b = myFun3(a)
  MsgBox a

  a = 5
  b = myFun4(a)
  MsgBox a

End Sub

在預設的情況下，VBA 函數都會以傳參考的方式傳遞參數。

應用範例

偶數加總

這是一個可以將 Excel 特定範圍內的偶數篩選出來，並計算總和的函數：

Function SumEvenNumbers(r As Range) As Integer
  Dim c As Range
  SumEvenNumbers =
  ' 將範圍內的每一個儲存格資料取出
  For Each c In r
    ' 檢查是否為偶數
    If c.Value Mod 2 =  Then
      ' 將偶數加總起來
      SumEvenNumbers = SumEvenNumbers + c.Value
    End If
  Next c
End Function

定義好之後，就可以在 Excel 中使用：

Excel 加總特定範圍內的偶數

這樣就可以計算範圍內的所有偶數總和。

Excel 加總特定範圍內的偶數

參考資料