VBA 매크로가 32000행 후 충돌합니다.

VBA 매크로가 32000행 후 충돌합니다.

3열의 셀 값을 기준으로 한 워크시트에서 다른 워크시트로 행을 복사하는 VBA 매크로가 있습니다.매크로는 작동하지만 32767행에 도달하면 작동하지 않습니다.이 행에는 공식이나 특수 형식이 없습니다.게다가, 저 행을 뺐지만, 여전히 그 행 번호에 충돌합니다.이것은 엑셀의 제한 사항입니까?워크시트에 처리 중인 43000개가 있습니다.

따라서 매크로에 어떤 문제가 있는지, 워크시트의 끝에 도달하려면 어떻게 해야 하는지 묻습니다.

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."

VBA 'Int' 유형은 부호 있는 16비트 필드이므로 -32768 ~ +32767 사이의 값만 유지할 수 있습니다.이러한 변수를 '길이'로 변경합니다. '길이'는 서명된 32비트 필드이며 -2147483648 ~ +2147483647의 값을 유지할 수 있습니다.엑셀로 충분할 겁니다.;)

이것은 정수 문제처럼 들립니다.

정수 및 긴 데이터 유형에는 양수 또는 음수 값이 모두 포함될 수 있습니다.크기가 다른 점은 다음과 같습니다.정수 변수는 -32,768과 32,767 사이의 값을 포함할 수 있는 반면, 긴 변수는 -2,147,483,648에서 2,147,483,647 사이의 값을 포함할 수 있습니다.

그런데 어떤 버전을 사용하고 있습니까?이유:

전통적으로 VBA 프로그래머는 적은 메모리를 필요로 하기 때문에 작은 숫자를 저장하기 위해 정수를 사용했습니다.그러나 최근 버전에서는 정수 유형으로 선언된 경우에도 VBA가 모든 정수 값을 Long 유형으로 변환합니다.따라서 Integer 변수를 사용하면 성능 면에서 더 이상 이점이 없습니다. 실제로 VBA가 변수를 변환할 필요가 없기 때문에 Long 변수가 약간 더 빠를 수 있습니다.

이 정보는 MSDN에서 직접 제공됩니다.

갱신하다

첫 번째 댓글도 읽어주세요!MSDN 정보를 잘못 해석하고 있었습니다!

MSDN은 오해의 소지가 있습니다. VBA는 정수를 길게 변환하지 않습니다.이 표지에서 CPU는 정수를 긴 값으로 변환하고 산술을 수행한 다음 결과 긴 값을 다시 정수로 변환합니다.따라서 VBA 정수는 여전히 32K보다 큰 숫자를 수용할 수 없습니다 – Charles Williams

정수 대행을 늘리는 대신 각 행에 대해 사용하여 긴 문제를 해결합니다.범위 선택을 피하는 것이 일반적으로 더 빠릅니다.다음은 예입니다.

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

언급URL : https://stackoverflow.com/questions/10558540/vba-macro-crashes-after-32000-rows