基于Ｅｘｃｅｌ ＶＢＡ的固定資產(chǎn)折舊計(jì)算模型設(shè)計(jì)

［摘 要］ 本文以固定資產(chǎn)折舊計(jì)算模型設(shè)計(jì)為實(shí)例，探討如何利用Excel內(nèi)嵌的VBA程序設(shè)計(jì)功能解決會(huì)計(jì)核算與管理中的有關(guān)問(wèn)題，讓財(cái)務(wù)人員了解更多處理會(huì)計(jì)數(shù)據(jù)的方法，而不是僅僅局限于財(cái)務(wù)軟件。

［關(guān)鍵詞］ Excel；VBA；固定資產(chǎn)折舊；模型

［中圖分類號(hào)］F232［文獻(xiàn)標(biāo)識(shí)碼］A［文章編號(hào)］1673-0194（2008）16-0030-02

隨著會(huì)計(jì)電算化的不斷普及，我國(guó)企業(yè)解決會(huì)計(jì)核算問(wèn)題大多是利用財(cái)務(wù)軟件進(jìn)行。利用財(cái)務(wù)軟件解決會(huì)計(jì)核算問(wèn)題應(yīng)該說(shuō)既簡(jiǎn)單又方便，但也有其一定的局限性：第一，由于企業(yè)的業(yè)務(wù)種類繁多，有時(shí)會(huì)出現(xiàn)財(cái)務(wù)軟件解決不了的問(wèn)題；第二，由于電算化軟件的功能有限，也使其不能完全滿足企業(yè)管理的需要。隨著計(jì)算機(jī)應(yīng)用的不斷普及與深入以及財(cái)務(wù)人員素質(zhì)的不斷提高，筆者一直在思考，應(yīng)該讓財(cái)務(wù)人員了解更多的處理會(huì)計(jì)數(shù)據(jù)的工具，而不應(yīng)僅僅局限于財(cái)務(wù)軟件。只有這樣，在工作中遇到問(wèn)題時(shí)，思路才會(huì)廣，解決的辦法才會(huì)多，工作效率才會(huì)高。下面以固定資產(chǎn)折舊計(jì)算模型設(shè)計(jì)為實(shí)例來(lái)說(shuō)明如何利用Excel的VBA程序設(shè)計(jì)功能解決會(huì)計(jì)核算與管理中的問(wèn)題。

一、設(shè)計(jì)固定資產(chǎn)折舊計(jì)算的基本模型

為了使設(shè)計(jì)的模型具有通用性，在設(shè)計(jì)基本模型時(shí)要考慮能夠根據(jù)需要隨時(shí)改變基礎(chǔ)數(shù)據(jù)和折舊方法，同時(shí)通過(guò)計(jì)算產(chǎn)生的折舊計(jì)算表的表名要隨折舊方法的改變而自動(dòng)改變。在Excel的sheet 1工作表中建立如圖1所示的基本模型。

圖中的數(shù)據(jù)除“計(jì)算”按鈕和折舊方法右側(cè)的“組合框”控件、第7行的折舊計(jì)算表名稱外，其他數(shù)據(jù)和表格都是通過(guò)鍵盤輸入和定義單元格格式設(shè)置。其中的“計(jì)算”按鈕和組合框通過(guò)鼠標(biāo)右鍵單擊“工具欄”選擇“窗體”，在彈出的“窗體”工具條中選擇相應(yīng)工具按鈕建立。在建好的“組合框”控件上單擊鼠標(biāo)右鍵選擇“設(shè)置控件格式”，在彈出的“設(shè)置控件格式”對(duì)話框中的“數(shù)據(jù)源區(qū)域”框中輸入“$E$3：$E$5”（待選擇的3種折舊方法），在“單元格鏈接”框中輸入“$F$2”，在A7單元格中輸入公式“=IF（F2=1，E3，IF（F2=2，E4，E5））\"折舊計(jì)算表\"”。

二、為“計(jì)算”按鈕指定宏并對(duì)宏進(jìn)行定義

在建好的“計(jì)算”按鈕上單擊鼠標(biāo)右鍵選擇“指定宏”，在彈出的“指定宏”對(duì)話框中的宏名框中輸入“計(jì)算折舊”，再單擊右邊的“新建”按鈕，在彈出的模塊1中輸入以下代碼：

Sub 計(jì)算折舊（）

' hexuewu 記錄的宏 2007-10-21

'清空工作表指定區(qū)域

Sheets（\"sheet1\"）. Select

Rows（\"9：65536\"）. Select

Selection.Delete

' 改變計(jì)算范圍的顏色

rang = \"A9：f \" + Trim（Str（Val（Range（\"C4\"）.Value） + 8））

Range（rang）. Select

With Selection.Interior

.ColorIndex = 4

.Pattern = xlSolid

.PatternColorIndex = xlAutomatic

End With

'將固定資產(chǎn)基本數(shù)據(jù)賦值給變量

cost = Cells（3， 3）'固定資產(chǎn)原值

life = Cells（4， 3）'預(yù)計(jì)使用年限

salvage = Cells（5， 3） '預(yù)計(jì)殘值

rang1 = \"$E$9：E\" + Trim（Str（Val（Range（\"C4\"）.Value） + 6））

Dim a As Integer

'填寫(xiě)計(jì)算表中第1~3列數(shù)據(jù)

For a = 1 To life

Cells（8 + a， 1）.Value = a

Cells（8 + a， 2）.Value = life - a + 1

Cells（8 + a， 3）.Value = cost - salvage

Next

'填寫(xiě)計(jì)算表中第4~6列數(shù)據(jù)

For a = 1 To life

rang2 = \"A\" + Trim（Str（Val（8 + a）））

rang3 = \"B\" + Trim（Str（Val（8 + a)））

rang4 = \"$A$\" + Trim（Str（Val（life + 9）））

'定義折舊率計(jì)算公式

zjl = \"=IF（$F$2=1，1/$c$4，if（$F$2=2，\" + rang3 + \"/sum（$a$9：\" + rang4 + \"），IF（$c$4-\" + rang2 + \">=2，2/$C$4，）））\"

Cells（8 + a， 4）.Value = zjl'公式賦值到表中第4列

'定義折舊額計(jì)算公式

zje = \"=IF（$F$2=1，SLN（$C$3，$C$5，$C$4），IF（$F$2=2，SYD（$C$3，$C$5，$C$4，\" + rang2 + \"），\" + \"IF（$c$4-\" + rang2 + \">=2，DDB（$C$3，$C$5，$C$4，\" + rang2 + \"，2），SLN（$C$3-SUM（\" +rang1 + \"），$C$5，2））））\"

Cells（8 + a， 5）.Value = zje'公式賦值到表中第5列

'定義累計(jì)折舊計(jì)算公式

ljzj = \"=IF（\" + rang2 + \"=1，\" + \"E\" + Trim（Str（Val（8 + a））） + \"，F(xiàn)\" + Trim（Str（Val（7 + a））） + \"+\" + \"E\" + Trim（Str（Val（8 + a））） + \"）\"

Cells（8 + a， 6）.Value = ljzj'公式賦值到表中第6列

' 給計(jì)算范圍畫(huà)邊框線

For b = 1 To 6

With Cells（8 + a， b）

.Borders.LineStyle = xlContinuous

End With

Next

Next

End Sub

本程序的核心技術(shù)是如何定義表中的“年折舊率”、“年折舊額”和“累計(jì)折舊”的計(jì)算公式，因?yàn)檫@些計(jì)算公式要隨折舊年份、預(yù)計(jì)使用年限和折舊方法的改變而自動(dòng)改變。因此代碼中定義了循環(huán)結(jié)構(gòu)，調(diào)用了Excel的內(nèi)嵌函數(shù)IF（）以及折舊計(jì)算函數(shù)SLN（）、SYD（）和DDB（），且設(shè)置了較多的中間變量，如：rang1～rang4，同時(shí)還要考慮采用“雙倍余額遞減法”計(jì)算折舊時(shí)，最后兩年應(yīng)改為直線折舊即“平均年限法”。另一個(gè)難點(diǎn)是計(jì)算表格的表格線區(qū)域要隨“預(yù)計(jì)使用年限”的改變而自動(dòng)改變，此處是在循環(huán)結(jié)構(gòu)中通過(guò)再嵌套循環(huán)解決。

三、模型的使用

此設(shè)計(jì)模型具有很強(qiáng)的通用性，并且操作簡(jiǎn)單，當(dāng)用戶需要進(jìn)行折舊計(jì)算時(shí)，只要在基本數(shù)據(jù)區(qū)輸入相關(guān)數(shù)值，然后單擊“計(jì)算”按鈕，模型可以自動(dòng)計(jì)算出表中采用當(dāng)前折舊方法的折舊數(shù)據(jù)，計(jì)算結(jié)果如圖2所示。用戶還可以單擊“組合框”中的下拉列表按鈕選擇其他折舊方法，折舊計(jì)算表會(huì)自動(dòng)計(jì)算出當(dāng)前選定的折舊方法的折舊數(shù)據(jù)，便于用戶進(jìn)行數(shù)據(jù)的對(duì)比，這在進(jìn)行固定資產(chǎn)投資決策分析和固定資產(chǎn)審計(jì)時(shí)十分有用。

主要參考文獻(xiàn)

［1］ 李政 等. VBA應(yīng)用基礎(chǔ)及實(shí)例教程［M］. 北京：國(guó)防工業(yè)出版社，2005.

［2］ 韓良智. Excel在財(cái)務(wù)管理與分析中的應(yīng)用［M］. 北京：中國(guó)水利水電出版社，2004.

［3］ 何學(xué)武. Excel在工資核算與管理中的應(yīng)用［J］. 職業(yè)圈，2007（4）.

［4］ 沈旭輝 等. 中文Visual Basic 6. 0實(shí)用教程［M］. 成都：電子科技大學(xué)出版社，1999.