小微企業(yè)中EXCEL VBA的開發(fā)應用

肖罡

摘 要：如今，大中型企業(yè)都斥巨資邀請專業(yè)的軟件公司開發(fā)企業(yè)的專用軟件。小微企業(yè)盈利有限，怎樣少花錢也能擁有自己的專屬軟件，EXCEL中的vba開發(fā)可以助一臂之力。

關鍵詞：小微企業(yè)；EXCEL VBA；開發(fā)；應用

無論大小企業(yè)里，辦公用的各種表格數(shù)據(jù)是缺不了的，但大部分企業(yè)僅使用EXCEL中基本的表格公式等進行數(shù)據(jù)的處理，稍高級些的應用也只在諸如數(shù)據(jù)透視表、各種分析餅圖等的使用上。深入使用EXCEL中vba開發(fā)的不是太多。

在此，將筆者使用EXCEL vba開發(fā)專屬的應用軟件的經(jīng)驗分享如下。

1 收集用戶需求

不管使用何種軟件開發(fā)系統(tǒng)，首先要收集企業(yè)對數(shù)據(jù)的需求，換句話說就是企業(yè)需要什么樣的數(shù)據(jù)，通過怎樣的處理能得到企業(yè)需要的結果。

這是一家小型的信貸公司，主要的業(yè)務就是通過替客戶辦理專項分期貸款，然后將客戶的資料遞交銀行，把自己前期墊付給客戶的貸款轉換為客戶從銀行的分期貸款，由于這項貸款時限比較長，一般都是三年，由客戶按月交款到公司，再由公司還上銀行的分期貸款。每個客戶從開始辦理業(yè)務，正常按月向公司繳納月供金額，連續(xù)36個月后，客戶結清貸款，與公司履行合同完畢，該客戶才從客戶資料表中退出。

通過對該公司業(yè)務流程的梳理，公司的業(yè)務不是很復雜，但隨著用戶量的增加，再加之三年36個月的還款期，使數(shù)據(jù)量陡然增大，靠人工進行登記處理顯然已經(jīng)不現(xiàn)實了。

2 建立數(shù)據(jù)模型

經(jīng)過收集用戶的需求分析得出，需建立九個數(shù)據(jù)表、一個用戶表和一個臨時表。分別為客戶資料表、月供表、還款表、銀行余額表、銀行卡貸方明細表、結清客戶表、結清月供表、結清還款表、用戶表、臨時表等。其中客戶資料表、月供表、還款表以客戶身份證號碼為關鍵數(shù)據(jù)相互連接，月供表、還款表、銀行余額表以銀行卡為關鍵數(shù)據(jù)連接，從而形成了數(shù)個主要數(shù)據(jù)表之間的邏輯關系

3 程序設計

項目的框架有了，下面正式進入了程序的設計和用戶交互的界面了。

3.1 數(shù)據(jù)的錄入維護

既然是一個項目，那該有的如菜單、按鈕、顯示界面、背景等等也應該有一點吧，所以選擇了EXCEL vba中的窗體設計，因為追求實用、簡捷，所以沒有什么美術設計之類的參與，很直白的菜單，一看大體都知道是什么。

第一步當然是數(shù)據(jù)的錄入了，數(shù)據(jù)錄入是系統(tǒng)能正常運行的基石。所以對錄入系統(tǒng)的原始數(shù)據(jù)必須有一個合法、合理、合規(guī)的判斷，對合法、合理、合規(guī)的數(shù)據(jù)進入系統(tǒng)保存，非法輸入的數(shù)據(jù)提示用戶，直到數(shù)據(jù)合法，或者退出錄入界面放棄錄入客戶資料中的客戶姓名、身份證號碼、電話號碼等信息必須是文本格式，并且客戶姓名必須是漢字，數(shù)字、字母等符號在姓名欄中的第一位是無法輸入的，身份證號碼必須滿足首位是數(shù)字且必須是十八位（當然還有更嚴格的判斷身份證號碼是否符合身份證號碼的編排規(guī)則，考慮加入這段代碼會增大數(shù)據(jù)的體積和執(zhí)行效率，沒有使用）。當用戶錄入一個合法的身份證信息后，同時在月供表、還款表中同時加入這個信息，作為識別用戶數(shù)據(jù)的關鍵關聯(lián)數(shù)據(jù)。還有一個重要的數(shù)據(jù)就是客戶開始辦理業(yè)務的日期，這個日期的合法性直接影響系統(tǒng)生成報表和判斷該客戶是否有逾期繳納月供的情形的主要依據(jù)。這個數(shù)據(jù)的格式是日期型。貸款本息數(shù)據(jù)、和為了方便生成報表數(shù)據(jù)的客戶累計月供及累計期數(shù)也在客戶資料表中存儲。

銀行卡的初始信息也是要先期維護進銀行余額表中，包括卡號、銀行名稱、開卡金額信息。此表中包括多張銀行卡信息，用以接收客戶交來的月供。

3.2 數(shù)據(jù)的日常處理

基礎表信息有了后，可以開始日常的運營了。運營的流程是這樣的：每月客戶按時交來月供金額，將此金額及收到此金額的卡號、交來日期、客戶的身份證號按照一定規(guī)則存入對應客戶月供表中，同時將此金額按對應卡號累加進銀行余額表中，并將該客戶交來月供金額累加一次計入該客戶的累計月供中。按此規(guī)則逐一處理完當月所有客戶交來的月供款項。如果期間有新增客戶，操作與之前步驟相同，維護進客戶資料表中。如果期間銀行卡有利息收入，也需維護進銀行余額表中。

日常運營中，數(shù)據(jù)處理模塊需要完成前述步驟的功能，采取批量導入客戶的月供數(shù)據(jù)和單個客戶月供數(shù)據(jù)的錄入功能，根據(jù)用戶的需要自由選擇。對于批量導入客戶，需建立設置好數(shù)據(jù)模板，按照客戶姓名、身份證號碼、還款卡號、金額及繳款日期的格式，由用戶做成一張EXCEL表，使用vba中系統(tǒng)的打開文件函數(shù)讀取表格，逐一處理表中的所有數(shù)據(jù)。同樣的，對于表格中的每一行數(shù)據(jù)都要經(jīng)過判斷其合法、合規(guī)、合理，才能進入系統(tǒng)。對于錯誤的客戶身份證、錯誤的卡號、非法的日期格式等等一系列錯誤，在臨時表中對數(shù)據(jù)判斷的結果予以記錄，在處理完表中所有的數(shù)據(jù)后，以提示文件的形式展示給用戶，成功處理了多少筆用戶的月供數(shù)據(jù)，未成功處理的用戶逐一列出錯誤，由用戶修改后再次處理。

對于單個客戶的數(shù)據(jù)錄入，采取下拉框方式，輸入客戶姓名的拼音首字母，迅速篩選目標用戶，便于快速定位客戶，同時也規(guī)避了輸入錯誤的用戶身份證號的可能，銀行卡號也使用下拉框的方式，日期采用日期控件，也同樣規(guī)避了非法的格式或錯誤的數(shù)據(jù)。

在數(shù)據(jù)處理模塊，數(shù)據(jù)的累加是經(jīng)常要用到的，但對于錄入系統(tǒng)的數(shù)據(jù)格式一定要進行相應的處理，比如：

Sheets（1）.Cells（k， 20） = Val（Sheets（1）.Cells（k， 18）） + Val（Sheets（1）.Cells（k， 20））

加入val函數(shù)，無論所在單元格是文本格式還是數(shù)字格式，都可以很好的進行累加，不至于出現(xiàn)系統(tǒng)報錯或者導致計算錯誤。

當一個月的所有客戶的數(shù)據(jù)處理完畢，各個銀行卡的余額和銀行的余額至少是一致的。所以對銀行卡余額的核對也是對錄入系統(tǒng)中數(shù)據(jù)正確與否的大致判斷。之所以說是大致的判斷，還有可能錄入系統(tǒng)的數(shù)據(jù)有串戶的可能，所以核對每個客戶的數(shù)據(jù)是另一項重要的工作。

3.3 數(shù)據(jù)的查詢、輸出

這時對系統(tǒng)各個數(shù)據(jù)的查詢就是非常必要的。為了能快捷、迅速地查詢到目標客戶的數(shù)據(jù)，同樣采用了下拉框合并姓名首字母的方式，選定客戶，并列出該客戶的所有月供明細數(shù)據(jù)，便于核對。

這個是系統(tǒng)的一種查詢方式，還有按身份證號、月供金額、貸款額等等的各種模糊查詢方式。其中上圖中客戶月供列表的方式使用了ListView控件。這個控件在很多安裝了Office套件的電腦上并不是默認就有的。需要在系統(tǒng)中注冊一個MSCOMCTL.OCX文件，這個文件的版本為6.1.98.34。注冊成功后，在工具箱中右鍵添加控件，在彈出的窗口中勾選MicrosoftListview Control 6.0 （SP6）后就出現(xiàn)了ListView控件。對控件進行初始化時，設定表頭的內(nèi)容及寬度，使用循環(huán)語句將數(shù)據(jù)加載到列表中。

查詢模塊中對于用戶需要進一步得到完整全面的信息，可以綜合客戶資料表、月供表、銀行余額表等的信息，可形成一個全部客戶的貸款信息、月供繳納情況、是否逾期等一個輔助的表格。

3.4 數(shù)據(jù)的修改、刪除

在日常處理數(shù)據(jù)中，雖然通過對錄入數(shù)據(jù)進行了各種各樣的規(guī)范和過濾，防止不合規(guī)數(shù)據(jù)進入系統(tǒng)，但難免還會出現(xiàn)手下的錯誤操作或者粗心導致的各種錯誤。增加數(shù)據(jù)的修改、刪除功能是另一道保證數(shù)據(jù)準確的補救措施。

修改刪除包括客戶資料、月供數(shù)據(jù)、銀行卡信息等。但這些功能僅僅是一個發(fā)現(xiàn)錯誤的補救方式，不能濫用，否則難以保證數(shù)據(jù)的嚴肅性、準確性。為此可以設計增加不同的用戶，使錄入數(shù)據(jù)和修改刪除數(shù)據(jù)的人員權限分離。甚至可以對刪除修改操作建表來保存用戶的操作記錄。

4 對開發(fā)的vba應用系統(tǒng)的保護

畢竟EXCEL VBA只是一個簡易的開發(fā)環(huán)境，應用軟件和數(shù)據(jù)都在一個工作簿中保存，使程序的可移植性、通用性及數(shù)據(jù)的安全都大打折扣。另外vba程序是依托EXCEL軟件運行，必須使EXCEL的宏安全級別降低才能啟動。其次，數(shù)據(jù)和軟件都暴露于用戶面前，容易導致誤操作損壞數(shù)據(jù)結構。鑒于此，用Visual Basic將程序入口打包編譯成一個可執(zhí)行程序，通過該執(zhí)行程序啟動工作簿，從而使工作表隱藏于后臺，用戶的操作都在窗體中進行，也不用關心當前EXCEL 中宏的安全級別。

總結，對于小微企業(yè)，使用常見的EXCEL vba來開發(fā)適合自己企業(yè)的專用軟件，既大大提高了對大量數(shù)據(jù)的規(guī)范操作，又可以滿足企業(yè)對處理大量數(shù)據(jù)的需求，還可以提高效率，降低使用人員的門檻，當然開發(fā)使用費用也是很低的。