基于Excel VBA 的CMM 手冊查詢系統(tǒng)設計

■ 崔璐/東方航空技術有限公司

0 引言

對于多機隊運營的航空公司來說，客艙構(gòu)型多樣，涉及客艙類部件的CMM 手冊數(shù)量和種類較多，傳統(tǒng)通過部件號在IPC 中查詢CMM 手冊章節(jié)號的步驟較為煩瑣，對網(wǎng)絡環(huán)境依賴度較高，效率較低。

VBA 是內(nèi)嵌于Office 軟件中的一個開發(fā)模塊，提供程序自主開發(fā)，其語言基 礎 和VB（Visual Basic）相 似。VBA運行環(huán)境依賴性不強，與常用辦公軟件（Excel/Word/PowerPoint/WPS/Access 等）完美兼容，編程語言簡單易用，分享性和移植性很強，對計算機的配置要求不高，幾乎所有測試通過的程序放到別的機器上都可輕易執(zhí)行（不考慮各種本地文件配置）。

本文利用字典查找法，基于Excel VBA 開發(fā)了一套通過機號、客艙件類別和區(qū)域位置信息多關鍵字匹配查詢的客艙CMM 手冊查詢系統(tǒng)，可提升維修人員CMM 手冊的查詢效率。

1 系統(tǒng)設計分析

1.1 系統(tǒng)開發(fā)工具

VBA 編程環(huán)境是面向?qū)ο蟮木幊汰h(huán)境，例如，工作表中的單元格是其中一個對象，對單元格的輸入是一個事件，該事件可以觸發(fā)一段程序塊的運行，程序塊就是設計者需要編寫的劇本。

窗體控件是Excel 的一種用戶界面設計工具，可以很好地實現(xiàn)用戶與Excel 文件的交互。窗體控件可以顯示固定信息來提示用戶，如提示、警示等；也可以與用戶交互，如輸入文本框、下拉列表框選擇等；還可以給控件設置屬性，編寫對窗體反應的VBA 過程和控制事件，調(diào)用執(zhí)行用戶編輯的程序塊，實現(xiàn)所需的功能。

考慮到界面友好性、運行環(huán)境依賴度、系統(tǒng)的可移植性以及開發(fā)周期，本系統(tǒng)基于Microsoft Excel 2016 版VBA開發(fā)環(huán)境（WPS 兼容）實現(xiàn)，開發(fā)了一套通過機號、客艙件類別和區(qū)域位置信息等多關鍵字匹配查詢的客艙CMM 手冊查詢系統(tǒng)，具有操作簡單、運行環(huán)境依賴度低、可移植程度高、開發(fā)周期短、維護方便等優(yōu)點。

1.2 系統(tǒng)設計

系統(tǒng)共有兩大主要功能：查詢功能和數(shù)據(jù)庫維護功能。系統(tǒng)界面有三個：歡迎界面、查詢系統(tǒng)界面和數(shù)據(jù)維護系統(tǒng)界面，如圖1、圖2 所示。

圖1 歡迎界面與查詢系統(tǒng)界面

圖2 數(shù)據(jù)維護系統(tǒng)界面

整個系統(tǒng)由35 個Sheet 組成，其中三個系統(tǒng)界面各為1 個Sheet，數(shù)據(jù)庫有32 個Sheet。數(shù)據(jù)庫分為查詢數(shù)據(jù)庫和資料版本數(shù)據(jù)庫兩大類，查詢數(shù)據(jù)庫有24 個，數(shù)據(jù)結(jié)構(gòu)如表1 所示。

表1 查詢數(shù)據(jù)庫數(shù)據(jù)結(jié)構(gòu)（以某機型座椅為例）

查詢數(shù)據(jù)庫中第5 ～6 列數(shù)據(jù)，使用Excel 內(nèi)置函數(shù)，根據(jù)CMM 手冊號調(diào)用資料版本數(shù)據(jù)庫數(shù)據(jù)。資料版本數(shù)據(jù)庫分機型存放，共有8 個Sheet。

在歡迎界面點擊查詢系統(tǒng)按鈕可進入查詢功能模塊。查詢條件有三個：機號、客艙類別和區(qū)域。三個條件通過Excel 內(nèi)置函數(shù)與數(shù)據(jù)有效性驗證功能實現(xiàn)三級聯(lián)動，支持部分條件默認的模糊查找。在輸入機號信息后，客艙類別列自動關聯(lián)為對應機型的下拉選擇菜單，選擇客艙類別后，區(qū)域列自動關聯(lián)為該機型對應客艙類別的客艙件所屬區(qū)域。根據(jù)不同的查詢條件輸入，查詢結(jié)果有4 種顯示情況，如表2 所示。

表2 查詢結(jié)果顯示分類

點擊查詢按鈕，在結(jié)果區(qū)域顯示查詢結(jié)果；點擊“重置查詢條件”按鈕可清空當前查詢條件；點擊“返回主界面”按鈕返回歡迎界面。

點擊后臺數(shù)據(jù)維護按鈕，正確輸入管理員密碼即可進入系統(tǒng)數(shù)據(jù)庫。點擊顯示數(shù)據(jù)庫，根據(jù)對話框提示選擇所需的數(shù)據(jù)庫。數(shù)據(jù)維護分為兩大類：機隊數(shù)據(jù)維護和資料版本維護，機隊數(shù)據(jù)維護主要是客艙工程師對飛機號及相應CMM 手冊編號進行維護，資料版本維護主要是資料管理人員進行CMM 手冊相關信息的更新、修改及增刪等。系統(tǒng)功能界面流程圖如圖3 所示。

圖3 系統(tǒng)功能界面流程圖

2 功能算法設計

2.1 查詢功能算法設計及其關鍵代碼

典型的數(shù)據(jù)查找算法有順序查找、折半查找、二叉樹排序查找等。這些技術是通過一系列給定值與關鍵碼的比較，查找效率依賴于過程中給定值與關鍵碼的比較次數(shù)。VBA 字典查找法基于哈希（Hash）查找原理，在存儲位置與關鍵碼之間建立一個確定的對應關系，是一種面向查找的存儲結(jié)構(gòu)，查找方式不依賴比較次數(shù)，其簡要算法邏輯如圖4 所示。針對本系統(tǒng)多關鍵字查詢和多查詢結(jié)果顯示的功能需求，相較于典型查找方法，采用字典查找法的效率更高。

圖4 算法邏輯

本系統(tǒng)將存放有機號、客艙類別、區(qū)域、CMM 手冊號、手冊版本號和廠家等信息的查詢數(shù)據(jù)庫Sheet 名稱按照“機型+座椅/廚房/廁所”的規(guī)則定義，如“B737 座椅”“B777 廚房”“A330廁所”等，共24 個查詢子數(shù)據(jù)庫。

每個子數(shù)據(jù)庫可以看作一個字典，子數(shù)據(jù)庫Sheet 中對應單元格的行、列信息為數(shù)據(jù)的存儲位置，查詢條件中的“客艙類別”與數(shù)據(jù)庫Sheet 名稱相對應，且具有唯一標識性，將對應Sheet 內(nèi)已有數(shù)值的單元格數(shù)據(jù)賦值給數(shù)組，完成字典創(chuàng)建。將機號、區(qū)域信息依次作為關鍵碼KEY，完成字典遍歷查找，確定結(jié)果信息的存儲位置。

在查詢系統(tǒng)界面新建文本框按鈕“查詢”，并為其指定宏模塊“數(shù)據(jù)查找”，在VBA 編輯界面新建“Sub 數(shù)據(jù)查找（）”過程模塊，創(chuàng)建代碼完成字典創(chuàng)建、遍歷查找及結(jié)果可視化顯示。將查詢數(shù)據(jù)庫中所有標題通過Split語句用“|”隔開的字符串分離成數(shù)組賦予vTitle，根據(jù)輸入的查詢條件“客艙類別”sPlace 找到對應的查詢子數(shù)據(jù)庫，運用Function Dict 將該客艙類別的Sheet 中所有已用單元格的數(shù)值賦值給數(shù)組vData，并通過語句CreateObject（“Scripting.Dictionary”）完成字典創(chuàng)建。

通過Sheets（“查詢系統(tǒng)”）.[單元格名稱]語句獲取輸入的查詢條件“機號”“區(qū)域”，分別賦值于sName和sArea 變量，運用邏輯判斷語句，完成字典數(shù)組遍歷獲得查詢結(jié)果，將查詢到的存儲位置對應的所有數(shù)據(jù)信息賦予vFill 結(jié)果數(shù)組。

通過With Sheets（“查詢系統(tǒng)”）.[需寫入內(nèi)容的單元格位置信息]= Application.WorksheetFunction.Transpose（vFill）語句對查詢系統(tǒng)所在Sheet 下的單元格寫入數(shù)據(jù)，實現(xiàn)在Excel 窗口下的結(jié)果可視化顯示。

當輸入錯誤或未檢索到結(jié)果時，通過MsgBox 語句實現(xiàn)相關提示：MsgBox“未查詢到信息，請檢查是否輸入有誤”，vbExclamation，“提示”。

2.2 系統(tǒng)界面設計及其關鍵代碼

系統(tǒng)主界面為歡迎界面，在歡迎界面點擊“查詢系統(tǒng)”按鈕可進入查詢功能模塊，點擊后臺數(shù)據(jù)維護按鈕，根據(jù)提示輸入正確的管理員密碼可進入數(shù)據(jù)維護系統(tǒng)界面。兩大功能界面均設計有返回主界面按鈕，可快速回到歡迎界面。在數(shù)據(jù)維護系統(tǒng)界面中設計有顯示數(shù)據(jù)庫和隱藏數(shù)據(jù)庫按鈕，管理員可根據(jù)提示，調(diào)出需要維護的1 個或多個數(shù)據(jù)庫Sheet 進行操作。數(shù)據(jù)維護系統(tǒng)界面設計如圖5 所示。

圖5 數(shù)據(jù)維護系統(tǒng)界面設計

在數(shù)據(jù)維護系統(tǒng)界面新建文本框按鈕“顯示數(shù)據(jù)庫”，并為其指定宏模塊shtvisible，在VBA 編輯界面新建“Sub shtvisible（）”過程模塊，運用語句InputBox（“輸入提示語”）實現(xiàn)引導式提示輸入，根據(jù)用戶的輸入信息，找到所有匹配的Sheet 名稱，運用sht.Visible=xlSheetVisible 語句更改Sheet的顯示屬性為Visible，實現(xiàn)數(shù)據(jù)庫顯示。

在數(shù)據(jù)維護系統(tǒng)界面新建文本框按鈕“返回主界面”，并為其指定宏模塊“返回主界面”，在VBA 編輯界面新建“Sub 返回主界面（）”過程模塊，運用Sheets（“歡迎界面”）.Visible=xlSheetVisible 和sht.Visible=xlSheetVery-Hidden 語句更改各個子Sheet 的顯示屬性，實現(xiàn)返回主界面功能。

2.3 數(shù)據(jù)源保護設計及其關鍵代碼

考慮到數(shù)據(jù)安全性，本系統(tǒng)通過VBA 代碼設置密碼實現(xiàn)數(shù)據(jù)源保護功能。如圖6 所示，在歡迎界面點擊“后臺數(shù)據(jù)維護”按鈕，根據(jù)提示輸入正確的管理員密碼才可進入數(shù)據(jù)維護系統(tǒng)界面。

圖6 數(shù)據(jù)源保護設計

Excel 中Sheet 的顯示屬性分為顯示、隱藏、深度隱藏三種，本系統(tǒng)采用深度隱藏屬性xlSheetVeryHidden，以防止用戶通過右鍵點擊Sheet 改變數(shù)據(jù)庫的顯示屬性，可進一步實現(xiàn)數(shù)據(jù)源保護。

除此之外，系統(tǒng)還通過語句“Sheets（“查詢系統(tǒng)”）.Protect Password：=XXX，DrawingObjects：=True，Contents：=True，Scenarios：=True，AllowFormattingRows：=True”設 計 了界面顯示保護功能。在查詢系統(tǒng)界面，除查詢條件可由用戶輸入選擇外，其他單元格以密碼形式設置了自動單元格保護，以防被修改。

3 總結(jié)

本文利用字典查找法，基于Excel VBA 開發(fā)了多關鍵字匹配查詢的客艙CMM 手冊查詢系統(tǒng)，輸入基本查詢條件后，平均查詢時長不到1s。系統(tǒng)具有操作簡單、運行環(huán)境依賴度低、可移植程度高、維護方便等特點，為維修人員提供了一種有力的輔助手段，大大提升了查詢客艙CMM 手冊的效率，為提高公司飛機客艙設備的維護水平提供助力。

雖然VBA 在應用過程中存在局限性，但與眾多程序設計語言相比仍有長處。本文在數(shù)據(jù)查詢處理方面提供了一種新思路。為了簡化機械繁重的數(shù)據(jù)處理工作，VBA 平臺提供了開發(fā)高效、精確數(shù)據(jù)處理工具的可能性，可以較好地提升工作效率和便捷性。