利用Excel和VBA實現單位設備的信息化管理

摘要：本文通過簡述利用Excel管理設備并通過VBA自動實現單位設備的信息化管理。

關鍵詞：Excel VBA 設備的信息化管理

1 情況概述

本人所在單位是一所高校，在過去設備長期使用手工管理的方式，近年隨著設備數量增加雖然部分使用Excel，但是因為管理中經手人眾多，Excel的使用水平參差不齊再加上沒有標準的規范。導致了目前整個設備管理低效、混亂。鑒于這種情況，學校決定全面采用信息化管理。

2 解決方案

按照現狀我們決定，采用Excel管理設備并通過VBA自動實現。首階段目標：全面方便的了解設備情況，對各類設備的數量進行統計。

設計思路：在Excel中建立“設備詳細信息”表、“校園樓宇分布示意圖”表和“樓層房間分布示意圖”表。這3個表在后文中我們分別稱為信息表、校園圖、樓層圖。在校園圖和樓層圖中表現出各個樓以及樓內部結構，并統計出每個房間的設備數量。需要了解房間設備具體情況時，通過某個操作（比如雙擊對應目標）顯示出該房間設備的詳細信息。

3 方案的具體實施

3.1 建立“信息表” 該表內容為設備各種屬性，越全面越好。有了這些詳細的數據，用數據透視表功能在幾十秒內就能按照要求統計結果。需要注意的是，要給每臺設備建立一條記錄，如1個房間有多臺同樣設備就要有多條記錄，但是設備編號、使用人信息不同。

3.2 建立“校園圖”和各樓宇的“樓層圖” 用繪制自定義圖形畫出每一棟樓的外形，然后按照它的地理位置將它分布到合適位置。這樣就畫出了整個校園的樓宇分布圖。

給單元格寫入門牌號模擬1個房間，按照房間分布情況1層層排列單元格，就畫出了每棟樓的“樓層圖”。我們用1張工作表建立1棟樓的示意圖，并且將表名稱改為對應樓名稱。我們在每個門牌號下方留出1個空單元格，調整到合適大小（能夠寫的下包含的各類設備數量）用來填寫統計的設備數量。

3.3 校園圖?陴樓層圖 在“校園圖”中，選中1個樓宇?陴右鍵單擊?陴超鏈接?陴本文檔中對應“樓層圖”所在的工作表。這樣將來直接單擊它就會打開對應樓的“樓層圖”。

3.4 統計每個房間的設備情況 通過門牌號要在“信息表”中統計出該房間各類設備的數量，并填到下方空單元格內。

①建立1個“統計數量”表。②表格第1行建立表頭，內容為“門牌號、設備類型（計算機、打印機、筆記本等）。③表格第A列第2 行開始填入所有房間的門牌號。④以門牌號在“信息表”中查詢出該房間各類設備數量填入對應類別下。具體可用“=sumproduct((信息!$F$2:$F$3000=統計數量!$A17)*(信息!$G$2:$G$3000=統計數量!B$1))”公式實現。公式說明：該公式用sumproduct函數在“信息表”F列（設備信息中F列為地址）中查詢和“統計數量表”$A17（為具體門牌號地址，如：4-206）內容相同的記錄，同時又在G列（設備信息中G列為設備類型）中查詢和B$1（“統計數量”表頭中的設備類型）相同的記錄。當2個條件都滿足是記一次數。最終統計出全部3000記錄中符合條件的設備總數。用同樣方法得到其他類別設備總數。⑤用字符串運算符“”將統計總數變成文字。字符運算符，可以直接將前后字符鏈接起來。例如：“=IF(B3=0，\"\"，B3\"臺\"B$1\";\")”。公式說明：B3是設備數量（如為1），B1是設備類別（如為計算機），結果是“1臺計算機；”。IF用來判斷房間沒有該類型設備時的情況。用同樣方法將別的類型變成文字描述。再用“”將它們都連起來，就形成了該房間設備的完整文字描述。如：“1臺計算機；2臺打印機；3臺筆記本”。⑥將上面的結果直接引用到“樓層圖”中門牌號下方空單元格內顯示。

3.5 雙擊門牌號單元格時，高亮顯示該房間設備詳細記錄 后面的功能我們用VBA代碼編寫的宏實現。

This WorkBook中的代碼：

“雙擊鼠標左鍵”事件宏

這個宏用來在工作表中發生“雙擊鼠標左鍵”事件時，調用“高亮顯示對應記錄”宏

Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object， ByVal Target As Range， Cancel As Boolean)

高亮顯示對應記錄

End Sub

模塊中的代碼：

“高亮顯示對應記錄”宏

這個宏用來在雙擊一個門牌號單元過后，將地址相對應的記錄設置為高亮。過程如下：①將被雙擊的 “門牌號”賦值給1個變量。②選取信息表?陴清除整個背景色?陴字體設為默認色。③用For Next建立1個循環，循環次數為表中據記錄條數。④在循環中將每一記錄對應地址單元格內容和賦值變量比較，當相同時把這一行設置為高亮，進行下一次循環。否則直接進行下一次循環。⑤將“門牌號”輸出到屏幕。

Sub 高亮顯示對應記錄()

a = Selection.Value

Sheets(\"設備列表\").Select

Cells.Select

Selection.Interior.ColorIndex = xlNone

Selection.Font.ColorIndex = xlAutomatic

For i = 2 To 3000

b = Range(\"F\" i)

If b = a Then

Rows(i).Select

Selection.Interior.ColorIndex = 6

Selection.Font.ColorIndex = 1

End If

Next i

MsgBox a

End Sub

到此為止設計要求已經實現。但為使用戶獲得更好的視覺效果，同時也防止用戶通過Excel系統功能產生誤操作。下面用VBA對Excel的界面進行設置，以保證打開文檔時屏蔽部分Excel系統功能，但關閉文檔時要讓界面恢復，防止電腦上再次打開Excel沒有系統功能。

3.6 界面的設置This WorkBook中的代碼

“打開文檔時顯示指定工作表并隱藏部分Excel系統功能”宏

這個宏在Excel文檔打開是執行，顯示“校園圖”工作表，并運行“HideMenu”宏（該宏用來隱藏部分Excel系統功能）

Private Sub Workbook_Open()

Sheets(\"校園圖\").Select

HideMenu

End Sub

“關閉Excel時恢復Excel系統功能”宏

這個宏在通過Excel系統功能關閉時執行，運行“ShowMenu”宏（該宏用來恢復Excel系統功能）

Private Sub Workbook_BeforeClose(Cancel As Boolean)

With Application

ShowMenu

End Sub

模塊中的代碼：

“隱藏部分Excel系統功能”宏

Sub HideMenu()

With Application

.DisplayFullScreen = True

.CommandBars(\"Full Screen\").Visible = False

.DisplayStatusBar = False

.DisplayFormulaBar = False

.CommandBars(\"Formatting\").Visible = False

.CommandBars(\"Standard\").Visible = False

.CommandBars(\"Worksheet Menu Bar\").Enabled = False

End With

With ActiveWindow

.DisplayHorizontalScrollBar = False

.DisplayVerticalScrollBar = False

.DisplayWorkbookTabs = False

.DisplayHeadings = False

.DisplayGridlines = False

End With

End Sub

“恢復Excel系統功能”宏

Sub ShowMenu()

With Application

.DisplayFullScreen = False

.DisplayStatusBar = True

.DisplayFormulaBar = True

.CommandBars(\"Formatting\").Visible = True

.CommandBars(\"Standard\").Visible = True

.CommandBars(\"Worksheet Menu Bar\").Enabled = True

End With

With ActiveWindow

.DisplayHorizontalScrollBar = True

.DisplayVerticalScrollBar = True

.DisplayWorkbookTabs = True

.DisplayHeadings = True

.DisplayGridlines = True

End With

End Sub

通過上面的代碼文檔打開時就會將Excel部分系統功能隱藏，所以用戶將無法退出系統和在工作表中切換。這個問題可以使用按鈕實現，為了界面美觀，我們沒用“控件”。而用了自定義圖形?指定宏來實現，因為自定義圖形可以設置背景和邊框。

3.7 制作按鈕 “返回樓層圖”宏

按鈕放在信息表中，當用戶雙擊門牌號高亮顯示對應記錄后，用來返回。

Sub 返回樓層圖()

Sheets(\"樓層圖\").Select

End Sub

“退出系統”宏

這個按鈕放在校園圖中用來退出系統。下面代碼中首先恢復系統功能，然后設置對所有改的都默認不保存，最后直接退出Excel。

Sub 退出系統()

With Application.ShowMenu

Application.DisplayAlerts = False

Application.Quit

End Sub

參考文獻：

[1]（美）沃肯巴赫.中文版Excel 2010高級VBA編程寶典[M].清華大學出版社.2012-1-1.

[2]楊章偉，張婉婉.Excel VBA語法辭典.[M]機械工業出版社.2010-1-1.

[3]為你的Excel表設置密碼.網絡與信息.河馬[J].2009年第8期.