Vấn đề về mảng Ubound ?

Thanh Bình PV · 9/1/20

Em đang tập về mảng Ubound. Em có lấy 1 code mẫu trên diễn đàn về tùy biến theo ý mình.
Nhưng khi em viết lại theo kiểu khác ( thay tên dim ) thì code không chạy và bị báo lỗi ở mảng Ubound.
Em tìm mãi mà vẫn không ra lý do. Anh chị giúp em với ạ.

Mã:

Sub loi()
Dim sh As Worksheet
Dim i, a, b, ls As Long
Dim dic As Object, ts(1 To 1000, 1 To 9)
Dim dk, dia As String
Set dic = CreateObject("scripting.dictionary")
For Each sh In ThisWorkbook.Worksheets
    If InStr(1, sh.Name, "Sheet") Then
        dia = sh.Range("A5:C500").Value
            For i = 1 To UBound(dia)
            If dia(i, 1) <> Empty Then
                    dk = UCase(dia(i, 1))
                    If Not dic.exists(dk) Then
                        a = a + 1
                        dic.Add dk, a
                    ts(a, 1) = dia(i, 1)             
                    End If         
                        b = dic.Item(dk)
                ts(b, 3) = ts(b, 3) + dia(i, 3)           
            End If
        Next i
    End If
Next
With Sheets("Sheet 1")
    ls = .Range("J" & Rows.Count).End(xldowm).Row
    .Range("J2:S" & ls).ClearContents
    If a Then .Range("J2:V2").Resize(a).Value = ts
End With
End Sub

befaint · 9/1/20

Thanh Bình PV đã viết:
Dim i, a, b, ls As Long

Tình trạng này căng lắm. Không chỉnh ngay từ đâu về sau bị lủng ngay.

Thanh Bình PV đã viết:
Dim dk, dia As String

PHP:

Dim dk As String, dia As variant

Hoặc:

PHP:

Dim dk As String, dia() As String

Thanh Bình PV · 9/1/20

befaint đã viết:
Tình trạng này căng lắm. Không chỉnh ngay từ đâu về sau bị lủng ngay.

PHP:

Dim dk As String, dia As variant

Hoặc:

PHP:

Dim dk As String, dia() As String

Mà em có chạy bên module 2 như code dưới thì vẫn chạy tốt.. anh giải thích giúp em vì sao được không ạ. Em cảm ơn.

Mã:

Sub chay_ok()
Dim sh As Worksheet
   
     Dim j, k, m, ls As Long
     Dim dir As Object, kqq(1 To 1000, 1 To 9)
     Dim pat, dkk As String
     Set dir = CreateObject("scripting.dictionary")
     For Each sh In ThisWorkbook.Worksheets
        If InStr(1, sh.Name, "Sheet") Then
       
            pat = sh.Range("A5:C500").Value
            For j = 1 To UBound(pat)
                If pat(j, 1) <> Empty Then
                   dkk = UCase(pat(j, 1))
                   If Not dir.exists(dkk) Then
                      k = k + 1
                      dir.Add dkk, k
                      kqq(k, 1) = pat(j, 1)
                   End If
                      m = dir.Item(dkk)
                      kqq(m, 3) = kqq(m, 3) + pat(j, 3)
                End If
            Next j
       End If
   Next
   With Sheets("Sheet 2")
        ls = .Range("N" & Rows.Count).End(xlUp).Row
        If ls > 1 Then .Range("N2:S" & ls).ClearContents
        If k Then .Range("N2:S2").Resize(k).Value = kqq
   End With
End Sub

ppc0312 · 9/1/20

Túm lại: Tìm hiểu lại khái niệm biến (variable) và Khai báo biến, Kiểu của biến

VetMini · 9/1/20

Thanh Bình PV đã viết:
Mà em có chạy bên module 2 như code dưới thì vẫn chạy tốt.. anh giải thích giúp em vì sao được không ạ. Em cảm ơn.

Mã:

Sub chay_ok() Dim sh As Worksheet Dim j, k, m, ls As Long Dim dir As Object, kqq(1 To 1000, 1 To 9) Dim pat, dkk As String

Khai báo tét bét hết trơn. Sỡ dĩ "chạy tốt" là vì nó chưa gặp lúc để nằm vạ. Chiếc xe mòn bánh vẫn "chạy tốt"; lúc cần thắng gấp hoặc cua nhanh vào trời mưa đường trơn trợt thì mới biết đá vàng.
VBA không có chuyện một kiểu khai chung cho nhiều biến. Mỗi biến phải khai kiểu riêng, dẫu cùng một dòng. Biến nào không có khai kiểu thì VBA mặc định kiểu Variant.
Nếu bạn chưa nắm vững luật khai báo thì tốt hơn hết là mỗi biến một dòng Dim.

batman1 · 9/1/20

Thanh Bình PV đã viết:

Em đang tập về mảng Ubound. Em có lấy 1 code mẫu trên diễn đàn về tùy biến theo ý mình.
Nhưng khi em viết lại theo kiểu khác ( thay tên dim ) thì code không chạy và bị báo lỗi ở mảng Ubound.
Em tìm mãi mà vẫn không ra lý do. Anh chị giúp em với ạ.

Mã:

Sub loi()
Dim sh As Worksheet
Dim i, a, b, ls As Long
Dim dic As Object, ts(1 To 1000, 1 To 9)
Dim dk, dia As String
Set dic = CreateObject("scripting.dictionary")
For Each sh In ThisWorkbook.Worksheets
    If InStr(1, sh.Name, "Sheet") Then
        dia = sh.Range("A5:C500").Value
            For i = 1 To UBound(dia)
            If dia(i, 1) <> Empty Then
                    dk = UCase(dia(i, 1))
                    If Not dic.exists(dk) Then
                        a = a + 1
                        dic.Add dk, a
                    ts(a, 1) = dia(i, 1)          
                    End If      
                        b = dic.Item(dk)
                ts(b, 3) = ts(b, 3) + dia(i, 3)        
            End If
        Next i
    End If
Next
With Sheets("Sheet 1")
    ls = .Range("J" & Rows.Count).End(xldowm).Row
    .Range("J2:S" & ls).ClearContents
    If a Then .Range("J2:V2").Resize(a).Value = ts
End With
End Sub

Không chỉ Ubound mà còn nhiều chỗ sai.

Mã:

dia = sh.Range("A5:C500").Value

Vế phải trả về 1 mảng giá trị có 496 dòng và 3 cột, vế trái là chuỗi (Dim dia As String). Không thể nhập mảng giá trị vào chuỗi được.

Mã:

For i = 1 To UBound(dia)

dia là chuỗi chứ có phải là mảng đâu mà "tuồn" nó vào hàm Ubound?
Tương tự

Mã:

If dia(i, 1) <> Empty Then
...
dk = UCase(dia(i, 1))
...
ts(a, 1) = dia(i, 1)
...
ts(b, 3) = ts(b, 3) + dia(i, 3)

cũng sai vì dia không là mảng.

Mã:

ls = .Range("J" & Rows.Count).End(xldowm).Row

Không phải là xldowm mà là xldown

Nếu sửa thành

Mã:

ls = .Range("J" & Rows.Count).End(xlDown).Row

thì không sai nhưng tốn điện nước. Với code đó thì luôn có ls = 1048576 (tập tin Excel >= 2007 có 1048576 dòng), vậy tính làm gì cho phí công. Còn nếu muốn tìm dòng cuối có dữ liệu ở cột J thì phài dùng xlUp. Tức xuất phát từ ô cuối cùng ở cột J và đi lên (xlUp) chứ không phải đi xuống (xlDown), vì đã là "đáy" rồi thì còn chỗ đâu để mà đi xuống. Lớp bùn không tính.

Thanh Bình PV · 10/1/20

VetMini đã viết:
Khai báo tét bét hết trơn. Sỡ dĩ "chạy tốt" là vì nó chưa gặp lúc để nằm vạ. Chiếc xe mòn bánh vẫn "chạy tốt"; lúc cần thắng gấp hoặc cua nhanh vào trời mưa đường trơn trợt thì mới biết đá vàng.
VBA không có chuyện một kiểu khai chung cho nhiều biến. Mỗi biến phải khai kiểu riêng, dẫu cùng một dòng. Biến nào không có khai kiểu thì VBA mặc định kiểu Variant.
Nếu bạn chưa nắm vững luật khai báo thì tốt hơn hết là mỗi biến một dòng Dim.

Em cảm ơn ạ.

batman1 đã viết:
Không chỉ Ubound mà còn nhiều chỗ sai.

Mã:

dia = sh.Range("A5:C500").Value

Vế phải trả về 1 mảng giá trị có 496 dòng và 3 cột, vế trái là chuỗi (Dim dia As String). Không thể nhập mảng giá trị vào chuỗi được.

Mã:

For i = 1 To UBound(dia)

dia là chuỗi chứ có phải là mảng đâu mà "tuồn" nó vào hàm Ubound?
Tương tự

Mã:

If dia(i, 1) <> Empty Then ... dk = UCase(dia(i, 1)) ... ts(a, 1) = dia(i, 1) ... ts(b, 3) = ts(b, 3) + dia(i, 3)

cũng sai vì dia không là mảng.

Mã:

ls = .Range("J" & Rows.Count).End(xldowm).Row

Không phải là xldowm mà là xldown

Nếu sửa thành

Mã:

ls = .Range("J" & Rows.Count).End(xlDown).Row

thì không sai nhưng tốn điện nước. Với code đó thì luôn có ls = 1048576 (tập tin Excel >= 2007 có 1048576 dòng), vậy tính làm gì cho phí công. Còn nếu muốn tìm dòng cuối có dữ liệu ở cột J thì phài dùng xlUp. Tức xuất phát từ ô cuối cùng ở cột J và đi lên (xlUp) chứ không phải đi xuống (xlDown), vì đã là "đáy" rồi thì còn chỗ đâu để mà đi xuống. Lớp bùn không tính.

Rất cảm ơn anh đã giúp em giải đáp thắc mắc ạ. Em cũng đã điều chỉnh được rồi ạ.
Mà em thắc mắc nếu sửa xlDown thành xlUp trường hợp dữ liệu em muốn xóa dữ liệu ở J2:S tới dòng cuối :
1. Trường hợp dữ liệu dòng cuối của em nằm ở cột J2 thì sẽ xóa hết.
2. Trường hợp dữ liệu dòng cuối của em nằm ở cột khác ngoài cột J2 thì không xóa hết dữ liệu.
=> Vậy mình có cách nào để xóa dữ liệu tới dòng cuối trong phạm vi J2:S không ạ. Như em thấy dùng xlDown cũng thấy dư nhưng không biết cách nào khác cả.
Em cảm ơn.

batman1 · 10/1/20

Thanh Bình PV đã viết:
Em cảm ơn ạ.

Rất cảm ơn anh đã giúp em giải đáp thắc mắc ạ. Em cũng đã điều chỉnh được rồi ạ.
Mà em thắc mắc nếu sửa xlDown thành xlUp trường hợp dữ liệu em muốn xóa dữ liệu ở J2:S tới dòng cuối :
1. Trường hợp dữ liệu dòng cuối của em nằm ở cột J2 thì sẽ xóa hết.
2. Trường hợp dữ liệu dòng cuối của em nằm ở cột khác ngoài cột J2 thì không xóa hết dữ liệu.
=> Vậy mình có cách nào để xóa dữ liệu tới dòng cuối trong phạm vi J2:S không ạ. Như em thấy dùng xlDown cũng thấy dư nhưng không biết cách nào khác cả.
Em cảm ơn.

Thường thì trong nhiều cột ta biết chắc chắn 100% là có ít nhất 1 cột cụ thể sẽ có hết dữ liệu, tức cột đó luôn có dữ liệu tới dòng cuối.
Ví dụ trong bảng thi các môn thì cột họ tên chắc chắn có dữ liệu tới dòng cuối. Trong khi đó cột vd. điểm thi Toán, Lý, Hóa ... chưa chắc có dòng cuối, vì có thể học sinh cuối cùng thi Toán, Hóa nhưng nghỉ ốm hôm thi Lý nên cột Lý không có dòng cuối.

Bảng sản phẩm thì cột Mã sản phẩm luôn chứa dòng cuối.

Khi ta biết chắc chắn 1 cột cụ thể luôn chứa dòng cuối thì tìm theo cột đó thôi. Vd. ta xét các cột J-S và biết cột O chắc chắn chứa dòng cuối thì

Mã:

Sub test1()
Dim lastRow As Long
    lastRow = Sheet1.Range("O" & Rows.Count).End(xlUp).Row
    If lastRow >= 2 Then Sheet1.Range("J2:S" & lastRow).ClearContents
End Sub

Khi không thể chắc chắn cột nào luôn có dòng cuối thì có thể dùng phương thức FIND của đối tượng RANGE.
Trong vd. dưới đây ta xét vùng J2:S<dòng cuối cùng của trang tinh>. Ta tìm dòng cuối của vùng này.
Nếu cell_ = Nothing thì có nghĩa là vùng trên chứa toàn ô trống. Chỉ vùng trên - vùng đang xét thôi, vì J1:S1 hoàn toàn có thể có ô <> trống. Nếu muốn tìm dòng cuối trong vùng các cột J-S, tức không giới hạn từ dòng 2, thì vùng cần xét là J1:S<dòng cuối cùng của trang tinh>

Mã:

Sub test2()
Dim cell_ As Range
    With Sheet1.Range("J2:S" & Rows.Count)
        Set cell_ = .Find("*", .Cells(1), xlFormulas, xlPart, xlByRows, xlPrevious)
    End With
    If cell_ Is Nothing Then
        MsgBox "Cac cot J-S tu dong thu hai tro xuong khong co du lieu"
    Else
        MsgBox "Dong cuoi cung co du lieu la " & cell_.Row
'        xoa du lieu
        Sheet1.Range("J2:S" & cell_.Row).ClearContents
    End If
End Sub

Tất nhiên bạn có thể làm theo cách khác. Bạn có thể ước lượng là sẽ không bao gìờ, kể từ hôm nay tới ngày tận thế + thêm 1 ngày, không bao giờ dữ liệu của bạn vượt quá dòng vd. 10000. Thế thì thay cho trò chơi "tìm dòng cuối" bạn chỉ cần

Mã:

Sheet1.Range("J2:S10000").ClearContents

Thường thì tùy theo đặc thù công việc, dữ liệu, bạn luôn có thể ước lượng được. Mà cứ cho là không xác định được thì luôn có thể

Mã:

Sheet1.Range("J2:S" ̃ Rows.Count).ClearContents

Code trên chắc chắn không thua các code khác trước đó. Người ta chỉ muốn xác định dòng cuối khi cần thao tác gì đó, vd. lấy vào mảng, tìm kiếm dòng thỏa điều kiện nào đấy để lấy kết quả ... Còn để xóa dữ liệu thì cứ dùng 2 code cuối ở trên cũng được.

Vấn đề về mảng Ubound ?

Thanh Bình PV

Thành viên hoạt động

File đính kèm

befaint

|||||||||||||

Thanh Bình PV

Thành viên hoạt động

ppc0312

whom?

VetMini

Ăn cùng góc phố

batman1

Thành viên gạo cội

Thanh Bình PV

Thành viên hoạt động

batman1

Thành viên gạo cội

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

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

Đếm ngược thời gian