seychellesartprojects.org

Bộ đếm VBA trong Excel

Có nhiều chức năng khác nhau trong MS Excel để đếm các giá trị cho dù đó là một chuỗi, số. Việc đếm có thể được thực hiện dựa trên một số tiêu chí. Các hàm bao gồm COUNT, COUNTA, COUNTBLANK, COUNTIF và COUNTIFS trong excel. Tuy nhiên, các hàm này không thể thực hiện một số tác vụ như đếm các ô dựa trên màu của chúng, chỉ đếm các giá trị in đậm, v.v. Đó là lý do tại sao chúng ta sẽ tạo một bộ đếm trong VBA để chúng ta có thể đếm các loại tác vụ này trong excel.

Hãy để chúng tôi tạo một số bộ đếm trong VBA excel.

Ví dụ về Bộ đếm VBA trong Excel

Bạn có thể tải xuống Mẫu Excel Counter VBA này tại đây - Mẫu VBA Counter Excel

Dưới đây là các ví dụ về bộ đếm trong VBA.

Ví dụ 1

Giả sử, chúng ta có dữ liệu như trên cho 32 hàng. Chúng tôi sẽ tạo một bộ đếm VBA, bộ đếm này sẽ đếm các giá trị lớn hơn 50 và một bộ đếm nữa để đếm các giá trị nhỏ hơn 50. Chúng tôi sẽ tạo mã VBA theo cách này để người dùng có thể có dữ liệu cho hàng không giới hạn trong excel.

Để làm điều tương tự, các bước sẽ là:

Đảm bảo rằng  tab Nhà phát triển Excel hiển thị. Để hiển thị tab (nếu không), các bước là:

Nhấp vào tab 'Tệp' trong ruy-băng và chọn 'Tùy chọn' từ danh sách.

Chọn ' Customize Ribbon' từ danh sách, chọn hộp 'Developer' và nhấp vào OK .

Bây giờ tab 'Nhà phát triển' đã hiển thị.

Chèn nút lệnh bằng lệnh 'Chèn' có sẵn trong nhóm 'Điều khiển' trong tab 'Nhà phát triển' .

Trong khi nhấn phím ALT , tạo nút lệnh bằng chuột. Nếu chúng ta tiếp tục nhấn phím ALT , thì các cạnh của nút lệnh sẽ tự động đi với đường viền của các ô.

Nhấp chuột phải vào nút lệnh để mở menu ngữ cảnh (đảm bảo 'Chế độ thiết kế' được kích hoạt nếu không chúng tôi sẽ không thể mở menu ngữ cảnh).

Chọn 'Thuộc tính' từ menu.

Thay đổi các thuộc tính của nút lệnh, tức là Tên, Chú thích và Phông chữ, v.v.

Nhấp chuột phải một lần nữa và chọn 'Mã Chế độ xem' từ trình đơn ngữ cảnh.

Visual Basic Editor được mở ngay bây giờ và theo mặc định, một chương trình con đã được tạo cho nút lệnh.

Chúng tôi sẽ viết mã ngay bây giờ. Chúng tôi sẽ khai báo 3 biến. Một cho mục đích lặp, một để đếm và một để lưu giá trị cho hàng cuối cùng.

Chúng tôi sẽ sử dụng mã để chọn ô A1 và sau đó là vùng hiện tại của ô A1 và sau đó xuống hàng được điền cuối cùng để lấy số hàng được điền cuối cùng.

Chúng tôi sẽ chạy vòng lặp 'for' trong VBA để kiểm tra các giá trị được ghi trong ô A2 đến ô được điền cuối cùng trong cột A. Chúng tôi sẽ tăng giá trị của biến 'bộ đếm' lên 1 nếu giá trị lớn hơn 50 và sẽ thay đổi màu phông chữ của ô thành 'Xanh lam' và nếu giá trị nhỏ hơn 50 thì màu phông chữ của ô sẽ là 'Màu đỏ' .

Sau khi kiểm tra và đếm, chúng ta cần hiển thị các giá trị. Để làm tương tự, chúng tôi sẽ sử dụng 'VBA MsgBox'.

Mã:

 Private Sub CountingCellsbyValue_Click () Dim i, counter As Integer Dim lastrow As Long lastrow = Range ("A1"). CurrentRegion.End (xlDown) .Row For i = 2 To lastrow If Cells (i, 1) .Value> 50 Then counter = counter + 1 Cells (i, 1) .Font.ColorIndex = 5 Else Cells (i, 1) .Font.ColorIndex = 3 End If Next i MsgBox "Có giá trị" & counter & "lớn hơn 50" & _ vbCrLf & "Có" & cuối cùng - bộ đếm & "các giá trị nhỏ hơn 50" End Sub 

Hủy kích hoạt 'Chế độ Thiết kế' và nhấp vào 'Nút lệnh' . Kết quả sẽ như sau.

Ví dụ số 2

Giả sử chúng ta muốn tạo bộ đếm thời gian bằng excel VBA như sau:

Nếu chúng ta nhấp vào nút 'Bắt đầu' , đồng hồ sẽ bắt đầu và nếu chúng ta nhấp vào nút 'Dừng' , đồng hồ sẽ dừng.

Để làm điều tương tự, các bước sẽ là:

Tạo một định dạng như thế này trong một trang tính excel.

Thay đổi định dạng của ô A2 thành 'hh: mm: ss' .

Hợp nhất các ô từ C3 với G7 bằng cách sử dụng lệnh Merge and Center Excel  trong nhóm 'Alignment' trong tab 'Home' .

Cung cấp tham chiếu của ô A2 cho ô vừa hợp nhất và sau đó thực hiện định dạng như tạo kiểu phông chữ thành 'Baskerville' , kích thước phông chữ thành 60, v.v.

Tạo hai nút lệnh 'Bắt đầu' và 'Dừng' bằng lệnh 'Chèn' có sẵn trong nhóm 'Điều khiển' trong tab 'Nhà phát triển' .

Sử dụng lệnh 'Thuộc tính' có sẵn trong nhóm 'Điều khiển' trong tab 'Nhà phát triển' , hãy thay đổi các thuộc tính.

Chọn lần lượt các nút lệnh và chọn lệnh 'Mã Chế độ xem' từ nhóm 'Điều khiển' trong tab 'Nhà phát triển' để viết mã như sau.

Chọn từ menu thả xuống nút lệnh thích hợp.

Chèn một mô-đun vào 'ThisWorkbook' bằng cách nhấp chuột phải vào 'Thisworkbook' , sau đó chọn 'Chèn' rồi chọn 'Mô-đun' .

Viết mã sau vào mô-đun.

Mã:

 Sub start_time () Application.OnTime Now + TimeValue ("00:00:01"), "next_moment" End Sub Sub end_time () Application.OnTime Now + TimeValue ("00:00:01"), "next_moment",, False End Sub Sub next_moment () If Worksheets ("Time Counter"). Range ("A2"). Value = 0 Then Exit Sub Worksheets ("Time Counter"). Range ("A2"). Value = Worksheets ("Time Bộ đếm "). Phạm vi (" A2 "). Giá trị - Giá trị thời gian (" 00:00:01 ") start_time End Sub 

Chúng tôi đã sử dụng phương thức ' onTime ' của đối tượng Ứng dụng , được sử dụng để chạy một thủ tục tại một thời điểm đã lên lịch. Thủ tục mà chúng tôi đã lên lịch chạy là “next_moment” .

Lưu mã. Viết thời gian vào ô A2 và nhấp vào nút 'Bắt đầu' để bắt đầu bộ đếm thời gian.

Ví dụ # 3

Giả sử, chúng ta có một danh sách các sinh viên cùng với điểm của họ. Chúng tôi muốn đếm số học sinh đã đậu và không đạt.

Để làm tương tự, chúng tôi sẽ viết mã VBA.

Các bước sẽ là:

Mở trình soạn thảo Visual Basic bằng cách nhấn phím tắt trong excel Alt + F11 và nhấp đúp vào 'Sheet3 (Đếm số học sinh)' để chèn một chương trình con dựa trên một sự kiện trong Sheet3.

Chọn 'Trang tính' từ trình đơn thả xuống.

Khi chúng tôi chọn 'Trang tính' từ danh sách, chúng tôi có thể thấy, có nhiều sự kiện khác nhau trong trình đơn thả xuống liền kề. Chúng ta cần chọn 'SelectionChange' từ danh sách.

Chúng ta sẽ khai báo biến VBA 'lastrow' để lưu trữ số hàng cuối cùng vì danh sách sinh viên có thể tăng lên, 'pass' để lưu trữ một số sinh viên đã đậu và 'fail' để lưu trữ một số sinh viên không đạt.

Chúng tôi sẽ lưu trữ giá trị của số hàng cuối cùng trong 'lastrow'.

Chúng tôi sẽ tạo vòng lặp 'for' để đếm dựa trên điều kiện.

Chúng tôi đã đặt điều kiện nếu tổng số dấu lớn hơn 99 thì thêm giá trị 1 vào biến 'đạt' và thêm 1 giá trị vào biến 'không đạt' nếu điều kiện không thành công.

Câu lệnh cuối cùng làm cho tiêu đề 'Tóm tắt' được in đậm.

Để in các giá trị trong trang tính, mã sẽ là:

Mã:

 Private Sub Worksheet_SelectionChange (ByVal Target As Range) Dim lastrow Khi vượt qua Long Dim khi Integer Dim thất bại As Integer lastrow = Range ("A1"). CurrentRegion.End (xlDown) .Row For i = 2 To lastrow If Cells (i, 5 )> 99 Sau đó pass = pass + 1 Khác fail = fail + 1 End If Cells (1, 7) .Font.Bold = True Next i Range ("G1"). Value = "Summary" Range ("G2"). Value = "Số học sinh đã đậu là" & vượt qua Phạm vi ("G3"). Giá trị = "Số học sinh không đạt là" & không đạt End Sub 

Bây giờ bất cứ khi nào có sự thay đổi trong lựa chọn, các giá trị sẽ được tính toán lại như sau:

Những điều cần ghi nhớ

1. Lưu tệp sau khi viết mã trong VBA với phần mở rộng .xlsm excel, nếu không macro sẽ không hoạt động.
2. Sử dụng vòng lặp 'For' khi đã quyết định rằng mã trong vòng lặp VBA sẽ chạy bao nhiêu lần.