贊贊小屋

VBA如何自定義健保級距的計算函數

上一節分享利用IF函數,依照健保級距計算應負擔金額,並且介紹如何以VBA達到相同效果。VBA程式在設計上非常靈活自由,但純粹用在計算式上,有個麻煩點在定義儲存格,如果報表的內容和位置不會更新,直接在程式裡寫好固定的作用範圍即可,但如果報表可能會變動,能夠參照儲存格的函數公式,似乎方便許多。對此,完美解決方案是將VBA程式設定為函數,也就是自定義函數,以下具體介紹:

一、健保級距計算公式:「=IF(D2<20100,284,IF(D2<21000,296,IF(D2<21900,309,IF(D2<22800,323,336))))」。有多少級距,架多少層IF判斷式,這是Excel函數公式的標準用法。

有多少級距,架多少層IF判斷式

二、開啟「Visual Basic(Alt+F11)」編輯器。現在常用VBA,於是把這個指令加到快速工具列。具體作法,可參考《會計人的Excel小教室》第一章第一節。

開啟「Visual Basic(Alt+F11)」編輯器

三、在VBE(Visual Basic Editor,VBA編輯器)上方的工具列:「插入」、「模組」。

VBE(Visual Basic Editor,VBA編輯器)

四、和上個步驟比較,可以發現在左邊的專案視窗中(VBAProject),多了一個模組(Module1),打開「Module1」的編輯視窗,輸入如下程式:

Public Function TAX(income) If income < 20100 Then TAX = 284 If income >= 20100 And income < 21000 Then TAX = 296 If income >= 21000 And income < 21900 Then TAX = 309 If income >= 21900 And income < 22800 Then TAX = 323 If income >= 22800 And income < 24000 Then TAX = 336 End Function

「Function」是設定函數的意思,「TAX」是自定義名稱,「income」是函數的第一個參數,如須設定第二個以上的參數,要用「,」隔開。中間便是原本IF函數公式的VBA版,最後以「End Function」結束。

左邊的專案視窗中(VBAProject),多了一個模組(Module1)

五、關掉VBE,回到工作表,在資料編輯列輸入公式:「=TAX(D2)」。神奇的事情發生了,原本一串又臭又長的公式,變得如此乾淨俐落。重點是,計算結果完全一樣。

在資料編輯列輸入公式:「=TAX(D2)」

六、開啟「插入函數」視窗,會發現「或選取類別」多了一個「使用者定義」,下面的「選取函數」有剛新增的「TAX」函數。

會發現「或選取類別」多了一個「使用者定義」

七、按下資料編輯列,跳出「函數引數」,從另外一個角度,欣賞自己一手打造的Excel函數。

按下資料編輯列,跳出「函數引數」

VBA自定義函數有蠻多用途的,除了簡化Excel工作表計算公式,它還同時也能使用在VBE程式中,這是一般程式語言都會有的功能。一方面,在程式代碼越寫越長的情況下,簡化整體代碼、顯得更有架構和層次、更易於閱讀理解,另方面,有些工作表的函數可以直接套用在VBE,例如這篇文章的IF函數,兩邊通用,但也有些常用的工作表函數,例如COUNTIF、SUNIF、VLOOKUP,並不能直接在VBE環境使用,這是因為VBA和Excel本來就是兩個不同的環境,這時候如果有需要,可以在VBA自己寫相同效果的自定義函數,具體用法,以後有機會再分享。

本網站所有內容皆受版權保護,网站备案编号:苏ICP备14051307号-1