Lỗi Run-time Error '6': Over flow

[cudo38]

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.

 

[befaint]

Sub ganSourceListbox()
With Worksheets("DU LIEU").Range("A6")
MaxCol = 100
MaxRow = Cells(Rows.Count, .Column).End(xlUp).Row ' = 1033
ArrayData = .Resize(MaxRow, MaxCol).Value ' <--- ngỏm củ tỏi ở đây vì dung lượng RAM không dủ
End With
ReDim TempArr(1 To MaxRow, 1 To MaxCol)
Me.lstDanhSachData.List = ArrayData
End Sub

 

[cudo38]

Sub ganSourceListbox()
With Worksheets("DU LIEU").Range("A6")
MaxCol = 100
MaxRow = Cells(Rows.Count, .Column).End(xlUp).Row ' = 1033
ArrayData = .Resize(MaxRow, MaxCol).Value ' <--- ngỏm củ tỏi ở đây vì dung lượng RAM không dủ
End With
ReDim TempArr(1 To MaxRow, 1 To MaxCol)
Me.lstDanhSachData.List = ArrayData
End Sub

Đoạn này phải sửa như thế nào ạ? Em cảm ơn!

 

[huhumalu]

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

 

[befaint]

Value2

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?

 

[ptm0412]

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?

Được chứ. Lỗi đâu thì triệt đó: Thiếu ram thì thêm ram. Tuy nhiên sẽ có những trường hợp:
- Máy công ty, và công ty không muốn
- Máy nhà/ máy công ty nhưng phần cứng không thể nâng cấp: Hết khe cắm, còn khe nhưng Ram đời cũ quá không ai bán (DD1, DD2 chẳng hạn, chưa nói tới SD)
- Không đủ khả năng mua máy mới
---------
Máy của ai mà chạy được code này thì đừng sửa code, vì không có điều kiện để test xem mình sửa xong trả cho tác giả có còn thiếu hay không. Ví dụ:
Giả sử dùng Value2 thay cho Value sẽ giảm 1 nửa (có phóng đại), máy có ram 4Gb chạy được, nhưng biết đâu máy tác giả chỉ có 2 Gb? hay chỉ 512Mb?
-----
Máy tôi 16Gb ram, cũng bị lỗi. Tôi giảm 100 cột xuống 48 (số cột thực), vẫn chưa hết lỗi.
Để nguyên 100 cột, thay Value2, hết lỗi. Xem mức Memory đã sử dụng thì từ 5 đến 8Gb. Không dám ho vì sợ máy của tác giả có 4GB trở xuống.

Đó là chưa nói, dữ liệu mẫu mới có 1033 dòng, xóa bớt còn 25. Dữ liệu thực nhiều hơn hoặc tăng thêm thì không biết ra sao

 

[huhumalu]

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.

 

[HieuCD]

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.

Đâ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

 

[befaint]

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.

 

[VetMini]

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.

 

[befaint]

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ể ổ.

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

 

[ptm0412]

Bảng trong file bài #1 dính lỗi ở ô X36.

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.

 

[VetMini]

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

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)

 

[cudo38]

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.

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

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

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

Bài đã được tự động gộp: 16/11/22

Được chứ. Lỗi đâu thì triệt đó: Thiếu ram thì thêm ram. Tuy nhiên sẽ có những trường hợp:
- Máy công ty, và công ty không muốn
- Máy nhà/ máy công ty nhưng phần cứng không thể nâng cấp: Hết khe cắm, còn khe nhưng Ram đời cũ quá không ai bán (DD1, DD2 chẳng hạn, chưa nói tới SD)
- Không đủ khả năng mua máy mới
---------
Máy của ai mà chạy được code này thì đừng sửa code, vì không có điều kiện để test xem mình sửa xong trả cho tác giả có còn thiếu hay không. Ví dụ:
Giả sử dùng Value2 thay cho Value sẽ giảm 1 nửa (có phóng đại), máy có ram 4Gb chạy được, nhưng biết đâu máy tác giả chỉ có 2 Gb? hay chỉ 512Mb?
-----
Máy tôi 16Gb ram, cũng bị lỗi. Tôi giảm 100 cột xuống 48 (số cột thực), vẫn chưa hết lỗi.
Để nguyên 100 cột, thay Value2, hết lỗi. Xem mức Memory đã sử dụng thì từ 5 đến 8Gb. Không dám ho vì sợ máy của tác giả có 4GB trở xuống.

Đó là chưa nói, dữ liệu mẫu mới có 1033 dòng, xóa bớt còn 25. Dữ liệu thực nhiều hơn hoặc tăng thêm thì không biết ra sao

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

ArrayData = .Resize(MaxRow, MaxCol).Value2

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

 

[ptm0412]

Em đã sửa ...

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.