Các câu đố, bài tập nhằm ôn tập & bổ sung kiến thức căn bản VBA

[Hoàng Trọng Nghĩa]

Với tinh thần chơi mà học, học mà chơi, nên tôi đã mở ra topic này, hy vọng các thành viên tham gia, nhất là các thành viên mới biết về VBA.

Sau đây là câu hỏi đầu tiên:

Câu hỏi 1: Bằng phương pháp nào nhanh nhất để tìm ra ô nào trong một cột chứa một điều kiện.

Tôi có 1 file Excel 2007, với cột A, từ A1 đến A1048576 đều có giá trị.

Bằng phương pháp nào nhanh nhất (dùng mảng, dùng For Each v.v...) để tìm ra ô nào trong cột A chứa chữ "Nghia", đồng thời với ô ở cột B tương ứng nhập giá trị "OK" vào đó?

Ví dụ tìm thấy trong ô A2 có giá trị là "Nghia" thì ô B2 nhập vào "OK".

Hiện tại, đáp án nhanh nhất mà tôi có được đã gửi mail riêng (nhằm ghi lại thời gian gửi, để tránh nói ăn gian).

Để tiện việc theo dõi các câu đố, các bài tập tôi đã tạo ra topic này các bạn click vào đây:

Các link của topic "Các câu đố, bài tập nhằm ôn tập & bổ sung kiến thức căn bản VBA"

 

[Hoàng Trọng Nghĩa]

Muốn nhanh hơn nữa có lẽ phải dùng Data Object. Cái này chờ cụ Hai Lúa thử xem. Hình như với số lượng này, DAO hữu hiệu hơn ADO một bậc.

Vâng, mặc dù là những câu đố "căn bản", nhưng tôi tin là các thành viên đã, đang, sẽ học VBA trong đó có tôi, sẽ chờ một đáp án bằng DAO hoặc ADO để giải quyết vấn đề ở Câu đố 1 nhằm mục đích để học hỏi, để mở rộng thêm tầm mắt của mình.

Dù là tốc độ đã xét rồi, nhưng sáng nay tôi cũng ngẫm nghĩ và có một giải pháp cho bài đó cũng khá nhanh (có lẽ không bằng kiểu xử lý mảng rồi gán trực tiếp xuống chuỗi dùng cho trường hợp dữ liệu như trong file đính kèm), nhưng đây cũng là một phương pháp hữu hiệu nhưng ít thành viên nhắc đến, đó là dùng FIND METHOD.

Dùng Find Method tôi đã thấy người ta dùng rất nhiều, nhưng tôi chưa đọc được bài nào viết trên diễn đàn nói về dùng Find Next. Vậy nhân đây tôi xin giới thiệu cách viết này cho các bạn cùng tham khảo, góp ý.

Private Sub FindNext()
    Dim c As Long, i As Long, j As Long, r As Long, _
        fRng As Range, lRng As Range
    With Sheet1.Range("A1:A1048576")
        Set lRng = .Cells(1, 1)
        For i = 1 To .Rows.Count
            Set fRng = .Find(What:="Nghia", After:=lRng, LookIn:=xlFormulas, LookAt:=xlWhole)
            If fRng Is Nothing Then Exit For
            If fRng.Row = 1 Then Exit For
            .FindNext(After:=lRng).Offset(, 1) = "OK"
            Set lRng = fRng
        Next
    End With
End Sub

Các bạn lưu ý: Với cách tìm kiếm giá trị thì LookIn:=xlFormulas luôn luôn nhanh hơn LookIn:=xlValues.

--------------------------------------------

Do code trên tôi làm một cách vội vã, chưa kiểm tra kỹ nên sẽ phát sinh vòng lặp chạy liên tục nếu ô đầu tiên không thỏa điều kiện, cho nên tôi đã sửa lại ở bài này:

http://www.giaiphapexcel.com/forum/showthread.php?86400-Đố-vui-căn-bản-về-VBA&p=538878#post538878

Chân thành Xin lỗi các bạn!

 

[VetMini]

Theo trường phái của tôi thì thích nói chuyện lý thuyết.

Theo lý thuyết 2 mảng Arr1 và Arr2 không tương đương với nhau.
Arr1 là mảng có xác định kiểu và kích thước hẳn hoi. Nó là mảng tĩnh.
Arr2 không được xác định, tức là mảng Variant. Và là mảng động.

Variant là biến có kết nối trễ. Mảng động là mảng không được trình dịch cấp phát bộ nhớ trước khi chạy. Nếu tính theo đường lối "chạy nhanh" của bạn thì Arr2 chạy chậm hơn. Tuy nhiên, vì Arr2 là Variant nên nó có thể lợi dụng hàm Array() của VBA để khở trị cho nhanh - hàm có sẵn của hệ thống được viết với dạng tối ưu.

Vì VBA có ngầm chứa phép ép kiểu cho nên cách so sánh bình thường có thể đưa đến kết quả chủ quan. Muốn so sánh thực tiễn phải so cả kiểu dữ liệu.

 

[quocphuoc88]

2 mảng 1 chiều:

Lúc đọc cột bình thường thì được mảng 2 chiều. Nhưng nếu lúc đọc cộng thêm hàm Application.Transpose thì có thể ép nó thành mảng 1 chiều.

mngA = Application.Transpose(Range("A1:A1000000").Value)
mngB = Application.Transpose(Range("B1:B1000000").Value)

Đương nhiên làm những cái này rất tốn nhiên liệu nên chỉ hữu dụng khi mảng rất lớn.

Chú thích thêm về chiều của mảng:

Lệnh Arr2chieu(i,j) sử dụng một con toán tính ra vị trí thứ "i*n+j" của mảng, cho nên theo lý thuyết nó sẽ chậm hơn Arr1chieu(i) chỉ sử dụng con toán tính vị trí thứ "i" thôi.

Cái thèn transpose này nó có 1 đặc điểm là không chỉ hoán đổi dữ liệu mà nó còn can thiệp vào dữ liệu của mình luôn trong quá trình chuyển đổi

e xin thử đưa ra câu trả lời cho câu 2:
Nếu xét từ lúc đặt biến thì e cho rằng 2 mảng này không giống nhau, vì 1 thằng ( arr1) nó là mảng có 5 phần tử, còn thằng ( arr2 ) nó là mảng động , còn chuyện gán xuống sheet nó bằng nhau lý do mình đã cho cái arr2 có số phần tử giống với thằng arr1
Không biết e suy nghĩ đúng chưa, mong a chỉ bảo thêm !

 

[Hoàng Trọng Nghĩa]

Theo trường phái của tôi thì thích nói chuyện lý thuyết.

Theo lý thuyết 2 mảng Arr1 và Arr2 không tương đương với nhau.
Arr1 là mảng có xác định kiểu và kích thước hẳn hoi. Nó là mảng tĩnh.
Arr2 không được xác định, tức là mảng Variant. Và là mảng động.

Variant là biến có kết nối trễ. Mảng động là mảng không được trình dịch cấp phát bộ nhớ trước khi chạy. Nếu tính theo đường lối "chạy nhanh" của bạn thì Arr2 chạy chậm hơn. Tuy nhiên, vì Arr2 là Variant nên nó có thể lợi dụng hàm Array() của VBA để khở trị cho nhanh - hàm có sẵn của hệ thống được viết với dạng tối ưu.

Vì VBA có ngầm chứa phép ép kiểu cho nên cách so sánh bình thường có thể đưa đến kết quả chủ quan. Muốn so sánh thực tiễn phải so cả kiểu dữ liệu.

Đây là một câu trả lời cho Câu đố 2 chính xác, tôi cũng xin mở rộng ra một tí là, tôi đã "gài bẫy" khi khai báo biến Arr1(0 To 4) As Long, vì thế cho dù bạn dùng phương thức nào (dù nhanh hay chậm) để tạo ra mảng một chiều, mặc dù là kết quả là như nhau, nhưng chúng khác nhau ở 1 điểm "gài" đó là Data Type!

Kiểm tra:

Sub check2()
    MsgBox TypeName(Arr1)
    MsgBox TypeName(Arr2)
End Sub

Bài học rút ra cho các thành viên mới học VBA: Khai báo biến một cách cẩn thận, nếu không sẽ có những lỗi mà mình không biết tại sao!

 

[Hoàng Trọng Nghĩa]

Câu hỏi 3: [A1] và Range("A1") có khác nhau không?

Mặc dù code để viết ngắn gọn khi dùng Range("A1") nhiều người thường dùng [A1], vậy chúng có gì khác nhau không?

Câu đố rất căn bản phải không các bạn?

 

[VetMini]

Cái thèn transpose này nó có 1 đặc điểm là không chỉ hoán đổi dữ liệu mà nó còn can thiệp vào dữ liệu của mình luôn trong quá trình chuyển đổi

Có lẽ đúng. Xin lỗi, tôi quên mất mục đích đề tài này là "tốc độ". Tôi chỉ theo thói quen lấy 2 mảng để xử lý 2 mảng song song không nhất thiết cùng một cụm. Vấn đề thường xuyên gặp khi cần cập nhật một cột dữ liệu dựa trên một cột dữ liệu khác.

 

[Hoàng Trọng Nghĩa]

Các bạn tham gia với bài này nhé:

Câu hỏi 3: [A1] và Range("A1") có khác nhau không?

Mặc dù code để viết ngắn gọn khi dùng Range("A1") nhiều người thường dùng [A1], vậy chúng có gì khác nhau không?

Câu đố rất căn bản phải không các bạn?

 

[ptm0412]

Dùng Find Method tôi đã thấy người ta dùng rất nhiều, nhưng tôi chưa đọc được bài nào viết trên diễn đàn nói về dùng Find Next. Vậy nhân đây tôi xin giới thiệu cách viết này cho các bạn cùng tham khảo, góp ý.

        For i = 1 To .Rows.Count
            Set fRng = .Find(What:="Nghia", After:=lRng, LookIn:=xlFormulas, LookAt:=xlWhole)
            If fRng Is Nothing Then Exit For
            If fRng.Row = 1 Then Exit For
            .FindNext(After:=lRng).Offset(, 1) = "OK"
            Set lRng = fRng
        Next

Nghĩa nói sai: GPE có hẳn 1 chuyên đề về Find của bác Chanh và Thu Nghi thảo luận rất sôi nổi. Thậm chí đã tổng hợp thành ebook. Và đương nhiên trong đó có Find Next.

Ngoài ra xét theo code Nghĩa đưa lên, đã dùng find và find next thì dùng vòng lặp Do Loop đánh 1 số lần (đến khi find hết), chứ không dùng for next đánh 1 triệu lần.

 

[Hoàng Trọng Nghĩa]

Ngoài ra xét theo code Nghĩa đưa lên, đã dùng find và find next thì dùng vòng lặp Do Loop đánh 1 số lần (đến khi find hết), chứ không dùng for next đánh 1 triệu lần.

Vậy là Sư phụ chưa test thử rồi, với code đó For có dùng có 1 tỷ lần lặp đi chăng nữa thì nó cũng chạy đúng số điều kiện trong Range mà thôi. Chẳng hạn trong dữ liệu có 5 hàng chứa "Nghia" thì nó cũng chỉ chạy đúng 6 vòng lặp!

Theo nguyên tắc của Find Next, nếu nó đã chạy từ đầu hàng đến cuối hàng thì nó sẽ quay lại từ đầu, vì thế ta đã chặn nó tại hàng mà chúng duyệt qua rồi thì sẽ thoát For.

Code của bài đó em sửa lại như sau, bởi vì nó bị thiếu duyệt qua ô đầu tiên:

Private Sub FindNext2()
    Dim i As Long, j As Long, m As Long, r As Long, _
        fRng As Range, lRng As Range
        
            
    With Sheet1.Range("A1:A1048576")
        
        r = .Rows.Count
        Set lRng = .Cells(r, 1)
        
        For i = 1 To [SIZE=4][COLOR=#ff0000][B]1000000000 [/B][/COLOR][/SIZE][COLOR=#0000ff]'r[/COLOR]


[COLOR=#0000ff]          ''Đếm số lần lặp tại đây:[/COLOR]
[B][COLOR=#ff0000]            m = m + 1[/COLOR][/B]
[B][COLOR=#ff0000]            MsgBox m[/COLOR][/B]
            
            Set fRng = .Find(What:="Nghia", After:=lRng, LookIn:=xlFormulas, LookAt:=xlWhole)
            
            If fRng Is Nothing Then Exit For
            If fRng.Row <= j Then Exit For
            
            .FindNext(After:=lRng).Offset(, 1) = "OK"
            
            j = fRng.Row
            Set lRng = fRng


        Next
    End With
End Sub

Có thể dùng Do ... Loop, thì số vòng lặp cũng như nhau mà thôi.

 

[ptm0412]

Vậy là Sư phụ chưa test thử rồi, với code đó For có dùng có 1 tỷ lần lặp đi chăng nữa thì nó cũng chạy đúng số điều kiện trong Range mà thôi. Chẳng hạn trong dữ liệu có 5 hàng chứa "Nghia" thì nó cũng chỉ chạy đúng 6 vòng lặp!

Theo nguyên tắc của Find Next, nếu nó đã chạy từ đầu hàng đến cuối hàng thì nó sẽ quay lại từ đầu, vì thế ta đã chặn nó tại hàng mà chúng duyệt qua rồi thì sẽ thoát For.

Có thể dùng Do ... Loop, thì số vòng lặp cũng như nhau mà thôi.

Thứ nhất: người đang học căn bản nhìn vào thấy 1 triệu lần
Thứ hai: Nếu ô đâu tiên (A1) không phải "Nghia" thì fRng.Row không bao giờ bằng 1, không Exit For được, và phải đánh đủ 1 triệu lần.
Thứ ba: Biến i không hề được sử dụng, nhưng là vòng lặp For i

Code sau khi sửa ở bài 49:

Dĩ nhiên sau khi sửa thì giảm số lần lặp. Nhưng biến i cũng vẫn không được sử dụng!

 

[Hoàng Trọng Nghĩa]

Thứ nhất: người đang học căn bản nhìn vào thấy 1 triệu lần
Thứ hai: Nếu ô đâu tiên (A1) không phải "Nghia" thì fRng.Row không bao giờ bằng 1, không Exit For được, và phải đánh đủ 1 triệu lần.
Thứ ba: Biến i không hề được sử dụng, nhưng là vòng lặp For i

Code sau khi sửa ở bài 49:

Dĩ nhiên sau khi sửa thì giảm số lần lặp. Nhưng biến i cũng vẫn không được sử dụng!

Xin thưa với Sư phụ, dòng màu đỏ là không chính xác! Code đầu do nó không tính tới hàng 1 nên nếu số hàng có "Nghia" là 5 thì nó lặp đúng 5 lần chứ không phải là 6 lần như đã sửa ở code sau đâu Sư phụ!

Nhưng biến i cũng vẫn không được sử dụng!

Ta đâu cần sử dụng biến i làm gì! Miễn số vòng lặp tối thiểu bằng tổng số hàng mà chúng duyệt qua là được, đừng nhỏ hơn sẽ bị thiếu nếu dữ liệu 100% có từ "Nghia".

Thứ hai: Nếu ô đâu tiên (A1) không phải "Nghia" thì fRng.Row không bao giờ bằng 1, không Exit For được, và phải đánh đủ 1 triệu lần.

Chính vì thế mà em mới sửa code để tối ưu nó (sáng làm vội nên chưa check nên sau đó check lại và sửa lại), nhưng đó cũng là một phương pháp để giải toán.

 

[quocphuoc88]

Các bạn tham gia với bài này nhé:

Câu đố 3: [A1] và Range("A1") có khác nhau không?

E nghĩ là nó khác nhau, vì hình như cái dấu "[]" là hàm evaluate thì phải( tức là tính toán giá trị tại a1, còn range("a1") là chỉ đến cái vùng trong a1
E chỉ hiểu như thế, mong mọi người góp ý !

 

[Hoàng Trọng Nghĩa]

E nghĩ là nó khác nhau, vì hình như cái dấu "[]" là hàm evaluate thì phải( tức là tính toán giá trị tại a1, còn range("a1") là chỉ đến cái vùng trong a1
E chỉ hiểu như thế, mong mọi người góp ý !

Mục đích của topic này không phải chỉ là đố vui đâu, tôi đặt topic này còn là nơi chia sẽ, trao đổi kinh nghiệm và học hỏi lẫn nhau.

Bạn tham gia thật nhiệt tình. Đúng như bạn nói nó chính là một Evaluate!

Thay vì ta viết Evaluate("A1") thì ta viết [A1] cho ngắn gọn.

Nếu như Range() ta có thể làm được như thế này:

Range("A" & lRow)

Nhưng với [A & lRow] thì hoàn toàn không! Vì thế ta phải viết như thế này: Evaluate("A" & lRow)

Bởi Evaluate là một hàm, cho nên chúng sẽ tính toán chậm hơn khi ta dùng Range().

Nhưng chúng ta sẽ rất tiện dụng khi làm công thức với Evalute. Thay vì ghi macro cho các ô ta gán công thức sẽ được như thế này:

Sub Macro2()
    Range("E1").FormulaR1C1 = "=VLOOKUP(""Nghia"",RC[-4]:R[6]C[-3],2,0)"
    Range("E2").FormulaR1C1 = "=SUMIF(R[-1]C[-4]:R[5]C[-4],""Nghia"",R[-1]C[-3]:R[5]C[-3])"
End Sub

Chuyển sang Evaluate, chúng có dạng như thế này:

Sub Macro3()
    Range("E1") = Evaluate("VLOOKUP(""Nghia"",A1:B7,2,0)")
    Range("E2") = Evaluate("SUMIF(A1:A7,""Nghia"",B1:B7)")
End Sub

Thì ta viết ngắn gọn hơn, đỡ phải lằng ngoằng bởi các dấu ngoặc kép (""):

Sub Macro4()
    [E1] = [VLOOKUP("Nghia",A1:B7,2,0)]
    [E2] = [SUMIF(A1:A7,"Nghia",B1:B7)]
End Sub

Lưu ý: Phải nhớ rằng vì nó là một hàm, cho nên nó tính toán sẽ chậm hơn!

 

[nmhung49]

Chủ đề này khá hay đấy chứ mình cũng tham gia chút gọi là ủng hộ a.Nghĩa bài 1 mình xin góp code dùng ADO nhưng sao mà có lẽ thấy cũng chậm đấy chứ lâu quá không đóng góp gì cũng ngại với dữ liệu của a.Nghĩa ta có thể dùng ADO

[GPECODE=vb]
Sub DovuiADO()


Dim sSQL As String, cnn As Object, rst As Object
Set cnn = CreateObject("ADODB.Connection")
Set rst = CreateObject("ADODB.Recordset")
With cnn
.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=" & ThisWorkbook.FullName & _
";Extended Properties=""Excel 12.0;HDR=No;IMEX=1"";"
.Open
End With
With rst
.ActiveConnection = cnn
sSQL = "SELECT IIf([F1]='Nghia','OK',[F1]) FROM [Dovui$];"
.Open sSQL
End With
With Sheets("Dovui")
.[B1:B1048576].ClearContents
.[B1].CopyFromRecordset rst
End With
rst.Close: Set rst = Nothing
cnn.Close: Set cnn = Nothing

End Sub
[/GPECODE]

 

[Hoàng Trọng Nghĩa]

Cũng vì [] là một Evaluate, nên tôi nói rộng ra một chút cho các bạn dùng mảng hiểu được tại sao ta bị lỗi!

Với Range() ta sử dụng thế nào cũng được:

Sub Macro7()
    Dim Arr()
    [COLOR=#0000ff]Arr = Range("B1:B6")[/COLOR][B][COLOR=#ff0000].Value[/COLOR][/B][COLOR=#0000ff][/COLOR]
''Hoặc:
[COLOR=#0000ff]    ''Arr = Range("B1:B6")[/COLOR]
End Sub

Nhưng với [] thì không được như vậy, sẽ bị lỗi trong trường hợp này:

Sub Macro5()
    Dim Arr()
[COLOR=#0000ff]    Arr = [B1:B6][/COLOR]
End Sub

Tức là bị lỗi khi thiếu .Value. Cho nên ta chỉ được sử dụng chúng như thế này:

Sub Macro6()
    Dim Arr()
[COLOR=#0000ff]    Arr = [B1:B6][/COLOR][B][COLOR=#FF0000].Value[/COLOR][/B]
End Sub

VBA cũng thật là rối rắm phải không các bạn?!

 

[Hoàng Trọng Nghĩa]

Chủ đề này khá hay đấy chứ mình cũng tham gia chút gọi là ủng hộ a.Nghĩa bài 1 mình xin góp code dùng ADO nhưng sao mà có lẽ thấy cũng chậm đấy chứ lâu quá không đóng góp gì cũng ngại với dữ liệu của a.Nghĩa ta có thể dùng ADO

Trước tiên nó chậm vì có một thời gian nhất định khi kết nối ngầm.

Thứ 2 nó chậm là do dòng này: .[B1:B1048576].ClearContents, ta có thể bỏ qua nó mà!

 

[OverAC]

Đề nghị các cao thủ kiềm chế, không bàn quá sâu, topic này mang tên câu đố cơ bản, hãy động viện các người mới nào.
Các bác bàn kiểu này đố có ma mới nào dám bén mảng tới.

OverAC

 

[moneymong.pt]

Đề nghị các cao thủ kiềm chế, không bàn quá sâu, topic này mang tên câu đố cơ bản, hãy động viện các người mới nào.
Các bác bàn kiểu này đố có ma mới nào dám bén mảng tới.

OverAC

Chính xác là thế đó thầy!
Em không hiểu cái mô tê gì hết.
Em nghĩ anh Nghĩa nên mở thêm 1 topic nữa, cùng 1 ví dụ, 1 topic dành cho người mới bắt đầu học, 1 topic dành cho các anh chị đã biết và muốn mở rộng.
Sau này các anh chị hiểu biết nâng cao hơn, sẽ vào topic nâng cao bàn luận.
Như vậy cùng 1 lúc mà giải quyết 2 vấn đề.

 

[Hoàng Trọng Nghĩa]

Chính xác là thế đó thầy!
Em không hiểu cái mô tê gì hết.
Em nghĩ anh Nghĩa nên mở thêm 1 topic nữa, cùng 1 ví dụ, 1 topic dành cho người mới bắt đầu học, 1 topic dành cho các anh chị đã biết và muốn mở rộng.
Sau này các anh chị hiểu biết nâng cao hơn, sẽ vào topic nâng cao bàn luận.
Như vậy cùng 1 lúc mà giải quyết 2 vấn đề.

Trời ơi, các thành viên thông cảm cho, làm sao tôi biết được đâu là nhóm màu xanh và đâu là nhóm màu đỏ đây?

Thôi thì để topic này dành cho nhóm màu đỏ, tôi sẽ đố hoặc đặt ra các câu hỏi dựa vào những bài trong giáo trình của lớp VBA vừa rồi được không nhỉ?

Như thế thì các cao thủ xin vui lòng không trả lời cho đến khi các thành viên mới học VBA tham gia (sao biết mới học đây ta?), đồng thời khi trả lời các cao thủ vui lòng dẫn chứng vài trường hợp áp dụng cụ thể nhé!

 

[VetMini]

Đề nghị các cao thủ kiềm chế, không bàn quá sâu, topic này mang tên câu đố cơ bản, hãy động viện các người mới nào.
Các bác bàn kiểu này đố có ma mới nào dám bén mảng tới.

OverAC

Không phải tôi bắt bẻ bạn. Cái khó là tuỳ theo cách học mà vấn đề X đối với A là cơ bản trong khi đối với B là sâu.
Tốt hơn hết là định nghĩa cái giới hạn của cơ bản trước đã.

Đối với tôi chẳng hạn, chỉ riêng việc cóp range ra array để xử lý đã thuộc về cao cấp rồi.