Μετρητής Excel VBA
Υπάρχει η διάφορες συνάρτηση στο MS Excel για την καταμέτρηση τιμών, είτε πρόκειται για συμβολοσειρά, αριθμούς. Η μέτρηση μπορεί να γίνει με βάση ορισμένα κριτήρια. Οι συναρτήσεις περιλαμβάνουν COUNT, COUNTA, COUNTBLANK, COUNTIF και COUNTIFS στο excel. Ωστόσο, αυτές οι συναρτήσεις δεν μπορούν να κάνουν κάποιες εργασίες, όπως η μέτρηση των κελιών με βάση το χρώμα τους, η μέτρηση μόνο των έντονων τιμών κ.λπ.
Ας δημιουργήσουμε έναν μετρητή στο excel VBA.
Παραδείγματα μετρητή Excel VBA
Ακολουθούν παραδείγματα του μετρητή στο VBA.
Παράδειγμα # 1
Ας υποθέσουμε ότι έχουμε δεδομένα όπως παραπάνω για 32 σειρές. Θα δημιουργήσουμε έναν μετρητή VBA, ο οποίος θα μετρά τις τιμές, οι οποίες είναι μεγαλύτερες από 50 και ένας ακόμη μετρητής για να μετρήσει τις τιμές, οι οποίες είναι μικρότερες από 50. Θα δημιουργήσουμε τον κώδικα VBA με αυτόν τον τρόπο έτσι ώστε ο χρήστης να μπορεί να έχει δεδομένα για απεριόριστες σειρές στο Excel.
Για να κάνετε το ίδιο, τα βήματα θα ήταν:
Βεβαιωθείτε ότι η καρτέλα προγραμματιστής Excel είναι ορατή. Για να κάνετε την καρτέλα ορατή (αν όχι), τα βήματα είναι:
Κάντε κλικ στην καρτέλα «Αρχείο» στην κορδέλα και επιλέξτε «Επιλογή» από τη λίστα.
Επιλέξτε « Προσαρμογή κορδέλας» από τη λίστα, επιλέξτε το πλαίσιο «Προγραμματιστής» και κάντε κλικ στο OK .
Τώρα η καρτέλα «Προγραμματιστής» είναι ορατή.
Εισαγάγετε το κουμπί εντολής χρησιμοποιώντας την εντολή «Εισαγωγή» που είναι διαθέσιμη στην ομάδα «Έλεγχοι» στην καρτέλα «Προγραμματιστής» .
Ενώ πατάτε το πλήκτρο ALT , δημιουργήστε το κουμπί εντολής με το ποντίκι. Εάν συνεχίσουμε να πατάμε το πλήκτρο ALT , τότε τα άκρα του κουμπιού εντολής πηγαίνουν αυτόματα με το περίγραμμα των κελιών.
Κάντε δεξί κλικ στο κουμπί εντολής για να ανοίξετε το μενού με βάση τα συμφραζόμενα (βεβαιωθείτε ότι η "Λειτουργία σχεδίασης" είναι ενεργοποιημένη. Διαφορετικά, δεν θα μπορούμε να ανοίξουμε το μενού με βάση τα συμφραζόμενα).
Επιλέξτε "Ιδιότητες" από το μενού.
Αλλάξτε τις ιδιότητες του κουμπιού εντολής, δηλαδή Όνομα, Λεζάντα και Γραμματοσειρά κ.λπ.
Κάντε δεξί κλικ ξανά και επιλέξτε το «View Code» από το μενού με βάση τα συμφραζόμενα.
Το Visual Basic Editor ανοίγει τώρα και από προεπιλογή, έχει ήδη δημιουργηθεί μια υπορουτίνα για το κουμπί εντολής.
Θα γράψουμε κώδικα τώρα. Θα δηλώσουμε 3 μεταβλητές. Ένα για σκοπούς βρόχου, ένα για μέτρηση και ένα για να αποθηκεύσετε την τιμή για την τελευταία σειρά.
Θα χρησιμοποιήσουμε τον κωδικό για να επιλέξουμε το κελί A1 και έπειτα την τρέχουσα περιοχή του κελιού A1 και στη συνέχεια να κατεβούμε στην τελευταία συμπληρωμένη σειρά για να λάβουμε τον τελευταίο γεμάτο αριθμό σειράς.
We will run a ‘for’ loop in VBA to check the values written in the A2 cell to the last filled cell in the A column. We will increase the value of the ‘counter’ variable by 1 if the value is greater than 50 and will change the font color of the cell to ‘Blue,’ and if the value is less than 50, then the font color of the cell would be ‘Red.’
After checking and counting, we need to display the values. To do the same, we will use ‘VBA MsgBox.’
Code:
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 "There are " & counter & " values which are greater than 50" & _ vbCrLf & "There are " & lastrow - counter & " values which are less than 50" End Sub
Deactivate the ‘Design Mode’ and click on the ‘Command button.’ The result would be as follows.
Example #2
Suppose we want to create the time counter using excel VBA as follows:
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.
