Excel VBA skaitītājs
Programmā MS Excel ir dažādas funkcijas, lai skaitītu vērtības neatkarīgi no tā, vai tā ir virkne, skaitļi. Skaitīšanu var veikt, pamatojoties uz dažiem kritērijiem. Funkcijās ietilpst Excel, COUNT, COUNTA, COUNTBLANK, COUNTIF un COUNTIFS. Tomēr šīs funkcijas nevar veikt dažus uzdevumus, piemēram, skaitīt šūnas, pamatojoties uz to krāsu, skaitīt tikai treknrakstā norādītās vērtības utt. Tāpēc mēs izveidosim skaitītāju VBA, lai mēs varētu uzskaitīt šāda veida uzdevumus programmā Excel.
Izveidosim kādu skaitītāju programmā Excel VBA.
Excel VBA skaitītāja piemēri
Zemāk ir VBA skaitītāja piemēri.
1. piemērs
Pieņemsim, ka mums ir 32 rindu dati, piemēram, iepriekš. Mēs izveidosim VBA skaitītāju, kurā tiks skaitītas vērtības, kas ir lielākas par 50, un vēl viens skaitītājs, lai skaitītu vērtības, kas ir mazākas par 50. Šādi izveidosim VBA kodu, lai lietotājam būtu dati par neierobežotas Excel rindas.
Lai darītu to pašu, būtu jāveic šādas darbības:
Pārliecinieties, vai ir redzama cilne Izstrādātājs Excel. Lai cilni padarītu redzamu (ja tā nav), rīkojieties šādi:
Lentē noklikšķiniet uz cilnes Fails un sarakstā izvēlieties opciju .
Sarakstā izvēlieties ' Pielāgot lenti' , atzīmējiet izvēles rūtiņu 'Izstrādātājs' un noklikšķiniet uz Labi .
Tagad ir redzama cilne Izstrādātājs .
Ievietojiet komandas pogu, izmantojot Ievietošana komandu pieejamo "kontroles grupā no cilnes" attīstītājs " .
Nospiežot taustiņu ALT , ar peli izveidojiet komandas pogu. Ja mēs turpinām nospiest taustiņu ALT , komandas pogas malas automātiski iet kopā ar šūnu robežu.
Ar peles labo pogu noklikšķiniet uz komandas pogas, lai atvērtu kontekstuālo izvēlni (pārliecinieties, vai ir aktivizēts 'Design Mode' ; pretējā gadījumā mēs nevarēsim atvērt konteksta izvēlni).
Izvēlnē izvēlieties 'Properties' .
Mainiet komandas pogas rekvizītus, ti, nosaukums, paraksts un fonts utt.
Ar peles labo pogu noklikšķiniet vēlreiz un konteksta izvēlnē izvēlieties 'Skatīt kodu' .
Visual Basic Editor ir atvērts tagad, un pēc noklusējuma komandu pogai jau ir izveidota apakšprogramma.
Mēs tagad uzrakstīsim kodu. Mēs deklarēsim 3 mainīgos. Viens cilpa mērķim, viens skaitīšanai un viens pēdējās rindas vērtības glabāšanai.
Mēs izmantosim kodu, lai atlasītu šūnu A1 un pēc tam pašreizējo šūnas A1 reģionu, un pēc tam nonāksim līdz pēdējai aizpildītajai rindai, lai iegūtu pēdējās aizpildītās rindas numuru.
Mēs veiksim “for” cilpu VBA, lai pārbaudītu vērtības, kas ierakstītas šūnā A2 līdz pēdējai aizpildītajai šūnai kolonnā A. Mēs palielināsim mainīgā “counter” vērtību par 1, ja vērtība ir lielāka par 50, un šūnas fonta krāsu mainīsim uz “Blue”, un, ja vērtība ir mazāka par 50, tad šūnas fonta krāsa būtu 'sarkans'.
Pēc pārbaudes un skaitīšanas mums jāparāda vērtības. Lai to izdarītu, mēs izmantosim 'VBA MsgBox'.
Kods:
Privāts apakšskaitsCellsbyValue_Click () Dim i, skaitītājs As Integer Dim pēdējais kā garais pēdējais = diapazons ("A1"). CurrentRegion.End (xlDown). Rinda i = 2, lai aizpildītu, ja šūnas (i, 1). skaitītājs = skaitītājs + 1 šūna (i, 1). Font.ColorIndex = 5 citas šūnas (i, 1). Font.ColorIndex = 3 Beigt Ja Nākamais i MsgBox "Ir" & counter & "vērtības, kas ir lielākas par 50" & _ vbCrLf & "Ir" & lastrow - counter & "vērtības, kas ir mazākas par 50" End Sub
Deaktivizējiet dizaina režīmu un noklikšķiniet uz pogas Komanda. Rezultāts būtu šāds.
2. piemērs
Pieņemsim, ka mēs vēlamies izveidot laika skaitītāju, izmantojot Excel VBA šādi:
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.
