Počítadlo VBA aplikace Excel
V programu MS Excel existuje různá funkce pro počítání hodnot, ať už jde o řetězec, čísla. Počítání lze provést na základě některých kritérií. Mezi funkce patří Excel, COUNTA, COUNTBLANK, COUNTIF a COUNTIFS. Tyto funkce však nemohou provádět některé úkoly, jako je počítání buněk na základě jejich barvy, počítání pouze tučných hodnot atd. Proto ve VBA vytvoříme počítadlo, abychom mohli pro tyto typy úkolů počítat v aplikaci Excel.
Vytvořme nějaké počítadlo v aplikaci Excel VBA.
Příklady čítače Excel VBA
Níže jsou uvedeny příklady počítadla ve VBA.
Příklad č. 1
Předpokládejme, že máme data jako výše pro 32 řádků. Vytvoříme VBA čítač, který bude počítat hodnoty, které jsou větší než 50 a ještě jeden čítač pro počítání hodnot, které jsou menší než 50. Vytvoříme VBA kód tímto způsobem, aby uživatel mohl mít data pro neomezený počet řádků v aplikaci Excel.
Stejné kroky by byly:
Ujistěte se, že je na kartě Vývojář viditelná Excel. Chcete-li kartu zviditelnit (pokud ne), postupujte takto:
Klikněte na pásu karet na kartu „Soubor“ a v seznamu vyberte možnost „Možnost“ .
V seznamu vyberte možnost „ Přizpůsobit pás karet“ , zaškrtněte políčko „Vývojář“ a klikněte na OK .
Nyní je karta Vývojář viditelná.
Vložte příkazové tlačítko pomocí příkazu „Vložit“ dostupného ve skupině „Ovládací prvky“ na kartě „Vývojář“ .
Při stisknutí klávesy ALT vytvořte příkazové tlačítko myší. Pokud stále stiskneme klávesu ALT , pak okraje příkazového tlačítka budou automaticky s ohraničením buněk.
Kliknutím pravým tlačítkem na příkazové tlačítko otevřete kontextové menu (ujistěte se, že je aktivován režim „Design Mode“ ; v opačném případě nebudeme moci otevřít kontextové menu).
V nabídce vyberte možnost Vlastnosti .
Změňte vlastnosti příkazového tlačítka, tj. Název, Titulek a Písmo atd.
Znovu klepněte pravým tlačítkem myši a v kontextové nabídce vyberte možnost „Zobrazit kód“ .
Editor jazyka je nyní otevřen a ve výchozím nastavení je pro příkazové tlačítko již vytvořen podprogram.
Nyní napíšeme kód. Deklarujeme 3 proměnné. Jeden pro účely smyčky, jeden pro počítání a druhý pro uložení hodnoty pro poslední řádek.
Pomocí kódu vybereme buňku A1 a poté aktuální oblast buňky A1 a poté se dostaneme dolů k poslednímu vyplněnému řádku, abychom získali číslo posledního vyplněného řádku.
Budeme spustit ‚pro‘ smyčku v VBA zkontrolovat hodnoty napsané v buňce A2 na poslední vyplněným buňky ve sloupci A. Pokud je hodnota větší než 50, zvýšíme hodnotu proměnné „counter“ o 1 a změníme barvu písma buňky na „Blue“, a pokud je hodnota menší než 50, pak barva písma buňky bude „červená“.
Po kontrole a počítání musíme zobrazit hodnoty. K tomu použijeme 'VBA MsgBox.'
Kód:
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 "Existují hodnoty" & counter & ", které jsou větší než 50" & _ vbCrLf & „Existují“ & lastrow - counter & „hodnoty, které jsou menší než 50“ End Sub
Deaktivujte „návrhový režim“ a klikněte na „příkazové tlačítko“. Výsledek by byl následující.
Příklad č. 2
Předpokládejme, že chceme vytvořit počítadlo času pomocí aplikace Excel VBA takto:
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.
