Contor Excel VBA
Există diferite funcții în MS Excel pentru a număra valorile, indiferent dacă este un șir, numere. Numărarea se poate face pe baza unor criterii. Funcțiile includ COUNT, COUNTA, COUNTBLANK, COUNTIF și COUNTIFS în excel. Cu toate acestea, aceste funcții nu pot îndeplini anumite sarcini, cum ar fi numărarea celulelor pe baza culorii lor, numărarea numai a valorilor aldine etc. De aceea vom crea un contor în VBA, astfel încât să putem conta pentru aceste tipuri de sarcini în Excel.
Să creăm un contor în Excel VBA.
Exemple de contor Excel VBA
Mai jos sunt exemple de contor în VBA.
Exemplul nr. 1
Să presupunem că avem date ca mai sus pentru 32 de rânduri. Vom crea un contor VBA, care va număra valorile, care sunt mai mari de 50 și încă un contor pentru a număra valorile, care sunt mai mici de 50. Vom crea codul VBA în acest fel, astfel încât utilizatorul să poată avea date pentru rânduri nelimitate în excel.
Pentru a face același lucru, pașii ar fi:
Asigurați-vă că fila Dezvoltator Excel este vizibilă. Pentru a face fila vizibilă (dacă nu), pașii sunt:
Faceți clic pe fila „Fișier” din panglică și alegeți „Opțiune” din listă.
Alegeți „ Personalizați panglica” din listă, bifați caseta „Dezvoltator” și faceți clic pe OK .
Acum , fila „Dezvoltator” este vizibilă.
Introduceți butonul de comandă folosind comanda „Inserare” disponibilă în grupul „Controale” din fila „Dezvoltator” .
În timp ce apăsați tasta ALT , creați butonul de comandă cu mouse-ul. Dacă continuăm să apăsăm tasta ALT , atunci marginile butonului de comandă merg automat cu marginea celulelor.
Faceți clic dreapta pe butonul de comandă pentru a deschide meniul contextual (asigurați-vă că „Mod de proiectare” este activat; în caz contrar, nu vom putea deschide meniul contextual).
Alegeți „Proprietăți” din meniu.
Schimbați proprietățile butonului de comandă, adică, Nume, Legenda și Font etc.
Faceți clic din nou cu butonul din dreapta și alegeți „Vizualizați codul” din meniul contextual.
Editorul Visual Basic este deschis acum și, în mod implicit, este deja creat un subrutină pentru butonul de comandă.
Vom scrie cod acum. Vom declara 3 variabile. Una pentru scop buclă, una pentru numărare și una pentru stocarea valorii pentru ultimul rând.
Vom folosi codul pentru a selecta celula A1 și apoi regiunea curentă a celulei A1 și apoi vom coborî la ultimul rând umplut pentru a obține ultimul număr de rând completat.
Vom rula o buclă „pentru” în VBA pentru a verifica valorile scrise în celula A2 până la ultima celulă completată din coloana A. Vom mări valoarea variabilei „contor” cu 1 dacă valoarea este mai mare de 50 și vom schimba culoarea fontului celulei la „Albastru”, iar dacă valoarea este mai mică de 50, atunci culoarea fontului celulei ar fi „Roșu”.
După verificare și numărare, trebuie să afișăm valorile. Pentru a face același lucru, vom folosi „VBA MsgBox”.
Cod:
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 "There are " & counter & " values which are greater than 50" & _ vbCrLf & "There are " & lastrow - counter & " values which are less than 50" End Sub
Deactivate the ‘Design Mode’ and click on the ‘Command button.’ The result would be as follows.
Example #2
Suppose we want to create the time counter using excel VBA as follows:
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.
