小微企業中EXCEL VBA的開發應用

肖罡

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

關鍵詞：小微企業；EXCEL VBA；開發；應用

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

在此，將筆者使用EXCEL vba開發專屬的應用軟件的經驗分享如下。

1 收集用戶需求

不管使用何種軟件開發系統，首先要收集企業對數據的需求，換句話說就是企業需要什么樣的數據，通過怎樣的處理能得到企業需要的結果。

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

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

2 建立數據模型

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

3 程序設計

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

3.1 數據的錄入維護

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

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

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

3.2 數據的日常處理

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

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

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

在數據處理模塊，數據的累加是經常要用到的，但對于錄入系統的數據格式一定要進行相應的處理，比如：

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

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

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

3.3 數據的查詢、輸出

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

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

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

3.4 數據的修改、刪除

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

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

4 對開發的vba應用系統的保護

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

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