Archive for the 'office' category

用Excel中的宏减少重复劳动

五 21 2012 由 创建在标签 office

客户发过来的测试报告都是用Excel表格制成的,但是他们的格式总是不停地变,而且不能方便我们监控问题的改动。最好的办法就是自己制定一个表格,按照自己的想法把它组合起来,将格式设成我们想要的格式,然后再重客户的Excel表中将数据考到表格中来。返回拷贝是不是太麻烦了?还是用Excel为我们提供的工具——宏。

关于宏及VBA,我以前写过一篇简单的描述。现在就重点说说如何实现吧。我使用的是Office2010,2007应该和10的界面差不多,03的就自己去找一下,但是名字都是通用的,百度或者Google一下应该就知道了。

格式控制

主要是根据我们现在对问题的控制,按照固定的格式显示以示区分,如添加单元格背景色或者改变字体。主要通过数据有效性和条件格式来实现这样的格式控制。

条件格式

根据这个描述就应该猜出是满足一定条件后显示出一定的格式,我们要区分问题的不同状态,就使用它了。这个地方条件可以使用Excel的函数。

比如我们就只使用两个状态——已处理与未处理,已处理的将文字格式设置成倾斜+删除线+背景灰色;未处理的问题用普通字体+红色背景。

开始->条件格式->管理规则,然后新建规则,上面可以选择规则的类型,下面选择符合规则的格式,中间填写你的规则。

但是规则如何判断呢?我们的输入可以任由用户输入,而程序又不能理解人类的语言,如何保证用户的输入都是满足我们的条件呢?就看下面的数据有效性。

数据有效性

数据有效性就是用来控制单元格的格式,比如某个单元格我只让他输入某些固定的字段,可以用一个下拉框供选择;总之就是让单元格的数据与你设置的保持一致,可以设置不一致就不让输入,从而方便条件格式用字段来当作条件判断。

数据->数据有效性,设置页签中有有效性条件的选择,整数、小数、序列、日期、长度和自定义,基本满足我们的日常需求,实在不行就自己定义一个(我没有尝试过),重点说一下序列,序列就是提前定义好单元格中输入的数据,在来源中设置有效序列,用英文的逗号“,”作为分隔符,有选项可以设置是否提供下拉,按照需求设置。

设置好了数据有效性后,只有输入符合条件才能够输入,这样就可以解决条件格式中的条件了,后面可帮助前面的人解决条件不一致的问题,将问题扼杀在萌芽之中。

多表单问题

有时我们拷贝数据时并不是第一个表单(sheet),有时甚至可能是多个表单,需要给提供一个选择的空间。每次手动输入用户会很烦躁,而且表单的位置之间看是看不出来的,也就是说直接用index方式索引表单其实是不准确的,需要根据表单的表名去索引,而表名有时会很长或复杂,手动输入的方法都不是很友好。

最友好的办法还是通过UI显示出来,获取到要导入的Excel表中所有的表单,将他们的表名提供给用户选择,然后按照用户的意愿来操作。显示表名时也会有些问题,由于不知道表单的个数,需要动态显示的表名,需要计算好每一个表名的索引,由于没有设置好,这个地方在VBA实现的时候出了很大的Bug,有表单与选择不一致、翻页错误等一系列的问题,这个地方还是看细心,虽然调试也能调出来,但是会耗费一些时间的。

下面是关于多表单的一些代码:

Public Sub load_UserForm()

    lcounttmp = oIBLBook.Worksheets.Count

    If oIBLDisIndex <= lcounttmp Then
        setVerNoForm.CheckBox1.Caption = oIBLBook.Worksheets(oIBLDisIndex).Name
        oIBLDisIndex = oIBLDisIndex + 1
        lFormDisNum = 1

        setVerNoForm.CheckBox1.Value = False
    Else
        'setVerNoForm.CheckBox1.Visible = False
    End If

    If oIBLDisIndex <= lcounttmp Then
        setVerNoForm.CheckBox2.Caption = oIBLBook.Worksheets(oIBLDisIndex).Name
        oIBLDisIndex = oIBLDisIndex + 1
        lFormDisNum = lFormDisNum + 1
        setVerNoForm.CheckBox2.Visible = True
        setVerNoForm.CheckBox2.Value = False
    Else
        setVerNoForm.CheckBox2.Visible = False
    End If

    If oIBLDisIndex <= lcounttmp Then
        setVerNoForm.CheckBox3.Caption = oIBLBook.Worksheets(oIBLDisIndex).Name
        oIBLDisIndex = oIBLDisIndex + 1
        lFormDisNum = lFormDisNum + 1
        setVerNoForm.CheckBox3.Visible = True
        setVerNoForm.CheckBox3.Value = False
    Else
        setVerNoForm.CheckBox3.Visible = False
    End If

    If oIBLDisIndex <= lcounttmp Then
        setVerNoForm.CheckBox4.Caption = oIBLBook.Worksheets(oIBLDisIndex).Name
        oIBLDisIndex = oIBLDisIndex + 1
        lFormDisNum = lFormDisNum + 1
        setVerNoForm.CheckBox4.Visible = True
        setVerNoForm.CheckBox4.Value = False
    Else
        setVerNoForm.CheckBox4.Visible = False
    End If

    If oIBLDisIndex <= lcounttmp Then
        setVerNoForm.CheckBox5.Caption = oIBLBook.Worksheets(oIBLDisIndex).Name
        oIBLDisIndex = oIBLDisIndex + 1
        lFormDisNum = lFormDisNum + 1
        setVerNoForm.CheckBox5.Visible = True
        setVerNoForm.CheckBox5.Value = False
    Else
        setVerNoForm.CheckBox5.Visible = False
    End If

    If oIBLDisIndex <= lcounttmp Then
        setVerNoForm.CheckBox6.Caption = oIBLBook.Worksheets(oIBLDisIndex).Name
        oIBLDisIndex = oIBLDisIndex + 1
        lFormDisNum = lFormDisNum + 1
        setVerNoForm.CheckBox6.Visible = True
        setVerNoForm.CheckBox6.Value = False
    Else
        setVerNoForm.CheckBox6.Visible = False
    End If

    If oIBLDisIndex <= lcounttmp Then
        setVerNoForm.CheckBox7.Caption = oIBLBook.Worksheets(oIBLDisIndex).Name
        oIBLDisIndex = oIBLDisIndex + 1
        lFormDisNum = lFormDisNum + 1
        setVerNoForm.CheckBox7.Visible = True
        setVerNoForm.CheckBox7.Value = False
    Else
        setVerNoForm.CheckBox7.Visible = False
    End If

    If oIBLDisIndex <= lcounttmp Then
        setVerNoForm.CheckBox8.Caption = oIBLBook.Worksheets(oIBLDisIndex).Name
        oIBLDisIndex = oIBLDisIndex + 1
        lFormDisNum = lFormDisNum + 1
        setVerNoForm.CheckBox8.Visible = True
        setVerNoForm.CheckBox8.Value = False
    Else
        setVerNoForm.CheckBox8.Visible = False
    End If

End Sub

'UI上上一页和下一页按钮事件处理函数
Private Sub NextButton_Click()
    If oIBLDisIndex = oIBLBook.Worksheets.Count + 1 Then
        Exit Sub
    Else
        Call load_UserForm
    End If
End Sub

Private Sub PrevButton_Click()

    'Get the sheet count
    If oIBLDisIndex = 9 Then
        Exit Sub
    Else
        oIBLDisIndex = oIBLDisIndex - 8 - lFormDisNum
    End If

    Call load_UserForm
End Sub

 

无规则合并单元格的区分

通过单元格的MergeArea.Rows.Count属性来判断一个单元格是否被合并,因为单元格的合并是我们不能控制的,而且这种情况可以用两重循环来对表格中每一行数据及部分合并的情况。

'lFromIndex  开始的行索引
'lfromend    结束的行索引号
     For ltmpindex = lFromIndex To lfromend Step 1

        '填充第一行序列号
        If 2 = lToIndex Then
            oToSheet.Cells(2, 1).Value = 1
        Else
            oToSheet.Cells(lToIndex, 1).Value = oToSheet.Cells(lToIndex - 1, 1).Value + 1
        End If

        '判断是否有单元格被合并
        lmergedcnt = oFromSheet.Cells(ltmpindex, 1).MergeArea.Rows.Count
        If lmergedcnt > 1 Then
            '有单元格合并,需要单独处理合并的每个单元格
            oToSheet.Cells(lToIndex, 2).Value = oFromSheet.Cells(ltmpindex, 2).Value
            oToSheet.Cells(lToIndex, 4).Value = "Test"
            oToSheet.Cells(lToIndex, 5).Value = oFromSheet.Cells(ltmpindex, 4).Value

            '处理合并单元格中的详细描述
            Dim lMergeTmp As Long
            Dim sDesInfo As String
            sDesInfo = ""
            For lMergeTmp = 0 To lmergedcnt - 1 Step 1
                lDesIndex = oFromSheet.Cells(ltmpindex + lMergeTmp, 3).MergeArea.Rows.Count
                sDesInfo = sDesInfo & oFromSheet.Cells(ltmpindex + lMergeTmp, 3).Value & Chr(10)

                lMergeTmp = lMergeTmp + lDesIndex - 1
            Next lMergeTmp
            ltmpindex = ltmpindex + lmergedcnt - 1
            'MsgBox sDesInfo
            oToSheet.Cells(lToIndex, 3).Value = sDesInfo
        Else
            '直接依次填充相应的数据即可~~
            oToSheet.Cells(lToIndex, 2).Value = oFromSheet.Cells(ltmpindex, 2).Value
            oToSheet.Cells(lToIndex, 3).Value = oFromSheet.Cells(ltmpindex, 3).Value
            oToSheet.Cells(lToIndex, 4).Value = "Test"
            oToSheet.Cells(lToIndex, 5).Value = oFromSheet.Cells(ltmpindex, 4).Value
        End If
        oToSheet.Cells(lToIndex, 10).Value = oFromSheet.Cells(ltmpindex, 7).Value

        '移动到下一行
        lToIndex = lToIndex + 1
    Next ltmpindex

 结束语

Excel中的宏可以让我们减少很多重复的工作量,只要能够发现其中的需求,就可以用宏来解决问题。而且Excel还可以和Word结合起来,可以批量导入Word中的数据,但是Word也需要定制,可以使用Word的表单工具,我有时间再写一篇关于Word和Excel联合工作。

无评论

VBA的学习分享

四 06 2011 由 创建在标签 office

以前见过有人使用过Excel中的宏,自己也尝试着用过,因为不了解,所以总觉得功能不是很强,而网上的同学们都说宏的功能很强大,自己觉得没有,后来看到有人用VBA后,突然觉得Office的功能好强大啊,竟然可以那样减少我们的工作量,太不可思议了,遂开始学习VBA。经过一段时间的学习,对VBA有了一定的了解,但是还没有完全搞清楚,因为刚刚开始建设自己的Blog,没什么大的研究,这两天也有人想了解一下VBA上的东西,就把自己学习的一些过程拿出来分享一下。
对于VBA和宏的差别,大家Google或者Baidu一下就知道了,其实两者没有太本质的区别,运行宏的时候可以调用VBA写的过程或函数。
学习VBA的第一步是了解基本语法和一些常用函数,基本语法其实就是VB(对于没有学过VB的程序员来说也不是难事,毕竟所有的语言都是有相通之处的,了解了一种语言其他的学起来应该不会吃力;对于没有学过编程的人来说还是花的时间去了解一下基本语法);对于常用的函数就是一些输入(InputBox)、弹框(MsgBox)、字符处理等,在网上搜一下就会有很多教程可以用的。
第二步就是开始学习Excel的一些对象,这是用VBA来减轻我们工作的第一步,没有对这些概念有一个简单的了解,对于用VBA来减轻工作量基本上是不现实的;在使用这些对象的时候我们可以直接在编辑器中直接写一些简单的调用来加深我们对对象的理解,加深我们的印象。参考文档其实很简单,就是我们的安装的Office中的帮助,里面的开发人员参考对一些对象有很详细的介绍。 帮助文档的打开方式如下:

  • Excel中点击“开发工具”->“Visual Basic”进入VBA编辑器,然后点击“帮助”->“Microsoft Visual Basic帮助”;也可以直接在Excel中使用“alt+F11”快捷键进入VBA编辑器,然后按“F1”进入帮助。

第三步就是根据一个实际的需求来写一些简单的应用,这个过程就是减轻我们工作量的过程了,将我们在平常工作中的重复劳动提取出来,通过一些组合来减轻我们的工作,让自己从重复的工作中释放出来。
可能你会觉得花很多时间来写这个模板,如果只是用一次的话,是不是很不值得。但是如果我们发现的是我们经常要做的一些东西呢?把我们的重复劳动改为自动的话可以想象一下他给我带来的便捷,也可以给别人减轻很多负担,还可以给别人留下一点东西,是一举多得的事情。
后面会继续将自己的一些心得或者过程分享出来。

无评论