Excel VBA števec
V programu Excel Excel obstaja različna funkcija za štetje vrednosti, ne glede na to, ali gre za niz ali števila. Štetje se lahko opravi na podlagi nekaterih meril. Funkcije vključujejo COUNT, COUNTA, COUNTBLANK, COUNTIF in COUNTIFS v Excelu. Vendar te funkcije ne morejo opraviti nekaterih nalog, kot je štetje celic glede na njihovo barvo, štetje samo krepkih vrednosti itd. Zato bomo v VBA ustvarili števec, da bomo lahko te vrste nalog šteli v Excelu.
Ustvarimo nekaj števcev v excelu VBA.
Primeri števca Excel VBA
Spodaj so primeri števca v VBA.
Primer # 1
Recimo, da imamo podatke, kot zgoraj, za 32 vrstic. Ustvarili bomo števec VBA, ki bo štel vrednosti, ki so večje od 50, in še en števec za štetje vrednosti, ki so manjše od 50. Kodo VBA bomo ustvarili na ta način, da bo imel uporabnik podatke za neomejene vrstice v excelu.
Če želite narediti enako, bi bili naslednji koraki:
Prepričajte se, da je zavihek razvijalec Excel viden. Če želite, da je zavihek viden (če ne), so naslednji koraki:
Na traku kliknite zavihek »Datoteka « in na seznamu izberite »Možnost« .
Na seznamu izberite » Prilagodi trak« , potrdite polje »Razvijalec« in kliknite V redu .
Zdaj je viden zavihek »Razvijalec« .
Ukazni gumb vstavite z ukazom »Vstavi«, ki je na voljo v skupini »Kontrole« na zavihku »Razvijalec« .
Medtem ko pritisnete tipko ALT , z miško ustvarite ukazni gumb. Če še naprej pritiskamo tipko ALT , potem robovi ukaznega gumba samodejno gredo z obrobo celic.
Z desno miškino tipko kliknite ukazni gumb, da odprete kontekstni meni (poskrbite, da je aktiviran 'Način oblikovanja' ; sicer kontekstnega menija ne bomo mogli odpreti).
V meniju izberite 'Properties' .
Spremenite lastnosti ukaznega gumba, tj. Ime, Napis in Pisava itd.
Znova kliknite z desno miškino tipko in v kontekstnem meniju izberite 'Prikaži kodo' .
Urejevalnik Visual Basic je zdaj odprt in privzeto je za ukazni gumb že ustvarjena podprogram.
Zdaj bomo napisali kodo. Deklarirali bomo 3 spremenljivke. Ena za namen zanke, ena za štetje in ena za shranjevanje vrednosti za zadnjo vrstico.
S kodo bomo izbrali celico A1 in nato trenutno območje celice A1, nato pa pridemo do zadnje izpolnjene vrstice, da dobimo številko zadnje izpolnjene vrstice.
V VBA bomo zagnali zanko »for«, da preverimo vrednosti, zapisane v celici A2, do zadnje napolnjene celice v stolpcu A. Vrednost spremenljivke 'counter' bomo povečali za 1, če je vrednost večja od 50, in spremenili bomo barvo pisave celice v 'Blue', če pa je vrednost manjša od 50, pa bo barva pisave celice bi bila "Rdeča."
Po preverjanju in štetju moramo prikazati vrednosti. Za enako bomo uporabili »VBA MsgBox«.
Koda:
Private Sub CountingCellsbyValue_Click () Dim i, števec As Integer Dim lastrow As Long lastrow = Obseg ("A1"). CurrentRegion.End (xlDown) .Row For i = 2 Lastrow If Cells (i, 1) .Value> 50 Potem counter = counter + 1 Cells (i, 1) .Font.ColorIndex = 5 drugih celic (i, 1) .Font.ColorIndex = 3 End If Next i MsgBox "Obstajajo" & counter & "vrednosti, ki so večje od 50" & _ vbCrLf & "Obstajajo" & lastrow - counter & "vrednosti, ki so manjše od 50" End Sub
Izključite "Način oblikovanja" in kliknite "Ukazni gumb". Rezultat bi bil naslednji.
2. primer
Recimo, da želimo ustvariti števec časa z uporabo excela VBA, kot sledi:
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.
