我是如何一步步打造自己的应用系统的

yuanzhuping

春节以后花了很长一段时间（断断续续的，边测试边修改）做了一个适合我的本职工作，对工作很有帮助的应用系统，使用后，简化了很多工作，大大提高了我的工作效率。自认为这是我做过的最有用的东西，现在我把做这个应用系统的过程和大家一起分享。
首先申明：
    1、本帖不是教学帖，我也不是在教大家开发什么软件，只是和大家分享一下我是如何用Excel来简化工作，提高工作效率的一些思路，心得，只希望在大家做自己的应用东西时有所启发。
   

2、此系统只是一个行业应用的东西，不具备通用性；我也不是科班出身，所以写代码以实用为主，只要达到自己的目的就行了，有不对的地方请各位不要笑话。

[此贴子已经被作者于2007-11-22 23:36:06编辑过]

[ 本帖最后由 yuanzhuping 于 2009-2-17 07:07 编辑 ]

yuanzhuping

首先说说我的工作环境和此系统的应用环境。大家不要怪我罗嗦，看了以后就会明白为什么我会说这是我做的最有用的东西。我所在的公司有两家分公司（益民公司和安装公司），独立核算，但工资是由总公司统一核算，向分公司收取后由银行代发；社会保险由总公司统一计算、缴纳后向分公司收取；安装公司的个人所得税单独申报，益民公司的个人所得税和总公司合并申报后再收取；益民公司的工资表并不是全额发放，还有一部分不在工资表中，另外总公司会不定时的有一张发放表。（怎么会是这样的，这里就不多说了）

我是从去年下半年开始在总公司做财务（以前在分公司），接手的工作（与此系统有关的）：1、三个公司的工资表，这好办，根据各部门的考勤记录（我为公司做了一个[考勤表]受到各部门特别是考勤员的好评，见http://club.excelhome.net/viewthread.php?tid=226239&px=0），用的是工资软件。2、益民公司的工资表以外的一张发放表，要根据工资表的应发数、四项扣款和应扣的个人所得税再计算它的各项数据。3、全公司不定时的有工资表以外的发放表，比益民公司的发放表还要难算，其中益民公司部分要根据工资表和第一张发放表数据计算。4、汇总本月所有的实发数向银行提供明细表由银行代发工资。5、汇总本月所有的应发数，填写社会保险申报表后申报。6、计算总公司和益民公司的个税数据、计算安装公司的个税数据并填写申报。7、开收据向分公司收取他们的社会保险、住房公积金、个人所得税等。

接手后的第一个月发现，我的前任竟然都是手工操作的（除了工资表），根据打印出来的工资表数据再一步步在打印好的表格中做后面的工作，用计算器纯手工（请注意是计算器不是计算机），我晕，难怪每到月末他就一副忙碌状。第一个月的月末月头的十天就在忙碌与精神高度紧张中渡过，忙碌是必然的，之所以说精神高度紧张，因为每一笔数字都要计算，仔细核对，不能有一点差错。

第二个月我当然不会这么做了，不能忍受这种毫无效率的工作方式。首先从工资软件中导出工资表数据到Excel表中，然后再用Excel做了张益民公司的发放表，设好公式，填好应发数，从工资表中复制粘贴其他的数据计算好益民公司的发放表，又做了张全公司的发放表，再复制粘贴。代发工资表和个税申报的表是把三张表的数据复制粘贴到辅助列中，相加后再复制粘贴到各自的表中。代发工资表中有一人是不发的，粘贴时顺序不对，不能一次性粘贴，只能分两次。社保申报表因为顺序和导出的工资表不同，先用辅助列按社保表的顺序取得数据再粘贴到申报表中。第二个月轻松点了。

第三个月我就考虑用VBA来做了。做了[益民公司发放表]、[公司发放表]、[代发工资明细表]、[社会保险表]和两张[个税计算表]，在表中分别设了[导入数据]和[计算]按纽，只要把当月的工资导出到一个固定文件夹后，分别计算各自的数据，然后再把社保和个税的计算好的数据复制到申报表中，因为它们有自己的申报表格式。这时的工作已经是相当的轻松了。

后来自然而然的就萌生了一个想法，为什么不把几张表放在一起，所有的操作都在一个工作簿中呢？这样不是操作起来更加方便吗？说到现在才说到正题，呵呵。

说做就做，开始只是把几张表简单的合并到一起，操作起来还不错。后来发现这样工作表太多，代码重复，数据导来导去的，效率太低。

[此贴子已经被作者于2007-5-18 0:18:42编辑过]

yuanzhuping

于是重新来过，这次用了自定义菜单，把要做的几项工作内容在菜单中分别操作，做了一半感觉这样有点乱，再次否定了。痛苦中………

在仔细考虑了整个工作流程后，决定按操作的先后顺序来做。
   

第一步：导入数据
   

[导入] 菜单，有两个子菜单：[导入工资数据]用来导入当月的工资表数据并保存在[当月工资表]中，这是后面计算发放表的基础；[导入其他数据]用来导入另行计算个人所得税，但要合并代发和申报社会保险的年终资金。
   

两个子菜单用了一个窗体，用窗体上的Caption来决定执行哪一项操作：
   

Sub DaoRuGongZi() '导入工资数据模块
   

   Sheet2.Select '工资表
   

   导入数据.Caption = "导入工资数据" '窗体重命名，根据命名决定要做的操作是导入工资数据还是其他数据
   

   导入数据.Show

End Sub

Sub DaoRuQiTa() '导入其他数据模块
   

   Sheet8.Select '汇总表
   

   导入数据.Caption = "导入其他数据"

   导入数据.Show

End Sub

用：
   

If 导入数据.Caption = "导入工资数据" Then

If 导入数据.Caption = "导入其他数据" Then

来决定是导入工资数据还是导入其他数据，这样就少用了一个窗体。
   

[此贴子已经被作者于2007-9-21 15:51:50编辑过]

yuanzhuping

用窗体上的组合框来选择打开哪个月的工资表。

Private Sub UserForm_Initialize()

Dim MyMonth As String

MyMonth = Month(Date) '取得当前月份

 ComboBox1.RowSource = "月份"

 ComboBox1.ListIndex = MyMonth - 1 '下拉框显示的是当前的月份

用上述代码使组合框显示的月份始终是当月的月份，这样基本上就不用选择了。

用Open的方法打开从工资软件中导出的工资表，因为SQL、ADO咱不会，现学也来不及，只好用苯方法，打开取数。在打开时用代码将选项中的[任务栏中的窗口]去掉，这样感觉的不打开工作表取数差不多，呵呵。

代码如下：

Application.ShowWindowsInTaskbar = True '取消任务栏中的窗口选项

MyMonth = ComboBox1.Value  '取得要导入的月份

Workbooks.Open ThisWorkbook.Path & "\工资表\" & MyMonth & "工资表.xls" '打开该月份的工资表

ThisWorkbook.Activate

打开后用Find方法，以唯一的人员编号为依据在工资表中查找数据后写入工资表中。一开始用的是姓名查找，因为姓名重复，查找后会有错误，后来改用唯一的人员编号为依据。

For sh = 1 To Workbooks(MyMonth & "工资表.xls").Sheets.Count '从该月的所有工资表中循环[共有3张]

 With Workbooks(MyMonth & "工资表.xls").Sheets(sh).Range("A:A") '从所有工资表的A列查找

  For I = 2 To R - 1

   Set Rng = .Find(What:=Cells(I, 1).Value) '用人员编号查询

    If Not Rng Is Nothing Then

     For L = 2 To 27 '导入工资表中的数据

      Cells(I, L + 1).Value = Rng.Offset(0, L).Value

      Cells(1, 29).Value = MyMonth '在表中记录当前数据的月份

      Cells(R, L + 1).FormulaR1C1 = "=SUM(R[-139]C:R[-1]C)" '写入列的合计公式

      Cells(R, L + 1) = Cells(R, L + 1).Value '公式改成数值

     Next

   End If

  Next

 End With

Next

 

[此贴子已经被作者于2007-5-15 1:07:18编辑过]

yuanzhuping

然后关闭该月的工资表。

    Workbooks(MyMonth & "工资表.xls").Close '关闭该月工资表

Application.ShowWindowsInTaskbar = True '选中任务栏中的窗口

[导入其他数据]因为不涉及发放表的计算，只要把数据合并到[汇总]表就OK了，只是在导入前要把本月的个人所得税数据先导出到个税申报表，因为年终奖的个税是一次申报的，和正常的个税申报是分开的，所以导入前用MsgBox做个提醒：

If MsgBox("确定本月的个人所得税数据已经导出了吗?", 32 + vbYesNo, "系统提示") = vbNo Then

    Unload Me

      Sheet1.Select

      Application.ShowWindowsInTaskbar = True

      Application.ScreenUpdating = True '开启屏幕刷新

     Exit Sub

    End If

工资表数据导入后就可以计算发放表的数据了。

第二步：计算

[计算] 菜单，有两个子菜单：[益民公司发放表]、[公司其他发放表]，为什么益民的要在上面？因为要先算益民公司的发放表后才能算公司其他的发放表。有了工资表的数据这就很简单了，只是要注意工资表的月份和要计算的发放表的月份是否相同，如果不同计算结果当然不对，这点在代码中都做了限制：

先用MsgBox来个提醒：

If MsgBox("确定要计算益民公司" & MyMonth & "的发放表吗?", 32 + vbYesNo, "系统提示") = vbNo Then

 Unload Me

  Sheet1.Select

 Exit Sub '提示月份是否正确

End If

再检查要计算的月份和导入的工资表月份是否相符：

If MyMonth <> Sheet2.Cells(1, 29).Value Then '如果要计算的月份不等于导入的工资表的月份

Unload Me

  MsgBox "请先导入" & MyMonth & "的工资数据!", 64, "系统提示"

  Sheet1.Select

 Exit Sub '结束,否则会计算错误

End If

关于计算的代码因为太罗嗦就不贴出来了，大家自己看。

yuanzhuping

[益民公司发放表]计算好以后，如果有其他的发放表，再根据工资数据和[益民公司发放表]的数据计算其他发放表，主要也是做了限制：

除了上述外，还要检查益民公司的当月发放表是否已经计算：

If MyMonth <> Sheet3.Cells(1, 22).Value Then '如果益民公司发放表的月份不是要计算的月份

 Unload Me

  MsgBox "请先计算益民公司" & MyMonth & "的发放表!", 64, "系统提示"

  Sheet1.Select

 Exit Sub

End If

 

If Sheet3.Cells(5, 10).Value = "" Then '如果益民公司发放表还没有计算

Unload Me

  MsgBox "请先计算益民公司" & MyMonth & "的发放表!", 64, "系统提示"

  Sheet1.Select

 Exit Sub

End If

计算也是采用了一个窗体，用窗体上的Caption来决定执行哪一项操作。

计算好了之后就可以进行数据汇总的工作了。

否汇总完整。

yuanzhuping

第三步数据汇总

[汇总]菜单有三个子菜单：[当月工资表]、[益民发放表]、[其他发放表]，把应发工资、实发工资、四项扣款汇总到[汇总表]中，[汇总表]的数据是后面代发工资、社会保险、个人所得税的依据，即不能少汇也不能多汇总，这在代码中也一一做了限制。如：

If Sheet8.Cells(R2 + 1, MyColumn).Value = "" Then '如果标记所在是空白,说明当前月的工资还没有汇总

 MsgBox "请先汇总" & MyMonth & "的工资数据!", 64, "系统提示"

  Sheet1.Select

 Exit Sub

End If

 

If Sheet8.Cells(R2 + 1, MyColumn).Value = "发1" Then '如果标记所在是[发1],说明当前月的发放表已经汇总过了

 MsgBox "不能重复汇总" & MyMonth & "的发放表数据!", 64, "系统提示"

  Sheet1.Select

 Exit Sub

End If

当然在后面的导出数据时也会检查是否汇总完整。

yuanzhuping

第四步就是打印发放表，顺便也做了个发放条的打印，工资表有发放条，那发放表不能没有啊。一开始用的是固定格式的两张发放表各打各的，后来改成一张打印表，平时只有几行表头，没有数据，到打印时再从发放表中取得数据，打印也了再删除，尽量减小文件的体积。

第五步把汇总好的数据导出，把每人的实发合计导出到另个的[代发工资明细表]中，把应发合计导出到[社会保险申报表]中，把应发合计和四项扣款合计导出到[个人所得税申报表]表计算应交的个人所得税。用的方法和导入工资表的方法差不多，在导出社会保险申报数据时用个人唯一的社保号码导出，因为社保申报表中没有人员编号，个人所得税用身份证号码导出。当然在导出前要检查本月的所有数据是否已经全部汇总：

If Sheet8.Cells(R + 1, MyColumn).Value = "" Then '检查是否已经导入工资数据

Unload Me

  MsgBox "请先汇总" & MyMonth & "的工资数据!", 64, "系统提示"

  Sheet1.Select

 Exit Sub

End If

 

If Sheet8.Cells(R + 1, MyColumn).Value = "工资" Then '检查是否已经导入益民发放表数据

Unload Me

  MsgBox "请先汇总" & MyMonth & "的益民公司发放表数据!", 64, "系统提示"

  Sheet1.Select

 Exit Sub

End If

 

If Sheet8.Cells(R + 1, MyColumn).Value = "发1" And Sheet6.Cells(1, 22).Value = MyMonth Then '检查本月是否有其他发放表数据

Unload Me '如果本月有其他发放表没有汇总

  MsgBox "请先汇总" & MyMonth & "的公司其他发放表数据!", 64, "系统提示"

  Sheet1.Select

 Exit Sub

End If

现在本系统的所有功能已经完成，经过测试无误运用到实际工作中，以前10天的工作量现在只要10分钟就OK了，现在最大的工作量就是打印了，对此已是相当的满意了。

yuanzhuping

在测试过程中，虽然功能已经实现了，但总觉得有什么地方不对，后发现是界面，即然用了自定义的菜单，还保留了原来的EXCEL的菜单、工具栏，觉得有点乱。

干脆把EXCEL的菜单、工具栏禁用了，反正在实际工作中也用不到，增加了一个[系统]菜单，有[保存]功能，[备份]功能，[退出]功能，后来又加了个[转换]功能，用来在自定义菜单和EXCEL菜单之间转换，有时候还是要用用EXCEL的菜单滴，也不能全废了。要做就做彻底了，最后做了个主界面，把[系统]菜单的功能放在界面的左边，一些常用的功能放在了界面的右边，这样就像个应用系统了。因为禁用了工作表标签，所以又增加了个[打开]菜单，用来打开工作表。用任务栏来显示打开的是哪张表以及表中数据的月份，因为在表中是看不到的。

yuanzhuping

[此贴子已经被作者于2008-3-25 11:27:42编辑过]