Excel VBA 程式設計教學:函數(Function)與子程序(Sub)

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


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

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

以下介紹函數(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 輸出結果。

其他類型的參數用法也都類似,例如:DoubleString 等,請依據資料特性自行選擇適合的參數類型。

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

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)的用法,請繼續閱讀下一頁。

Windows, 程式設計

3 Comments

  1. Caspar

    感謝您的用心提供VBA教學,對我很有幫助!

  2. Amity

    感受到您的用心,在您的部落格裏學到不少東西。謝謝!

  3. Sum

    你好, 我想在做一個macro,
    1)在Table-A, 選取C2的文字/數字,
    2)然後在Table-B上搜尋和Table-A,C2相同的字,
    3)然後在Table-B的行數(row)(和C2文字相同的行數)
    ,最後一個沒有資料的欄位(column)的儲存格,
    4)將Table-A, A2的數字, copy寫在Table-B, 最後一個沒有資料的欄位(column)的儲存格.

    煩請大大幫忙以下可以怎樣改, 謝謝.
    以下”Cells.Find(What:=”BB”, _”,這個”BB”是每次都不同的,

    Sub Macro1()
    Application.ScreenUpdating = False

    Range(“C2”).Select
    Selection.Copy
    Windows(“Table A.xlsx”).Activate
    Cells.Find(What:=”BB”, _
    After:=ActiveCell, _
    LookIn:=xlFormulas, _
    LookAt:=xlPart, _
    SearchOrder:=xlByRows, _
    SearchDirection:=xlNext, _
    MatchCase:=False, _
    MatchByte:=False, _
    SearchFormat:=False).Activate

    Application.CutCopyMode = False
    Windows(“Table B.xlsm”).Activate
    Range(“A2”).Select
    Selection.Copy
    Windows(“Table A.xlsx”).Activate
    ‘Range(“D19”).Select
    ‘ActiveSheet.Paste
    ‘Application.CutCopyMode = False
    ‘Windows(“Table B.xlsm”).Activate
    Application.ScreenUpdating = True
    End Sub

Leave a Reply