Lỗi VBA trong việc tính toán số quá lớn!

Hoàng Trọng Nghĩa · 9/3/14

Nếu như Excel 2003, ta muốn đếm 1 sheet có bao nhiêu cells, ta mở VBE, Ctrl+G để mở Immediate làm như sau:

?cells.Count

Kết quả: 16,777,216 cells.

Hoặc ta chọn khối toàn bộ sheet và gõ:

?Selection.Count

Kết quả: 16,777,216 cells.

Nhưng với Excel 2007 trở về sau, ta không thể thực hiện được như vậy, bởi nó bị lỗi Runtime Error '6' (Overflow)

=> Anh Bill khi đã tăng lượng cột và lượng hàng trong quá trình tính toán cửa số VBA nó không tính được con số quá lớn:

Mã:

Sub test()
    Dim d As Double
    'Số hàng * Số cột:
    d = 1048576 * 16384
    MsgBox d
End Sub

Với thủ tục trên nó sẽ bị lỗi Overflow.

Trong khi tính toán trong sheet, kết quả: 17,179,869,184 cells.

Như thế, một kinh nghiệm ở đây là nếu chúng ta dùng sự kiện Worksheet_SelectionChange mà chúng ta bẫy lỗi bằng cách:

If Selection.Count > 1 then

Hoặc:

If Target.Count > 1 Then

Thì với việc chọn khối toàn bộ sheet của Excel 2007 trở về sau sẽ phát sinh ra lỗi số 6.

Cho nên, để không mắc lỗi này, chúng ta nên bẫy lỗi thay cho 2 dòng lệnh đó như sau:

If Target.Rows.Count > 1 Or Target.Columns.Count > 1 Then

Làm như thế nó sẽ không phát sinh ra lỗi khi ta chọn toàn bộ sheet nữa, đảm bảo được sự kiện Selection đúng địa chỉ.

Hoàng Trọng Nghĩa · 9/3/14

Sẳn bài này, tôi đố các bạn làm sao ta có thể nhân 2 số này với nhau (chỉ thực hiện trên code) mà MsgBox cho ra kết quả mà không bị lỗi?

Mã:

Sub test2()
    Dim d As Double
    d = 1048576 * 16384
    MsgBox d
End Sub

Phuocam · 9/3/14

Hoàng Trọng Nghĩa đã viết:
Sẳn bài này, tôi đố các bạn làm sao ta có thể nhân 2 số này với nhau (chỉ thực hiện trên code) mà MsgBox cho ra kết quả mà không bị lỗi?

Mã:

Sub test2() Dim d As Double d = 1048576 * 16384 MsgBox d End Sub

Đổi phép chia hết lỗi, nhưng không biết giải thích! Hic!

Mã:

Sub test2()
    Dim d As Double
    d = 1048576 / (1/16384)
    MsgBox d
End Sub

Hoàng Trọng Nghĩa · 9/3/14

phuocam đã viết:
Đổi phép chia hết lỗi, nhưng không biết giải thích! Hic!

Mã:

Sub test2() Dim d As Double d = 1048576 / (1/16384) MsgBox d End Sub

Thật ra cũng có nhiều cách để cho nó ra kết quả, nhưng không phải rắc rối như bạn làm đâu.

Cách thứ 1: Cho chúng trở thành dạng chuỗi: d = "1048576" * "16384"

Cách thứ 2: Bạn tìm tiếp xem! @#!^%

siwtom · 9/3/14

Hoàng Trọng Nghĩa đã viết:
Sẳn bài này, tôi đố các bạn làm sao ta có thể nhân 2 số này với nhau (chỉ thực hiện trên code) mà MsgBox cho ra kết quả mà không bị lỗi?

Mã:

Sub test2() Dim d As Double d = 1048576 * 16384 MsgBox d End Sub

Đã có 1 lần tôi viết trên GPE. Viết lại.

Khi thực hiện phép tính thì luôn có một "vị trị tạm thời" để lưu các kết quả tính trung gian. Khi tính toán kết thúc thì giá trị ở "vị trị tạm thời" kia được trả về, và được gán cho biến nào đó. Biểu thức có thể có 1 phép tính vd. phép nhân, nhưng cũng có thể có nhiều phép tính phức tạp.

Khi ta cộng hay nhân v...v 2 số thì "vị trị tạm thời" được gán cho kiểu sao cho "đủ" cho số lớn nhất. Gán ở đây có nghĩa là chỉ có xyz bai trong bộ nhớ được dùng để lưu kết quả, giá trị. Nếu kiểu vd. là LONG thì chỉ có 4 bai được dùng cho việc này. Tức có thể lưu giá trị tới 2,147,483,647. Thế thôi.

Vậy khi kết quả tính lại "vượt" quá kiểu được gán kia thì sẽ có lỗi Overflow. Số bai được giành cho "việc kia" quá nhỏ.

Cách làm việc với các kết quả lớn: ta phải làm sao để "vị trị tạm thời" được gán cho kiểu đủ để "chứa" kết quả lớn

Ta xét code

Mã:

Sub test()
    Dim d As Double
    d = 1048576 * 16384
    MsgBox d
End Sub

hoặc
Sub test2()
Dim d As Double, a As Long, b As Long
    a = 1048576
    b = 16384
    d = a * b
    MsgBox d
End Sub

Do cả 2 thừa số đều không vượt quá giới hạn của kiểu LONG nên "vị trị tạm thời" được gán cho kiểu LONG. Vậy thì kết quả 17,179,869,184 rõ ràng vượt quá giới hạn của kiểu LONG.

Nhưng code

Mã:

Sub test()
    Dim d As Double
    d = CDbl(1048576) * 16384
    MsgBox d
End Sub

Sub test2()
Dim d As Double, a As Long, b As Long
    a = 1048576
    b = 16384
    d = CDbl(a) * b
    MsgBox d
End Sub

thì chạy mượt. Vì sao? Vì lúc đó 1 thừa số ta xác định có kiểu Double nên "vị trị tạm thời" được gán cho kiểu Double. Mà rõ ràng 17,179,869,184 nằm trong giới hạn của kiểu Double.

Cũng có thể

Mã:

d = 1048576 * CDbl(16384)

hoặc

d = a * CDbl(b)

Cũng có thể

Mã:

d = CDbl(1048576) * CDbl(16384)

hoặc

d = CDbl(a) * CDbl(b)

Nhưng không cần tới mức đó. Chỉ cần ép kiểu 1 thừa số là đủ.

Hoàng Trọng Nghĩa · 9/3/14

siwtom đã viết:
Đã có 1 lần tôi viết trên GPE. Viết lại.

Khi thực hiện phép tính thì luôn có một "vị trị tạm thời" để lưu các kết quả tính trung gian. Khi tính toán kết thúc thì giá trị ở "vị trị tạm thời" kia được trả về, và được gán cho biến nào đó. Biểu thức có thể có 1 phép tính vd. phép nhân, nhưng cũng có thể có nhiều phép tính phức tạp.

Khi ta cộng hay nhân v...v 2 số thì "vị trị tạm thời" được gán cho kiểu sao cho "đủ" cho số lớn nhất. Gán ở đây có nghĩa là chỉ có xyz bai trong bộ nhớ được dùng để lưu kết quả, giá trị. Nếu kiểu vd. là LONG thì chỉ có 4 bai được dùng cho việc này. Tức có thể lưu giá trị tới 2,147,483,647. Thế thôi.

Vậy khi kết quả tính lại "vượt" quá kiểu được gán kia thì sẽ có lỗi Overflow. Số bai được giành cho "việc kia" quá nhỏ.

Cách làm việc với các kết quả lớn: ta phải làm sao để "vị trị tạm thời" được gán cho kiểu đủ để "chứa" kết quả lớn

Ta xét code

Mã:

Sub test() Dim d As Double d = 1048576 * 16384 MsgBox d End Sub hoặc Sub test2() Dim d As Double, a As Long, b As Long a = 1048576 b = 16384 d = a * b MsgBox d End Sub

Do cả 2 thừa số đều không vượt quá giới hạn của kiểu LONG nên "vị trị tạm thời" được gán cho kiểu LONG. Vậy thì kết quả 17,179,869,184 rõ ràng vượt quá giới hạn của kiểu LONG.

Nhưng code

Mã:

Sub test() Dim d As Double d = CDbl(1048576) * 16384 MsgBox d End Sub Sub test2() Dim d As Double, a As Long, b As Long a = 1048576 b = 16384 d = CDbl(a) * b MsgBox d End Sub

thì chạy mượt. Vì sao? Vì lúc đó 1 thừa số ta xác định có kiểu Double nên "vị trị tạm thời" được gán cho kiểu Double. Mà rõ ràng 17,179,869,184 nằm trong giới hạn của kiểu Double.

Cũng có thể

Mã:

d = 1048576 * CDbl(16384) hoặc d = a * CDbl(b)

Cũng có thể

Mã:

d = CDbl(1048576) * CDbl(16384) hoặc d = CDbl(a) * CDbl(b)

Nhưng không cần tới mức đó. Chỉ cần ép kiểu 1 thừa số là đủ.

Cách đơn giản nhất mà em mới phát hiện:

Gán dấu # đằng sau dãy số (1 trong 2 dãy số hoặc cả 2):

Hoặc:

d = 1048576# * 16384

Hoặc:

d = 1048576 * 16384#

Hoặc:

d = 1048576# * 16384#

Nhưng tốt nhất gán vào số lớn thậm chí vài ngàn tỷ cũng chả sao!

siwtom · 9/3/14

Hoàng Trọng Nghĩa đã viết:
Cách đơn giản nhất mà em mới phát hiện:

Gán dấu # đằng sau dãy số (1 trong 2 dãy số hoặc cả 2):

Hoặc:

d = 1048576# * 16384

Hoặc:

d = 1048576 * 16384#

Hoặc:

d = 1048576# * 16384#

Nhưng tốt nhất gán vào số lớn thậm chí vài ngàn tỷ cũng chả sao!

Thì # nó chính là Double chứ còn gì nữa?

Chỉ có điều tôi không thích kiểu

Mã:

Dim a#
...
d = a# + b

Vì nếu bỏ lập trình vài năm thì khi "học lại" đọc code trên thì "con người" vò đầu bứt tai không nhớ nó là cái gì.

Còn

Mã:

Dim a As Double
...
d = CDbl(a) + b

Lúc đó chưa kịp suy nghĩ nó là gì thì đã hiểu.

Cũng chính vì thế mà theo tôi thói quen kiểu

Mã:

myRange.End(3)

hay 

DoSometing 12

là thói quen cực xấu. Code rất tối, khó hiểu, không phải ai cũng hiểu. Có phải ai cũng làm việc với Excel 8 x 365 đâu. Mà cái đầu không phải nơi nhồi nhét đủ thứ, học thuộc lòng.

Với tôi phải là

Mã:

Private Const WM_LBUTTONDOWN      = &H0201
...

myRange.End(xlUp)

hay 

DoSometing WM_LBUTTONDOWN

VetMini · 9/3/14

Bình thường, để diễn tả một số literal dạng số thực, tôi luôn luôn cho nó một dấu chấm
d = 1048576. * 16384

Khổ nổi, cái VBE này có tật vô duyên là nếu sau số không còn gì nữa (hoặc 0) thì nó tự động đổi thành #
d = 1048576# * 16384
(Nếu gõ d = 1048576.1 * 16384 thì nó không đổi)

Đây là một trong những điều đáng ghét của VBE. Hình như đối với nó cái dấu # trông dễ nhìn.

ndu96081631 · 10/3/14

Cu Nghĩa này làm bộ "đố đố" để học hỏi chiêu của người khác đấy mà
Rành cu này quá
Ẹc... Ẹc...
(thật ra vấn đề này đã được nói đến từ lâu rồi, có điều là cu không để ý nên bây giờ lại.. đố)

Hoàng Trọng Nghĩa · 10/3/14

Thật ra là khi chưa biết thì khó, chứ biết rồi thì đó cũng là quy định của VBA, chẳng hạn dạng ngày tháng thì nó quy định phải rào trước và sau dấu # ở 2 đầu:

Dim MyDate As Date

MyDate = #9/14/1976#

Và tương tự như vậy đối với dạng số lớn hơn LONG data type:

Dim MyNum As Double

MyNum = 123456789123456#

Chính vì thế việc nó quy định như thế thì ta cứ thế mà nhớ, chứ nhớ vòng vèo làm cái gì cho mệt!

siwtom · 10/3/14

Hoàng Trọng Nghĩa đã viết:
Thật ra là khi chưa biết thì khó, chứ biết rồi thì đó cũng là quy định của VBA, chẳng hạn dạng ngày tháng thì nó quy định phải rào trước và sau dấu # ở 2 đầu:

Dim MyDate As Date

MyDate = #9/14/1976#

Và tương tự như vậy đối với dạng số lớn hơn LONG data type:

Dim MyNum As Double

MyNum = 123456789123456#

Chính vì thế việc nó quy định như thế thì ta cứ thế mà nhớ, chứ nhớ vòng vèo làm cái gì cho mệt!

Thế nhớ cái đỏ đỏ không là nhớ à?

Lỗi VBA trong việc tính toán số quá lớn!

Hoàng Trọng Nghĩa

Chuyên gia GPE

Hoàng Trọng Nghĩa

Chuyên gia GPE

Phuocam

Thành viên mới

Hoàng Trọng Nghĩa

Chuyên gia GPE

siwtom

Thành viên gắn bó

Hoàng Trọng Nghĩa

Chuyên gia GPE

siwtom

Thành viên gắn bó

VetMini

Ăn cùng góc phố

ndu96081631

Huyền thoại GPE

Hoàng Trọng Nghĩa

Chuyên gia GPE

siwtom

Thành viên gắn bó

Bài viết mới nhất

Facebook

Group

Thành viên có số lượng bài viết cao nhất tháng

Thành viên có điểm tương tác cao nhất tháng