EXCEL VBA 函數(Function)與副程式(Sub)

by •12月 26, 2019•0 Comments

函數種類

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

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

副程式(Sub)

以下是一個最簡單的 Hello World 副程式範例程式碼，當呼叫MySub()時，會執行裡面的程式碼，彈出「Hello, world!」訊息方塊(MsgBox)

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

輸入參數

我們可以定義輸入參數供副程式或函數執行運算

Sub MySub(a As Integer, x As Integer, b As Integer)
    MsgBox ("y=a*x+b=" & (a * x + b))
End Sub

這個副程式有三個輸入參數，以a*x+b計算後，以訊息方塊(MsgBox)顯示。
輸入參數除了整數Integer，還有許多變數型態，詳細請參考MSDN網頁。
常用的變數型態有

  • 整數Integer
  • 浮點數Double
  • 布林值Boolean
  • 字串String
  • 萬用型態Variant(預設)

如果在宣告變數時不指定變數類型，則VBA預設會將變數視為Variant類型，所以這兩種寫法的效果是相同的。
另外，若未宣告直接定義的變數也是視為Variant類型。
Variant類型雖然方便，但效率較差，建議少使用。

Dim x As Variant
Dim x ' 預設為Variant'

為了避免撰寫程式時，忘記定宣告變數，可以在程式碼前加上「Option Explicit On」，詳細說明參考MSDN網頁

Option Explicit ' 強迫變數宣告'
Sub MySub(a As Integer, b As Integer)
    y = a + b
    MsgBox ("y=a+b=" & y)
End Sub

因y沒有宣告，所以上面這段程式，編譯時就會出現錯誤訊息 :

預設參數值

輸入參數可以透過關鍵字「Optional」將某些參數設定為選擇性的，並且加上參數的預設值，當呼叫副程式或函數，若未給予輸入參數數值，則該輸入變數為預設值。

Sub MySub(x As Integer, Optional a As Integer = 1, Optional b As Integer = 3)
    MsgBox ("y=a*x+b=" & (a * x + b))
End Sub
Sub MainSub()
    MySub 2 ' 顯示y=a*x+b=5'
    MySub 2, 0 ' 顯示y=a*x+b=3'
    MySub 2, 1, 5 ' 顯示y=a*x+b=7'
End Sub
※非選擇性的輸入變數一定要放在最前面，否則編譯時會出現錯誤訊息

傳值與傳址呼叫

VB輸入參數預設為傳參考呼叫(Call by reference, ByRef)，即為變數a與x數值相同且記憶體位址相同，因記憶體位址相同，若修改變數a，也會修改變數x；而傳值(ByVal)只是把值傳給變數b，因此修改變數b並不會修改到變數x。

Sub MySub1(ByRef a As Integer) ' 預設為傳參考(ByRef)，故ByRef可忽略不寫'
    a = a + 1
End Sub

Sub MySub2(ByVal b As Integer) ' 傳值(ByVal)'
    b = b + 1
End Sub

Sub MainSub()
    Dim x As Integer
    x = 1

    MySub1 x
    MsgBox "MySub1執行後x=" & x ' 顯示「MySub1執行後x=2」'

    MySub2 x
    MsgBox "MySub2執行後x=" & x ' 顯示「MySub2執行後x=2」'
End Sub

函數(Function)

VBA的函數(Function)與副程式(Sub)類似，但在執行完會回傳值，因此在定義函數時，需定義回傳的型態

Function fnSum(a As Integer, b As Integer) As Integer
    fnSum = a + b
End Function

Sub MySub()
    a = fnSum(2, 3)
    MsgBox (a)
End Sub

執行MySub()，會先呼叫fnSum(2, 3)，並將回傳值給變數a，再以訊息方塊(MsgBox)顯示

0留言

<