智力差距

         咪咪的聪明是毋庸置疑的。下面这个例子，可以充分的说明小丫头的智商和我之间的鸿沟。

 

         有一天，我的高中同学得知我略通excel VBA，于是托我帮一个忙，希望实现如下表格中载频总数一栏的自动计算（其值为载频配置一栏对应单元格表达式的和）。表格如下：

 

         我先试了一下，发现excel好像没有简单的方法能够计算一个文本字符串的值，然后很自然的想到了——

 

第一种方法（笨办法）：解析字符串！把字符串里面所有的“+”都找出来，解析出“+”号前后的字符串，把它们加起来。鉴于要考虑到“乘”的情况，还要同时解析出“*”号前后的字符串，把它们乘起来，同时还要考虑到先乘后加……我花了两个小时才调试出来，VBA代码如下：

 

Sub Calculate()

 

    Dim iConfigureColumnNumber, iSumColumnNumber As Integer

    Dim iTotalRow, iTotalColumn As Integer

   

    Dim iPlusSignNumber As Integer

    Dim iTimesSignNumber As Integer

    Dim oCollection1 As New Collection

    Dim oCollection2 As New Collection  'Store times member

    Dim oCollection3 As New Collection

   

    iConfigureColumnNumber = 5

    iSumColumnNumber = 6

   

    iPlusSignNumber = 0

    iTimesSignNumber = 0

   

    Dim oRange As Range

    Set oRange = Application.ActiveSheet.UsedRange

   

    iTotalRow = oRange.Rows.Count

    iTotalColumn = oRange.Columns.Count

   

    Dim StrText As String

    Dim i, j, k As Integer

    Dim iResult As Integer

    Dim iStartNumber As Integer

    Dim StrTempText As String

   

    Dim iNumber As Integer

    iNumber = 1

   

    For i = 2 To (iTotalRow - 1) Step 1

        iResult = 0

        iStartNumber = 1

        StrText = Trim(oRange.Cells(i, iConfigureColumnNumber))

        Do While (InStr(iStartNumber, StrText, Chr(43), vbTextCompare) <> 0)

            iResult = InStr(iStartNumber, StrText, Chr(43), vbTextCompare)

            StrTempText = Mid(StrText, iStartNumber, iResult - iStartNumber)

            Call oCollection1.Add(StrTempText)

            iStartNumber = iResult + 1

            iPlusSignNumber = iPlusSignNumber + 1

        Loop

        StrTempText = Mid(StrText, iStartNumber, Len(StrText) - iResult)

        Call oCollection1.Add(StrTempText)

       

        For j = 1 To oCollection1.Count Step 1

            StrText = oCollection1.Item(j)

            iStartNumber = 1

            iResult = 0

            Do While (InStr(iStartNumber, StrText, Chr(42), vbTextCompare) <> 0)

                iResult = InStr(iStartNumber, StrText, Chr(42), vbTextCompare)

                StrTempText = Mid(StrText, iStartNumber, iResult - iStartNumber)

                Call oCollection2.Add(StrTempText)

                iStartNumber = iResult + 1

                iTimesSignNumber = iTimesSignNumber + 1

            Loop

            StrTempText = Mid(StrText, iStartNumber, Len(StrText) - iResult)

            Call oCollection2.Add(StrTempText)

           

            Dim iTotal As Integer

            iTotal = 1

            For k = 1 To oCollection2.Count Step 1

                Dim iTemp As Integer

               

                iTemp = oCollection2.Item(k)

                iTotal = iTotal * iTemp

            Next

           

            oCollection3.Add (iTotal)

           

            iNumber = oCollection2.Count

            For k = 1 To iNumber Step 1

                oCollection2.Remove (oCollection2.Count)

            Next

           

        Next

 

        iNumber = 0

        For j = 1 To oCollection3.Count Step 1

            iNumber = iNumber + oCollection3.Item(j)

        Next

        oRange.Cells(i, iSumColumnNumber) = iNumber

       

        iNumber = oCollection1.Count

        For j = 1 To iNumber Step 1

            oCollection1.Remove (oCollection1.Count)

        Next

       

        iNumber = oCollection2.Count

        For j = 1 To iNumber Step 1

            oCollection2.Remove (oCollection2.Count)

        Next

       

        iNumber = oCollection3.Count

        For j = 1 To iNumber Step 1

            oCollection3.Remove (oCollection3.Count)

        Next

       

    Next

   

End Sub

 

第二种方法（聪明办法）：就在我绞尽脑汁调试时，在旁边看电视的咪咪看见我痛苦的表情，于是走过来询问了一下我的情况。听完我大致介绍，小丫头轻松的说道：“哪里用得着那么复杂，在文本的前面加个= 号，excel不就自己就会算出来了么？”我恍然大悟，试了一下，果然几行代码就搞定……VBA代码如下：

 

Sub Calculate2()

 

    Dim iConfigureColumnNumber, iSumColumnNumber As Integer

    Dim iTotalRow, iTotalColumn As Integer

      

    iConfigureColumnNumber = 5

    iSumColumnNumber = 6

      

    Dim oRange As Range

    Set oRange = Application.ActiveSheet.UsedRange

   

    iTotalRow = oRange.Rows.Count

    iTotalColumn = oRange.Columns.Count

   

    Dim StrText As String

   

    Dim i As Integer

    For i = 2 To (iTotalRow - 1) Step 1

        StrText = "=" & oRange.Cells(i, iConfigureColumnNumber)

        oRange.Cells(i, iSumColumnNumber) = StrText

    Next

 

End Sub

 

         唉，正所谓事半功倍……要是早想到这个办法，我哪至于花这个冤枉时间呢，看来小丫头的智商果然是要远远超我老夫啊啊啊啊啊啊~~~~~