VBA loendur - Kuidas Exceli VBA-s loendurit luua? (koos näidetega)

Exceli VBA loendur

MS Excelis on väärtuste loendamiseks mitmesugune funktsioon, olgu see string, numbrid. Loendamist saab teha mõne kriteeriumi alusel. Funktsioonide hulka kuuluvad Excelis COUNT, COUNTA, COUNTBLANK, COUNTIF ja COUNTIFS. Kuid need funktsioonid ei saa teha mõnda ülesannet, näiteks lahtrite loendamine nende värvi põhjal, ainult paksude väärtuste loendamine jne. Seetõttu loome VBA-s loenduri, et saaksime seda tüüpi ülesannete jaoks Excelis arvestada.

Loogem Exceli VBA-s mõni loendur.

Näited Exceli VBA loendurist

Allpool on toodud näited VBA loendurist.

Näide 1

Oletame, et meil on 32 rea kohta ülaltoodud andmeid. Loome VBA loenduri, mis loeb väärtused, mis on suuremad kui 50, ja veel ühe loenduri, et loendada väärtusi, mis on väiksemad kui 50. Loome VBA koodi sel viisil, et kasutajal oleks andmeid piiramatu rida excelis.

Samamoodi toimige järgmiselt.

Veenduge, et vahekaart Arendaja Excel oleks nähtav. Vahekaardi nähtavaks tegemiseks (kui seda pole), toimige järgmiselt.

Klõpsake lindil vahekaarti "Fail" ja valige loendist "Option" .

Valige loendist „ Kohanda linti” , märkige ruut „Arendaja” ja klõpsake nuppu OK .

Nüüd on vahekaart Arendaja nähtav.

Sisestage käsk nuppu kasutades "Insert" käsku saadaval "Kontroll" rühm on "Arendaja" sakk .

ALT- klahvi vajutamise ajal looge hiirega käsunupp. Kui jätkame ALT-klahvi vajutamist, lähevad käsunupu servad automaatselt lahtrite piiriga.

Kontekstimenüü avamiseks paremklõpsake käsunupul (veenduge, et „Kujundusrežiim” oleks sisse lülitatud; vastasel juhul ei saa me kontekstimenüüd avada).

Valige menüüst 'Atribuudid' .

Muutke käsunupu atribuute, nt Nimi, pealkiri ja font jne.

Paremklõpsake uuesti ja valige kontekstimenüüst 'Kuva kood' .

Visual Basic Editor on nüüd avatud ja vaikimisi on käsunupu jaoks juba loodud alamprogramm.

Kirjutame koodi kohe. Deklareerime 3 muutujat. Üks silmuse otstarbeks, üks loendamiseks ja teine ​​viimase rea väärtuse salvestamiseks.

Valime koodi abil lahtri A1 ja seejärel lahtri A1 praeguse piirkonna ning jõuame siis viimase täidetud reani, et saada viimane täidetud rea number.

Käivitame VBA-lingi „for” , et kontrollida veerus A lahtrisse A2 täidetud lahtrisse kirjutatud väärtusi. Suurendame muutuja 'counter' väärtust 1 võrra, kui väärtus on suurem kui 50, ja muudame lahtri fondivärviks 'Blue' ning kui väärtus on väiksem kui 50, siis lahtri fondi värv oleks "punane".

Pärast kontrollimist ja loendamist peame väärtused kuvama. Samamoodi kasutame ka VBA MsgBoxi.

Kood:

Private Sub CountingCellsbyValue_Click () Dim i, counter As Integer Dim lastrow As Long lastrow = Range ("A1"). CurrentRegion.End (xlDown). Rida jaoks i = 2 Kui lahtrid (i, 1) täidavad. Väärtus> 50 Siis loendur = loendur + 1 lahtrit (i, 1) .Font.ColorIndex = 5 muud lahtrit (i, 1) .Font.ColorIndex = 3 Lõpeta kui järgmine i MsgBox "Seal on & & loenduri &" väärtused, mis on suuremad kui 50 " & _ vbCrLf & "On" & lastrow - counter & "väärtusi, mis on väiksemad kui 50" End Sub

Deaktiveerige „Kujundusrežiim” ja klõpsake nuppu „Käsu”. Tulemus oleks järgmine.

Näide 2

Oletame, et tahame Exceli VBA abil luua ajaloenduri järgmiselt:

Kui klõpsame nuppu 'Start' , siis taimer käivitub ja kui klõpsame nuppu 'Stop' , siis taimer peatub.

Samamoodi toimige järgmiselt.

Looge Exceli lehel selline vorming.

Muutke lahtri A2 vormingut 'hh: mm: ss'.

Merge rakkude C3 G7 abil Merge ja Center Excel käsk "Alignment" rühm on "Kodu" sakk .

Andke lahus A2 viide äsja liidetud lahtrile ja tehke seejärel vormindamine, näiteks tehke fondi stiiliks „Baskerville”, fondi suuruseks 60 jne.

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

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