VBA-teller | Hoe maak je een teller in Excel VBA? (met voorbeelden)

Excel VBA-teller

Er zijn verschillende functies in MS Excel om waarden te tellen, of het nu een string is, getallen. Tellen kan worden gedaan op basis van enkele criteria. Functies omvatten COUNT, COUNTA, COUNTBLANK, COUNTIF en COUNTIFS in Excel. Deze functies kunnen echter sommige taken niet uitvoeren, zoals het tellen van de cellen op basis van hun kleur, het tellen van alleen vetgedrukte waarden, enz. Daarom zullen we een teller in VBA aanmaken zodat we voor dit soort taken in Excel kunnen tellen.

Laten we een teller maken in Excel VBA.

Voorbeelden van Excel VBA-teller

U kunt deze VBA-teller Excel-sjabloon hier downloaden - VBA-teller Excel-sjabloon

Hieronder staan voorbeelden van de teller in VBA.

Voorbeeld 1

Stel dat we gegevens hebben zoals hierboven voor 32 rijen. We zullen een VBA-teller maken, die de waarden telt die groter zijn dan 50 en nog een teller om de waarden te tellen, die kleiner zijn dan 50. We zullen de VBA-code op deze manier maken zodat de gebruiker gegevens kan hebben voor onbeperkt aantal rijen in Excel.

Om hetzelfde te doen, zouden de stappen zijn:

Zorg ervoor dat het tabblad Ontwikkelaar Excel zichtbaar is. Om het tabblad zichtbaar te maken (zo niet), zijn de stappen:

Klik op het tabblad 'Bestand' in het lint en kies 'Optie' uit de lijst.

Kies ' Lint aanpassen' uit de lijst, vink het vakje voor 'Ontwikkelaar' aan en klik op OK .

Nu is het tabblad 'Ontwikkelaar' zichtbaar.

Voeg de opdrachtknop in met de opdracht 'Invoegen' die beschikbaar is in de groep 'Besturing' op het tabblad 'Ontwikkelaar' .

Terwijl u de ALT- toets ingedrukt houdt, maakt u de opdrachtknop met de muis. Als we de ALT-toets blijven indrukken , gaan de randen van de opdrachtknop automatisch mee met de rand van de cellen.

Klik met de rechtermuisknop op de opdrachtknop om het contextmenu te openen (zorg ervoor dat 'Ontwerpmodus' is geactiveerd, anders kunnen we het contextmenu niet openen).

Kies 'Eigenschappen' in het menu.

Wijzig de eigenschappen van de opdrachtknop, dwz naam, bijschrift en lettertype, enz.

Klik nogmaals met de rechtermuisknop en kies 'Bekijk code' in het contextmenu.

Visual Basic Editor is nu geopend en standaard is er al een subroutine gemaakt voor de opdrachtknop.

We gaan nu code schrijven. We zullen 3 variabelen declareren. Eén voor lusdoeleinden, één om te tellen en één om de waarde voor de laatste rij op te slaan.

We zullen de code gebruiken om cel A1 te selecteren en vervolgens het huidige gebied van cel A1 en dan naar de laatst gevulde rij gaan om het laatst gevulde rijnummer te krijgen.

We zullen een ' for'-lus in VBA uitvoeren om de waarden te controleren die in de A2-cel zijn geschreven naar de laatst gevulde cel in de A-kolom. We verhogen de waarde van de variabele 'teller' met 1 als de waarde groter is dan 50 en veranderen de lettertypekleur van de cel in 'Blauw' en als de waarde kleiner is dan 50, wordt de lettertypekleur van de cel 'Rood' .

Na het controleren en tellen, moeten we de waarden weergeven. Om hetzelfde te doen, zullen we 'VBA MsgBox' gebruiken.

Code:

 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 Cellen (i, 1) .Font.ColorIndex = 5 Else Cells (i, 1) .Font.ColorIndex = 3 End If Next i MsgBox "Er zijn" & counter & "waarden die groter zijn dan 50" & _ vbCrLf & "Er zijn" & lastrow - counter & "waarden die kleiner zijn dan 50" End Sub

Schakel de 'Ontwerpmodus' uit en klik op de 'Commandoknop' . Het resultaat zou als volgt zijn.

Voorbeeld # 2

Stel dat we de tijdteller als volgt willen maken met Excel VBA:

Als we op de 'Start'- knop klikken, start de timer en als we op de ' Stop'- knop klikken stopt de timer.

Om hetzelfde te doen, zouden de stappen zijn:

Maak een indeling zoals deze in een Excel-sheet.

Wijzig het formaat van de cel A2 als 'uu: mm: ss' .

Voeg de cellen C3 tot en met G7 samen met de opdracht Merge and Centre Excel in de groep 'Alignment' op het tabblad 'Home' .

Geef de referentie van cel A2 voor zojuist samengevoegde cel en voer vervolgens de opmaak uit zoals de lettertypestijl naar 'Baskerville' , lettergrootte naar 60, enz.

Maak twee opdrachtknoppen 'Start' en 'Stop' met de opdracht 'Invoegen' die beschikbaar is in de groep 'Besturing' op het tabblad 'Ontwikkelaar' .

Wijzig de eigenschappen met behulp van de opdracht 'Eigenschappen' die beschikbaar is in de groep 'Besturingselementen' op het tabblad 'Ontwikkelaars' .

Selecteer de commandoknoppen een voor een en kies het 'View Code' commando uit de 'Controls' groep in het 'Developer' tabblad om de code als volgt te schrijven.

Kies uit de vervolgkeuzelijst de juiste opdrachtknop.

Voeg een module in 'ThisWorkbook' in door met de rechtermuisknop op 'Thisworkbook' te klikken en vervolgens 'Invoegen' en vervolgens 'Module' te kiezen .

Schrijf de volgende code in de 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 Verlaat dan Sub Worksheets ("Time Counter"). Range ("A2"). Value = Worksheets ("Time Teller "). Bereik (" A2 "). Waarde - 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.