VBA在冶金運(yùn)輸企業(yè)中的應(yīng)用實(shí)例

程炎林

(安徽馬鋼汽車運(yùn)輸服務(wù)有限公司　安徽馬鞍山　243041)

?

VBA在冶金運(yùn)輸企業(yè)中的應(yīng)用實(shí)例

程炎林

(安徽馬鋼汽車運(yùn)輸服務(wù)有限公司安徽馬鞍山243041)

摘要：通過一個Excel VBA程序的應(yīng)用實(shí)例，論述Excel平臺下的VBA編程在實(shí)際工作中發(fā)揮的作用；VBA程序編寫環(huán)境的進(jìn)入和設(shè)置；通過對實(shí)例應(yīng)用程序的注釋，講解基本語法的應(yīng)用，程序結(jié)構(gòu)流程。此實(shí)例為實(shí)際工作中應(yīng)用的程序。

關(guān)鍵詞：Excel VBA程序；Excel工作表；程序循環(huán)；分類統(tǒng)計(jì)

1Excel在日常工作和生活中的應(yīng)用

Excel是我們?nèi)粘Ｉ詈凸ぷ髦袘?yīng)用最廣泛的軟件。從企業(yè)管理的應(yīng)用程序到隨手匯總幾個數(shù)據(jù)；從生活中的收入開支記賬到企業(yè)經(jīng)營數(shù)據(jù)的匯總分析，都離不開Excel的應(yīng)用。如：單位的電話號碼簿、值班表、生產(chǎn)數(shù)據(jù)的統(tǒng)計(jì)報(bào)表，用于傳遞原始數(shù)據(jù)的報(bào)表，我們都會通過excel文件進(jìn)行傳遞。對企業(yè)經(jīng)營情況的走勢分析，成本結(jié)構(gòu)分析，可以結(jié)合柱狀圖，折線圖直觀的表現(xiàn)出來。科學(xué)實(shí)驗(yàn)中的數(shù)據(jù)回歸統(tǒng)計(jì)分析，可以在excel中進(jìn)行數(shù)據(jù)處理。

2Excel應(yīng)用的局限性

我們通常應(yīng)用excel只是應(yīng)用了它的統(tǒng)計(jì)匯總，固有函數(shù)的調(diào)用，以及表格布局的外觀樣式，在編輯數(shù)據(jù)時大部分是直接輸入或者是通過復(fù)制粘貼的方法進(jìn)行的。在數(shù)據(jù)量不大的時候,能滿足工作需求,當(dāng)數(shù)據(jù)量大的時候,或者需要根據(jù)部分原始數(shù)據(jù),計(jì)算出新的數(shù)據(jù)時,這種直接的編輯方法就不能滿足我們需求。

3什么是VBA ，它能干什么？

Visual Basc for Application(VBA)是一種Visual Basic 的一種宏語言，主要是用來擴(kuò)展Windows的應(yīng)用程序功能。它是寄生于父應(yīng)用程序的VB語言，它的應(yīng)用不限于微軟Office的Excel和Word等幾個辦公程序，在許多設(shè)計(jì)軟件中也有廣泛應(yīng)用，如：CAD、CORELDRAW等等。

VBA的應(yīng)用必須依賴于其父程序，如在Excel上開發(fā)的VBA程序就是一個包含VB語言程序代碼的Excel程序。必須用Excel主程序才能打開應(yīng)用。

在Excel中可以使用VBA對多步驟且重復(fù)操作的工作通過編寫代碼程序進(jìn)行替代；可以實(shí)現(xiàn)一些手工操作無法實(shí)現(xiàn)的功能；可以規(guī)范用戶的操作，控制用戶的操作行為；可以實(shí)現(xiàn)操作界面的人性化，方便用戶的操作。

4應(yīng)用背景

安徽馬鋼汽車運(yùn)輸服務(wù)有限公司下轄六個分公司，主要從事馬鋼內(nèi)部的原材料、中間產(chǎn)品、生產(chǎn)輔料、產(chǎn)成品的公路運(yùn)輸任務(wù)。

具體的服務(wù)對象是馬鋼幾十個二級生產(chǎn)單位。每個分公司同時服務(wù)幾個二級生產(chǎn)單位，同一個二級生產(chǎn)單位又有幾個分公司為其服務(wù)。在運(yùn)輸量統(tǒng)計(jì)時各分公司上報(bào)其承運(yùn)的工作量和費(fèi)用，總公司需要按照二級生產(chǎn)單位進(jìn)行分類并匯總，進(jìn)行費(fèi)用結(jié)算。

一直以來，總公司統(tǒng)計(jì)人員，將各分公司上報(bào)的統(tǒng)計(jì)表打開，先建立各二級生產(chǎn)單位的空白表，將分公司報(bào)表中的項(xiàng)復(fù)制到對應(yīng)的二級生產(chǎn)單位的表中。這樣一條條的處理，一個分公司的處理完成再處理下一個分公司的表報(bào)。如此往復(fù)，生成按照二級生產(chǎn)單位分類的報(bào)表。傳遞給各用戶單位進(jìn)行結(jié)賬。

5實(shí)例程序思路

用VBA程序代碼，按照人工操作的思路，進(jìn)行程序編制。首先在表格中對要處理的分公司的名稱和月份進(jìn)行列表，以便程序讀??；將各分公司的表報(bào)統(tǒng)一命名格式，單位名+月份.xls。用統(tǒng)一內(nèi)部表格結(jié)構(gòu)，以便代碼操作。各分公司的報(bào)表存放在同一個目錄中。

表1　各分公司報(bào)表結(jié)構(gòu)

利用兩個大的循環(huán)，內(nèi)部循環(huán)對分公司的每條記錄進(jìn)行讀取，分別加到各自的二級廠礦的對應(yīng)表中；外部循環(huán)讀取下一個分公司的名稱和月份，再進(jìn)行一個內(nèi)部循環(huán)。如此往復(fù)，將所有分公司表報(bào)進(jìn)行處理，完成分類工作。

圖1　總表界面

6簡單介紹環(huán)境設(shè)置

在Excel2007中打開的表中，選擇開發(fā)工具菜單，點(diǎn)擊設(shè)計(jì)模式，使其處于選中狀態(tài)，選擇插入菜單，在ActiveX控件組中，選擇命令按鈕，在表格頁面中繪制適當(dāng)大小按鈕；在設(shè)計(jì)模式選中的狀態(tài)下，雙擊剛才繪制的命令按鈕，系統(tǒng)自動進(jìn)入代碼編輯窗口。(在Excel程序中按Alt-F11進(jìn)入VBE工作環(huán)境，VBA編輯代碼和控件的視口)

7程序代碼(單引號后為注釋)

Private Sub CommandButton1_Click()

Dim wkb As Workbook, m As String, dh% ’各定義一個表格變量，字符串變量，整型變量。

dh = 2

While Workbooks("總表.xls").Sheets("sad").Cells(dh, 9) <> "" ’最外層循環(huán)開始：在總表的“sad”工作表的第dh=2行，9列提取要拆分處理的分公司的報(bào)表。

ai = Cells(dh, 9) & Cells(dh, 10) & "月.xls"’組成報(bào)表名稱字符串.

Set wkb = Workbooks.Open(ThisWorkbook.Path & "" & ai) ’打開分公司的報(bào)表。

cy = Workbooks(ai).Sheets(1).Cells(2, 2)

yf = Workbooks(ai).Sheets(1).Cells(2, 4) ’讀取承運(yùn)單位名稱和運(yùn)費(fèi)發(fā)生時間。

y = 4

While Workbooks(ai).Sheets(1).Cells(y, 8) <> "" ’第二級循環(huán)開始：處理分公司數(shù)據(jù),從y=4行開始讀取第一托運(yùn)單位費(fèi)用數(shù)據(jù).

xh = y - 3

tydw = Workbooks(ai).Sheets(1).Cells(y, 2)

ysxm = Workbooks(ai).Sheets(1).Cells(y, 3)

ysxl = Workbooks(ai).Sheets(1).Cells(y, 4)

jsfs = Workbooks(ai).Sheets(1).Cells(y, 5)

jsjg = Workbooks(ai).Sheets(1).Cells(y, 6)

yszl = Workbooks(ai).Sheets(1).Cells(y, 7)

sjyf = Workbooks(ai).Sheets(1).Cells(y, 8) ’將數(shù)據(jù)分別存入變量。

Dim dw As Worksheet ’定義一個工作表變量

For Each dw In Workbooks("總表.xls").Sheets ’在總表工作簿中遍歷工作表

If dw.Name = tydw Then’如果有工作表的名稱和托運(yùn)單位名稱相同，則在表中加入數(shù)據(jù)。

x = 4

While dw.Cells(x, 2) <> "" ’用循環(huán)的方法自上而下查找第一個空行。

x = x + 1

Wend

With dw

.Cells(x, 1) = x - 3

.Cells(x, 2) = cy

.Cells(x, 3) = ysxm

.Cells(x, 4) = ysxl

.Cells(x, 5) = jsfs

.Cells(x, 6) = jsjg

.Cells(x, 7) = yszl

.Cells(x, 8) = sjyf

End With’將變量中的數(shù)據(jù)填入到空行中。

GoTo 100’跳出遍歷循環(huán)，轉(zhuǎn)到100：

End If

Next’for each .....next 循環(huán)遍歷結(jié)束。

Workbooks("總表.xls").Worksheets("mo").Copy Workbooks("總表.xls").Worksheets("sad")

’如果遍歷結(jié)束沒有找到和托運(yùn)單位相同的表，則將空白的模板工作表復(fù)制增加一個新表。

Workbooks("總表.xls").Worksheets("mo (2)").Name = tydw

’將新增工作表的name改為托運(yùn)單位名稱。

x = 4

With Workbooks("總表.xls").Worksheets(tydw)

.Cells(1, 4) = yf & tydw & "運(yùn)費(fèi)表"

.Cells(x, 1) = x - 3

.Cells(x, 2) = cy

.Cells(x, 3) = ysxm

.Cells(x, 4) = ysxl

.Cells(x, 5) = jsfs

.Cells(x, 6) = jsjg

.Cells(x, 7) = yszl

.Cells(x, 8) = sjyf

End With’將新增的表填入分表提取的數(shù)據(jù)。

100:

y = y + 1 ’分公司報(bào)表行號加1，讀取下一條數(shù)據(jù)，進(jìn)行第二次循環(huán)，

Wend’第二級循環(huán)結(jié)束：直到分公司的數(shù)據(jù)讀完，y行，8列為空，結(jié)束第一個分公司的數(shù)據(jù)拆分。

Workbooks(ai).Close ’關(guān)閉該分公司的表。

dh = dh + 1’總表工作表(sad)中讀取下一個分公司的名稱和月份。進(jìn)行下一個分公司的報(bào)表數(shù)據(jù)拆分。

Wend’直到單位列表結(jié)束：sheels(sad)的dh行，9列為空，結(jié)束整個工作循環(huán)，退出程序。

End Sub

8結(jié)束語

通過兩年的實(shí)際應(yīng)用，操作簡單，容易上手，程序執(zhí)行效率高，原來需要幾個小時才能完成的工作，在瞬間就能完成；對于經(jīng)常修改數(shù)據(jù)的分公司表報(bào)，可以輕松的進(jìn)行重新計(jì)算。報(bào)表生成準(zhǔn)確可靠，無誤差。

通過VBA在實(shí)際工作中的應(yīng)用，我們可以獲得以下結(jié)論：在Excel獲得普遍應(yīng)用的企業(yè)管理中，存在大量的重復(fù)性工作，這些重復(fù)性工作使我們工作效率低下，可靠性得不到保證，不能滿足現(xiàn)代快節(jié)奏的需求。而VBA使用門檻較低，簡單易學(xué)，上手很快，容易推廣使用?？梢宰鳛樽詣愚k公系統(tǒng)的有效補(bǔ)充，或替代自動辦公系統(tǒng)。

參 考 文 獻(xiàn)

[1]Excel home. Excel VBA 2010 實(shí)戰(zhàn)技巧精粹[M].北京：人民郵電出版社，2015

[2]馬龍高新教育. Office VBA 從新手到高手[M].北京：人民郵電出版社，2015

Application Examples of VBA in the Metallurgical Transportation Enterprises

CHEN Yan-lin

Abstract：Though a practical example of an Excel VBA Procedure, it is discussed what a role the VBA procedure under a Excel platform play in the real work; the access and setting of VBA procedure writing environment; though the annotation of practical application, the use of basic syntax and procedure structure process is explained. This example is the procedure applied in the practical work.

Key words：Excel VBA Procedure；Excel worksheet；procedure circulation；classified statistic.

收稿日期：2016-04-20

作者簡介：程炎林(1963-)，男，安徽馬鋼汽車運(yùn)輸服務(wù)有限公司生產(chǎn)經(jīng)營部，調(diào)度員，工程師。

中圖分類號：TP391.13：U169.9

文獻(xiàn)標(biāo)識碼：B

文章編號：1672-9994(2016)02-0029-03