Exercice VBA total heures à trouver

Question

Je suis bloquer sur un exercice à faire dans le cadre d'une formation VBA. Je suis débutant (j'ai eu 3 cours de VBA) et j'ai un exercice à faire et j'avoue être perdu. Je dois trouver des totaux selon des caractéristiques spécifiques. Si quelqu'un pouvait m'aider j'en serai extrêmement reconnaissant. Merci...

MFerrand · Answer

J'ai regardé ton exercice dont les consignes me laissent rêveur ! Passons sur la copie de sauvegarde de la feuille à faire manuellement, je suppose que tu sais faire... On demande (en macro) le volume horaire effectué par chaque enseignant, sans ajouter ou supprimer de colonnes. Cette dernière précision me paraît superflue car je ne vois de solution rationnelle qu'en ajoutant une feuille pour y consigner les résultats. On donne ensuite quelques fonctions susceptibles d'être utilisées... ! Je dirais que je n'ai besoin d'aucune de ces fonctions ! On dispose d'une colonne Enseignants (L) et d'une colonne Durée en mn qui plus est (J). On n'a donc besoin de rien d'autre pour sommer les durées effectuées par chaque enseignant. Donc, méthode "classique", avec ou sans Dictionary, pour aboutir à un tableau à deux colonnes enseignants/durées (on convertira à la fin les durées en minutes en valeurs horaires), tableau à trier sur la colonne enseignants (puisque pas de directives à ce propos), à affecter à une plage, puis un peu de mise en forme pour affiner la présentation... Te faire cela n'est pas un problème, mais est-ce bien te rendre service ? Tu dis que tu as eu 3 cours de VBA, qui ont portés sur quoi ? Est-ce un exercice d'application des cours que tu as eu (pour montrer que tu les as compris) ? Et si on te fournit une procédure qui fait le job, dans le cadre de tes études, il importe que tu sois en mesure de maîtriser le code utilisé dans tous ses détails... Car si tu arrives avec quelque chose qui marche mais sans que tu saches comment et sans pouvoir le refaire, ce serait un peu loupé niveau exercice d'application ! Cordialement...

MFerrand · Answer

Si tu te sens d'étudier la procédure : Sub DuréeEnseignants() Dim de As Object, DEns(), E, D&amp;, n%, i%, j% Set de = CreateObject("Scripting.Dictionary") With Worksheets("EDT 2013-2014") n = .Cells(.Rows.Count, 12).End(xlUp).Row For i = 2 To n If Trim(.Cells(i, 12)) &lt;&gt; "" Then D = .Cells(i, 10) E = Split("," &amp; Replace(.Cells(i, 12), " ", ""), ",") For j = 1 To UBound(E) If E(j) &lt;&gt; "" Then If de.exists(E(j)) Then D = D + CLng(de(E(j))) de(E(j)) = D End If Next j End If Next i End With ReDim DEns(de.Count, 1): n = 0 For Each E In de.keys n = n + 1: DEns(n, 0) = E Next E For i = 1 To n - 1 For j = i + 1 To n If DEns(j, 0) &lt; DEns(i, 0) Then E = DEns(j, 0): DEns(j, 0) = DEns(i, 0): DEns(i, 0) = E End If Next j Next i For i = 1 To n DEns(i, 1) = CLng(de(DEns(i, 0))) / 1440 Next i DEns(0, 0) = "Enseignants": DEns(0, 1) = "Durée" With Worksheets.Add(after:=ActiveSheet) With .Range("A1").Resize(n + 1, 2) .Value = DEns With .Rows(1) .HorizontalAlignment = xlCenter .Font.Italic = True End With .Columns(2).NumberFormat = "&#91;h&#93;:mm" .Columns(1).HorizontalAlignment = xlCenter End With With .Range("A2:B" &amp; n + 1).Borders .LineStyle = xlContinuous: Weight = xlThin End With .Activate End With End Sub Un bouton sur la feuille Consignes permet de la lancer. Elle insère une feuille pour afficher les résultats... Je reviens pour quelques explications. Cordialement...

MFerrand · Answer

Une petite erreur à rectifier... ! For j = 1 To UBound(E) If E(j) &lt;&gt; "" Then If de.exists(E(j)) Then de(E(j)) = D + CLng(de(E(j))) Else de(E(j)) = D End If End If Next j (En cas de plusieurs enseignants regroupés, je cumulais les heures... )...

MFerrand · Answer

Explications pas à pas...

1) Les déclarations de variables :

    Dim de As Object, DEns(), E, D&, n%, i%, j%

On déclare toutes les variables que l'on va utiliser dans la procédure. Ces déclarations sont à faire en tête de procédure, avant tout code exécutable.

Une variable objet, de, à laquelle on affectera un objet Dictionary.

L'objet Dictionnaire est une collection d'éléments composés d'une clé (identifiant l'élément) et d'une valeur ou item.

Pour une documentation plus étoffée, taper Dictionary dans l'Aide...

On composera un dictionnaire d'éléments dont la clé de chacun sera un enseignant et la valeur, la durée cumulée en minutes.

Un dictionnaire n'acceptant pas de clé doublon, on aura à la fin autant d'éléments que d'enseignants répertoriés...

Pour créer un élément, on opère avec la syntaxe suivante : de(enseignant) = durée. Cette syntaxe est compactée, on pourrait écrire en plus développé : de.Add enseignant, durée.

Pour ajouter une durée à un élément enseignant existant : de(enseignant) = nouvelle durée + CLng(de(enseignant)). Les données d'un dictionnaire étant de type String, il convient de ne pas omettre la conversion en valeur numérique (type Long, s'agissant de minutes...) pour additionner les durées.

DEns() déclare un tableau dynamique de type Variant, qui nous servira à accueillir les résultats.

E, variable de type Variant, que l'on va utiliser pour définir les enseignants : la colonne enseignant mentionnant éventuellement plusieurs enseignants, on les prélèvera sous forme de tableau (voir plus loin).

D&, variable de type Long, destinée à recueillir les durées en minutes. & est un caractère de déclaration de type signifiant : As Long.

n%, i%, j%, variables de type Integer, utilisées pour dimensionner ou comme compteurs de boucles. % est un caractère de déclaration de type signifiant : As Integer.

Ces caractères existant pour certains types de données permettent de raccourcir l'écriture des déclarations.

thibier1 · Answer

...

MFerrand · Answer

2) 1re partie : prélèvement des données (constitution du dictionnaire) :

    Set de = CreateObject("Scripting.Dictionary")
    With Worksheets("EDT 2013-2014")
        n = .Cells(.Rows.Count, 12).End(xlUp).Row
        For i = 2 To n
            If Trim(.Cells(i, 12)) <> "" Then
                D = .Cells(i, 10)
                E = Split("," & Replace(.Cells(i, 12), " ", ""), ",")
                For j = 1 To UBound(E)
                    If E(j) <> "" Then
                        If de.exists(E(j)) Then
                            de(E(j)) = D + CLng(de(E(j)))
                        Else
                            de(E(j)) = D
                        End If
                    End If
                Next j
            End If
        Next i
    End With

On commence par créer une instance d'objet Dictionary, affectée à de (Set est une instruction obligatoire pour affecter une référence d'objet à une variable objet).

On met la feuille sur laquelle on va opérer sous bloc With... End With : entre ces deux instructions, toute expression précédée d'un point réfèrera à la feuille. Cela garantit que toutes les expressions seront dotées de qualificateurs d'objet (au cas particulier, que pour toute cellule ou plage, on se réfère explicitement à la feuille sans laisser VBA chercher quelle serait la feuille active à laquelle rattacher une expression renvoyant un objet Range). Le code résultant est plus sûr, et plus rapide...

De même, l'utilisation de blocs With est à réaliser chaque fois qu'on le peut, cela simplifie l'écriture, mais aussi et surtout l'exécution sera plus rapide.

On dimensionne le nombre de lignes à parcourir avec n, variable à laquelle on affecte le numéro de la dernière ligne utilisée en colonne L (méthode Range.End).

On parcourt cette colonne L de la ligne 2 à la ligne n au moyen d'une boucle initiée avec i.

On teste si la cellule en L contient quelque chose (utilisation de la fonction Trim pour éliminer d'éventuelles espaces parasitaires).

S'il y a un contenu : on affecte la valeur de la cellule en J de la ligne à la variable D (la durée...), et en ce qui concerne le contenu de L, on en élimine les espaces avec la fonction Replace, on ajoute avant une virgule, et on transforme cette chaîne texte en tableau d'éléments séparés par des virgules, au moyen de la fonction Split. Ayant placé une virgule avant le contenu de la cellule, on obtiendra donc toujours un tableau d'au moins 2 éléments, dont l'élément 0 sera toujours vide. Ce tableau est affecté à la variable E.

Les enseignants figurant dans la cellule sont séparés par des virgules. E sera donc un tableau dont chaque élément à partir de 1 peut contenir l'identification d'un enseignant.

On parcourt donc ce tableau E au moyen d'une boucle de 1 à l'indice maximal de E, initiée avec la variable j.

On teste si l'élément E(j) n'est pas vide (il y a en effet quelques cellules du tableau ou la liste d'enseignants se termine par une virgule, ce qui nous ferait créer une enseignant fantôme si on ne testait pas...)

L'élément contenant l'indication d'un enseignant, on teste si un élément dictionnaire à ce nom (E(j)) existe déjà : si c'est le cas, on ajoute la durée à celle déjà contenue dans l'élément dictionnaire (en la convertissant...), sinon on crée un nouvel élément dictionnaire auquel on affecte la durée .

Au terme de la boucle i, on aura constitué le dictionnaire de tous les enseignant, avec les durées cumulées.

En ce qui concerne l'écriture du code, noter aussi à ce stade l'indentation (retraits successifs à chaque bloc d'instructions imbriquées). L'indentation a pour rôle de faciliter la lecture du code (on lit au moins 4 fois plus vite un code indenté..., et je dois dire que quand je vois du code non indenté, ce qui est trop fréquent, je n'ai en général pas envie de lire ! )

MFerrand · Answer

3) 2e partie : affectation des enseignants au tableau de résultats :

    ReDim DEns(de.Count, 1): n = 0
    For Each E In de.keys
        n = n + 1: DEns(n, 0) = E
    Next E

Il y a autant d'enseignants que d'éléments dictionnaire, et les noms des enseignants constituent les clés de ces éléments dico.

On va donc dimensionner le tableau résultats sur le nombre d'éléments dico +1 (lignes) et 2 colonnes (NB- les indices minimaux d'un tableau sont 0...). On réservera la ligne d'indice 0 pour y placer des en-têtes.

On réinitialise la variable n à 0 pour pouvoir la réutiliser pour l'affectation des enseignants au tableau.

On initie une boucle sur les clés des éléments dico en utilisant la variable E (à l'intérieur de la boucle, E est la clé de l'élément examiné, soit un nom d'enseignant).

A chaque tour de boucle, on incrémente n et on affecte à l'élément n du tableau E (le nom d'un enseignant).

4) 3e partie : tri des enseignants :

    For i = 1 To n - 1
        For j = i + 1 To n
            If DEns(j, 0) < DEns(i, 0) Then
                E = DEns(j, 0): DEns(j, 0) = DEns(i, 0): DEns(i, 0) = E
            End If
        Next j
    Next i

On a servi une colonne du tableau avec les enseignants. On va trier cette colonne en ordre alphabétique...

Le tri se fait au moyen de 2 boucles imbriquées : une boucle i de 1 à n-1, une boucle j de i+1 à n. A chaque tour, on compare l'élément i et l'élément j : si j est inférieur (on fait un tri croissant), on switche ces deux éléments, chacun prenant la place de l'autre. Il faut utiliser une variable intermédiaire pour dégager un emplacement afin de pouvoir faire la substitution. On utilise E pour ce faire.

Ce procédé est appelé Tri à bulles, parce qu'à chaque tour de la boucle i (externe), l'élément affecté en i est à sa position définitive, à l'instar de bulles qui s'élèvent au fur et à mesure des substitutions.

5) 4e partie : affectation des durées au tableau :

    For i = 1 To n
        DEns(i, 1) = CLng(de(DEns(i, 0))) / 1440
    Next i
    DEns(0, 0) = "Enseignants": DEns(0, 1) = "Durée"

Le tableau de résultats comporte les enseignants dans sa première colonne, reclassés en ordre alphabétique. On va le compléter par les durées en 2e colonne.

Une boucle i de 1 à n permet de parcourir le tableau. L'élément (i, 0) (nom de l'enseignant) constitue la clé de l'élément dico qui contient la durée. A chaque ligne on peut donc extraire cette durée de l'élément dico (sans omettre de la convertir) et en la divisant par 1440 pour obtenir une valeur horaire.

1440 parce qu'il y a 1440 minutes dans une journée de 24 heures.

Les nombres-temps dans Excel ont pour unité le jour. Dans un tel nombre, la partie entière indique une date ou un nombre de jours, et la partie décimale donne une valeur horaire (comprise entre 0 et 23:59:59). Ces nombres sont également nommés numéros de séries. Toujours se souvenir que les dates et heures sont des nombres et que leur affichage dans une feuille de calcul n'est qu'une question de format de cellule.

Pour compléter le tableau, on affecte des en-têtes à la ligne 0.

MFerrand · Answer

6) Finalisation : With Worksheets.Add(after:=ActiveSheet) With .Range("A1").Resize(n + 1, 2) .Value = DEns With .Rows(1) .HorizontalAlignment = xlCenter .Font.Italic = True End With .Columns(2).NumberFormat = "&#91;h&#93;:mm" .Columns(1).HorizontalAlignment = xlCenter End With With .Range("A2:B" &amp; n + 1).Borders .LineStyle = xlContinuous: Weight = xlThin End With .Activate End With On dispose d'un tableau des résultats. Il n'y a plus qu'à l'afficher sur une feuille... On ajoute donc une feuille pour cela, après la feuille active (qui n'a pas changé et est Consignes), directement sous bloc With... End With (on gagne des répétitions...) On initie un nouveau bloc With... End With imbriqué pour dimensionner la plage d'accueil des résultats à partir de A1. On la dimensionne à la dimension du tableau en utilisant la propriété Resize appliquée à A1. La commande d'affectation se limite à : .Value = DEns Les valeurs du tableau sont affectées à la plage. Lorsqu'il s'agit d'affecter des valeurs, de plage à plage ou par l'intermédiaire d'un tableau, cette méthode est la plus rapide. Des commandes telles que plage2.Value = plage1.Value ou plage2.Value = Tablo permettent d'éviter des affectations à l'unité ou des copier-coller... Dans la foulée, on procède à un minimum de mise en forme de cette plage : sa ligne 1 est centrée horizontalement et mise en italique, sa 2e colonne est mise au format &#91;h&#93;:mm (heures:minutes, le h entre crochets permet l'affichage d'heures supérieures à 24), sa 1re colonne est centrée horizontalement. En se décalant d'une ligne, on place des bordures... L'opération étant terminée, il n'y a plus qu'à activer la feuille pour que l'utilisateur voit le résultat. Bon courage pour la suite.....

MFerrand · Answer

En 3 fois 2 heures, même en ayant bien digéré variables, boucles et instructions conditionnelles, il manquera au moins quelques réflexes qui s'acquièrent avec la pratique... mais tu devrais disposer des éléments pour analyser la procédure et la maîtriser. C'est l'essentiel dans l'immédiat.

Je remets le fichier rectifié au cas où...

Dû le zipper, dépassait légèrement la limite.

22gaia76-exercice-vba.zip (924.02 Ko)