Excel VBA的一个总结

我以前的VBA都是满足我自己的一个小小的需求的,他们通常都比较简单,属于自娱自乐型的,都很简单,今天终于写了一个稍微复杂一点的, 程序有两个bug, 在写这篇博客之前, 我刚花费两个小时把它们解决掉, 趁着还没忘, 对吸取到的一些经验教训加以总结.

 

下面是部分excel代码,代码后面是总结, 总结的地方已使用"- == BUG == -"标出:

' -- ==  BUG == --
' use this option, the interpreter will help you~~~
' Option Explicit


'used in step 01
Dim st
Dim et
Dim maxrow
Dim count

'used in step 02
Dim waste

Private Sub CommandButton1_Click()
    
    st = DateValue("2008-08-29")
    et = DateValue("2008-9-06")
    maxrow = 61000
    count = 1

    
    'MsgBox DateValue(Cells(2, 1))

    Call processSheet(Sheet2)
    Call processSheet(Sheet3)
    Call processSheet(Sheet4)

    MsgBox (count)
    
End Sub

Private Sub processSheet(sheet)

    sheet.Activate
    
    For i = 2 To maxrow
        If sheet.Cells(i, 1) <> "" Then
            t = DateValue(sheet.Cells(i, 1))
            If t >= st And t <= et Then
                count = count + 1
                sheet.Rows(i).Copy
                Sheet5.Rows(count).Insert Shift:=xlDown
            End If
        End If
    Next


   
    'MsgBox (Cells(2, 1))
    'MsgBox (count)
    
End Sub


Private Sub CommandButton2_Click()

    
    For i = 2 To 5815
        d = DateValue(Cells(i, 1))
        t = TimeValue(Cells(i, 1))
        
        Call findPos(d, t, Cells(i, 2), Cells(i, 3))
        Cells(i, 14) = waste
        
        If waste = Cells(i, 13) Then
            Cells(i, 15) = "Yes"
        Else
            Cells(i, 15) = "NO"
        End If
    Next
    
End Sub


Sub findPos(d, t, asset, serial)

    'Sheet5.Select
    waste = "-"
    
    For i = 2 To 60000
        'asert date is not null
        If Sheet5.Cells(i, 1) = "" Then
            Exit For
        End If
        
        'find waste box
        td = DateValue(Sheet5.Cells(i, 1))
        tt = TimeValue(Sheet5.Cells(i, 1))
        diff = DateDiff("n", t, tt)
        
        If d = td And (diff > -10 And diff < 10) And asset = Sheet5.Cells(i, 2) And serial = Sheet5.Cells(i, 3) Then
            waste = Sheet5.Cells(i, 14)
            Exit For
        End If
        
    Next
    
    'Sheet1.Select
    
End Sub

Private Sub CommandButton3_Click()

    
    For i = 2 To 5815
        
        If "NO" = Cells(i, 15) Then
            d = DateValue(Cells(i, 1))
            t = TimeValue(Cells(i, 1))
            
            Cells(i, 1).Select
        
            Call findFuzzy(d, t, Cells(i, 2), Cells(i, 3))
            Cells(i, 14) = waste
        
            If waste = Cells(i, 13) Then
                Cells(i, 15) = "Yes"
            Else
                Cells(i, 15) = "NO"
            End If
        End If
    Next
    

End Sub



Sub findFuzzy(d, t, asset, serial)

    Min = DateValue("2099-08-29")
    mintime = TimeValue("23:59:59")
    mintimebeh = TimeValue("23:59:59")

    waste = "-"
    flag = True
    
    For i = 2 To 60000
        'asert date is not null
        If Sheet5.Cells(i, 1) = "" Then
            flag = False
            Exit For
        End If
        
        'judge wheather is the same printer
        If asset = Sheet5.Cells(i, 2) And serial = Sheet5.Cells(i, 3) Then
        
        
            'find waste box
            td = DateValue(Sheet5.Cells(i, 1))
            tt = TimeValue(Sheet5.Cells(i, 1))
            diff = DateDiff("n", t, tt)
        
                
            'record the min date before last day
            If DateDiff("d", d, td) = 1 Then
                    If tt < mintimebeh Then
                        mintimebeh = tt
                        ' -- ==  BUG == -- i spell mpbeh as mpben by mistake
                        ' -- ==  BUG == -- pay more attantion on the spelling of the var
                        mpbeh = i
                    End If
            End If
            
            'record the min value today
            If DateDiff("d", d, td) = 0 Then
                    If tt > t And tt < mintime Then
                        mintime = tt
                        mp = i
                    End If
            End If
            
        'end judge
        End If

    Next
    
    
    If mintime <> TimeValue("23:59:59") Then
        ' -- ==  BUG == --  waste = Cells(mp, 14)
        ' -- ==  BUG == --  when use the cells that are not in the same sheet of the button ,you must use  "Sheet5" to modify it.
        waste = Sheet5.Cells(mp, 14)
    Else
        waste = Sheet5.Cells(mpbeh, 14)
    End If
    
    'Sheet1.Select
    
End Sub

Private Sub CommandButton4_Click()

    d1 = DateValue("2008-08-29")
    d2 = DateValue("2008-7-06")
    

    MsgBox DateDiff("d", d1, d2)
    
End Sub

 

总结两点:

1. 第一个bug的原因是我误将mpbeh拼写成了mpben,以至于我没有得到我想要的值, 该死的键盘设计的有问题, h & n靠的那么近, 还张得那么像, 在一堆字符里错误的出现通常很难辨识,尤其在脑袋大的时候.

 

为此MrLiu建议使用:"Option Explicit"来减少,变量拼写的错误, 恩,不错,thx

 

2. 在引用了不和空间在一个sheet里面的cells的时候,没有使用sheet进行限定.

虽然通篇我都很小心的注意了这一点, 但我还是漏掉了一处, 为此我也付出了应有的代价. 以后只有更加留神,或者写vba的时候尽量避免这种情况出现. 毕竟你想我刚开始那样直接写上一个Cells, 他在解释器看来是再合法不过的了, 虽然你可能并不想这么做.

 

另:玩具一样的例子中学到的东西也有限, 如果我有一个机会出一个实际题目, 让自己或者别人通过这个例子强化一下vba, 我会出什么? 刚才想了一下这个问题, 一个答案是做一个:"SQL学习环境", 毕竟他和数据库还都挺关系的. 还有别的更好的答案吗?