用EXCEL开发管理信息系统
摘要：Excel97是Windows95环境下的一种电子表格软件,可向用户提供史无前例的超强功能和易用性，内嵌有一种Visual Basic for Application(简称VBA)超级宏语言，读者在熟练应用Excel的基础上，可按用户的需求用VBA建立适用的信息系统。
关键词：EXCEL97 VBA 管理信息系统（MIS）
目前国内管理信息系统(MIS)开发研制一般采用人们熟悉的dBaseX、Foxbase或Foxpro等,本文介绍了如何用Excel开发MIS。一般来说,一套MIS主要包括信息输入、信息处理、信息输出三大组成部分,用Excel 处理这些部分均显得游刃有余。Excel是Windows环境下的一种电子表格软件,可向用户提供史无前例的超强功能和易用性。它同时具有电子数据表、图表和数据库的功能,具有极强的分析性能、报表制作工具和丰富的统计图表。在本文笔者用中文Excel97 for Windows开发了水电管理系统、销售管理系统、试卷分析系统。充分感受到其强大功能和方便性。下面详细阐述水电管理系统、销售管理系统、试卷分析系统的开发过程。
应用程序一：水电计价系统
水电管理是每一个单位每月都要进行的工作。下面我们以保定农校水电计价系统为例讲解VBA在水电管理中的应用。
1、水电计价系统的功能
本程序具有如下功能：
自动计算功能：可完成各户水费、电费、合计的计算。
自动汇总功能：自动汇总全校教职工楼的用电量、用水量及分类汇总。
查询功能：可查询各户的电表数、水表数、电费数、水费数。
报表的打印输出功能。
系统保护功能：具有口令保护、工作表保护以防非法用户进入和修改。
2、程序设计过程
（1）程序总体设计（图1）
（2）界面设计
水电计价系统主界面如图2所示。（图2）
主界面是以EXCEL工作表作为输入输出界面。
具体设计如下：
选定一个工作表并命名为“主界面”。
选定单元格区域，把区域颜色设置成你喜欢的颜色。放置命令按钮，并给按钮指定宏。
分割区域并冻结，锁定界面使之不能滚动。
工作表加密保护使用户不能修改。
记录单界面如（图3）所示。
具体设计过程如下：
选定一个工作表并命名为“记录单”。
制表（如上图所示）。
选定年、月、水表数、电表数空白单元格区域，取消锁定。放置命令按钮，并给按钮指定宏。
工作表保护使用户不能修改。
主报表如（图4、图5）所示。
具体设计过程如下：
选定一个工作表并命名为“主报表”。
制表（如上图所示）。
纵向冻结A、B两列，横向冻结1-5行。
放置命令按钮，并给按钮指定宏。
输入公式：计算上次表底、本月查表记录、水电用量、水、电费、水电费合计的校内、校外合计、楼房合计、平房合计。
在C338单元格输入下面公式，拖动填充柄复制公式至L338。
=SUMIF($M$6:$M$331,"=l校内",C6:C331)+SUMIF($M$6:$M$331,"=p校内",C6:C331)
在C339单元格输入下面公式，拖动填充柄复制公式至L339。
=SUMIF($M$6:$M$331,"=l校外",C6:C331)+SUMIF($M$6:$M$331,"=p校外",C6:C331)
在C340单元格输入下面公式，拖动填充柄复制公式至L340。
=SUMIF($M$6:$M$331,"=l校外",C6:C331)+SUMIF($M$6:$M$331,"=l校内",C6:C331)
在C341单元格输入下面公式，拖动填充柄复制公式至L341。
=SUMIF($M$6:$M$331,"=p校外",C6:C331)+SUMIF($M$6:$M$331,"=p校内",C6:C331)
工作表保护使用户不能修改。
查表档案界面如（下图）所示：
具体设计过程如下：
选定一个工作表并命名为“档案”。
制表（如图6所示）。
纵向冻结A、B两列，横向冻结1-3行。放置命令按钮，并给按钮指定宏。
工作表保护使用户不能修改。
查询档案如（下图）所示：
具体设计过程如下：
选定一个工作表并命名为“查询”。
制表（如图7所示）。
纵向冻结A列，横向冻结1-3行。
放置命令按钮，并给按钮指定宏。
工作表保护使用户不能修改。
(3)、水电计价系统的VBA代码及说明
Dim s As Variant
Dim y As Variant
Dim u As Variant ‘模块级变量声明
Sub shuru() ?指定给主界面输入按钮
s = Worksheets("主界面").Range("v1").Value ‘获取密码For y2 = 1 To 2
x = InputBox("请输入密码：", "保定农校水电计价系统") If x = s Then
Worksheets("主界面").Activate
ActiveSheet.Unprotect
ActiveSheet.DrawingObjects("按钮2").Select Selection.OnAction = "vbb"
ActiveSheet.DrawingObjects("按钮3").Select Selection.OnAction = "ibda"
'ActiveSheet.DrawingObjects("按钮5").Select
'Selection.OnAction = "退出"
ActiveSheet.DrawingObjects("按钮4").Select
Selection.OnAction = "报表查询"
ActiveSheet.Protect
Worksheets("记录单").Activate
Exit Sub
ElseIf x = "" Then
MsgBox "请输入密码"
Else
Exit Sub
End If
Next y2
End Sub ‘以上是根据密码正确与否选择执行代码段
Sub 返回1() ‘指定给返回按钮
Worksheets("主界面").Activate
End Sub
Sub 存档() ?指定给记录单上的存档按钮
thecode = vbYesNo + vbDefaultButton2 + vbExclamation + vbapplicationmodel
'MsgBox "请对输入进行检查，是否正确？", thecode
thereply = MsgBox(prompt:="请对输入进行检查，是否正确？", Buttons:=thecode) Select Case thereply
Case vbYes
m = Worksheets("记录单").Range("c5")
n = Worksheets("记录单").Range("d5")
If m <> "" And n <> "" Then
Worksheets("记录单").Range("c5:d330").Select
Selection.Copy
ElseIf m <> "" And n = "" Then
Worksheets("记录单").Range("c5:c330").Select
Selection.Copy
ElseIf m = "" And n <> "" Then
Worksheets("记录单").Range("d5:d330").Select
Selection.Copy
ElseIf m = "" And n = "" Then
Exit Sub
Else
End If
Worksheets("报表").Activate
ActiveSheet.Unprotect
If m <> "" And n <> "" Then
'Worksheets("报表").Activate
Worksheets("报表").Range("e6").Select
Selection.PasteSpecial
ElseIf m <> "" And n = "" Then
Worksheets("报表").Range("e6").Select
Selection.PasteSpecial
ElseIf m = "" And n <> "" Then
Worksheets("报表").Activate
Worksheets("报表").Range("f6").Select
Selection.PasteSpecial
Else
End If
Worksheets("档案").Activate
y1 = Worksheets("记录单").Range("c2").Value
x = 0
Do Until z = y1
x = x + 1
z = Worksheets("档案").Cells(2, x).Value
Loop
If m <> "" And n <> "" Then
Worksheets("档案").Cells(4, x).Select
Selection.PasteSpecial
ElseIf m = "" And n <> "" Then
Worksheets("档案").Cells(4, x + 1).Select
Selection.PasteSpecial
ElseIf m <> "" And n = "" Then
Worksheets("档案").Cells(4, x).Select
Selection.PasteSpecial
Else
End If
Worksheets("主界面").Activate
Case vbNo
End Select
Exit Sub
End Sub ‘以上代码是把录入数据存入主报表和查表档案
Sub 返回2() ‘指定给主报表上的返回主界面按钮
ActiveSheet.Unprotect
Worksheets("主界面").Activate
End Sub
Sub 取电表底() ‘指定给主报表上的取电表底按钮
y = InputBox("请输入电表底（y0,1,2...12）：", "保定农校水电计价系统") ActiveSheet.Unprotect
If y = "y0" Then
Worksheets("档案").Activate
Range("c4:c329").Select
Selection.Copy
Range("c6").Select Selection.PasteSpecial ElseIf y = "1" Then Worksheets("档案").Activate Range("e4:e329").Select Selection.Copy Worksheets("报表").Activate Range("c6").Select Selection.PasteSpecial ElseIf y = "2" Then Worksheets("档案").Activate Range("g4:g329").Select Selection.Copy Worksheets("报表").Activate Range("c6").Select Selection.PasteSpecial ElseIf y = "3" Then Worksheets("档案").Activate Range("i4:i329").Select Selection.Copy Worksheets("报表").Activate Range("c6").Select Selection.PasteSpecial ElseIf y = "4" Then Worksheets("档案").Activate Range("k4:k329").Select Selection.Copy Worksheets("报表").Activate Range("c6").Select Selection.PasteSpecial ElseIf y = "5" Then Worksheets("档案").Activate Range("m4:m329").Select Selection.Copy Worksheets("报表").Activate Range("c6").Select Selection.PasteSpecial ElseIf y = "6" Then Worksheets("档案").Activate Range("o4:o329").Select Selection.Copy Worksheets("报表").Activate Range("c6").Select
Selection.PasteSpecial ElseIf y = "7" Then Worksheets("档案").Activate Range("q4:q329").Select Selection.Copy Worksheets("报表").Activate Range("c6").Select Selection.PasteSpecial ElseIf y = "8" Then Worksheets("档案").Activate Range("s4:s329").Select Selection.Copy Worksheets("报表").Activate Range("c6").Select Selection.PasteSpecial ElseIf y = "9" Then Worksheets("档案").Activate Range("u4:u329").Select Selection.Copy Worksheets("报表").Activate Range("c6").Select Selection.PasteSpecial ElseIf y = "10" Then Worksheets("档案").Activate Range("w4:w329").Select Selection.Copy Worksheets("报表").Activate Range("c6").Select Selection.PasteSpecial ElseIf y = "11" Then Worksheets("档案").Activate Range("y4:y329").Select Selection.Copy Worksheets("报表").Activate Range("c6").Select Selection.PasteSpecial ElseIf y = "12" Then Worksheets("档案").Activate Range("aa4:aa329").Select Selection.Copy Worksheets("报表").Activate Range("c6").Select Selection.PasteSpecial ElseIf y = "" Then
ActiveSheet.Protect
Exit Sub
Else
End If
With Application
.Calculation = xlAutomatic
.MaxChange = .001
End With
ActiveWorkbook.PrecisionAsDisplayed = False
ActiveSheet.Protect
End Sub ‘以上代码是使用选择语句从查表档案中取电表底
Sub 取水表底() ‘指定给主报表上的取电表底按钮
ActiveSheet.Unprotect
u = InputBox("请输入水表底（y0,1,2...12）：", "保定农校水电计价系统") If u = "y0" Then
Worksheets("档案").Activate
Range("d4:d329").Select
Selection.Copy
Worksheets("报表").Activate
Range("d6").Select
Selection.PasteSpecial
ElseIf u = "1" Then
Worksheets("档案").Activate
Range("f4:f329").Select
Selection.Copy
Worksheets("报表").Activate
Range("d6").Select
Selection.PasteSpecial
ElseIf u = "2" Then
Worksheets("档案").Activate
Range("h4:h329").Select
Selection.Copy
Worksheets("报表").Activate
Range("d6").Select
Selection.PasteSpecial
ElseIf u = "3" Then
Worksheets("档案").Activate
Range("j4:j329").Select
Selection.Copy
Worksheets("报表").Activate
Range("d6").Select
Selection.PasteSpecial
ElseIf u = "4" Then
Worksheets("档案").Activate
Range("l4:l329").Select Selection.Copy Worksheets("报表").Activate Range("d6").Select Selection.PasteSpecial ElseIf u = "5" Then Worksheets("档案").Activate Range("n4:n329").Select Selection.Copy Worksheets("报表").Activate Range("d6").Select Selection.PasteSpecial ElseIf u = "6" Then Worksheets("档案").Activate Range("p4:p329").Select Selection.Copy Worksheets("报表").Activate Range("d6").Select Selection.PasteSpecial ElseIf u = "7" Then Worksheets("档案").Activate Range("r4:r329").Select Selection.Copy Worksheets("报表").Activate Range("d6").Select Selection.PasteSpecial ElseIf u = "8" Then Worksheets("档案").Activate Range("t4:t329").Select Selection.Copy Worksheets("报表").Activate Range("d6").Select Selection.PasteSpecial ElseIf u = "9" Then Worksheets("档案").Activate Range("v4:v329").Select Selection.Copy Worksheets("报表").Activate Range("d6").Select Selection.PasteSpecial ElseIf u = "10" Then Worksheets("档案").Activate Range("x4:x329").Select Selection.Copy
Worksheets("报表").Activate
Range("d6").Select
Selection.PasteSpecial
ElseIf u = "11" Then
Worksheets("档案").Activate
Range("z4:z329").Select
Selection.Copy
Worksheets("报表").Activate
Range("d6").Select
Selection.PasteSpecial
ElseIf u = "12" Then
Worksheets("档案").Activate
Range("ab4:ab329").Select
Selection.Copy
Worksheets("报表").Activate
Range("d6").Select
Selection.PasteSpecial
ElseIf u = "" Then
ActiveSheet.Protect
Exit Sub
Else
End If
With Application
.Calculation = xlAutomatic
.MaxChange = .001
End With
ActiveWorkbook.PrecisionAsDisplayed = False
ActiveSheet.Protect
End Sub ‘以上代码是使用选择语句从查表档案中取水表底
Sub bsy() ‘由报表存档过程调用
Worksheets("主界面").Activate
x = 3
Do While Not (IsEmpty(Worksheets("主界面").Cells(11, x).Value))
x = x + 1
Loop
Worksheets("主界面").Cells(11, x).Value = Worksheets("记录单").Range("c2") Worksheets("主界面").Cells(11, x + 1).Value = "月"
Worksheets("主界面").Cells(12, x).Value = u
Worksheets("主界面").Cells(13, x).Value = y
Worksheets("主界面").Cells(12, x + 1).Value = "月"
Worksheets("主界面").Cells(13, x + 1).Value = "月"
Worksheets("报表").Activate
Range("c3:f5").Select
Selection.Copy
Range("c334:f336").Select
Selection.PasteSpecial
Range("a1").Select
ActiveWindow.ScrollRow = 6
ActiveSheet.Protect
End Sub ‘以上代码是把报表月份和表底月份放入主界面上的表格里Sub 返回3() ?指定给返回主界面按钮
ActiveSheet.Unprotect
Worksheets("主界面").Activate
End Sub
Sub 打印设置() ‘指定给主界面上的打印设置按钮
With ActiveSheet.PageSetup
.PrintTitleRows = "$1:$5"
.PrintTitleColumns = ""
End With
ActiveSheet.PageSetup.PrintArea = ""
With ActiveSheet.PageSetup
.LeftHeader = ""
.CenterHeader = "水电计价系统"
.RightHeader = "第&P 页"
.LeftFooter = ""
.CenterFooter = ""
.RightFooter = ""
.LeftMargin = Application.InchesToPoints(.15748031496063)
.RightMargin = Application.InchesToPoints(.15748031496063)
.TopMargin = Application.InchesToPoints(.984251968503937)
.BottomMargin = Application.InchesToPoints(.984251968503937)
.HeaderMargin = Application.InchesToPoints(.511811023622047)
.FooterMargin = Application.InchesToPoints(.511811023622047)
.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 = 100
End With
End Sub ‘进行打印设置
Sub vbb() ‘指定给主界面上的主报表按钮
Worksheets("报表").Activate
ActiveSheet.Protect
End Sub ‘激活主报表
Sub ibda() ‘指定给主界面上的报表查询按钮
Worksheets("档案").Activate
ActiveSheet.Protect
End Sub ‘激活档案工作表
Sub 打印() ‘指定给主报表上的打印按钮
d = InputBox("请选择打印项（“楼房校内＝1；楼房校外＝2；平房校内＝3；平房校外＝4”）：", "打印选项窗口")
If d = "1" Then
ActiveSheet.Unprotect
Range("A5:M331").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange _
:=Range("c342:c343"), Unique:=False
ActiveWindow.SelectedSheets.PrintOut Copies:=1
ElseIf d = "2" Then
ActiveSheet.Unprotect
Range("A5:M331").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange _
:=Range("d342:d343"), Unique:=False
ActiveWindow.SelectedSheets.PrintOut Copies:=1
ElseIf d = "3" Then
ActiveSheet.Unprotect
Range("A5:M331").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange _
:=Range("e342:e343"), Unique:=False
ActiveWindow.SelectedSheets.PrintOut Copies:=1
ElseIf d = "4" Then
ActiveSheet.Unprotect
Range("A5:M331").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange _
:=Range("f342:f343"), Unique:=False
ActiveWindow.SelectedSheets.PrintOut Copies:=1
ElseIf d <> "1" Or d <> "2" Or d <> "3" Or d <> "4" Then
MsgBox "打印项选择有误，请重选！"
Beep
Exit Sub
Else
End If
ActiveSheet.ShowAllData
ActiveSheet.Protect
End Sub ‘进行选项打印
Sub 退出() ‘指定给主界面上的退出按钮
宏2 ‘系统初始化的逆过程
ActiveSheet.Unprotect
ActiveSheet.DrawingObjects("按钮2").Select
Selection.OnAction = "" ‘撤销按钮2的指定宏
ActiveSheet.DrawingObjects("按钮3").Select
Selection.OnAction = "" ‘撤销按钮3的指定宏
'ActiveSheet.DrawingObjects("按钮5").Select
'Selection.OnAction = ""
ActiveSheet.DrawingObjects("按钮4").Select
Selection.OnAction = "" ‘撤销按钮4的指定宏
ActiveSheet.Protect
thecode = vbYesNo + vbDefaultButton2 + vbExclamation + vbapplicationmodel
thereply = MsgBox(prompt:="是否存盘？（如果不是正式报表请选否!）", Buttons:=thecode) Select Case thereply
Case vbYes
dj = Worksheets("报表").Range("$g$278").Value
uj = Worksheets("报表").Range("$h$278").Value
'zj = worksheets("报表").Range("$h$332").Value
da = Worksheets("报表").Range("$g$277").Value
ub = Worksheets("报表").Range("$h$277").Value
If dj <> 0 Or uj <> 0 Or da <> 0 Or ub <> 0 Then
ActiveWorkbook.Save
ChDrive "a:\"
ActiveWorkbook.SaveAs Filename:="SHUIDIAN.XLS", FileFormat:=xlNormal,
Password:="",WriteResPassword:="",ReadOnlyRecommended:= False, CreateBackup:=False Application.Quit
Else
MsgBox "不是正式报表，不予存盘！"
Application.DisplayAlerts = False
Application.Quit
End If
Case vbNo
Application.DisplayAlerts = False
Application.Quit
End Select
End Sub ‘ 以上是执行退出存盘
Sub 初始化() ‘指定给主界面上的系统初始化按钮
定义界面‘调用定义界面宏改变应用程序标题
Worksheets("记录单").Activate
Worksheets("记录单").Range("c5:d330").Select
Selection.ClearContents
Worksheets("报表").Activate
ActiveSheet.Unprotect
Worksheets("报表").Range("c6:f331").Select
Selection.ClearContents
Worksheets("主界面").Activate ‘以上代码是清除记录单和报表数据
宏1‘调用宏1
End Sub
Sub 宏1()
With Toolbars(1)
.Visible = False
.Position = xlFloating
.Left = 89
.Top = 67
End With
With Toolbars(2)
.Visible = False
.Position = xlFloating
.Left = 79
.Top = 78
End With
ActiveWindow.DisplayWorkbookTabs = False End Sub
' 宏2 Macro
' ykq记录宏1997/1/10
'
' 快捷键: Ctrl+e
Sub 宏2()
ActiveWindow.DisplayWorkbookTabs = True Toolbars(1).Visible = True
Toolbars(2).Visible = False
With Application
.ShowToolTips = True
.LargeButtons = False
.ColorButtons = True
End With
Toolbars(1).Visible = True
With Toolbars(2)
.Visible = True
.Position = xlTop
End With
With Application
.ShowToolTips = True
.LargeButtons = False
.ColorButtons = True
End With
Toolbars(1).Position = xlTop
Worksheets("主界面").Activate
End Sub
Sub 定义界面()
Application.Caption = "保定农校水电计价系统"
ActiveWindow.Caption = ""
End Sub
'快捷键ctrl+s
Sub 密码() ‘指定给主界面上的密码设置按钮
d = InputBox("请输入旧密码：", "保定农校水电计价系统")
If d = Worksheets("主界面").Range("v1").Value Then
For x = 1 To 2
a = InputBox("请输入自定义密码：", "保定农校水电计价系统")
b = InputBox("请重新输入自定义密码：", "保定农校水电计价系统") If a = b And a <> "" Then
c = a
Worksheets("主界面").Range("v1").Value = c
Exit For
Else
MsgBox "密码输入不正确"
End If
Next x
Else Exit Sub
End If
End Sub ‘以上代码是设置密码
Sub 报表查询() ‘指定给报表查询按钮
Worksheets("查询档案").Activate
ActiveSheet.Protect
End Sub ‘以上代码是激活查询档案
Sub 返回4() ‘指定给返回按钮
ActiveSheet.Unprotect ‘解除当前工作表保护
Worksheets("主界面").Activate
End Sub
Sub 报表存档() ‘指定给主报表上的报表存档按钮
dj = Range("$g$278").Value
uj = Range("$h$278").Value
'zj = Range("$h$332").Value
da = Range("$g$277").Value
ub = Range("$h$277").Value
If dj <> 0 Or uj <> 0 Or da <> 0 Or ub <> 0 Then
ActiveSheet.Unprotect
Range("g5:h331,j5:l331").Select
Selection.Copy
Worksheets("查询档案").Activate
x = 1
Do While Not (IsEmpty(Worksheets("查询档案").Cells(3, x).Value))
x = x + 1
Loop
ActiveSheet.Cells(3, x).Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Worksheets("查询档案").Cells(2, x).Value = y
Worksheets("查询档案").Cells(2, x).Value = Worksheets("记录单").Range("$c$2")
Worksheets("查询档案").Cells(2, x + 1).Value = "月"
Worksheets("主界面").Activate
bsy ‘调用bsy宏
Else
MsgBox "不是正式报表，不予存档！"
Exit Sub
End If
End Sub ‘以上代码是把报表数据存入查询档案
' 查询档案清理
' 快捷键: Ctrl+h
Sub 宏3() ‘清理查询档案
Worksheets("查询档案").Activate
Range("B2:AN329").Select
Selection.ClearContents
Range("a1").Select
Worksheets("主界面").Activate
End Sub
'
' 宏4 Macro
' ykq记录宏1997/1/10
' 档案库清理
'
' 快捷键: Ctrl+j
Sub 宏4() ‘清理查表档案
Worksheets("档案").Activate
Range("C4:AB329").Select
Selection.ClearContents
Range("a1").Select
Worksheets("主界面").Activate
End Sub
应用程序二：销售管理系统
在商业企业的经营活动中，主要业务活动是进销存。本程序主要解决销售的问题，关于进货和库存留给读者自己在原来程序的基础上进一步开发。
1、销售管理系统的功能
自动计算进货金额、销货金额和毛利。
分类汇总功能，可按商品和经手人代码进行分类汇总。
打印功能
查询功能（可按商品和经手人代码进行查询）。
2、销售管理系统的程序设计