vba - 某些文件会出现溢出错误

Question

我有一个宏，它通过目录进行计算并编译结果。我在这里还参与了跳过损坏或“不可读”的 Excel 文件的工作，效果很好。

现在的问题是，它在大多数情况下都有效，但某些文件给我一个溢出错误。我的代码有问题吗？还是工作簿？

突出显示这部分代码:

 lrw = ws.Columns("A:Y").Find("*", , xlValues, , xlRows, xlPrevious).Row

这是我的完整代码:

Sub StackExchange()
'added function to skip corrupt files and add the skipped to worksheet( works)
'testing more to skip other random files that arent corrupt some don't work for some reason.



Dim wb As Workbook, fileNames As Object, errCheck As Boolean 'part of loop


Dim ws As Worksheet
Dim resultSheet As Worksheet
Dim i As Long
Dim lco As Integer
Dim lrw As Integer
Dim resultRow As Integer
Dim measurement As Double

Dim wksSkipped As Worksheet
Set wksSkipped = ThisWorkbook.Worksheets("Skipped")


Set resultSheet = Application.ActiveSheet
resultRow = 1

'Optimize Macro Speed
  Application.ScreenUpdating = False
  Application.EnableEvents = False
  Application.Calculation = xlCalculationManual
'get user input for files to search
  Set fileNames = CreateObject("Scripting.Dictionary")
  errCheck = UserInput.FileDialogDictionary(fileNames)
  If errCheck Then Exit Sub






For Each Key In fileNames 'loop through the dictionary I added the below Sept 9, 2015




On Error Resume Next
Set wb = Workbooks.Open(fileNames(Key))
If Err.Number <> 0 Then
    Set wb = Nothing    ' or set a boolean error flag
End If
On Error GoTo 0    ' or your custom error handler

If wb Is Nothing Then
wksSkipped.Cells(wksSkipped.Cells(wksSkipped.Rows.Count, "A").End(xlUp).Row + 1, 1) = fileNames(Key)
Else
    Debug.Print "Successfully loaded " & fileNames(Key)
    wb.Application.Visible = False 'make it not visible


    For Each ws In wb.Worksheets
        If Not Application.WorksheetFunction.CountA(ws.Cells) = 0 Then
            'define the range to measure
            lco = ws.Cells.Find("*", , xlValues, xlWhole, xlByColumns, xlPrevious, False).Column
            lrw = ws.Columns("A:Y").Find("*", , xlValues, , xlRows, xlPrevious).Row
            If lrw = 1 Then lrw = 2
            For i = 1 To lco
                measurement = Application.WorksheetFunction.CountA(ws.Range(ws.Cells(1, i), ws.Cells(lrw, i))) / lrw
                resultSheet.Cells(resultRow, 1).Value = wb.Name
                resultSheet.Cells(resultRow, 2).Value = ws.Name
                resultSheet.Cells(resultRow, 3).Value = ws.Cells(1, i).Value
                resultSheet.Cells(resultRow, 4).Style = "Percent"
                resultSheet.Cells(resultRow, 5).Value = measurement
                resultRow = resultRow + 1
            Next
        End If
    Next
    wb.Application.Visible = True '' I added this Sept 9, 2015
    wb.Close savechanges:=False 'close the workbook do not save
    Set wb = Nothing 'release the object
    End If
Next 'End of the fileNames loop

Set fileNames = Nothing
'Message Box when tasks are completed
MsgBox "Task Complete!"

ResetSettings:
  'Reset Macro Optimization Settings
    Application.EnableEvents = True
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
End Sub

Function Col_Letter(lngCol As Long) As String
    Dim vArr
    vArr = Split(Cells(1, lngCol).Address(True, False), "$")
    Col_Letter = vArr(0)
End Function

Answer 1

VBA Integer 保存 2 个字节，范围为 –32,768 到 32,767。 VBA long 是完整整数，包含 4 个字节，范围为 –2,147,483,648 到 2,147,486,647