Penghitung VBA | Bagaimana cara membuat Penghitung di Excel VBA? (dengan Contoh)

Penghitung VBA Excel

Ada berbagai macam fungsi di MS Excel untuk menghitung nilai apakah itu string, angka. Penghitungan dapat dilakukan berdasarkan beberapa kriteria. Fungsinya meliputi COUNT, COUNTA, COUNTBLANK, COUNTIF, dan COUNTIFS di excel. Namun, fungsi-fungsi ini tidak dapat melakukan beberapa tugas seperti menghitung sel berdasarkan warnanya, hanya menghitung nilai tebal, dll. Itulah sebabnya kami akan membuat penghitung di VBA sehingga kami dapat menghitung jenis tugas ini di excel.

Mari kita buat beberapa penghitung di excel VBA.

Contoh Penghitung VBA Excel

Anda dapat mengunduh Template Excel Penghitung VBA ini di sini - Template Excel Penghitung VBA

Di bawah ini adalah contoh penghitung di VBA.

Contoh 1

Misalkan, kami memiliki data seperti di atas untuk 32 baris. Kami akan membuat penghitung VBA, yang akan menghitung nilai, yang lebih besar dari 50 dan satu penghitung lagi untuk menghitung nilai, yang kurang dari 50. Kami akan membuat kode VBA dengan cara ini sehingga pengguna dapat memiliki data untuk baris tak terbatas di excel.

Untuk melakukan hal yang sama, langkah-langkahnya adalah:

Pastikan tab Pengembang Excel terlihat. Untuk membuat tab terlihat (jika tidak), langkah-langkahnya adalah:

Klik pada tab 'File' di pita dan pilih 'Opsi' dari daftar.

Pilih ' Sesuaikan Pita' dari daftar, centang kotak untuk 'Pengembang' , dan klik OK .

Sekarang tab 'Pengembang' terlihat.

Sisipkan tombol perintah menggunakan perintah 'Sisipkan' yang tersedia di grup 'Kontrol' di tab 'Pengembang' .

Sambil menekan tombol ALT , buat tombol perintah dengan mouse. Jika kita terus menekan tombol ALT , maka tepi tombol perintah secara otomatis mengikuti batas sel.

Klik kanan pada tombol perintah untuk membuka menu kontekstual (pastikan 'Mode Desain' diaktifkan jika tidak kami tidak akan dapat membuka menu kontekstual).

Pilih 'Properties' dari menu.

Ubah properti tombol perintah yaitu Nama, Keterangan, dan Font, dll.

Klik kanan lagi dan pilih 'Lihat Kode' dari menu kontekstual.

Editor Visual Basic dibuka sekarang dan secara default, subrutin sudah dibuat untuk tombol perintah.

Kami akan menulis kode sekarang. Kami akan mendeklarasikan 3 variabel. Satu untuk tujuan perulangan, satu untuk menghitung dan satu untuk menyimpan nilai untuk baris terakhir.

Kami akan menggunakan kode untuk memilih sel A1 dan kemudian wilayah sel A1 saat ini dan kemudian turun ke baris terisi terakhir untuk mendapatkan nomor baris terisi terakhir.

Kami akan menjalankan loop 'untuk' di VBA untuk memeriksa nilai yang ditulis dalam sel A2 ke sel yang terakhir diisi di kolom A. Kami akan meningkatkan nilai variabel 'counter' sebesar 1 jika nilainya lebih besar dari 50 dan akan mengubah warna font sel menjadi 'Biru' dan jika nilainya kurang dari 50 maka warna font sel akan menjadi 'Merah' .

Setelah memeriksa dan menghitung, kita perlu menampilkan nilainya. Untuk melakukan hal yang sama, kami akan menggunakan 'VBA MsgBox'.

Kode:

 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 "Ada" & counter & "nilai yang lebih besar dari 50" & _ vbCrLf & Nilai "Ada" & lastrow - counter & "yang kurang dari 50" End Sub

Nonaktifkan 'Mode Desain' dan klik 'tombol Perintah' . Hasilnya adalah sebagai berikut.

Contoh # 2

Misalkan kita ingin membuat penghitung waktu menggunakan excel VBA sebagai berikut:

Jika kita mengklik tombol 'Start' , pengatur waktu akan dimulai dan jika kita mengklik tombol 'Stop' , pengatur waktu berhenti.

Untuk melakukan hal yang sama, langkah-langkahnya adalah:

Buat format seperti ini di lembar excel.

Ubah format sel A2 menjadi 'hh: mm: ss' .

Gabungkan sel C3 ke G7 dengan menggunakan perintah Merge and Center Excel di grup 'Alignment' di tab 'Home' .

Beri referensi sel A2 untuk sel yang baru saja digabungkan lalu lakukan pemformatan seperti membuat gaya font menjadi 'Baskerville' , ukuran font menjadi 60, dll.

Buat dua tombol perintah 'Mulai' dan 'Hentikan' menggunakan perintah 'Sisipkan' yang tersedia di grup 'Kontrol' di tab 'Pengembang' .

Dengan menggunakan perintah 'Properti' yang tersedia di grup 'Kontrol' di tab 'Pengembang' , ubah propertinya.

Pilih tombol perintah satu per satu dan pilih perintah 'Lihat Kode' dari grup 'Kontrol' di tab 'Pengembang' untuk menulis kode sebagai berikut.

Pilih dari drop-down tombol perintah yang sesuai.

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 ‘pass’ variable and add 1 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 macro will not work.
Use the ‘For’ loop when it is decided already that for how many times, the code in VBA loop will run.