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 · 16/10/13

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"

ptm0412 · 25/10/13

quanghai1969 đã viết:
Thảo đã cày hết cái file bài tập anh gởi cho Thảo chưa mà ham hố quá vậy? Hỏng lẻ học nhanh vậy sao ta?
Học hành gì mà mới 10g đã out rồi.

Vậy QuangHai post cái file bài tập đó lên đi

Hoàng Trọng Nghĩa · 25/10/13

Câu hỏi 7: Làm sao để biết bao nhiêu sự kiện trong sheet?

Khi thao tác trên sheet, ta thường có những sự kiện đại loại như thế này:

[GPECODE=vb]Private Sub Worksheet_Change(ByVal Target As Range)

End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

End Sub

v.v...

[/GPECODE]

Câu hỏi đặt ra là: Có bao nhiêu sự kiện có sẳn của một Worksheet? Và nếu là bạn, bạn sẽ làm gì để có được chúng?

Lê Duy Thương · 25/10/13

hoàng trọng nghĩa đã viết:
khi thao tác trên sheet, ta thường có những sự kiện đại loại như thế này:

[gpecode=vb]private sub worksheet_change(byval target as range)

end sub

private sub worksheet_selectionchange(byval target as range)

end sub

v.v...

[/gpecode]

câu hỏi đặt ra là: Có bao nhiêu sự kiện có sẳn của một worksheet? Và nếu là bạn, bạn sẽ làm gì để có được chúng?

hỏng biết diễn đạt như thế nào . Nhưng sự kiện của sheet không biết có cái nào khác nữa không

leonguyenz · 25/10/13

Lê Duy Thương đã viết:
hỏng biết diễn đạt như thế nào . Nhưng sự kiện của sheet không biết có cái nào khác nữa không

Cách này anh Nghĩa có chỉ cho em rồi, em tưởng là liệt kê những sự kiện đã viết.
Đề nghị anh Thương tham gia ra câu đố, không tham gia trả bài câu đố ở topic này.

Hoàng Trọng Nghĩa · 25/10/13

Lê Duy Thương đã viết:
hỏng biết diễn đạt như thế nào . Nhưng sự kiện của sheet không biết có cái nào khác nữa không

Đúng vậy đó bạn hiền, để có sự kiện của sheet ta phải làm như sau:

Đầu tiên ta chọn một Object cần có tại ComboBox Object, tại đây bạn chọn Worksheet:

Sau khi chọn vào nó, mặc định chúng sẽ hiện lên những dòng code đầu tiên cho sự kiện của Worksheet như sau:

Mã:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)


End Sub

Lúc này, để có nhiều sự kiện khác, ta đặt con trỏ vào bất kỳ chỗ nào trong thủ tục này để bấm vào ComboBox thứ hai đó là ComboBox Procedure, tại đây có rất nhiều sự kiện có sẳn của Worksheet, ta chỉ cần chọn những sự kiện cần thiết của nó.

Như thế, ta không phải mất công gõ từng chữ (mà gõ thì chắc gì nhớ cú pháp phải không?). Việc này cũng có thể áp dụng cho UserForm hoặc các Object khác.

Hoàng Trọng Nghĩa · 25/10/13

leonguyenz đã viết:
Cách này anh Nghĩa có chỉ cho em rồi, em tưởng là liệt kê những sự kiện đã viết.
Đề nghị anh Thương tham gia ra câu đố, không tham gia trả bài câu đố ở topic này.

Nó vừa liệt kê những thủ tục mình viết, đồng thời nó cũng tạo ra sự kiện nếu đối tượng đó có sự kiện. Ta cũng có thể di chuyển nhanh chóng đến thủ tục đã viết sẳn trong đó nhanh chóng bằng cách chọn vào chúng tại Procedure.

Đồng chí bạn hiền Lê Duy Thương của mình cũng bằng bằng cỡ Thảo mà thôi, tại hên là trả lời trúng đó chứ! kakakkaka

Hoàng Trọng Nghĩa · 25/10/13

Câu hỏi liên quan, nếu các sự kiện dưới đây:

PHP:

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
End Sub

Ta đều thấy cái này: ByVal Target As Range

Vậy Target là cái gì thế?

phihndhsp · 25/10/13

Target hình như là 1 cái biến kiểu range dùng để lưu 1 vùng địa chỉ nào đó được truyền vào hàm
Worksheet_BeforeRightClick ...., trong các ví dụ trên thì nó chỉ truyền vào hàm là tham trị thôi
không biết hiểu như vậy có đúng? hic hic

Hoàng Trọng Nghĩa · 25/10/13

phihndhsp đã viết:
Target hình như là 1 cái biến kiểu range dùng để lưu 1 vùng địa chỉ nào đó được truyền vào hàm
Worksheet_BeforeRightClick ...., trong các ví dụ trên thì nó chỉ truyền vào hàm là tham trị thôi
không biết hiểu như vậy có đúng? hic hic

Đúng thế, Target chẳng là cái gì hết, nhưng khi được khai báo thì nó chính là một biến kiểu Range, vì thế nếu đối tượng Range có tất cả những thuộc tính gì, phương thức nào thì Target sẽ có đầy đủ những thuộc tính và phương thức đó.

Tôi nghĩ, tới bài này, các Sư phụ, các Thầy đã có những bài tập căn bản về sự kiện của Worksheet rồi nhỉ? Các Thầy ra bài tập đi ạ!

ptm0412 · 25/10/13

Giải thích Target như vậy là chưa đầy đủ đối với các sự kiện.
Lát tối rảnh tôi sẽ viết thêm

Hoàng Trọng Nghĩa · 25/10/13

ptm0412 đã viết:
Giải thích Target như vậy là chưa đầy đủ đối với các sự kiện.
Lát tối rảnh tôi sẽ viết thêm

Em nghĩ nói Target là gì, thì câu trả lời đó là tên của một biến có kiểu là Range đã là rất hợp lý và đầy đủ lắm rồi.

Nếu Sư phụ đổi tên biến Target thành một tên biến bất kỳ nào khác thì nó vẫn hoạt động, chẳng hạn:

Mã:

Option Explicit


Private Sub Worksheet_SelectionChange(ByVal [COLOR=#0000cd]NghiaDepTrai [/COLOR]As Range)
    MsgBox [COLOR=#0000cd]NghiaDepTrai[/COLOR].Address
End Sub

Nếu Sư phụ có giải thích thì nên giải thích cho mọi người hiểu các sự kiện được thực hiện như thế nào, cách vận hành ra sao, khi nào mới xảy ra sự kiện thì em nghĩ sẽ hợp lý hơn.

ptm0412 · 25/10/13

Hoàng Trọng Nghĩa đã viết:
Nếu Sư phụ có giải thích thì nên giải thích cho mọi người hiểu các sự kiện được thực hiện như thế nào, cách vận hành ra sao, khi nào mới xảy ra sự kiện thì em nghĩ sẽ hợp lý hơn.

Bình tĩnh một chút. Tôi nói là không đầy đủ đối với sự kiện chứ không nói là sai.

Lê Duy Thương · 25/10/13

Hoàng Trọng Nghĩa đã viết:
Nó vừa liệt kê những thủ tục mình viết, đồng thời nó cũng tạo ra sự kiện nếu đối tượng đó có sự kiện. Ta cũng có thể di chuyển nhanh chóng đến thủ tục đã viết sẳn trong đó nhanh chóng bằng cách chọn vào chúng tại Procedure.

Đồng chí bạn hiền Lê Duy Thương của mình cũng bằng bằng cỡ Thảo mà thôi, tại hên là trả lời trúng đó chứ! kakakkaka

hoàn toàn đúng. tôi chỉ học vba nếu có thể đáp ứng cho công việc của tôi . thì tôi mới biết code. còn không code nó biết tôi chứ tôi hỏng biết nó.
nhân tiện nhờ các bạn viết cho đoạn code ngắn để dược kết quả như trong hình 2 trong file đính kèm.

Quang_Hải · 25/10/13

Lê Duy Thương đã viết:
hoàn toàn đúng. tôi chỉ học vba nếu có thể đáp ứng cho công việc của tôi . thì tôi mới biết code. còn không code nó biết tôi chứ tôi hỏng biết nó.
nhân tiện nhờ các bạn viết cho đoạn code ngắn để dược kết quả như trong hình 2 trong file đính kèm.

Sao giống Thương đang giỡn quá vậy trời

PHP:

Sub Noi_Chuoi()
Dim i As Long, Sarr()
Sarr = Range([A2], [A65536].End(3)).Resize(, 3).Value
For i = 1 To UBound(Sarr)
   Sarr(i, 3) = Sarr(i, 1) & Space(1) & Sarr(i, 2)
Next
[A2].Resize(i - 1, 3) = Sarr
End Sub

Lê Duy Thương · 25/10/13

quanghai1969 đã viết:
Sao giống Thương đang giỡn quá vậy trời

PHP:

Sub Noi_Chuoi() Dim i As Long, Sarr() Sarr = Range([A2], [A65536].End(3)).Resize(, 3).Value For i = 1 To UBound(Sarr) Sarr(i, 3) = Sarr(i, 1) & Space(1) & Sarr(i, 2) Next [A2].Resize(i - 1, 3) = Sarr End Sub

không dùng vòng lặp được không ?. code này vơi quang hai thì muỗi rồi
--=0

ptm0412 · 25/10/13

Nói về sự kiện và thủ tục cho sự kiện:

Trước hết, phải xác định rõ thế nào là sự kiện và thế nào là thủ tục cho sự kiện.
Ngoài ra các thủ tục sự kiện của sheet là các thủ tục có thể có tham số, chứ không phải hàm như phihn nói. Thủ tục sự kiện Sheet_Activate có tham số đối số gì đâu, sao gọi là hàm được?

1. Sự kiện là gì và thủ tục sự kiện là gì:

Bất kỳ 1 thao tác nào chúng ta tác động lên sheet là xảy ra 1 sự kiện. Thủ tục thực thi ngay sau khi sự kiện diễn ra và do xảy ra sự kiện mà chạy, đó là thủ tục sự kiện. Tuy nhiên không phải sự kiện nào cũng có sẵn 1 thủ tục sự kiện.

2. Phân loại:
Phân loại 1: Thủ tục sự kiện có tham số Cancel (As Boolean) và thủ tục sự kiện không có Cancel. Thí dụ thủ tục sự kiện Change không có tham số Cancel

Phân loại 2: Thủ tục sự kiện có tham số và Thủ tục sự kiện không có tham số. Thí dụ thủ tục Worksheet_Activate không có tham số.

Phân loại 3: Nếu phân loại các thủ tục sự kiện theo tham số thì các thủ tục sự kiện của sheet có 2 loại:
- Tham số truyền là Target As Range
- Tham số truyền không phải Target As Range.

3. Target là gì:

a. Trước tiên Target trong các thủ tục sự kiện là 1 tham số truyền, và có kiểu Range. (Gọi là biến nghe không ổn lắm). Sự kiện có tham số Target là sự kiện đánh vào 1 Range trên sheet. Tên tham số có thể đặt là gì cũng được, (dĩ nhiên), nhưng không phải ngẫu nhiên người ta đặt tên tham số là Target (mục tiêu): Target chính là range mục tiêu của sự kiện vừa diễn ra: Click chọn, tô chọn, thay đổi giá trị, nhấn chuột phải, nhấn đúp (double click), nhấn vào hyperlink trong range.

- Range nào không phải mục tiêu của sự kiện vừa diễn ra thì không phải Target

- Khi đặt kiểu cho tham số truyền là Range thì chỉ khi nào mục tiêu của sự kiện là range thì thủ tục sự kiện mới chạy, thí dụ thủ tục sự kiện Selection_Change không chạy khi click chọn biểu đồ, click chọn AutoShape, mà chỉ chạy khi click chọn (tô chọn) 1 Range.

Như vậy Target là 1 Range vừa mới chịu sự tác động, là mục tiêu của sự kiện vừa mới diễn ra.

b. Target là 1 Range, nên Target có thể là 1 Range 1 ô hay nhiều ô, và có thể là các ô (dãy ô) không liên tục. Thí dụ viết thủ tục cho sự kiện click chọn ô, tô chọn vùng:

PHP:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
MsgBox Target.Address
End Sub

Và thử click chọn 1 ô, tô chọn 1 vùng, nhấn Ctrl hoặc Shift chọn nhiều vùng không liên tục hoặc liên tục.

c. Target là 1 Range nên có đầy đủ tính chất của 1 range (Properties), và có thể dùng các phương thức khác nhau (methods) để đánh vào Target như Copy, định dạng, Find, ... Xin nhấn mạnh rằng target là 1 range với đầy đủ các thuộc tính, chứ không chỉ là thuộc tính giá trị như phihn nói. Chẳng qua giá trị (value) là property mặc định của range mà thôi. Nên nếu dùng câu lệnh MsgBox Target ta sẽ có giá trị của Target (nếu Target là ô đơn lẻ).

Quang_Hải · 25/10/13

Lê Duy Thương đã viết:
không dùng vòng lặp được không ?. code này vơi quang hai thì muỗi rồi

Không dùng vòng lặp chắc phải xử lý trên sheet mới được

PHP:

Sub noichuoi2()
Range([A2], [A65536].End(3)).Offset(, 2).FormulaR1C1 = "=RC[-2]& "" ""&RC[-1]"
End Sub

Hoặc xóa luôn công thức

PHP:

Sub noichuoi2()
With Range([A2], [A65536].End(3)).Offset(, 2)
   .FormulaR1C1 = "=RC[-2]& "" ""&RC[-1]"
   .Value = .Value
End With
End Sub

ptm0412 · 26/10/13

Thủ tục sự kiện có tham số Cancel.

Gồm có 2 thủ tục cho 2 sự kiện là DoubleClick và RightClick.

Đó là Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean) và
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

Tham số Cancel có kiểu boolean, và mặc định có giá trị False. Nếu ta gán giá trị True cho nó, sự kiện tiếp theo lẽ ra phải diễn ra, sẽ không diễn ra nữa.

Khi nhấn chuột phải vào 1 ô, bình thường sẽ xuất hiện menu ngữ cảnh (còn gọi là menu chuột phải). Nếu ta viết thủ tục sau:

PHP:

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
Cancel = True
End Sub

Thì menu chuột phải sẽ không xuất hiện.

Đối với sự kiện DoubleClick, bình thường sẽ có 1 trong 2 sự kiện tiếp theo xảy ra:
- Ô được double click, chuyển sang dạng Edit Mode để chỉnh sửa nội dung
- Nếu Option đã được thay đổi là Not allow edit directly in cells, và nếu ô có chứa công thức tham chiếu đến ô khác, ô hiện hành sẽ nhảy sang ô tham chiếu đó.

Nếu ta viết thủ tục sự kiện Worksheet_BeforeDoubleClick và gán Cancel = True, thì chẳng sự kiện nào trong 2 sự kiện trên xảy ra.

ptm0412 · 26/10/13

Có một số bạn thường nói "Sự kiện WorkSheet_Change", một cách nói vắn tắt và có thể hiểu là thủ tục cho sự kiện Change. Tạm chấp nhận.

Tuy nhiên nếu có bạn nào nói "định dạng ô không phải sự kiện nên code worksheet_change không chạy" thì là phát biểu sai.

Bất cứ hành vi nào tác động lên sheet đều là sự kiện. Tuy nhiên không phải sự kiện nào làm thay đổi range đều được thủ tục sự kiện WorkSheet_Change "bắt". Sự kiện tô màu kẻ khung ô vẫn diễn ra, chỉ có điều sự kiện đó không được đưa vào danh sách sự kiện của thủ tục sự kiện, hoặc nói khác đi, sự kiện đó không dẫn đến việc thực thi thủ tục sự kiện mà thôi.

Lê Duy Thương · 26/10/13

quanghai1969 đã viết:
Không dùng vòng lặp chắc phải xử lý trên sheet mới được

PHP:

Sub noichuoi2() Range([A2], [A65536].End(3)).Offset(, 2).FormulaR1C1 = "=RC[-2]& "" ""&RC[-1]" End Sub

Hoặc xóa luôn công thức

PHP:

Sub noichuoi2() With Range([A2], [A65536].End(3)).Offset(, 2) .FormulaR1C1 = "=RC[-2]& "" ""&RC[-1]" .Value = .Value End With End Sub

Đối với các anh em mới tập Vba thì nên dùng cách xử lý trên cell. Còn có thể viết cách khác. Như Do loop... cũng được.

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

Chuyên gia GPE

File đính kèm

Bad Excel Member

Chuyên gia GPE

Cạo lấy gì gội (Dịch quá không gội được)

File đính kèm

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

Chuyên gia GPE

File đính kèm

Chuyên gia GPE

Chuyên gia GPE

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

Chuyên gia GPE

Bad Excel Member

Chuyên gia GPE

Bad Excel Member

Cạo lấy gì gội (Dịch quá không gội được)

File đính kèm

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

Cạo lấy gì gội (Dịch quá không gội được)

Bad Excel Member

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

Bad Excel Member

Bad Excel Member

Cạo lấy gì gội (Dịch quá không gội được)

Facebook

Group