运行时错误“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
Run time error '6' OverFlow (Excel VBA)
提问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 Long
variables instead of Integer
variables 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 的最后一条评论所表明的那样,是扫描您的代码以查找逻辑错误,以确保您的计数器变量在您进行的每个循环中都被重置。