在excel VBA中如何呼叫正常呼叫自訂函式？

2020-12-01科學

需求

日常工作中經常用到比Excel內建函式復雜的數據計算，而且使用頻率非常高，有時候可以透過增加過渡數據，利用內建函式而解決，但有時候透過內建函式解決不了。此時可以用自訂函式的方法解決。

解決方案

在工作簿中增加一個模組，在其中用「Function 函式名稱（參數列）…End Function」定義一個函式過程，將Excel內建函式難以實作的功能都設定在函式過程中，在工作表中呼叫該函式即可。

將此工作簿另存為樣版檔「*.xltm」，在新建工作簿時選擇該樣版，即可重用此前設計的自訂函式。

1、個人所得稅的自訂函式套用

個人所得稅計算公式：應交個人所得稅＝計稅薪資×稅率－速算扣除值

稅率和速算扣除值根據計稅薪資而有不同的檔次，如下表所示（範例用，非真實稅率）。

計稅薪資（元）	稅率	速算扣除值（元）
500	0.05	0
2000	0.1	25
5000	0.15	125
20000	0.2	375
40000	0.25	1375
60000	0.3	3375
80000	0.35	6375

制作自訂函式

本例項設計流程：將個人所得稅的計算公式移植到函式過程中，利用「Select Case…Case…End Select」控制語句，判斷各檔次計稅薪資對應的稅率和速算扣除數值，然後根據公式「應交個人所得稅＝計稅薪資×稅率－速算扣除值」計算出個人所得稅，將計算結果再返回自訂函式。

具體的操作過程如下：

1、新建工作簿，選擇【開發工具】→【Visual Basic】命令，進入VBE編輯視窗

2、在工程資源視窗中的任一物件上右擊，彈出快捷選單，選擇【插入】|【模組】命令，插入一個模組「模組1」，如下圖所示。

3、在「模組1」中輸入如下宏程式碼。

'將計稅薪資所在的單元格傳值給函式 Function calTax(ByVal SalaryCell As Range) Dim TaxRatio As Single '定義稅率變量 Dim Taxlng As Long '定義速算扣除變量 Dim Salary As Single '定義計稅薪資變量 '從計稅薪資單元格中取得應稅薪資數值 Salary = SalaryCell.Value '判斷計稅薪資所屬的檔次，每個檔次對應不同的稅率和速算扣除值 Select Case Salary '判斷Salary變量值 Case 0 '變量Salary＝0 TaxRatio = 0 Taxlng = 0 Case Is <= 500 '變量Salary<= 500 TaxRatio = 0.05 Taxlng = 0 Case Is <= 2000 '變量Salary<= 2000 TaxRatio = 0.1 Taxlng = 25 Case Is <= 5000 '變量Salary<= 5000 TaxRatio = 0.15 Taxlng = 125 Case Is <= 20000 '變量Salary<= 20000 TaxRatio = 0.2 Taxlng = 375 Case Is <= 40000 '變量Salary<= 40000 TaxRatio = 0.25 Taxlng = 1375 Case Is <= 60000 '變量Salary<= 60000 TaxRatio = 0.3 Taxlng = 3375 Case Is <= 80000 '變量Salary<= 80000 TaxRatio = 0.35 Taxlng = 6375 End Select '計稅薪資×稅率－速算扣除值＝應交個人所得稅 calTax = Salary * TaxRatio - Taxlng End Function

程式程式碼中重點語法介紹：

（1）函式中參數數值的傳遞有兩種方法：按地址傳遞和按數值傳遞。

按地址傳遞參數（Byref）的方法效率較高，因為在過程內所有按地址傳遞的參數所花費的傳遞時間與空間的總數（4字節）都是一樣的，而不管參數的數據型別。

按值傳遞參數（Byval）會在過程內部消耗掉2～16字節，取決於參數的數據型別。對於大的數據型別按值傳遞會比小的數據型別需要稍長的時間。因此，String和Variant數據型別通常不按值來傳遞參數。

使用按地址傳遞參數的方法，將使過程存取到實際的變量。結果，過程可改變變量的真正值，不聲明傳遞參數的方法時，預設按地址傳遞參數。

使用按值的方法時會將原來的變量復制一份，當做參數傳遞過去，改變過程內部的參數時將不會影響到原來的變量。例如函式：

Function Factorial(ByVal MyVar As Integer) '按值傳遞MyVar變量值 MyVar = MyVar – 1 'MyVar變量減去1 If MyVar = 0 Then '判斷MyVar變量值，如果等於0，給函式返回值1 Factorial = 1 Exit Function '結束函式 End If Factorial = Factorial(MyVar) * (MyVar + 1) '呼叫函式自身迴圈 End Function 「testVar」

再制作一個測試過程，呼叫函式Factorial，變量S經過函式呼叫後，數值發生改變，但Factorial函式並沒有改變原來有數值。

Sub testVar() '測試按值傳遞參數 Dim S As Integer '聲明整數變量 S = 5 '給測試變量賦值 Debug.Print Factorial(S) ' 顯示 120 （5 的因子） Debug.Print S '顯示 5，Factorial函式並沒有改變原來有數值 End Sub

（2）利用Select…Case…End Select分支判斷語句判斷計稅薪資所屬的檔次，每個檔次對應不同的稅率和速算扣除值。

語句「Select Case Salary」首先確定判斷條件是薪資變量值「Salary」，然後用多個類似於「Case Is <= 5000」的語句判斷薪資變量「Salary」值是不是小於等於指定的數值，如果符合條件，則給稅率變量「TaxRatio」和速算扣除變量「Taxlng」賦予不同的數值。

（3）函式的返回值。語句「calTax = Salary * TaxRatio - Taxlng」將個人所得稅的計算結果賦予「calTax」函式本身，根據編程的需要，有時可以用類似於「Function calTax(ByVal SalaryCell As Range) as String 」的形式，直接指定函式「calTax」的型別。

呼叫自訂函式

如下所示，在薪資表的T5單元格中輸入自訂函式「=calTax(S5)」，單元格S5作為參數傳遞給自訂函式「calTax」，即可得到所應扣除的個人所得稅金額。

有兩種辦法將單元格T5的公式復制到T6和同列的其他行單元格：

（1）拖動單元格T5右下角的控制代碼

到同列的其他單元格，釋放滑鼠即可。

（2）復制單元格T5，選中同列其他需要計算個人所得稅的單元格，貼上即可。