- Tham gia
- 17/8/08
- Bài viết
- 8,662
- Được thích
- 16,725
- Giới tính
- Nam
Bài này tôi muốn truyền đạt một kinh nghiệm đối với một số thành viên đang tập tành với mảng lấy dữ liệu từ một vùng trong sheet.
1) Trước tiên chúng ta phải khẳng định với nhau rằng, tạo một mảng (array) từ một vùng (range) trên sheet, thì mảng đó luôn luôn là mảng 2 chiều và có biên dưới (Lbound) bắt đầu từ 1 ở cả 2 chiều.
2) Kế tiếp, phải khẳng định rằng, để trở thành một mảng từ 1 vùng, thì vùng đó phải có tối thiểu từ 2 ô (cell) trở lên. Với mảng chỉ có 1 ô thì đó không phải là một mảng.
Arr = Range("B1:B2") ---> Arr là một array
Arr = Range("B1") ----> Arr không phải là một array
3) Không phải lúc nào ta cũng có sẳn một vùng cố định để mảng nhận giá trị như:
Arr = Range("B1:B100")
Một cơ sở dữ liệu (CSDL) không bao giờ có chuyện cố định hàng cuối là 100 hay 1000 hay là một số cụ thể nào, vì thế, VBA cung cấp cho chúng ta một thuộc tính của Range đó là End (Range.End Property).
Các đối số của End là: xlDown, xlToLeft, xlToRight, xlUp
Với End ta có thể hiểu nôm na như chúng ta dùng phím Ctrl kết hợp với một phím mũi tên (lên, xuống, trái phải) để chúng ta di chuyển nhanh đến một ô cuối cùng có giá trị ở trên, ở dưới, ở bên trái, ở bên phải.
Thông thường, để xác định hàng (row) cuối cùng có giá trị của một CSDL thì ta thường dùng Range().End(xlUp) hoặc Range().End(xlDown).
Với Range().End(xlUp) thì Range phải xuất phát từ dưới lên, ngược lại, Range().End(xlDown) Range xuất phát từ trên xuống.
Ví dụ dưới đây để lấy hàng cuối cùng của một cột có giá trị:
Dim LastRow As Long
LastRow = Range("B65536").End(xlUp).Row
hoặc:
LastRow = Range("B1").End(xlDown).Row
Vậy khi nào ta dùng xlUp và khi nào dùng xlDown?
Trong một CSDL chuẩn thường thì chúng có các hàng liền kề nhau, và không có những dòng trống ở cột đầu tiên nên chúng ta có thể chọn phương án xlDown là hợp lý. Tuy nhiên khác với Access (một CSDL không chấp nhận có dòng trống), thì Excel đôi khi có một số hàng bị chúng ta xóa (Clear) đi thay vì phải Delete nên nếu dùng xlDown thì có thể chúng ta không thể lấy hết hàng cuối cùng.
4) Nếu dùng xlUp thì ta nên lấy số hàng lớn nhất nào? 65536 hay 1048576?
Nếu như Excel 2003 trở về trước, có số hàng lớn nhất là 65536, thì Excel 2007 trở về sau có số hàng lớn nhất là 1048576. Để code ta viết "thà giết lầm còn hơn bỏ sót" thì ta chẳng cần quan tâm đến các con số này, thay vào đó ta sẽ dùng Rows.Count để nó xác định số hàng lớn nhất của một file Excel mà không cần quan tâm đến phiên bản nào cả!
LastRow = Range("B" & Rows.Count).End(xlUp).Row
5) Cách bẫy lỗi để có được một mảng từ việc dùng End:
Tại sao chúng ta phải bẫy lỗi? Và lỗi thường xuất phát từ đâu?
Như đã nói ở mục 2, mảng lấy giá trị từ một range luôn luôn phải có tối thiểu từ 2 ô trở lên, nếu không chúng sẽ bị lỗi:
Với thủ tục trên nếu LastRow = 1 thì chắc chắn thủ tục trên sẽ báo lỗi Type mismatch.
Với mảng từ 2 cột trở lên thì ta sẽ không bao giờ bị lỗi này:
Arr = Range("B1:D" & LastRow)
Nhưng chúng ta cũng phải bẫy lỗi cho dữ liệu, vì sao?
Thường thì một CSDL sẽ có tiêu đề cột, khi ta muốn lấy dữ liệu từ CSDL này để gán cho một mảng thì ta phải loại trừ tiêu đề này ra, và ta cũng phải ngăn chặn việc dữ liệu chưa được nhập vào đó hàng nào, vì thế ta phải đặt điều kiện cho nó:
Giả sử hàng 1 là hàng tiêu đề, từ hàng 2 trở đi là dữ liệu thì ta phải bẫy lỗi như sau:
Code trên là dùng cho mảng nhiều cột, nhưng với mảng 1 cột thì phải bẫy lỗi khác hơn:
Vì sao ta phải làm như thế? Bởi vì để Arr là một mảng thì ít nhất Arr phải bằng Range("B2:B3") chứ không thể bằng Range("B2") được.
Nhưng chúng ta có thể lo xa hơn, nếu dữ liệu đã đạt tới hàng cuối cùng của sheet thì chúng ta không thể dùng xlUp được nữa đấy nhé! Tuy nhiên ta có thể không ngại vì theo tôi, chúng ta nên chừa dòng cuối cùng trong CSDL.
Với xlDown cũng phải bẩy lỗi, nếu dữ liệu chưa được nhập gì cả thì khả năng nó nhận hàng cuối cùng cũng là hàng cuối cùng lớn nhất, cho nên tùy theo "hoàn cảnh" mà ta bẫy lỗi như thế nào để phù hợp.
6) Luôn luôn đi kèm Range là TÊN SHEET:
Nếu ta không đưa tên sheet vào vùng gán vào mảng thì đương nhiên máy sẽ ngầm hiểu là sheet hiện hành (ActiveSheet), vì thế để xác định là sheet nào cần gán vào mảng thì ta phải ghi rõ tên sheet vào vùng đó.
Tên sheet có thể là Sheet Name và cũng có thể là Sheet CodeName.
Sheet Name là tên sheet mà ta có thể thấy trên sheet tab và CodeName là tên mà ta có thể thấy trong VBA.
Nếu như một sheet có SheetName là "TenSheet" và CodeName là "Sheet1" thì thủ tục phải như sau:
7) Lưu ý đến việc AutoFilter:
Nếu một CSDL đang ở chế độ Filter thì việc xác định hàng sẽ gặp một "sự cố" mất dữ liệu, những giá trị không bị ẩn ở vùng dữ liệu khi Filter sẽ được cập nhật còn lại sẽ bị mất, vì thế chúng ta phải kiểm tra chế độ này trước khi thực hiện việc gán mảng:
Nhưng nếu dữ liệu không có AutoFilter mà dữ liệu chỉ bị Hide thì dù hàng có bị ẩn hay không cũng không bị mất dữ liệu khi gán lên mảng đâu, cho nên chúng ta cũng không cần phải lo về vấn đề này.
Đó là những kinh nghiệm của tôi về xác định một vùng để gán vào một mảng, có thể có những bổ sung từ những thành viên khác, hy vọng bài này sẽ phần nào giúp ích cho các bạn khi mới học về mảng để vận dụng cho bài tập của mình.
1) Trước tiên chúng ta phải khẳng định với nhau rằng, tạo một mảng (array) từ một vùng (range) trên sheet, thì mảng đó luôn luôn là mảng 2 chiều và có biên dưới (Lbound) bắt đầu từ 1 ở cả 2 chiều.
2) Kế tiếp, phải khẳng định rằng, để trở thành một mảng từ 1 vùng, thì vùng đó phải có tối thiểu từ 2 ô (cell) trở lên. Với mảng chỉ có 1 ô thì đó không phải là một mảng.
Arr = Range("B1:B2") ---> Arr là một array
Arr = Range("B1") ----> Arr không phải là một array
3) Không phải lúc nào ta cũng có sẳn một vùng cố định để mảng nhận giá trị như:
Arr = Range("B1:B100")
Một cơ sở dữ liệu (CSDL) không bao giờ có chuyện cố định hàng cuối là 100 hay 1000 hay là một số cụ thể nào, vì thế, VBA cung cấp cho chúng ta một thuộc tính của Range đó là End (Range.End Property).
Các đối số của End là: xlDown, xlToLeft, xlToRight, xlUp
Với End ta có thể hiểu nôm na như chúng ta dùng phím Ctrl kết hợp với một phím mũi tên (lên, xuống, trái phải) để chúng ta di chuyển nhanh đến một ô cuối cùng có giá trị ở trên, ở dưới, ở bên trái, ở bên phải.
Thông thường, để xác định hàng (row) cuối cùng có giá trị của một CSDL thì ta thường dùng Range().End(xlUp) hoặc Range().End(xlDown).
Với Range().End(xlUp) thì Range phải xuất phát từ dưới lên, ngược lại, Range().End(xlDown) Range xuất phát từ trên xuống.
Ví dụ dưới đây để lấy hàng cuối cùng của một cột có giá trị:
Dim LastRow As Long
LastRow = Range("B65536").End(xlUp).Row
hoặc:
LastRow = Range("B1").End(xlDown).Row
Vậy khi nào ta dùng xlUp và khi nào dùng xlDown?
Trong một CSDL chuẩn thường thì chúng có các hàng liền kề nhau, và không có những dòng trống ở cột đầu tiên nên chúng ta có thể chọn phương án xlDown là hợp lý. Tuy nhiên khác với Access (một CSDL không chấp nhận có dòng trống), thì Excel đôi khi có một số hàng bị chúng ta xóa (Clear) đi thay vì phải Delete nên nếu dùng xlDown thì có thể chúng ta không thể lấy hết hàng cuối cùng.
4) Nếu dùng xlUp thì ta nên lấy số hàng lớn nhất nào? 65536 hay 1048576?
Nếu như Excel 2003 trở về trước, có số hàng lớn nhất là 65536, thì Excel 2007 trở về sau có số hàng lớn nhất là 1048576. Để code ta viết "thà giết lầm còn hơn bỏ sót" thì ta chẳng cần quan tâm đến các con số này, thay vào đó ta sẽ dùng Rows.Count để nó xác định số hàng lớn nhất của một file Excel mà không cần quan tâm đến phiên bản nào cả!
LastRow = Range("B" & Rows.Count).End(xlUp).Row
5) Cách bẫy lỗi để có được một mảng từ việc dùng End:
Tại sao chúng ta phải bẫy lỗi? Và lỗi thường xuất phát từ đâu?
Như đã nói ở mục 2, mảng lấy giá trị từ một range luôn luôn phải có tối thiểu từ 2 ô trở lên, nếu không chúng sẽ bị lỗi:
Mã:
Sub Test1()
Dim Arr()
Dim LastRow As Long
LastRow = Range("B" & Rows.Count).End(xlUp).Row
Arr = Range("B1:B" & LastRow)
End SubVới thủ tục trên nếu LastRow = 1 thì chắc chắn thủ tục trên sẽ báo lỗi Type mismatch.
Với mảng từ 2 cột trở lên thì ta sẽ không bao giờ bị lỗi này:
Arr = Range("B1:D" & LastRow)
Nhưng chúng ta cũng phải bẫy lỗi cho dữ liệu, vì sao?
Thường thì một CSDL sẽ có tiêu đề cột, khi ta muốn lấy dữ liệu từ CSDL này để gán cho một mảng thì ta phải loại trừ tiêu đề này ra, và ta cũng phải ngăn chặn việc dữ liệu chưa được nhập vào đó hàng nào, vì thế ta phải đặt điều kiện cho nó:
Giả sử hàng 1 là hàng tiêu đề, từ hàng 2 trở đi là dữ liệu thì ta phải bẫy lỗi như sau:
Mã:
Sub Test2()
Dim Arr()
Dim LastRow As Long
LastRow = Range("B" & Rows.Count).End(xlUp).Row
If LastRow > 1 Then
Arr = Range("B2:[COLOR=#ff0000]D[/COLOR]" & LastRow)
End If
End SubCode trên là dùng cho mảng nhiều cột, nhưng với mảng 1 cột thì phải bẫy lỗi khác hơn:
Mã:
Sub Test3()
Dim Arr()
Dim LastRow As Long
LastRow = Range("B" & Rows.Count).End(xlUp).Row
If LastRow > 1 Then
If LastRow = 2 Then
Dim ArrTmp(1 To 1, 1 To 1)
ArrTmp(1, 1) = Range("B2")
Arr = ArrTmp
Else
Arr = Range("B2:B" & LastRow)
End If
End If
End SubVì sao ta phải làm như thế? Bởi vì để Arr là một mảng thì ít nhất Arr phải bằng Range("B2:B3") chứ không thể bằng Range("B2") được.
Nhưng chúng ta có thể lo xa hơn, nếu dữ liệu đã đạt tới hàng cuối cùng của sheet thì chúng ta không thể dùng xlUp được nữa đấy nhé! Tuy nhiên ta có thể không ngại vì theo tôi, chúng ta nên chừa dòng cuối cùng trong CSDL.
Với xlDown cũng phải bẩy lỗi, nếu dữ liệu chưa được nhập gì cả thì khả năng nó nhận hàng cuối cùng cũng là hàng cuối cùng lớn nhất, cho nên tùy theo "hoàn cảnh" mà ta bẫy lỗi như thế nào để phù hợp.
6) Luôn luôn đi kèm Range là TÊN SHEET:
Nếu ta không đưa tên sheet vào vùng gán vào mảng thì đương nhiên máy sẽ ngầm hiểu là sheet hiện hành (ActiveSheet), vì thế để xác định là sheet nào cần gán vào mảng thì ta phải ghi rõ tên sheet vào vùng đó.
Tên sheet có thể là Sheet Name và cũng có thể là Sheet CodeName.
Sheet Name là tên sheet mà ta có thể thấy trên sheet tab và CodeName là tên mà ta có thể thấy trong VBA.
Nếu như một sheet có SheetName là "TenSheet" và CodeName là "Sheet1" thì thủ tục phải như sau:
Mã:
Sub Test4()
Dim Arr()
Dim LastRow As Long
LastRow = [COLOR=#ff0000]Sheet1[/COLOR].Range("B" & Rows.Count).End(xlUp).Row
'hoac:
LastRow = [COLOR=#0000ff]Sheets("TenSheet")[/COLOR].Range("B" & Rows.Count).End(xlUp).Row
If LastRow > 1 Then
Arr = [COLOR=#ff0000]Sheet1[/COLOR].Range("B2:D" & LastRow)
'hoac:
Arr = [COLOR=#0000ff]Sheets("TenSheet")[/COLOR].Range("B2:D" & LastRow)
End If
End Sub7) Lưu ý đến việc AutoFilter:
Nếu một CSDL đang ở chế độ Filter thì việc xác định hàng sẽ gặp một "sự cố" mất dữ liệu, những giá trị không bị ẩn ở vùng dữ liệu khi Filter sẽ được cập nhật còn lại sẽ bị mất, vì thế chúng ta phải kiểm tra chế độ này trước khi thực hiện việc gán mảng:
Mã:
Sub Test5()
Dim Arr()
Dim LastRow As Long
[COLOR=#ff0000] If Sheet1.AutoFilterMode Then[/COLOR]
[COLOR=#ff0000] Sheet1.AutoFilterMode = False[/COLOR]
[COLOR=#ff0000] End If[/COLOR]
LastRow = Sheet1.Range("B" & Rows.Count).End(xlUp).Row
If LastRow > 1 Then
Arr = Sheet1.Range("B2:D" & LastRow)
End If
End SubNhưng nếu dữ liệu không có AutoFilter mà dữ liệu chỉ bị Hide thì dù hàng có bị ẩn hay không cũng không bị mất dữ liệu khi gán lên mảng đâu, cho nên chúng ta cũng không cần phải lo về vấn đề này.
Đó là những kinh nghiệm của tôi về xác định một vùng để gán vào một mảng, có thể có những bổ sung từ những thành viên khác, hy vọng bài này sẽ phần nào giúp ích cho các bạn khi mới học về mảng để vận dụng cho bài tập của mình.
Lần chỉnh sửa cuối:
" & LastRow)
