运行时错误“6”溢出(Excel VBA)

声明:本页面是StackOverFlow热门问题的中英对照翻译,遵循CC BY-SA 4.0协议,如果您需要使用它,必须同样遵循CC BY-SA许可,注明原文地址和作者信息,同时你必须将它归于原作者(不是我):StackOverFlow 原文地址: http://stackoverflow.com/questions/13466385/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me): StackOverFlow

提示:将鼠标放在中文语句上可以显示对应的英文。显示中英文
时间:2020-09-08 14:27:28  来源:igfitidea点击:

Run time error '6' OverFlow (Excel VBA)

excelexcel-vbaoverflowvba

提问by user1828786

I am having this error, i have thousands of csv files to be loaded, it can only load about hundred files per time. Can anyone advise me where is the error?

我遇到了这个错误,我有数千个 csv 文件要加载,它每次只能加载大约一百个文件。谁能告诉我错误在哪里?

Option Explicit

Function ImportData()

    Dim wkbCrntWorkBook As Workbook
    Dim wkbSourceBook   As Workbook
    Dim rngSourceRange1  As Range
    Dim rngSourceRange2 As Range
    Dim rngDestination1  As Range
    Dim rngDestination2  As Range
    Dim intColumnCount  As Integer

    Set wkbCrntWorkBook = ActiveWorkbook

    Dim SelectedItemNumber As Integer

    Dim YesOrNoAnswerToMessageBox As String

    Dim Highest As Double
    Highest = 0

    Dim counter As Integer
    Dim h1 As Integer
    Dim h2 As Integer

    h1 = 1
    h2 = 7

    Do

    SelectedItemNumber = SelectedItemNumber + 1

    With Application.FileDialog(msoFileDialogOpen)
        .Filters.Clear
        .Filters.Add "Command Separated Values", "*.csv", 1
        '.Filters.Add "Excel 2007", "*.xlsx; *.xlsm; *.xlsa", 2
        '.Filters.Add "Excel 2002-03", "*.xls", 3
        .AllowMultiSelect = True
        .Show

    For SelectedItemNumber = 1 To .SelectedItems.Count

        If .SelectedItems.Count > 0 Then
            Workbooks.Open .SelectedItems(SelectedItemNumber)
            Set wkbSourceBook = ActiveWorkbook
            Set rngSourceRange1 = ActiveCell.Offset(1, 0)
            Set rngSourceRange2 = ActiveCell.Offset(1, 6)

            For counter = 0 To 300

            Columns("H:H").NumberFormat = "0.00"

            'Highest = Application.WorksheetFunction.Max(Range("H1:H300"))

                If Highest <= ActiveCell.Offset(h1, h2).Value Then
                    Highest = ActiveCell.Offset(h1, h2).Value
                End If

                h1 = h1 + 1

            Next

            wkbCrntWorkBook.Activate

            Set rngDestination1 = ActiveCell.Offset(1, 0)
            Set rngDestination2 = ActiveCell.Offset(1, 1)

            ActiveCell.Offset(1, 2).Value = Highest

            For intColumnCount = 1 To rngSourceRange1.Columns.Count

                If intColumnCount = 1 Then
                    rngSourceRange1.Columns(intColumnCount).Copy rngDestination1
                Else
                    rngSourceRange1.Columns(intColumnCount).Copy rngDestination1.End(xlDown).End(xlDown).End(xlUp).Offset(1)
                End If
            Next

            For intColumnCount = 1 To rngSourceRange2.Columns.Count

                If intColumnCount = 1 Then
                    rngSourceRange2.Columns(intColumnCount).Copy rngDestination2
                Else
                    rngSourceRange2.Columns(intColumnCount).Copy rngDestination2.End(xlDown).End(xlDown).End(xlUp).Offset(1)
                End If
            Next

            ActiveCell.Offset(1, 0).Select

            wkbSourceBook.Close False
        End If

    Next SelectedItemNumber

    End With

    YesOrNoAnswerToMessageBox = MsgBox("Continue?", vbYesNo)

    Loop While YesOrNoAnswerToMessageBox = vbYes


    Set wkbCrntWorkBook = Nothing
    Set wkbSourceBook = Nothing
    Set rngSourceRange1 = Nothing
    Set rngSourceRange2 = Nothing
    Set rngDestination1 = Nothing
    Set rngDestination2 = Nothing
    YesOrNoAnswerToMessageBox = Empty
    SelectedItemNumber = Empty
    Highest = Empty
    counter = Empty
    h1 = Empty
    h2 = Empty
    intColumnCount = Empty

End Function

回答by Lokerim

To remove this question from the Unanswered Questions list, I'm going to answer this in "community wiki" style to avoid taking credit for other people's work.

为了从“未回答的问题”列表中删除这个问题,我将以“社区维基”的风格回答这个问题,以避免因其他人的工作而受到赞扬。

As Tim Williams answered in the comments, part of the answer is to use Longvariables instead of Integervariables to make sure that you don't exceed the allowable variable value when running through large numbers of iterations.

正如 Tim Williams 在评论中回答的那样,部分答案是使用Long变量而不是Integer变量来确保在运行大量迭代时不会超过允许的变量值。

The other part of the answer, as made clear by the last comment from user1828786, is to scan your code for logical errors to make sure that your counter variables are being reset for each loop you make.

答案的另一部分,正如 user1828786 的最后一条评论所表明的那样,是扫描您的代码以查找逻辑错误,以确保您的计数器变量在您进行的每个循环中都被重置。