VBA 宏在 32000 行后崩溃
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10558540/
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
VBA Macro crashes after 32000 rows
提问by Andy5
I have a VBA macro that copies rows from one worksheet into another based upon finding values in cells in 3 columns. The macro works, but crashes when it reaches row 32767. There are no formulas or special formatting in this row. Further, I have taken that row out, but it still crashes on that row number. Is this a limitation in excel? There are some 43000 in the worksheet that is being process
我有一个 VBA 宏，它根据在 3 列中的单元格中查找值将行从一个工作表复制到另一个工作表中。该宏有效，但在到达第 32767 行时崩溃。该行中没有公式或特殊格式。此外，我已经删除了该行，但它仍然在该行号上崩溃。这是excel的限制吗？正在处理的工作表中有大约 43000 个
Therefore, I ask what is wrong with my macro and how I can get it reach the end of the worksheet:
因此，我问我的宏有什么问题以及如何让它到达工作表的末尾：
Dim LSearchRow As Integer
Dim LCopyToRow As Integer
Dim wks As Worksheet
On Error GoTo Err_Execute
For Each wks In Worksheets
对于工作表中的每个周
LSearchRow = 4
LCopyToRow = 4
ThisWorkbook.Worksheets.Add After:=Worksheets(Worksheets.Count)
Set wksCopyTo = ActiveSheet
wks.Rows(3).EntireRow.Copy wksCopyTo.Rows(3)
While Len(wks.Range("A" & CStr(LSearchRow)).Value) > 0
If wks.Range("AB" & CStr(LSearchRow)).Value = "Yes" And wks.Range("AK" & CStr(LSearchRow)).Value = "Yes" And wks.Range("BB" & CStr(LSearchRow)).Value = "Y" Then
Rows(CStr(LSearchRow) & ":" & CStr(LSearchRow)).Select
Selection.Copy
wksCopyTo.Select
wksCopyTo.Rows(CStr(LCopyToRow) & ":" & CStr(LCopyToRow)).Select
wksCopyTo.Paste
'Move counter to next row
LCopyToRow = LCopyToRow + 1
'Go back to Sheet1 to continue searching
wks.Select
End If
LSearchRow = LSearchRow + 1
Wend
Application.CutCopyMode = False
Range("A3").Select
MsgBox "All matching data has been copied."
Next wks
Exit Sub
Err_Execute:
MsgBox "An error occurred."
Please help!
请帮忙！
回答by Eight-Bit Guru
The VBA 'Int' type is a signed 16-bit field so it can only hold values from -32768 to +32767. Change those variables to 'Long', which is a signed 32-bit field and can hold values from -2147483648 to +2147483647. Should be enough for Excel. ;)
VBA 'Int' 类型是一个有符号的 16 位字段，因此它只能保存从 -32768 到 +32767 的值。将这些变量更改为“Long”，这是一个有符号的 32 位字段，可以保存从 -2147483648 到 +2147483647 的值。Excel 应该够了。;)
回答by Neysor
This sounds like an integer Problem
这听起来像一个整数问题
The Integerand Long data types can both hold positive or negative values. The difference between them is their size: Integer variables can hold values between -32,768 and 32,767, while Long variables can range from -2,147,483,648 to 2,147,483,647.
该整数和长数据类型既可以保持正值或负值。它们之间的区别在于它们的大小：整数变量可以保存-32,768 和 32,767之间的值，而 Long 变量的范围可以从 -2,147,483,648 到 2,147,483,647。
But which version are you using? Because:
但是您使用的是哪个版本？因为：
Traditionally, VBA programmers have used integers to hold small numbers, because they required less memory. In recent versions, however, VBA converts all integer values to type Long, even if they are declared as type Integer.Therefore, there is no longer a performance advantage to using Integer variables; in fact, Long variables might be slightly faster because VBA does not have to convert them.
传统上，VBA 程序员使用整数来保存小数，因为它们需要较少的内存。然而，在最近的版本中，VBA 将所有整数值转换为 Long 类型，即使它们被声明为 Integer 类型。因此，使用整数变量不再有性能优势；事实上，Long 变量可能会稍微快一点，因为 VBA 不必转换它们。
This Information is directly from MSDN
此信息直接来自MSDN
UPDATE
更新
Please also read the first comment! I was interpreting the MSDN Information the wrong way!
也请阅读第一条评论！我以错误的方式解释了 MSDN 信息！
Thats MSDN being misleading: VBA does not itself convert Integer to Long. Under the covers the CPU converts integer to long , does the arithmetic and then converts the resulting long back to integer. So VBA integers still cannot hold numbers larger than 32K – Charles Williams
那是 MSDN 误导：VBA 本身不会将整数转换为长整数。在幕后，CPU 将 integer 转换为 long ，进行算术运算，然后将得到的 long 转换回 integer。所以 VBA 整数仍然不能容纳大于 32K 的数字——查尔斯·威廉姆斯
回答by Dick Kusleika
You can avoid the Integer vs. Long issue by using a For Each rather than incrementing rows. For Each is generally faster, as is avoiding Selecting Ranges. Here's an example:
您可以通过使用 For Each 而不是递增行来避免 Integer 与 Long 问题。For Each 通常更快，避免选择范围也是如此。下面是一个例子：
Sub CopySheets()
Dim shSource As Worksheet
Dim shDest As Worksheet
Dim rCell As Range
Dim aSheets() As Worksheet
Dim lShtCnt As Long
Dim i As Long
Const sDESTPREFIX As String = "dest_"
On Error GoTo Err_Execute
For Each shSource In ThisWorkbook.Worksheets
lShtCnt = lShtCnt + 1
ReDim Preserve aSheets(1 To lShtCnt)
Set aSheets(lShtCnt) = shSource
Next shSource
For i = LBound(aSheets) To UBound(aSheets)
Set shSource = aSheets(i)
'Add a new sheet
With ThisWorkbook
Set shDest = .Worksheets.Add(, .Worksheets(.Worksheets.Count))
shDest.Name = sDESTPREFIX & shSource.Name
End With
'copy header row
shSource.Rows(3).Copy shDest.Rows(3)
'loop through the cells in column a
For Each rCell In shSource.Range("A4", shSource.Cells(shSource.Rows.Count, 1).End(xlUp)).Cells
If Not IsEmpty(rCell.Value) And _
rCell.Offset(0, 27).Value = "Yes" And _
rCell.Offset(0, 36).Value = "Yes" And _
rCell.Offset(0, 53).Value = "Yes" Then
'copy the row
rCell.EntireRow.Copy shDest.Range(rCell.Address).EntireRow
End If
Next rCell
Next i
MsgBox "All matching data has been copied."
Err_Exit:
'do this stuff even if an error occurs
On Error Resume Next
Application.CutCopyMode = False
Exit Sub
Err_Execute:
MsgBox "An error occurred."
Resume Err_Exit
End Sub
