Excel VBA-tæller
Der er de forskellige funktioner i MS Excel til at tælle værdier, uanset om det er en streng, tal. Tælling kan ske på baggrund af nogle kriterier. Funktioner inkluderer COUNT, COUNTA, COUNTBLANK, COUNTIF og COUNTIFS i excel. Disse funktioner kan dog ikke udføre nogle opgaver som at tælle cellerne baseret på deres farve, kun tælle fedte værdier osv. Derfor opretter vi en tæller i VBA, så vi kan tælle for disse typer opgaver i excel.
Lad os oprette en tæller i excel VBA.
Eksempler på Excel VBA-tæller
Nedenfor er eksempler på tælleren i VBA.
Eksempel nr. 1
Antag, at vi har data som ovenfor for 32 rækker. Vi opretter en VBA-tæller, der tæller værdierne, der er større end 50 og en tæller mere til at tælle værdierne, som er mindre end 50. Vi opretter VBA-koden på denne måde, så brugeren kan have data til ubegrænset række i Excel.
For at gøre det samme vil trin være:
Sørg for, at Excel-fanen Udvikler er synlig. For at gøre fanen synlig (hvis ikke), er trinene:
Klik på fanen 'File' i båndet, og vælg 'Option' fra listen.
Vælg ' Tilpas bånd' fra listen, marker afkrydsningsfeltet for 'Udvikler', og klik på OK .
Nu er fanen 'Udvikler' synlig.
Indsæt kommandoknappen ved hjælp af kommandoen 'Indsæt' tilgængelig i gruppen 'Kontrol' under fanen 'Udvikler' .
Mens du trykker på ALT- tasten, skal du oprette kommandoknappen med musen. Hvis vi fortsætter med at trykke på ALT-tasten , går kanterne på kommandoknappen automatisk med cellernes kant.
Højreklik på kommandoknappen for at åbne den kontekstuelle menu (sørg for, at 'Design Mode' er aktiveret; ellers kan vi ikke åbne den kontekstuelle menu).
Vælg 'Egenskaber' i menuen.
Skift egenskaberne for kommandoknappen, dvs. navn, billedtekst og skrifttype osv.
Højreklik igen og vælg 'Vis kode' i kontekstmenuen.
Visual Basic Editor åbnes nu, og som standard oprettes der allerede en subrutine til kommandoknappen.
Vi skriver kode nu. Vi erklærer 3 variabler. En til loopformål, en til at tælle og en til at gemme værdien for den sidste række.
Vi bruger koden til at vælge celle A1 og derefter den aktuelle region i celle A1 og derefter komme ned til den sidst udfyldte række for at få det sidst udfyldte række nummer.
Vi kører en 'for' løkke i VBA for at kontrollere de værdier, der er skrevet i A2-cellen til den sidst udfyldte celle i A-kolonnen. Vi øger værdien af 'tæller' -variablen med 1, hvis værdien er større end 50 og ændrer celleens skrifttypefarve til 'Blå', og hvis værdien er mindre end 50, så er celleens skriftfarve ville være 'rød'.
Efter kontrol og tælling skal vi vise værdierne. For at gøre det samme bruger vi 'VBA MsgBox.'
Kode:
Privat undertællingCellsbyValue_Click () Dim i, tæller Som heltal Dim lastrow Som Long lastrow = Range ("A1"). CurrentRegion.End (xlDown). Row For i = 2 To lastrow If Cells (i, 1). Value> 50 Then tæller = tæller + 1 celler (i, 1). Font.ColorIndex = 5 Andre celler (i, 1). Font.ColorIndex = 3 Afslut hvis næste i MsgBox "Der er" & tæller & "værdier, der er større end 50" & _ vbCrLf & "Der er" & lastrow - counter & "værdier, der er mindre end 50" End Sub
Deaktiver 'Design Mode' og klik på 'Command-knappen'. Resultatet ville være som følger.
Eksempel 2
Antag, at vi vil oprette tidstælleren ved hjælp af excel VBA som følger:
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.
