Compteur VBA

Compteur Excel VBA

Il existe différentes fonctions dans MS Excel pour compter les valeurs, que ce soit une chaîne, des nombres. Le comptage peut être effectué en fonction de certains critères. Les fonctions incluent COUNT, COUNTA, COUNTBLANK, COUNTIF et COUNTIFS dans Excel. Cependant, ces fonctions ne peuvent pas effectuer certaines tâches comme compter les cellules en fonction de leur couleur, ne compter que les valeurs en gras, etc. C'est pourquoi nous allons créer un compteur dans VBA afin que nous puissions compter pour ces types de tâches dans Excel.

Créons un compteur dans Excel VBA.

Exemples de compteur Excel VBA

Vous pouvez télécharger ce modèle Excel de compteur VBA ici - Modèle Excel de compteur VBA

Vous trouverez ci-dessous des exemples de compteur en VBA.

Exemple 1

Supposons que nous ayons des données comme ci-dessus pour 32 lignes. Nous allons créer un compteur VBA, qui comptera les valeurs, qui sont supérieures à 50 et un compteur de plus pour compter les valeurs, qui sont inférieures à 50. Nous allons créer le code VBA de cette manière afin que l'utilisateur puisse avoir des données pour lignes illimitées dans Excel.

Pour faire de même, les étapes seraient:

Assurez-vous que l'  onglet Développeur Excel est visible. Pour rendre l'onglet visible (sinon), les étapes sont:

Cliquez sur l' onglet "Fichier" dans le ruban et choisissez "Option" dans la liste.

Choisissez « Personnaliser le ruban» dans la liste, cochez la case «Développeur» et cliquez sur OK .

Maintenant, l' onglet "Développeur" est visible.

Insérez le bouton de commande à l'aide de la commande 'Insérer' disponible dans le groupe 'Contrôles' de l' onglet 'Développeur' .

Tout en appuyant sur la touche ALT , créez le bouton de commande avec la souris. Si nous continuons à appuyer sur la touche ALT , les bords du bouton de commande vont automatiquement avec la bordure des cellules.

Faites un clic droit sur le bouton de commande pour ouvrir le menu contextuel (assurez-vous que le «Mode Conception» est activé sinon nous ne pourrons pas ouvrir le menu contextuel).

Choisissez «Propriétés» dans le menu.

Modifiez les propriétés du bouton de commande, c'est-à-dire le nom, la légende et la police, etc.

Cliquez à nouveau avec le bouton droit et choisissez «Afficher le code» dans le menu contextuel.

Visual Basic Editor est ouvert maintenant et par défaut, un sous-programme est déjà créé pour le bouton de commande.

Nous allons écrire du code maintenant. Nous déclarerons 3 variables. Un pour la boucle, un pour compter et un pour stocker la valeur de la dernière ligne.

Nous utiliserons le code pour sélectionner la cellule A1, puis la région actuelle de la cellule A1, puis passerons à la dernière ligne remplie pour obtenir le dernier numéro de ligne rempli.

Nous allons exécuter une boucle «for» dans VBA pour vérifier les valeurs écrites dans la cellule A2 jusqu'à la dernière cellule remplie de la colonne A. Nous augmenterons la valeur de la variable `` compteur '' de 1 si la valeur est supérieure à 50 et changerons la couleur de police de la cellule en `` Bleu '' et si la valeur est inférieure à 50, la couleur de police de la cellule serait «Rouge» .

Après vérification et comptage, nous devons afficher les valeurs. Pour faire de même, nous utiliserons '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 "Il existe des valeurs" & counter & "supérieures à 50" & _ vbCrLf & "Il existe des valeurs" & lastrow - counter & "inférieures à 50" End Sub 

Désactivez le 'Design Mode' et cliquez sur le 'Command button' . Le résultat serait le suivant.

Exemple # 2

Supposons que nous souhaitons créer le compteur de temps à l'aide d'Excel VBA comme suit:

Si nous cliquons sur le bouton «Démarrer» , la minuterie démarre et si nous cliquons sur le bouton «Arrêter» , la minuterie s'arrête.

Pour faire de même, les étapes seraient:

Créez un format comme celui-ci dans une feuille Excel.

Modifiez le format de la cellule A2 en «hh: mm: ss» .

Fusionner les cellules C3 à G7 à l'aide de la commande Fusionner et centrer Excel  dans le groupe «Alignement» de l' onglet «Accueil» .

Donnez la référence de la cellule A2 pour la cellule juste fusionnée, puis effectuez le formatage comme créer le style de police `` Baskerville '' , la taille de la police à 60, etc.

Créez deux boutons de commande «Démarrer» et «Arrêter» à l'aide de la commande «Insérer» disponible dans le groupe «Contrôles» de l' onglet «Développeur» .

À l'aide de la commande 'Propriétés' disponible dans le groupe 'Contrôles' de l' onglet 'Développeur' , modifiez les propriétés.

Sélectionnez les boutons de commande un par un et choisissez la commande "Afficher le code" dans le groupe "Contrôles" de l' onglet "Développeur" pour écrire le code comme suit.

Choisissez dans la liste déroulante le bouton de commande approprié.

Insérez un module dans «ThisWorkbook» en cliquant avec le bouton droit de la souris sur «Thisworkbook» , puis choisissez «Insert» puis «Module» .

Écrivez le code suivant dans le 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 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 ‘pass’ variable and add 1 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

  1. Save the file after writing code in VBA with .xlsm excel extension otherwise macro will not work.
  2. Use the ‘For’ loop when it is decided already that for how many times, the code in VBA loop will run.