编写简单VBA做excel统计

上周末，一个朋友加班到很晚，说是做一些繁琐的工作焦头烂额，根据多个excel文件数据并做更新，我寻思着，给你写个按钮吧，可能会省你不少事，于是，写了个简单的VBA，一定按钮便能自动更新数据，省得一条条比对了。由于本人环境是繁体，移到简体环境上中文注释出现乱码，省略掉一些注释，下面记下部分代码（具体数据更新方式省略，改为简单数据更新，以减少代码的长度），方便以后查找。

Sub updatedata()
Dim strPath, strfile As String
Dim r As Variant, i, j As Integer
Dim a, b As Integer
Dim arr(1 To 27, 1 To 30)

With Application.FileSearch
.FileType = msoFileTypeExcelWorkbooks
.LookIn = "C:\Documents and Settings\leo jiang\"
.SearchSubFolders = False
.Execute

strPath = "C:\Documents and Settings\leo jiang\ "                            

Cells(1, 1) = ""
m = .FoundFiles.Count 
i = 1
     For a = 1 To 5                                             
            For b = 1 To 5
               arr(a, b) = 0
            Next
      Next
         
For Each F In .FoundFiles 
         For a = 7 To 9                                              '获取资料
             For b = 1 To 5
                   
              r = GetValue(strPath, Dir(F), "Sheet2", Cells(a + 2, b + 1).Address(0, 0))
            
              arr(a, b) = arr(a, b) + r
   
             Next
        Next
        Cells(1, 1) = "共有" & m & "分文档，现在更新到第" & i & "份"
    i = i + 1
Next
End With

With Sheets(2)
For a = 1 To 25                                               '写数据
    For b = 1 To 25
            Cells(a + 2, b + 1) = arr(a, b)
    Next
Next
End With

End Sub



Private Function GetValue(path, file, sheet, range_ref)
    Dim arg As String
    If Right(path, 1) <> "\" Then path = path & "\"
    If Dir(path & file) = "" Then
        GetValue = "File Not Found"
        Exit Function
    End If
    arg = "'" & path & "[" & file & "]" & sheet & "'!" & _
    Range(range_ref).Range("A1").Address(, , xlR1C1)                 
    GetValue = ExecuteExcel4Macro(arg)
End Function