Licznik VBA programu Excel
W programie MS Excel są różne funkcje do liczenia wartości, niezależnie od tego, czy jest to ciąg, czy liczby. Liczenie można przeprowadzić na podstawie pewnych kryteriów. Funkcje obejmują COUNT, COUNTA, COUNTBLANK, COUNTIF i COUNTIFS w programie Excel. Jednak te funkcje nie mogą wykonywać niektórych zadań, takich jak zliczanie komórek na podstawie ich koloru, liczenie tylko pogrubionych wartości itp. Dlatego utworzymy licznik w VBA, abyśmy mogli liczyć na tego typu zadania w programie Excel.
Stwórzmy jakiś licznik w Excel VBA.
Przykłady licznika VBA w programie Excel
Poniżej przykłady licznika w VBA.
Przykład 1
Załóżmy, że mamy dane jak powyżej dla 32 wierszy. Stworzymy licznik VBA, który będzie zliczał wartości większe niż 50 i jeszcze jeden licznik licząc wartości mniejsze niż 50. Kod VBA utworzymy w ten sposób, aby użytkownik miał dane dla nieograniczona liczba wierszy w programie Excel.
Aby zrobić to samo, kroki będą następujące:
Upewnij się, że karta Deweloper Excel jest widoczna. Aby karta była widoczna (jeśli nie), kroki są następujące:
Kliknij kartę „Plik” na wstążce i wybierz z listy opcję „Opcja” .
Wybierz z listy „ Dostosuj Wstążkę” , zaznacz pole „Deweloper” i kliknij OK .
Teraz widoczna jest zakładka „Deweloper” .
Wstaw przycisk polecenia za pomocą polecenia „Wstaw” dostępnego w grupie „Sterowanie” w zakładce „Deweloper” .
Trzymając wciśnięty klawisz ALT , utwórz przycisk polecenia za pomocą myszy. Jeśli będziemy nadal naciskać klawisz ALT , krawędzie przycisku polecenia zostaną automatycznie przeniesione z obramowaniem komórek.
Kliknij prawym przyciskiem myszy przycisk polecenia, aby otworzyć menu kontekstowe (upewnij się, że „Tryb projektowania” jest aktywny; w przeciwnym razie nie będziemy mogli otworzyć menu kontekstowego).
Wybierz z menu „Właściwości” .
Zmień właściwości przycisku polecenia, tj. Nazwa, Podpis, Czcionka itp.
Kliknij ponownie prawym przyciskiem myszy i wybierz „Wyświetl kod” z menu kontekstowego.
Edytor Visual Basic jest teraz otwarty i domyślnie dla przycisku polecenia jest już utworzony podprogram.
Teraz napiszemy kod. Zadeklarujemy 3 zmienne. Jeden do celów pętli, jeden do zliczania i jeden do przechowywania wartości dla ostatniego wiersza.
Użyjemy kodu, aby wybrać komórkę A1, a następnie bieżący region komórki A1, a następnie przejdziemy do ostatniego wypełnionego wiersza, aby uzyskać numer ostatniego wypełnionego wiersza.
Uruchomimy pętlę „for” w języku VBA, aby sprawdzić wartości zapisane w komórce A2 do ostatniej wypełnionej komórki w kolumnie A. Zwiększymy wartość zmiennej „licznik” o 1, jeśli wartość jest większa niż 50, i zmienimy kolor czcionki komórki na „Niebieski”, a jeśli wartość jest mniejsza niż 50, kolor czcionki komórki byłoby „Czerwone”.
Po sprawdzeniu i zliczeniu musimy wyświetlić wartości. Aby zrobić to samo, użyjemy „VBA MsgBox”.
Kod:
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 Cells (i, 1) .Font.ColorIndex = 5 Else Cells (i, 1) .Font.ColorIndex = 3 End If Next i MsgBox "Istnieją wartości" & counter & ", które są większe niż 50" & _ vbCrLf & "Istnieją" wartości & lastrow - counter & ", które są mniejsze niż 50" End Sub
Dezaktywuj „Tryb projektowania” i kliknij przycisk „Polecenie”. Wynik byłby następujący.
Przykład nr 2
Załóżmy, że chcemy utworzyć licznik czasu za pomocą programu Excel VBA w następujący sposób:
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.
