Bài tập VBA đơn giản dành cho người mới bắt đầu [Phần 2]

ChanhTQ@ · 29/5/14

Xin các bạn có bài tập nào hay hay đăng lên để cùng nhau luyện cho mau tiến bộ nhe!
Mình xin mở màn bài đầu:

ĐỀ BÀI 1:

Tôi có bảng số liệu từ cột [A..E] như sau:

| A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | T | U | V | W 2 |HoTen|Date1|Date2|Date3|Date4|1|2|3|4|5|6|7|8|9|10|11|12|13|14|15|16|17|18
3 |Hồ Lễ|3|5|7|13|Do|Do|Do|Xh|Xh|Vg|Vg|Tm|Tm|Tm|Tm|Tm|Tm||.|||
4 |Đỗ Nè|4|8|13|15|Nu|Nu|Nu|Nu|Xh|Xh|Xh|Xh|Xm|Xm|Xm|Xm|Xm|Dn|Dn|||
5 |Vũ Xe|2|4|12|13|Do|Do|Vg|Vg|Nu|Nu|Nu|Nu|Nu|Nu|Nu|Nu|Hg|||.|||

Phần từ cột [F] trở đi là phần cần viết 1 macro để nó tô màu nền khác nhau theo những giá trị cùng dòng từ cột [B..E];
Màu tô do bạn tự chọn, cốt fân biệt giữa chúng & dịu mắt là được!

PHẦN TỔNG HỢP CÁC ĐỀ BÀI TẬP:

(*) BĐT: Bài đọc thêm

.
.
.

SA_DQ · 2/6/14

Những tham khảo liên quan đến đề bài tập 2A

Ở "Bài tập 2A", tại dòng lệnh 2 trong macro của tác giả “Ngoai Thanh” ta thấy có dòng lệnh

PHP:

If Intersect(Target, [H2]) Is Nothing Then Exit Sub

Điều đầu tiên ta hiểu nôm na với nhau rằng Intersect() là 1 fương thức dùng để kiểm tra sự giao nhau giữa các vùng ô; Mà cụ thể ở đây là Target & ô [H2]
Trong sự kiện Worksheet_Change thì “Target” là ô hay vùng ô mà ta vừa tác động đến nó;
Bạn nào muốn nghiên cứu sâu về fương thức này, mình xin mời lại đây:
http://www.giaiphapexcel.com/forum/showthread.php?40179-Hỏi-về-Phương-thức-Intersect

(Bài của NDU): Target: Chính là cell mà bạn đang chọn (thường nằm trong sự kiện Change hoặc SelectionChange)... Khi bạn thay đổi cell chọn, hoặc thay đổi số liệu trên 1 cell thì cell ấy chính là Target
Resize: Chỉnh lại vùng Range, cái này gần giống với hàm OFFSET của công thức Excel

Về UsedRange các bạn nên đọc thêm ở đây: http://www.giaiphapexcel.com/forum/showthread.php?6419-Thuộc-tính-UsedRange

ChanhTQ@ · 2/6/14

Bài tập số 3

ĐỀ BÀI 3

Các bạn tạo giúp báo cáo thống kê điểm cao/thấp nhất của từng môn học theo từng lớp với các nội dung cần có như sau:

(Trên 1 trang tính mới) tại [H1] là ô Validation có 2 mục chọn "CAO" & "THẤP"

Tại [C3], nếu [H2] là "CAO" sẽ hiện dòng "THỐNG KÊ ĐIỂM CAO NHẤT CỦA TỪNG LỚP."

Ngược lại sẽ là "THỐNG KÊ ĐIỂM THẤP NHẤT CỦA TỪNG LỚP."

Dữ liệu báo cáo theo mẫu sau:

5 |TT|Lớp|Văn|Lí|Địa|Toán|Sinh|Anh
6 |1|12A1|10|5|6|10|7|9
7 |2|12A10|6.5|5|8.5|3.5|2.5|9
8 |3|12A11|6.5|5.5|4|3.5|2|9
.. |.| ;;;;;;;;;;;

|..|.|.|.||.

Chúc các bạn thành công!

chuot0106 · 2/6/14

ChanhTQ@ đã viết:
ĐỀ BÀI 3

Các bạn tạo giúp báo cáo thống kê điểm cao/thấp nhất của từng môn học theo từng lớp với các nội dung cần có như sau:

(Trên 1 trang tính mới) tại [H1] là ô Validation có 2 mục chọn "CAO" & "THẤP"

Tại [C3], nếu [H2] là "CAO" sẽ hiện dòng "THỐNG KÊ ĐIỂM CAO NHẤT CỦA TỪNG LỚP."

Ngược lại sẽ là "THỐNG KÊ ĐIỂM THẤP NHẤT CỦA TỪNG LỚP."

Dữ liệu báo cáo theo mẫu sau:

5 |TT|Lớp|Văn|Lí|Địa|Toán|Sinh|Anh
6 |1|12A1|10|5|6|10|7|9
7 |2|12A10|6.5|5|8.5|3.5|2.5|9
8 |3|12A11|6.5|5.5|4|3.5|2|9
.. |.||..|.|.|.||.

Chúc các bạn thành công!

Anh có File dữ liệu không ạ? Em nghĩ là trong mỗi lớp phải có cả danh sách học sinh của lớp đó chứ ạ? Nói thật là em vẫn chưa hình dung ra dữ liệu gốc có cấu trúc ra sao nữa.

phihndhsp · 2/6/14

ChanhTQ@ đã viết:
ĐỀ BÀI 3

Các bạn tạo giúp báo cáo thống kê điểm cao/thấp nhất của từng môn học theo từng lớp với các nội dung cần có như sau:

(Trên 1 trang tính mới) tại [H1] là ô Validation có 2 mục chọn "CAO" & "THẤP"

Tại [C3], nếu [H2] là "CAO" sẽ hiện dòng "THỐNG KÊ ĐIỂM CAO NHẤT CỦA TỪNG LỚP."

Ngược lại sẽ là "THỐNG KÊ ĐIỂM THẤP NHẤT CỦA TỪNG LỚP."

Dữ liệu báo cáo theo mẫu sau:

5 |TT|Lớp|Văn|Lí|Địa|Toán|Sinh|Anh
6 |1|12A1|10|5|6|10|7|9
7 |2|12A10|6.5|5|8.5|3.5|2.5|9
8 |3|12A11|6.5|5.5|4|3.5|2|9
.. |.||..|.|.|.||.

Chúc các bạn thành công!

Nộp bài này cho sư phụ xem áp dụng thuật toán lính canh

leonguyenz · 2/6/14

ChanhTQ@ đã viết:
ĐỀ BÀI 3

Các bạn tạo giúp báo cáo thống kê điểm cao/thấp nhất của từng môn học theo từng lớp với các nội dung cần có như sau:
(Trên 1 trang tính mới) tại [H1] là ô Validation có 2 mục chọn "CAO" & "THẤP"
Tại [C3], nếu [H2] là "CAO" sẽ hiện dòng "THỐNG KÊ ĐIỂM CAO NHẤT CỦA TỪNG LỚP."
Ngược lại sẽ là "THỐNG KÊ ĐIỂM THẤP NHẤT CỦA TỪNG LỚP."
...
Chúc các bạn thành công!

Thật sự là bài này "đơn giản dành cho người mới bắt đầu"?
Mặc dù em cũng "mới bắt đầu" nhưng khi gặp bài này lại phải dùng Dictionary và Mảng.
[gpecode=vb]
Private Sub Worksheet_Change(ByVal Target As Range)
Dim sArr(), rArr(), iR As Long, jC As Long
Dim Dic As Object, kD As Long, Tmp As Single
Set Dic = CreateObject("Scripting.Dictionary")
sArr = Sheet1.Range("H7:N" & Sheet1.Range("H65535").End(xlUp).Row).Value
ReDim rArr(LBound(sArr) To UBound(sArr), 1 To 8)
If Not Intersect(Target, [H2]) Is Nothing Then
For iR = LBound(sArr) To UBound(sArr)
If Not Dic.Exists(sArr(iR, 1)) Then
kD = kD + 1
Dic.Add sArr(iR, 1), kD
rArr(kD, 1) = kD
rArr(kD, 2) = sArr(iR, 1)
For jC = 1 To 6
rArr(kD, jC + 2) = sArr(iR, jC + 1)
Next jC
Else
For jC = 1 To 6
Tmp = Val(rArr(Dic.Item(sArr(iR, 1)), jC + 2))
If Target.Value = "CAO" Then
rArr(Dic.Item(sArr(iR, 1)), jC + 2) = _
IIf(Tmp > sArr(iR, jC + 1), Tmp, sArr(iR, jC + 1))
Else
rArr(Dic.Item(sArr(iR, 1)), jC + 2) = _
IIf(Tmp < sArr(iR, jC + 1), Tmp, sArr(iR, jC + 1))
End If
Next jC
End If
Next iR
End If
If kD Then
[A5:H50].Clear
[A5].Resize(kD, 8) = rArr
[A5].Resize(kD, 8).Borders.LineStyle = 1
End If
Set Dic = Nothing
End Sub
[/gpecode]

SA_DQ · 2/6/14

Anh có File dữ liệu không ạ? Em nghĩ là trong mỗi lớp phải có cả danh sách học sinh của lớp đó chứ ạ? Nói thật là em vẫn chưa hình dung ra dữ liệu gốc có cấu trúc ra sao nữa.

CSDL có đăng ở bài trước mà, bạn!

& cho fép mình khuyên bạn giải quyết bài này theo cách xài DMAX() hay DMIN() với sự trợ giúp nho nhỏ của macro là được; Bạn đừng đi theo vết xe đỗ của 2 bài trả lời cho BT 3 bên trên.

Trân trọng!

chuot0106 · 2/6/14

HYen17 đã viết:
CSDL có đăng ở bài trước mà, bạn!

& cho fép mình khuyên bạn giải quyết bài này theo cách xài DMAX() hay DMIN() với sự trợ giúp nho nhỏ của macro là được; Bạn đừng đi theo vết xe đỗ của 2 bài trả lời cho BT 3 bên trên.

Trân trọng!

Cảm ơn thầy nhiều! Em cũng thấy 2 bài trên đao to búa lớn quá! Chắc tại mấy bạn đó không phải là người mới bắt đầu theo đúng nghĩa của nó. hi

phihndhsp · 2/6/14

chuot0106 đã viết:
Cảm ơn thầy nhiều! Em cũng thấy 2 bài trên đao to búa lớn quá! Chắc tại mấy bạn đó không phải là người mới bắt đầu theo đúng nghĩa của nó. hi

Không có đao to búa lớn gì đâu nghĩ sao làm vậy thôi, bài tập này tôi sử dụng thuật toán lính canh thôi rất đơn giản, tôi cũng mới tiếp cận VBA thôi. chưa tới 1 năm , và đang tập chuyển thuật toán từ ngôn ngữ C++ qua VBA(phương pháp viết là chia để trị)

VetMini · 2/6/14

phihndhsp đã viết:
Không có đao to búa lớn gì đâu nghĩ sao làm vậy thôi, bài tập này tôi sử dụng thuật toán lính canh thôi rất đơn giản, tôi cũng mới tiếp cận VBA thôi. chưa tới 1 năm , và đang tập chuyển thuật toán từ ngôn ngữ C++ qua VBA(phương pháp viết là chia để trị)

Tôi chỉ nói cái này cho dân từng viết code C++ thôi. Không áp dụng cho những người khác.

Bạn là dân chuyên C++ mà quên mất "namespace" hay sao?
Từ code của Sheet3 (CommandButton_Click). Muốn gọi hàm của các modules khác thì phải ghi rõ phạm vi chúng.
Private Sub CommandButton1_Click()
Call Module2.LOP_DUYNHAT
Call Module4.DATA
End Sub

Mỗi module trong VBAProject có thể tạm coi như là một static class.

phihndhsp · 2/6/14

VetMini đã viết:
Tôi chỉ nói cái này cho dân từng viết code C++ thôi. Không áp dụng cho những người khác.

Bạn là dân chuyên C++ mà quên mất "namespace" hay sao?
Từ code của Sheet3 (CommandButton_Click). Muốn gọi hàm của các modules khác thì phải ghi rõ phạm vi chúng.
Private Sub CommandButton1_Click()
Call Module2.LOP_DUYNHAT
Call Module4.DATA
End Sub

Mỗi module trong VBAProject có thể tạm coi như là một static class.

Dạ do mới làm quen với VBA nên chưa có rành lắm, cảm ơn anh nhiều, cái này rất hay và em sẽ cần rất nhiều, vì em viết 1 file có rất nhiều module mà mỗi lần gọi là không biết nằm ở trong module nào, có cái này của anh em sẽ dễ tìm tới nơi chứa code nhanh hơn

leonguyenz · 2/6/14

HYen17 đã viết:
CSDL có đăng ở bài trước mà, bạn!

& cho fép mình khuyên bạn giải quyết bài này theo cách xài DMAX() hay DMIN() với sự trợ giúp nho nhỏ của macro là được; Bạn đừng đi theo vết xe đỗ của 2 bài trả lời cho BT 3 bên trên.

Trân trọng!

Dùng hàm CSDL phải dùng để vùng điều kiện, tạm thời là dùng 1 cột phụ rồi xoá nó.
[gpecode=vb]
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Rng As Range, Rng2 As Range, iR As Long
Set Rng = Sheet1.Range("H6:N" & Sheet1.Range("H65535").End(xlUp).Row)
If Not Intersect(Target, [H2]) Is Nothing Then
[B5] = "Lop"
Range([A6], [A6].End(xlDown)).Clear
Rng.AdvancedFilter 2, [B5], [B5], True
Set Rng2 = Range([B6], [B6].End(xlDown))
[K5:Q5].Value = [B5:H5].Value
For iR = 1 To Rng2.Rows.Count
[K6] = Rng2(iR)
Rng2(iR).Offset(, -1) = iR
If Target.Value = "CAO" Then
[L6].Resize(, 6).FormulaR1C1 = "=DMAX(Sheet1!R6C8:R65535C14,R5C,R[-1]C11:RC11)"
Else
[L6].Resize(, 6).FormulaR1C1 = "=DMIN(Sheet1!R6C8:R65535C14,R5C,R[-1]C11:RC11)"
End If
Rng2(iR).Offset(, 1).Resize(, 6).Value = [L6].Resize(, 6).Value
Next iR
[B5] = "L" & ChrW(7899) & "p"
[K5:Q6].Clear
[A6].Resize(iR - 1, 8).ClearFormats
[A6].Resize(iR - 1, 8).Borders.LineStyle = 1
End If
End Sub

[/gpecode]
Em chỉ làm được vậy thôi. Chờ bài khác tối ưu hơn.

chuot0106 · 2/6/14

ChanhTQ@ đã viết:
ĐỀ BÀI 3

Các bạn tạo giúp báo cáo thống kê điểm cao/thấp nhất của từng môn học theo từng lớp với các nội dung cần có như sau:

(Trên 1 trang tính mới) tại [H1] là ô Validation có 2 mục chọn "CAO" & "THẤP"

Tại [C3], nếu [H2] là "CAO" sẽ hiện dòng "THỐNG KÊ ĐIỂM CAO NHẤT CỦA TỪNG LỚP."

Ngược lại sẽ là "THỐNG KÊ ĐIỂM THẤP NHẤT CỦA TỪNG LỚP."

Dữ liệu báo cáo theo mẫu sau:

5 |TT|Lớp|Văn|Lí|Địa|Toán|Sinh|Anh
6 |1|12A1|10|5|6|10|7|9
7 |2|12A10|6.5|5|8.5|3.5|2.5|9
8 |3|12A11|6.5|5.5|4|3.5|2|9
.. |.||..|.|.|.||.

Chúc các bạn thành công!

Em nộp bài nhờ các anh chị xem và góp ý. Bài làm đúng theo "PHONG CÁCH" người mới bắt đầu biết VBA!
P/S: Có sử dụng gợi ý của HYEN17
Thank bạn!

chuot0106 · 2/6/14

Không hiểu sao bài của mình và anh leonguyen(Dùng Dmax, Dmin) ra kết quả giống nhau nhưng lại không giống kết quả của anh Phi và của anh ChanhTQ nhỉ. Phải chăng có sai sót ở đâu đây?

SA_DQ · 2/6/14

Bài đọc thêm

PHP:

Option Explicit
Function CucTri(CSDL As Range, Lop As String, Optional Max_ As Boolean = True)
 Dim Rw As Long, J As Long, W As Byte, Tmp As Double
 Dim Arr()
 
 Rw = CSDL.Rows.Count:              ReDim KQ(1 To 1, 1 To 6)
  Arr = CSDL.Value
 If Max_ Then
    Tmp = Application.WorksheetFunction.Min(CSDL) - 1
 Else
    Tmp = 1 + Application.WorksheetFunction.Max(CSDL)
 End If
 For W = 1 To 6
    KQ(1, W) = Tmp
 Next W
 
 For J = 1 To Rw
    If Arr(J, 1) = Lop Then
        For W = 1 To 6
            If Max_ Then
                If Arr(J, 1 + W) > KQ(1, W) Then KQ(1, W) = Arr(J, 1 + W)
            Else
                
            End If
        Next W
    End If
 Next J
 CucTri = KQ()
End Function

(1) Các bạn diễn dịch hàm này ra ngôn ngữ VN giúp;

(2) Cho biết cách xài nó trong BT 3 nêu trên;

SA_DQ · 2/6/14

Ha, ha, . . . Vậy là cũng có người sập bẫy!

Không hiểu sao bài của mình và anh leonguyen(Dùng Dmax, Dmin) ra kết quả giống nhau nhưng lại không giống kết quả của anh Phi và của anh ChanhTQ nhỉ. Phải chăng có sai sót ở đâu đây?

Cái bẫy đó là do kí tự đại diện trong ecel nó hại nhà ngươi!
Bạn đốt đuốc tìm coi lớp 12A1 có em nào 9.5 điểm môn địa không?
Thử đi, trên CSDL í là biết nhau ngay tấp lự!

Bài học này bạn sẽ để đời, mình tin là vậy!

chuot0106 · 2/6/14

HYen17 đã viết:
Cái bẫy đó là do kí tự đại diện trong ecel nó hại nhà ngươi!
Bạn đốt đuốc tìm coi lớp 12A1 có em nào 9.5 điểm môn địa không?
Thử đi, trên CSDL í là biết nhau ngay tấp lự!

Bài học này bạn sẽ để đời, mình tin là vậy!

Em đã thử tìm trong cột môn địa không có em nào lớp 12A1 được 9.5 điểm cả. Nên mới thắc mắc vậy?

HYen17 đã viết:
Cái bẫy đó là do kí tự đại diện trong ecel nó hại nhà ngươi!
.........................................................................
Bài học này bạn sẽ để đời, mình tin là vậy!

Mong Thầy giải thích rõ hơn giúp em với!

SA_DQ · 2/6/14

Em đã thử tìm trong cột môn địa không có em nào lớp 12A1 được 9.5 điểm cả. Nên mới thắc mắc vậy?

Ở ta cũng có, nhưng chưa quen tìm; Vậy đến đây đọc ké đi, #4 & #5 í
(Cũng là người của diễn đàn ta cả đó mà!)
http://www.webketoan.vn/forum/threads/14284-Tim-hieu-ve-Advanced-Filter

leonguyenz · 2/6/14

SA_DQ đã viết:

PHP:

Option Explicit
Function CucTri(CSDL As Range, Lop As String, Optional Max_ As Boolean = True)
 Dim Rw As Long, J As Long, W As Byte, Tmp As Double
 Dim Arr()
 
 Rw = CSDL.Rows.Count:              ReDim KQ(1 To 1, 1 To 6)
  Arr = CSDL.Value
 If Max_ Then
    Tmp = Application.WorksheetFunction.Min(CSDL) - 1
 Else
    Tmp = 1 + Application.WorksheetFunction.Max(CSDL)
 End If
 For W = 1 To 6
    KQ(1, W) = Tmp
 Next W
 
 For J = 1 To Rw
    If Arr(J, 1) = Lop Then
        For W = 1 To 6
            If Max_ Then
                If Arr(J, 1 + W) > KQ(1, W) Then KQ(1, W) = Arr(J, 1 + W)
            Else
                
            End If
        Next W
    End If
 Next J
 CucTri = KQ()
End Function

Sao em thấy giải thuật bài Dic (bài #25) em viết gần giống giải thuật của hàm này.
Và sau khi phát hiện ra hàm cơ sở dữ liệu lấy luôn ký tự đại diện thì hướng dẫn:

HYen17 đã viết:
& cho fép mình khuyên bạn giải quyết bài này theo cách xài DMAX() hay DMIN() với sự trợ giúp nho nhỏ của macro là được; Bạn đừng đi theo vết xe đỗ của 2 bài trả lời cho BT 3 bên trên.
Trân trọng!

... cũng thua luôn.
Đúng là bài học để đời. Ẹc ẹc...

chuot0106 · 2/6/14

leonguyenz đã viết:
Và sau khi phát hiện ra hàm cơ sở dữ liệu lấy luôn ký tự đại diện thì hướng dẫn:

... cũng thua luôn.
Đúng là bài học để đời. Ẹc ẹc...

Em vẫn mù mờ chỗ này quá(chỗ in đậm) mong anh leonguyen và thầy HYen17 giải thích kĩ hơn cho em được không ạ và phải sửa trong code thế nào ạ(Code của em đó).

leonguyenz · 2/6/14

chuot0106 đã viết:
Em vẫn mù mờ chỗ này quá(chỗ in đậm) mong anh leonguyen và thầy HYen17 giải thích kĩ hơn cho em được không ạ và phải sửa trong code thế nào ạ(Code của em đó).

Điều kiện trong hàm CSDL lấy ký tự đại diện, ví dụ điều kiện cột Lớp là 12A1 thì hàm sẽ lấy luôn dữ liệu có Lớp là 12A1* (ví dụ 12A11, 12A12, ...). Bạn test thử sẽ biết, nhưng chỉ lấy phần đầu, chứ không phải đại diện hoàn toàn (kiểu *12A1*).

Bài tập VBA đơn giản dành cho người mới bắt đầu [Phần 2]

0901452không62

/(hông là gì!

0901452không62

Thành viên gắn bó

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

File đính kèm

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

File đính kèm

/(hông là gì!

Thành viên gắn bó

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

Ăn cùng góc phố

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

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

File đính kèm

Thành viên gắn bó

File đính kèm

Thành viên gắn bó

/(hông là gì!

/(hông là gì!

Thành viên gắn bó

/(hông là gì!

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

Thành viên gắn bó

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