Lỗi Run-time Error ‘6’: Over flow

Chia sẻ bởi:hands

★★★★★

‹ Xin công thức tính tổng, trùng nhau... Hàm IF nhiều điều kiện thay đổi ›

Quảng cáo

Kính mong anh chị trên Diễn đàn giúp đỡ Em về lỗi File Excel VBA Nhân sự (File em đính kèm ạ). Cụ thể: Nhập thêm mới Người thứ 31 trở về sau là bị lỗi như trên. File này em tự học hỏi, tham khảo 1 số File và code khác để kết hợp lại.

Sub ganSourceListbox()
    With Worksheets("DU LIEU").Range("A6")
        MaxCol = 100
        MaxRow = Cells(Rows.Count, .Column).End(xlUp).Row
        ReDim ArrayData(1 To MaxRow, 1 To 100)
        ArrayData = .Resize(MaxRow, MaxCol).Value
    End With
    ReDim TempArr(1 To MaxRow, 1 To MaxCol)
    Me.lstDanhSachData.List = ArrayData
End Sub

Trong đó sửa dòng

ArrayData = .Resize(MaxRow, MaxCol).Value

Thành

ArrayData = .Resize(MaxRow, MaxCol).Value2

www.giaiphapexcel.com/diendan/threads/l%E1%BB%97i-run-time-error-6-over-flow.163017/

Không sửa code tẹo nào (không thay đổi maxCol, không dùng Value2), cấu trúc bảng dữ liệu giữ nguyên, thì có cách nào khắc phục được không bạn?

Do cũng đi mò mẫn ra thôi, có dùng

For i = 1 To 100
            MaxCol = i 'MaxCol = 20
            MaxRow = 20000
            Arr = .Range("A6").Resize(MaxRow, MaxCol).Value
        Next i

để nạp thử. Thấy lỗi tràn khi i = 24
Nên thử giải pháp thì tách ra 5 trường khác nhau

MaxCol = 20
MaxRow = 2000
Arr1 = .Range("A3").Resize(MaxRow, MaxCol).Value
Arr2 = .Range("V3").Resize(MaxRow, MaxCol).Value
Arr3 = .Range("AP3").Resize(MaxRow, MaxCol).Value
Arr4 = .Range("BJ3").Resize(MaxRow, MaxCol).Value
Arr5 = .Range("CD3").Resize(MaxRow, MaxCol).Value

Thì vẫn tràn …, nên cũng chưa biết có giải pháp nào.
Bản chất nó vẫn đang nạp tạm vào RAM thì hình thức này hay khác nó vẫn bị. Nên lưu trữ kiểu Value2 thì tiết kiệm hơn.

Đâu cần nạp 1 lần 100 cột, chỉ cần khoảng 10 cột và tạo thêm 2 nút lênh qua trái qua phải khi bấm nút lệnh sẽ lấy dữ liệu mới chuyển qua 10 cột kế(thường giữ lại cột số thứ tự và họ tên), các lệnh khác xử lý trên mảng hoặc trực tiếp trên range

Bài #5 nói tới Value2 là gợi ý 50% rồi mà.

Gợi ý tiếp: Chọn cả sheet, rồi clear formats, sau đó chạy code.

Value2 có hể nhanh hơn, mảng có thể nhỏ hơn, nhưng vì nó cốt làm nhanh cho nên có thể không kịp để VBA vét memory. Vì vậy chưa chắc đã giải quyết được chuyện thiếu tràn bộ nhớ.
Mảng có thể không lớn lắm về số lượng nhưng chứa toàn string dài thòn thì sẽ mau bể ổ.

1. Cuối sub nên Erase mấy cái mảng đã dùng xong.
2. Trong code có cái mảng TempData. Không biết để làm gì. Có lẽ là biến toàn cục.

Không phải anh.

Vụ này có một thớt gặp lỗi y chang rồi.
Đó là Range đưa vào Array có cell xảy ra lỗi định dạng Date, cụ thể là cell có giá trị âm hoặc giá trị đủ lớn mà khi đó xảy ra lỗi nếu gán định dạng Date cho cell đó (Cell hiển thị ######…).
Bảng trong file bài #1 dính lỗi ở ô X36.

Value sẽ bao gồm cả định dạng cell, Value2 chỉ lấy giá trị.
Vậy nên em ưu tiên dùng Value2 là ở lý do này, chứ không phải vì tốc độ hay mức chiếm dung lượng bộ nhớ.

Ai mà để ý tới mức đó chứ! Tôi thường xem xét cấu trúc dữ liệu chuẩn hay không, chỉ phát hiện:
– chỉ có 48 cột mà oánh tới 100
– chỉ có 25 dòng dữ liệu mà công thức kéo tới dòng 1033
– dùng 1033 làm MaxCol mà chưa trừ 6

Còn kiểu dữ liệu thì chỉ ngó sơ 10 dòng đầu.

Mảng hầu hết chỉ chứa pointers chỉ đến dữ liệu. Chỉ mấy dạng đơn giản mới chứa trực tiếp.

Tôi nghe có người nói mảng lấy qua Value2 chứa chuỗi static (độ dài xác định) cho nên còn lớn hơn. Mảng lấy qua Value thì chuỗi là 16 bytes, chứa chi tiết dẫn đến chuỗi thật.
(tôi khẳng định là chỉ nghe nói, không thấy dẫn chứng cho nên đừng bảo tôi chứng minh)

Em để 100 cột, bởi vì Em đang tính để thêm các trường về Lương, Phụ cấp, bảo hiểm và một số nội dung khác nữa ạ.
Còn dòng Dữ liệu có công thức để đánh số thứ tự tới dòng 1033 là thừa em quên mất không xóa. Việc đánh số thứ tự tự động đã có trong VBA rồi ạ.

Em đã sửa và không bị lỗi nữa rồi ạ. Em cảm ơn nhiều ạ!

Máy em 4Gb RAM, Em đã sửa thành:

và Không bị lỗi nữa rồi ạ.

Tốt nhất là sửa cả dữ liệu định dạng sai ở X36 và cả sau này. Và nên nhớ Value2 không phải thuốc tiên thuốc thánh trị bá bệnh. Dữ liệu chuẩn mới là điều kiện tiên quyết.