Bộ đếm VBA - Làm thế nào để tạo bộ đếm trong Excel VBA? (với các ví dụ)

Mục lục

Bộ đếm VBA trong Excel

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à 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

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 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 ' Tùy chỉnh ruy-băng' từ danh sách, chọn hộp 'Nhà phát triển' 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, chẳng hạn như 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à 'Đỏ.'

Sau khi kiểm tra và đếm, chúng ta cần hiển thị các giá trị. Để làm điều 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ó cá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:

If we click on the ‘Start’ button, the timer starts, and if we click on the ‘Stop’ button, the timer stops.

To do the same, steps would be:

Create a format like this in an excel sheet.

Change the format of the cell A2 as ‘hh:mm: ss.’

Merge the cells C3 to G7 by using the Merge and Center Excel command in the ‘Alignment’ group in the ‘Home’ tab.

Give the reference of cell A2 for just merged cell and then do the formatting like make the font style to ‘Baskerville,’ font size to 60, etc.

Create two command buttons, ‘Start’ and ‘Stop’ using the ‘Insert’ command available in the ‘Controls’ group in the ‘Developer’ tab.

Using the ‘Properties’ command available in the ‘Controls’ group in the ‘Developer’ tab, change the properties.

Select the commands buttons one by one and choose the ‘View Code’ command from the ‘Controls’ group in the ‘Developer’ tab to write the code as follows.

Choose from the drop-down the appropriate command button.

Insert a module into ‘ThisWorkbook‘ by right-clicking on the ‘Thisworkbook’ and then choose ‘Insert’ and then ‘Module.’

Write the following code in the module.

Code:

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 Counter").Range("A2").Value - TimeValue("00:00:01") start_time End Sub

We have used the ‘onTime‘ method of the Application object, which is used to run a procedure at a scheduled time. The procedure, which we have scheduled to run, is “next_moment.”

Save the code. Write the time in the A2 cell and click on the ‘Start’ button to start the time counter.

Example #3

Suppose we have a list of students along with marks scored by them. We want to count the number of students who passed and who failed.

To do the same, we will write the VBA code.

Steps would be:

Open Visual Basic editor by pressing shortcut in excel Alt+F11 and double click on ‘Sheet3 (Counting Number of students)’ to insert a subroutine based on an event in Sheet3.

Choose ‘Worksheet’ from the dropdown.

As we pick ‘Worksheet’ from the list, we can see, there are various events in the adjacent dropdown. We need to choose ‘SelectionChange’ from the list.

We will declare the VBA variable ‘lastrow’ for storing last row number as a list for students can increase, ‘pass’ to store a number of students who passed, and ‘fail’ to store a number of students who failed.

We will store the value of the last row number in ‘lastrow.’

We will create the ‘for’ loop for counting based on condition.

We have set the condition if the total marks are greater than 99, then add the value 1 to the ‘pass’ variable and add one value to the ‘fail’ variable if the condition fails.

The last statement makes the heading ‘Summary’ bold.

To print the values in the sheet, the code would be:

Code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim lastrow As Long Dim pass As Integer Dim fail As Integer lastrow = Range("A1").CurrentRegion.End(xlDown).Row For i = 2 To lastrow If Cells(i, 5)> 99 Then pass = pass + 1 Else fail = fail + 1 End If Cells(1, 7).Font.Bold = True Next i Range("G1").Value = "Summary" Range("G2").Value = "The number of students who passed is " & pass Range("G3").Value = "The number of students who failed is " & fail End Sub

Now whenever there is a change in selection, values will be calculated again as below:

Things to Remember

Save the file after writing code in VBA with .xlsm excel extension; otherwise, the macro will not work.
Use the ‘For’ loop when it is decided already for how many times the code in the VBA loop will run.