Excel VBA 程式設計教學：函數（Function）與子程序（Sub）

2017/06/27

函數（`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 = 0
  ' 將範圍內的每一個儲存格資料取出
  For Each c In r
    ' 檢查是否為偶數
    If c.Value Mod 2 = 0 Then
      ' 將偶數加總起來
      SumEvenNumbers = SumEvenNumbers + c.Value
    End If
  Next c
End Function