利用Excel和VBA實(shí)現(xiàn)單位設(shè)備的信息化管理

摘要：本文通過(guò)簡(jiǎn)述利用Excel管理設(shè)備并通過(guò)VBA自動(dòng)實(shí)現(xiàn)單位設(shè)備的信息化管理。

關(guān)鍵詞：Excel VBA 設(shè)備的信息化管理

1 情況概述

本人所在單位是一所高校，在過(guò)去設(shè)備長(zhǎng)期使用手工管理的方式，近年隨著設(shè)備數(shù)量增加雖然部分使用Excel，但是因?yàn)楣芾碇薪?jīng)手人眾多，Excel的使用水平參差不齊再加上沒(méi)有標(biāo)準(zhǔn)的規(guī)范。導(dǎo)致了目前整個(gè)設(shè)備管理低效、混亂。鑒于這種情況，學(xué)校決定全面采用信息化管理。

2 解決方案

按照現(xiàn)狀我們決定，采用Excel管理設(shè)備并通過(guò)VBA自動(dòng)實(shí)現(xiàn)。首階段目標(biāo)：全面方便的了解設(shè)備情況，對(duì)各類設(shè)備的數(shù)量進(jìn)行統(tǒng)計(jì)。

設(shè)計(jì)思路：在Excel中建立“設(shè)備詳細(xì)信息”表、“校園樓宇分布示意圖”表和“樓層房間分布示意圖”表。這3個(gè)表在后文中我們分別稱為信息表、校園圖、樓層圖。在校園圖和樓層圖中表現(xiàn)出各個(gè)樓以及樓內(nèi)部結(jié)構(gòu)，并統(tǒng)計(jì)出每個(gè)房間的設(shè)備數(shù)量。需要了解房間設(shè)備具體情況時(shí)，通過(guò)某個(gè)操作（比如雙擊對(duì)應(yīng)目標(biāo)）顯示出該房間設(shè)備的詳細(xì)信息。

3 方案的具體實(shí)施

3.1 建立“信息表” 該表內(nèi)容為設(shè)備各種屬性，越全面越好。有了這些詳細(xì)的數(shù)據(jù)，用數(shù)據(jù)透視表功能在幾十秒內(nèi)就能按照要求統(tǒng)計(jì)結(jié)果。需要注意的是，要給每臺(tái)設(shè)備建立一條記錄，如1個(gè)房間有多臺(tái)同樣設(shè)備就要有多條記錄，但是設(shè)備編號(hào)、使用人信息不同。

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

給單元格寫(xiě)入門(mén)牌號(hào)模擬1個(gè)房間，按照房間分布情況1層層排列單元格，就畫(huà)出了每棟樓的“樓層圖”。我們用1張工作表建立1棟樓的示意圖，并且將表名稱改為對(duì)應(yīng)樓名稱。我們?cè)诿總€(gè)門(mén)牌號(hào)下方留出1個(gè)空單元格，調(diào)整到合適大小（能夠?qū)懙南掳母黝愒O(shè)備數(shù)量）用來(lái)填寫(xiě)統(tǒng)計(jì)的設(shè)備數(shù)量。

3.3 校園圖?陴樓層圖 在“校園圖”中，選中1個(gè)樓宇?陴右鍵單擊?陴超鏈接?陴本文檔中對(duì)應(yīng)“樓層圖”所在的工作表。這樣將來(lái)直接單擊它就會(huì)打開(kāi)對(duì)應(yīng)樓的“樓層圖”。

3.4 統(tǒng)計(jì)每個(gè)房間的設(shè)備情況 通過(guò)門(mén)牌號(hào)要在“信息表”中統(tǒng)計(jì)出該房間各類設(shè)備的數(shù)量，并填到下方空單元格內(nèi)。

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

3.5 雙擊門(mén)牌號(hào)單元格時(shí)，高亮顯示該房間設(shè)備詳細(xì)記錄 后面的功能我們用VBA代碼編寫(xiě)的宏實(shí)現(xiàn)。

This WorkBook中的代碼：

“雙擊鼠標(biāo)左鍵”事件宏

這個(gè)宏用來(lái)在工作表中發(fā)生“雙擊鼠標(biāo)左鍵”事件時(shí)，調(diào)用“高亮顯示對(duì)應(yīng)記錄”宏

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

高亮顯示對(duì)應(yīng)記錄

End Sub

模塊中的代碼：

“高亮顯示對(duì)應(yīng)記錄”宏

這個(gè)宏用來(lái)在雙擊一個(gè)門(mén)牌號(hào)單元過(guò)后，將地址相對(duì)應(yīng)的記錄設(shè)置為高亮。過(guò)程如下：①將被雙擊的 “門(mén)牌號(hào)”賦值給1個(gè)變量。②選取信息表?陴清除整個(gè)背景色?陴字體設(shè)為默認(rèn)色。③用For Next建立1個(gè)循環(huán)，循環(huán)次數(shù)為表中據(jù)記錄條數(shù)。④在循環(huán)中將每一記錄對(duì)應(yīng)地址單元格內(nèi)容和賦值變量比較，當(dāng)相同時(shí)把這一行設(shè)置為高亮，進(jìn)行下一次循環(huán)。否則直接進(jìn)行下一次循環(huán)。⑤將“門(mén)牌號(hào)”輸出到屏幕。

Sub 高亮顯示對(duì)應(yīng)記錄()

a = Selection.Value

Sheets(\"設(shè)備列表\").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

到此為止設(shè)計(jì)要求已經(jīng)實(shí)現(xiàn)。但為使用戶獲得更好的視覺(jué)效果，同時(shí)也防止用戶通過(guò)Excel系統(tǒng)功能產(chǎn)生誤操作。下面用VBA對(duì)Excel的界面進(jìn)行設(shè)置，以保證打開(kāi)文檔時(shí)屏蔽部分Excel系統(tǒng)功能，但關(guān)閉文檔時(shí)要讓界面恢復(fù)，防止電腦上再次打開(kāi)Excel沒(méi)有系統(tǒng)功能。

3.6 界面的設(shè)置This WorkBook中的代碼

“打開(kāi)文檔時(shí)顯示指定工作表并隱藏部分Excel系統(tǒng)功能”宏

這個(gè)宏在Excel文檔打開(kāi)是執(zhí)行，顯示“校園圖”工作表，并運(yùn)行“HideMenu”宏（該宏用來(lái)隱藏部分Excel系統(tǒng)功能）

Private Sub Workbook_Open()

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

HideMenu

End Sub

“關(guān)閉Excel時(shí)恢復(fù)Excel系統(tǒng)功能”宏

這個(gè)宏在通過(guò)Excel系統(tǒng)功能關(guān)閉時(shí)執(zhí)行，運(yùn)行“ShowMenu”宏（該宏用來(lái)恢復(fù)Excel系統(tǒng)功能）

Private Sub Workbook_BeforeClose(Cancel As Boolean)

With Application

ShowMenu

End Sub

模塊中的代碼：

“隱藏部分Excel系統(tǒng)功能”宏

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

“恢復(fù)Excel系統(tǒng)功能”宏

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

通過(guò)上面的代碼文檔打開(kāi)時(shí)就會(huì)將Excel部分系統(tǒng)功能隱藏，所以用戶將無(wú)法退出系統(tǒng)和在工作表中切換。這個(gè)問(wèn)題可以使用按鈕實(shí)現(xiàn)，為了界面美觀，我們沒(méi)用“控件”。而用了自定義圖形?指定宏來(lái)實(shí)現(xiàn)，因?yàn)樽远x圖形可以設(shè)置背景和邊框。

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

按鈕放在信息表中，當(dāng)用戶雙擊門(mén)牌號(hào)高亮顯示對(duì)應(yīng)記錄后，用來(lái)返回。

Sub 返回樓層圖()

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

End Sub

“退出系統(tǒng)”宏

這個(gè)按鈕放在校園圖中用來(lái)退出系統(tǒng)。下面代碼中首先恢復(fù)系統(tǒng)功能，然后設(shè)置對(duì)所有改的都默認(rèn)不保存，最后直接退出Excel。

Sub 退出系統(tǒng)()

With Application.ShowMenu

Application.DisplayAlerts = False

Application.Quit

End Sub

參考文獻(xiàn)：

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

[2]楊章偉，張婉婉.Excel VBA語(yǔ)法辭典.[M]機(jī)械工業(yè)出版社.2010-1-1.

[3]為你的Excel表設(shè)置密碼.網(wǎng)絡(luò)與信息.河馬[J].2009年第8期.