怎樣用EXCEL開發(fā)管理信息系統(tǒng)方案

1、 .PAGE36 / NUMPAGES36用EXCEL開發(fā)管理信息系統(tǒng)摘 要：Excel97是Windows95環(huán)境下的一種電子表格軟件,可向用戶提供史無前例的超強功能和易用性，嵌有一種Visual Basic for Application(簡稱VBA)超級宏語言，讀者在熟練應(yīng)用Excel的基礎(chǔ)上，可按用戶的需求用VBA建立適用的信息系統(tǒng)。關(guān)鍵詞：EXCEL97 VBA 管理信息系統(tǒng)（MIS）目前國管理信息系統(tǒng)(MIS)開發(fā)研制一般采用人們熟悉的dBaseX、Foxbase或Foxpro等,本文介紹了如何用Excel開發(fā)MIS。一般來說,一套MIS主要包括信息輸入、信息處理、信息輸出三大組成

2、部分,用Excel處理這些部分均顯得游刃有余。Excel是Windows環(huán)境下的一種電子表格軟件,可向用戶提供史無前例的超強功能和易用性。它同時具有電子數(shù)據(jù)表、圖表和數(shù)據(jù)庫的功能,具有極強的分析性能、報表制作工具和豐富的統(tǒng)計圖表。在本文筆者用中文Excel97 for Windows開發(fā)了水電管理系統(tǒng)、銷售管理系統(tǒng)、試卷分析系統(tǒng)。充分感受到其強大功能和方便性。下面詳細闡述水電管理系統(tǒng)、銷售管理系統(tǒng)、試卷分析系統(tǒng)的開發(fā)過程。應(yīng)用程序一：水電計價系統(tǒng)水電管理是每一個單位每月都要進行的工作。 下面我們以農(nóng)校水電計價系統(tǒng)為例講解VBA在水電管理中的應(yīng)用。1、 水電計價系統(tǒng)的功能本程序具有如下功能：自動

3、計算功能：可完成各戶水費、電費、合計的計算。自動匯總功能：自動匯總?cè)＝搪毠堑挠秒娏?、用水量與分類匯總。查詢功能：可查詢各戶的電表數(shù)、水表數(shù)、電費數(shù)、水費數(shù)。報表的打印輸出功能。系統(tǒng)保護功能：具有口令保護、工作表保護以防非法用戶進入和修改。2、 程序設(shè)計過程（1）程序總體設(shè)計（ HYPERLINK :/ 5tb /files/yyrj/image/excel1.png t _blank 圖1）（2）界面設(shè)計水電計價系統(tǒng)主界面如圖2所示。（ HYPERLINK :/ 5tb /files/yyrj/image/excel6.png t _blank 圖2）主界面是以EXCEL工作表作為輸入輸出

4、界面。具體設(shè)計如下：選定一個工作表并命名為“主界面”。選定單元格區(qū)域，把區(qū)域顏色設(shè)置成你喜歡的顏色。放置命令按鈕，并給按鈕指定宏。分割區(qū)域并凍結(jié)，鎖定界面使之不能滾動。工作表加密保護使用戶不能修改。記錄單界面如（ HYPERLINK :/ 5tb /files/yyrj/image/excel7.png t _blank 圖3）所示。具體設(shè)計過程如下：選定一個工作表并命名為“記錄單”。制表（如上圖所示 ）。選定年、月、水表數(shù)、電表數(shù)空白單元格區(qū)域，取消鎖定。放置命令按鈕，并給按鈕指定宏。工作表保護使用戶不能修改。主報表如（ HYPERLINK :/ 5tb /files/yyrj/image/

5、excel8.png t _blank 圖4、 HYPERLINK :/ 5tb /files/yyrj/image/excel9.png t _blank 圖5）所示。具體設(shè)計過程如下：選定一個工作表并命名為“主報表”。制表（如上圖所示 ）。縱向凍結(jié)A、B兩列，橫向凍結(jié)1-5行。放置命令按鈕，并給按鈕指定宏。輸入公式：計算上次表底、本月查表記錄、水電用量、水、電費、水電費合計的校、校外合計、樓房合計、平房合計。在C338單元格輸入下面公式，拖動填充柄復(fù)制公式至L338。=SUMIF($M$6:$M$331,=l校,C6:C331)+SUMIF($M$6:$M$331,=p校,C6:C331)

6、 在C339單元格輸入下面公式，拖動填充柄復(fù)制公式至L339。=SUMIF($M$6:$M$331,=l校外,C6:C331)+SUMIF($M$6:$M$331,=p校外,C6:C331)在C340單元格輸入下面公式，拖動填充柄復(fù)制公式至L340。=SUMIF($M$6:$M$331,=l校外,C6:C331)+SUMIF($M$6:$M$331,=l校,C6:C331)在C341單元格輸入下面公式，拖動填充柄復(fù)制公式至L341。=SUMIF($M$6:$M$331,=p校外,C6:C331)+SUMIF($M$6:$M$331,=p校,C6:C331)工作表保護使用戶不能修改。查表檔案界面

7、如（下 HYPERLINK :/ 5tb /files/yyrj/image/excel10.png t _blank 圖）所示：具體設(shè)計過程如下：選定一個工作表并命名為“檔案”。制表（如 HYPERLINK :/ 5tb /files/yyrj/image/excel10.png t _blank 圖6所示 ）。縱向凍結(jié)A、B兩列，橫向凍結(jié)1-3行。放置命令按鈕，并給按鈕指定宏。工作表保護使用戶不能修改。查詢檔案如（下 HYPERLINK :/ 5tb /files/yyrj/image/excel11.png t _blank 圖）所示：具體設(shè)計過程如下：選定一個工作表并命名為“查詢”。制

8、表（如 HYPERLINK :/ 5tb /files/yyrj/image/excel11.png t _blank 圖7所示 ）?？v向凍結(jié)A列，橫向凍結(jié)1-3行。放置命令按鈕，并給按鈕指定宏。工作表保護使用戶不能修改。(3)、水電計價系統(tǒng)的VBA代碼與說明Dim s As VariantDim y As VariantDim u As Variant 模塊級變量聲明Sub shuru() ?指定給主界面輸入按鈕s = Worksheets(主界面).Range(v1).Value 獲取密碼For y2 = 1 To 2x = InputBox(請輸入密碼：, 農(nóng)校水電計價系統(tǒng))If x =

9、 s ThenWorksheets(主界面).ActivateActiveSheet.UnprotectActiveSheet.DrawingObjects(按鈕 2).SelectSelection.OnAction = vbbActiveSheet.DrawingObjects(按鈕 3).SelectSelection.OnAction = ibdaActiveSheet.DrawingObjects(按鈕 5).SelectSelection.OnAction = 退出ActiveSheet.DrawingObjects(按鈕 4).SelectSelection.OnAction =

10、 報表查詢ActiveSheet.ProtectWorksheets(記錄單).ActivateExit SubElseIf x = ThenMsgBox 請輸入密碼ElseExit SubEnd IfNext y2End Sub 以上是根據(jù)密碼正確與否選擇執(zhí)行代碼段Sub 返回1() 指定給返回按鈕Worksheets(主界面).ActivateEnd SubSub 存檔() ?指定給記錄單上的存檔按鈕thecode = vbYesNo + vbDefaultButton2 + vbExclamation + vbapplicationmodelMsgBox 請對輸入進行檢查，是否正確？,

11、thecodethereply = MsgBox(prompt:=請對輸入進行檢查，是否正確？, Buttons:=thecode)Select Case thereplyCase vbYesm = Worksheets(記錄單).Range(c5)n = Worksheets(記錄單).Range(d5)If m And n ThenWorksheets(記錄單).Range(c5:d330).SelectSelection.CopyElseIf m And n = ThenWorksheets(記錄單).Range(c5:c330).SelectSelection.CopyElseIf m

12、 = And n ThenWorksheets(記錄單).Range(d5:d330).SelectSelection.CopyElseIf m = And n = ThenExit SubElseEnd IfWorksheets(報表).ActivateActiveSheet.UnprotectIf m And n ThenWorksheets(報表).ActivateWorksheets(報表).Range(e6).SelectSelection.PasteSpecialElseIf m And n = ThenWorksheets(報表).ActivateWorksheets(報表).R

13、ange(e6).SelectSelection.PasteSpecialElseIf m = And n ThenWorksheets(報表).ActivateWorksheets(報表).Range(f6).SelectSelection.PasteSpecialElseEnd IfWorksheets(檔案).Activatey1 = Worksheets(記錄單).Range(c2).Valuex = 0Do Until z = y1x = x + 1z = Worksheets(檔案).Cells(2, x).ValueLoopIf m And n ThenWorksheets(檔案

14、).Cells(4, x).SelectSelection.PasteSpecialElseIf m = And n ThenWorksheets(檔案).Cells(4, x + 1).SelectSelection.PasteSpecialElseIf m And n = ThenWorksheets(檔案).Cells(4, x).SelectSelection.PasteSpecialElseEnd IfWorksheets(主界面).ActivateCase vbNoEnd SelectExit SubEnd Sub 以上代碼是把錄入數(shù)據(jù)存入主報表和查表檔案Sub 返回2() 指定給

15、主報表上的返回主界面按鈕ActiveSheet.UnprotectWorksheets(主界面).ActivateEnd SubSub 取電表底() 指定給主報表上的取電表底按鈕y = InputBox(請輸入電表底（y0,1,2.12）：, 農(nóng)校水電計價系統(tǒng))ActiveSheet.UnprotectIf y = y0 ThenWorksheets(檔案).ActivateRange(c4:c329).SelectSelection.CopyWorksheets(報表).ActivateRange(c6).SelectSelection.PasteSpecialElseIf y = 1 Th

16、enWorksheets(檔案).ActivateRange(e4:e329).SelectSelection.CopyWorksheets(報表).ActivateRange(c6).SelectSelection.PasteSpecialElseIf y = 2 ThenWorksheets(檔案).ActivateRange(g4:g329).SelectSelection.CopyWorksheets(報表).ActivateRange(c6).SelectSelection.PasteSpecialElseIf y = 3 ThenWorksheets(檔案).ActivateRan

17、ge(i4:i329).SelectSelection.CopyWorksheets(報表).ActivateRange(c6).SelectSelection.PasteSpecialElseIf y = 4 ThenWorksheets(檔案).ActivateRange(k4:k329).SelectSelection.CopyWorksheets(報表).ActivateRange(c6).SelectSelection.PasteSpecialElseIf y = 5 ThenWorksheets(檔案).ActivateRange(m4:m329).SelectSelection.

18、CopyWorksheets(報表).ActivateRange(c6).SelectSelection.PasteSpecialElseIf y = 6 ThenWorksheets(檔案).ActivateRange(o4:o329).SelectSelection.CopyWorksheets(報表).ActivateRange(c6).SelectSelection.PasteSpecialElseIf y = 7 ThenWorksheets(檔案).ActivateRange(q4:q329).SelectSelection.CopyWorksheets(報表).ActivateR

19、ange(c6).SelectSelection.PasteSpecialElseIf y = 8 ThenWorksheets(檔案).ActivateRange(s4:s329).SelectSelection.CopyWorksheets(報表).ActivateRange(c6).SelectSelection.PasteSpecialElseIf y = 9 ThenWorksheets(檔案).ActivateRange(u4:u329).SelectSelection.CopyWorksheets(報表).ActivateRange(c6).SelectSelection.Pas

20、teSpecialElseIf y = 10 ThenWorksheets(檔案).ActivateRange(w4:w329).SelectSelection.CopyWorksheets(報表).ActivateRange(c6).SelectSelection.PasteSpecialElseIf y = 11 ThenWorksheets(檔案).ActivateRange(y4:y329).SelectSelection.CopyWorksheets(報表).ActivateRange(c6).SelectSelection.PasteSpecialElseIf y = 12 The

21、nWorksheets(檔案).ActivateRange(aa4:aa329).SelectSelection.CopyWorksheets(報表).ActivateRange(c6).SelectSelection.PasteSpecialElseIf y = ThenActiveSheet.ProtectExit SubElseEnd IfWith Application.Calculation = xlAutomatic.MaxChange = .001End WithActiveWorkbook.PrecisionAsDisplayed = FalseActiveSheet.Prot

22、ectEnd Sub 以上代碼是使用選擇語句從查表檔案中取電表底Sub 取水表底() 指定給主報表上的取電表底按鈕ActiveSheet.Unprotectu = InputBox(請輸入水表底（y0,1,2.12）：, 農(nóng)校水電計價系統(tǒng))If u = y0 ThenWorksheets(檔案).ActivateRange(d4:d329).SelectSelection.CopyWorksheets(報表).ActivateRange(d6).SelectSelection.PasteSpecialElseIf u = 1 ThenWorksheets(檔案).ActivateRange(f

23、4:f329).SelectSelection.CopyWorksheets(報表).ActivateRange(d6).SelectSelection.PasteSpecialElseIf u = 2 ThenWorksheets(檔案).ActivateRange(h4:h329).SelectSelection.CopyWorksheets(報表).ActivateRange(d6).SelectSelection.PasteSpecialElseIf u = 3 ThenWorksheets(檔案).ActivateRange(j4:j329).SelectSelection.Copy

24、Worksheets(報表).ActivateRange(d6).SelectSelection.PasteSpecialElseIf u = 4 ThenWorksheets(檔案).ActivateRange(l4:l329).SelectSelection.CopyWorksheets(報表).ActivateRange(d6).SelectSelection.PasteSpecialElseIf u = 5 ThenWorksheets(檔案).ActivateRange(n4:n329).SelectSelection.CopyWorksheets(報表).ActivateRange

25、(d6).SelectSelection.PasteSpecialElseIf u = 6 ThenWorksheets(檔案).ActivateRange(p4:p329).SelectSelection.CopyWorksheets(報表).ActivateRange(d6).SelectSelection.PasteSpecialElseIf u = 7 ThenWorksheets(檔案).ActivateRange(r4:r329).SelectSelection.CopyWorksheets(報表).ActivateRange(d6).SelectSelection.PasteSp

26、ecialElseIf u = 8 ThenWorksheets(檔案).ActivateRange(t4:t329).SelectSelection.CopyWorksheets(報表).ActivateRange(d6).SelectSelection.PasteSpecialElseIf u = 9 ThenWorksheets(檔案).ActivateRange(v4:v329).SelectSelection.CopyWorksheets(報表).ActivateRange(d6).SelectSelection.PasteSpecialElseIf u = 10 ThenWorks

27、heets(檔案).ActivateRange(x4:x329).SelectSelection.CopyWorksheets(報表).ActivateRange(d6).SelectSelection.PasteSpecialElseIf u = 11 ThenWorksheets(檔案).ActivateRange(z4:z329).SelectSelection.CopyWorksheets(報表).ActivateRange(d6).SelectSelection.PasteSpecialElseIf u = 12 ThenWorksheets(檔案).ActivateRange(ab

28、4:ab329).SelectSelection.CopyWorksheets(報表).ActivateRange(d6).SelectSelection.PasteSpecialElseIf u = ThenActiveSheet.ProtectExit SubElseEnd IfWith Application.Calculation = xlAutomatic.MaxChange = .001End WithActiveWorkbook.PrecisionAsDisplayed = FalseActiveSheet.ProtectEnd Sub 以上代碼是使用選擇語句從查表檔案中取水表底

29、Sub bsy() 由報表存檔過程調(diào)用Worksheets(主界面).Activatex = 3Do While Not (IsEmpty(Worksheets(主界面).Cells(11, x).Value)x = x + 1LoopWorksheets(主界面).Cells(11, x).Value = Worksheets(記錄單).Range(c2)Worksheets(主界面).Cells(11, x + 1).Value = 月Worksheets(主界面).Cells(12, x).Value = uWorksheets(主界面).Cells(13, x).Value = yWo

30、rksheets(主界面).Cells(12, x + 1).Value = 月Worksheets(主界面).Cells(13, x + 1).Value = 月Worksheets(報表).ActivateRange(c3:f5).SelectSelection.CopyRange(c334:f336).SelectSelection.PasteSpecialRange(a1).SelectActiveWindow.ScrollRow = 6ActiveSheet.ProtectEnd Sub 以上代碼是把報表月份和表底月份放入主界面上的表格里Sub 返回3() ?指定給返回主界面按鈕Ac

31、tiveSheet.UnprotectWorksheets(主界面).ActivateEnd SubSub 打印設(shè)置() 指定給主界面上的打印設(shè)置按鈕With ActiveSheet.PageSetup.PrintTitleRows = $1:$5.PrintTitleColumns = End WithActiveSheet.PageSetup.PrintArea = With ActiveSheet.PageSetup.LeftHeader = .CenterHeader = 水電計價系統(tǒng).RightHeader = 第 &P 頁.LeftFooter = .CenterFooter =

32、.RightFooter = .LeftMargin = Application.InchesToPoints(.063).RightMargin = Application.InchesToPoints(.063).TopMargin = Application.InchesToPoints(.3937).BottomMargin = Application.InchesToPoints(.3937).HeaderMargin = Application.InchesToPoints(.2047).FooterMargin = Application.InchesToPoints(.2047

33、).PrintHeadings = False.PrintGridlines = False.PrintNotes = False.PrintQuality = Array(360, 180).CenterHorizontally = True.CenterVertically = False.Orientation = xlPortrait.Draft = False.PaperSize = xlPaperUser.FirstPageNumber = xlAutomatic.Order = xlDownThenOver.BlackAndWhite = False.Zoom = 100End

34、WithEnd Sub 進行打印設(shè)置Sub vbb() 指定給主界面上的主報表按鈕Worksheets(報表).ActivateActiveSheet.ProtectEnd Sub 激活主報表Sub ibda() 指定給主界面上的報表查詢按鈕Worksheets(檔案).ActivateActiveSheet.ProtectEnd Sub 激活檔案工作表Sub 打印() 指定給主報表上的打印按鈕d = InputBox(請選擇打印項（“樓房校1；樓房校外2；平房校3；平房校外4”）：, 打印選項窗口)If d = 1 ThenActiveSheet.UnprotectRange(A5:M331

35、).AdvancedFilter Action:=xlFilterInPlace, CriteriaRange _:=Range(c342:c343), Unique:=FalseActiveWindow.SelectedSheets.PrintOut Copies:=1ElseIf d = 2 ThenActiveSheet.UnprotectRange(A5:M331).AdvancedFilter Action:=xlFilterInPlace, CriteriaRange _:=Range(d342:d343), Unique:=FalseActiveWindow.SelectedSh

36、eets.PrintOut Copies:=1ElseIf d = 3 ThenActiveSheet.UnprotectRange(A5:M331).AdvancedFilter Action:=xlFilterInPlace, CriteriaRange _:=Range(e342:e343), Unique:=FalseActiveWindow.SelectedSheets.PrintOut Copies:=1ElseIf d = 4 ThenActiveSheet.UnprotectRange(A5:M331).AdvancedFilter Action:=xlFilterInPlac

37、e, CriteriaRange _:=Range(f342:f343), Unique:=FalseActiveWindow.SelectedSheets.PrintOut Copies:=1ElseIf d 1 Or d 2 Or d 3 Or d 4 ThenMsgBox 打印項選擇有誤，請重選！BeepExit SubElseEnd IfActiveSheet.ShowAllDataActiveSheet.ProtectEnd Sub 進行選項打印Sub 退出() 指定給主界面上的退出按鈕宏2 系統(tǒng)初始化的逆過程ActiveSheet.UnprotectActiveSheet.Draw

38、ingObjects(按鈕 2).SelectSelection.OnAction = 撤銷按鈕2的指定宏ActiveSheet.DrawingObjects(按鈕 3).SelectSelection.OnAction = 撤銷按鈕3的指定宏ActiveSheet.DrawingObjects(按鈕 5).SelectSelection.OnAction = ActiveSheet.DrawingObjects(按鈕 4).SelectSelection.OnAction = 撤銷按鈕4的指定宏ActiveSheet.Protectthecode = vbYesNo + vbDefaultB

39、utton2 + vbExclamation + vbapplicationmodelthereply = MsgBox(prompt:=是否存盤？（如果不是正式報表請選否!）, Buttons:=thecode)Select Case thereplyCase vbYesdj = Worksheets(報表).Range($g$278).Valueuj = Worksheets(報表).Range($h$278).Valuezj = worksheets(報表).Range($h$332).Valueda = Worksheets(報表).Range($g$277).Valueub = Wo

40、rksheets(報表).Range($h$277).ValueIf dj 0 Or uj 0 Or da 0 Or ub 0 ThenActiveWorkbook.SaveChDrive a:ActiveWorkbook.SaveAs Filename:=SHUIDIAN.XLS, FileFormat:=xlNormal,Password:=,WriteResPassword:=,ReadOnlyRecommended:= False, CreateBackup:=FalseApplication.QuitElseMsgBox 不是正式報表，不予存盤！Application.Display

41、Alerts = FalseApplication.QuitEnd IfCase vbNoApplication.DisplayAlerts = FalseApplication.QuitEnd SelectEnd Sub 以上是執(zhí)行退出存盤Sub 初始化() 指定給主界面上的系統(tǒng)初始化按鈕定義界面 調(diào)用定義界面宏改變應(yīng)用程序標題Worksheets(記錄單).ActivateWorksheets(記錄單).Range(c5:d330).SelectSelection.ClearContentsWorksheets(報表).ActivateActiveSheet.UnprotectWorksh

42、eets(報表).Range(c6:f331).SelectSelection.ClearContentsWorksheets(主界面).Activate 以上代碼是清除記錄單和報表數(shù)據(jù)宏1調(diào)用宏1End SubSub 宏1()With Toolbars(1).Visible = False.Position = xlFloating.Left = 89.Top = 67End WithWith Toolbars(2).Visible = False.Position = xlFloating.Left = 79.Top = 78End WithActiveWindow.DisplayWork

43、bookTabs = FalseEnd Sub 宏2 Macro ykq記錄宏1997/1/10 快捷鍵: Ctrl+eSub 宏2()ActiveWindow.DisplayWorkbookTabs = TrueToolbars(1).Visible = TrueToolbars(2).Visible = FalseWith Application.ShowToolTips = True.LargeButtons = False.ColorButtons = TrueEnd WithToolbars(1).Visible = TrueWith Toolbars(2).Visible = Tr

44、ue.Position = xlTopEnd WithWith Application.ShowToolTips = True.LargeButtons = False.ColorButtons = TrueEnd WithToolbars(1).Position = xlTopWorksheets(主界面).ActivateEnd SubSub 定義界面()Application.Caption = 農(nóng)校水電計價系統(tǒng)ActiveWindow.Caption = End Sub快捷鍵 ctrl+sSub 密碼() 指定給主界面上的密碼設(shè)置按鈕d = InputBox(請輸入舊密碼：, 農(nóng)校水電

45、計價系統(tǒng))If d = Worksheets(主界面).Range(v1).Value ThenFor x = 1 To 2a = InputBox(請輸入自定義密碼：, 農(nóng)校水電計價系統(tǒng))b = InputBox(請重新輸入自定義密碼：, 農(nóng)校水電計價系統(tǒng))If a = b And a Thenc = aWorksheets(主界面).Range(v1).Value = cExit ForElseMsgBox 密碼輸入不正確End IfNext xElse Exit SubEnd IfEnd Sub 以上代碼是設(shè)置密碼Sub 報表查詢() 指定給報表查詢按鈕Worksheets(查詢檔案).

46、ActivateActiveSheet.ProtectEnd Sub 以上代碼是激活查詢檔案Sub 返回4() 指定給返回按鈕ActiveSheet.Unprotect 解除當前工作表保護Worksheets(主界面).ActivateEnd SubSub 報表存檔() 指定給主報表上的報表存檔按鈕dj = Range($g$278).Valueuj = Range($h$278).Valuezj = Range($h$332).Valueda = Range($g$277).Valueub = Range($h$277).ValueIf dj 0 Or uj 0 Or da 0 Or ub

47、0 ThenActiveSheet.UnprotectRange(g5:h331,j5:l331).SelectSelection.CopyWorksheets(查詢檔案).Activatex = 1Do While Not (IsEmpty(Worksheets(查詢檔案).Cells(3, x).Value)x = x + 1LoopActiveSheet.Cells(3, x).SelectSelection.PasteSpecial Paste:=xlValues, Operation:=xlNone, _SkipBlanks:=False, Transpose:=FalseWorks

48、heets(查詢檔案).Cells(2, x).Value = yWorksheets(查詢檔案).Cells(2, x).Value = Worksheets(記錄單).Range($c$2)Worksheets(查詢檔案).Cells(2, x + 1).Value = 月Worksheets(主界面).Activatebsy 調(diào)用bsy宏ElseMsgBox 不是正式 報表，不予存檔！Exit SubEnd IfEnd Sub 以上代碼是把報表數(shù)據(jù)存入查詢檔案 查詢檔案清理 快捷鍵: Ctrl+hSub 宏3() 清理查詢檔案Worksheets(查詢檔案).ActivateRange(

49、B2:AN329).SelectSelection.ClearContentsRange(a1).SelectWorksheets(主界面).ActivateEnd Sub 宏4 Macro ykq記錄宏1997/1/10 檔案庫清理 快捷鍵: Ctrl+jSub 宏4() 清理查表檔案Worksheets(檔案).ActivateRange(C4:AB329).SelectSelection.ClearContentsRange(a1).SelectWorksheets(主界面).ActivateEnd Sub應(yīng)用程序二：銷售管理系統(tǒng)在商業(yè)企業(yè)的經(jīng)營活動中，主要業(yè)務(wù)活動是進銷存。本程序主要解

50、決銷售的問題，關(guān)于進貨和庫存留給讀者自己在原來程序的基礎(chǔ)上進一步開發(fā)。1、 銷售管理系統(tǒng)的功能自動計算進貨金額、銷貨金額和毛利。分類匯總功能，可按商品和經(jīng)手人代碼進行分類匯總。打印功能查詢功能（可按商品和經(jīng)手人代碼進行查詢）。2、 銷售管理系統(tǒng)的程序設(shè)計銷售管理系統(tǒng)總體設(shè)計界面設(shè)計：（ HYPERLINK :/ 5tb /files/yyrj/image/excel2.png t _blank 圖8）主界面如（ HYPERLINK :/ 5tb /files/yyrj/image/excel12.png t _blank 圖9）所示：界面具體設(shè)計過程：選定一工作表，重命名為“主界面”。選定工作

51、表區(qū)域，設(shè)置區(qū)域顏色。在區(qū)域添加應(yīng)用程序標題和命令按鈕。給按鈕指定宏 。對主界面進行口令保護。分割工作表區(qū)域并凍結(jié)，以防屏幕滾動。發(fā)貨單如（ HYPERLINK :/ 5tb /files/yyrj/image/excel13.png t _blank 圖10）所示：發(fā)貨單界面設(shè)計過程如下：按 HYPERLINK :/ 5tb /files/yyrj/image/excel13.png t _blank 圖10格式制表。放置命令按鈕。選定A5,C5,E5,E10,A7:F9解鎖定。在G7,G8,G9單元格里依次輸入公式E7*F7,=E8*F8,=E9*F9,?G10單元格里輸入 =SUM(G7

52、:G9)。保護工作表。毛利核算表如（ HYPERLINK :/ 5tb /files/yyrj/image/excel14.png t _blank 圖11）所示：界面具體設(shè)計過程：選定一工作表重命名為“毛利核算”。按 HYPERLINK :/ 5tb /files/yyrj/image/excel14.png t _blank 圖11所示制表。在毛利核算表上添加命令按鈕并給之指定宏?？v向凍結(jié)A、B兩列，橫向凍結(jié)13行。對工作表進行保護。3、 銷售管理系統(tǒng)的VBA代碼與說明Sub fhd() 指定給主界面上的發(fā)貨單按鈕Dim x, y As String 聲明過程級變量Sheets(fhd).

53、Activatex = Sheets(fhd).Range(a7).Valuey = Sheets(fhd).Range(e10).ValueIf x And y ThenWorksheets(mlhs).Activatez = 4Do While Not IsEmpty(Sheets(mlhs).Cells(z, 1).Value)z = z + 1LoopIf z - 1 50 ThenMsgBox$ 試用版限處理50筆業(yè)務(wù)！EndElsez1 = 7Do While Not IsEmpty(Sheets(fhd).Cells(z1, 1).Value)z1 = z1 + 1LoopFor

54、 z2 = 7 To z1 - 1Cells(z, 1) = Sheets(fhd).Cells(10, 5)Cells(z, 2) = Sheets(fhd).Cells(5, 1) & - & Sheets(fhd).Cells(5, 3) & - & Sheets(fhd).Cells(5, 5)Cells(z, 3) = Sheets(fhd).Cells(z2, 1)Cells(z, 4) = Sheets(fhd).Cells(z2, 2)Cells(z, 5) = Sheets(fhd).Cells(z2, 3)Cells(z, 6) = Sheets(fhd).Cells(z2

55、, 6)Cells(z, 7) = Sheets(fhd).Cells(z2, 4)Cells(z, 9) = Sheets(fhd).Cells(z2, 5)z = z + 1Next z2End IfElseMsgBox 發(fā)貨單記錄不完整，請檢查！EndEnd IfSheets(fhd).ActivateRange(a7:f9).SelectSelection.ClearContentsRange(a7).SelectEnd Sub 以上代碼把發(fā)貨單數(shù)據(jù)復(fù)制到毛利核算表并進行錯誤自檢。Sub 計算毛利() 指定給毛利核算表上的計算毛利按鈕Sheets(mlhs).UnprotectRang

56、e(h4).SelectActiveCell.FormulaR1C1 = =RC-2*RC-1Selection.AutoFill Destination:=Range(H4:H500), Type:=xlFillDefaultRange(j4).SelectActiveCell.FormulaR1C1 = =RC-4*RC-1Selection.AutoFill Destination:=Range(j4:j500), Type:=xlFillDefaultRange(k4).SelectActiveCell.FormulaR1C1 = =RC-1-RC-3Selection.AutoFil

57、l Destination:=Range(k4:k500), Type:=xlFillDefaultRange(a1).SelectActiveWindow.SmallScroll ToRight:=-4Sheets(mlhs).ProtectEnd Sub 自動輸入公式并自動復(fù)制。Sub dy() 指定給毛利核算表上的打印按鈕Sheets(mlhs).Activatex = 4Do While Not IsEmpty(Sheets(mlhs).Cells(x, 1).Value)x = x + 1LoopRange(Cells(1, 1), Cells(x, 11).SelectSelect

58、ion.PrintOutRange(a1).SelectSheets(vjm).ActivateEnd Sub 以上代碼是先自動檢測報表區(qū)域并選定，然后打印輸出。Sub jrfhd()Sheets(fhd).ActivateRange(a1).SelectEnd SubSub fhvjm()Sheets(vjm).ActivateRange(a1000).SelectEnd SubSub jrmlhs()Sheets(mlhs).ActivateRange(a1).SelectEnd SubSub iax() 指定給主界面和毛利核算表上的查詢按鈕Dim d As Stringd = Input

59、Box(請選擇查詢項（按人查詢1；按商品查詢2）：, 查詢選擇窗)If d = 1 ThenSheets(mlhs).SelectRange(A4).SelectActiveSheet.UnprotectSelection.Sort Key1:=Range(A4), Order1:=xlAscending, Header:= _xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:= _xlTopToBottomSelection.Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array

60、(8 _, 10, 11), Replace:=True, PageBreaks:=False, SummaryBelowData:= _TrueSheets(mlhs).SelectActiveSheet.Outline.ShowLevels RowLevels:=2Range(a1).Select宏5 調(diào)用宏5過程ActiveWindow.ScrollColumn = 3Sheets(mlhs).ProtectElseIf d = 2 ThenSheets(mlhs).SelectSelection.RemoveSubtotalSheets(mlhs).UnprotectSelection