Créer une plage dynamique de gestion du temps, Excel VBA
Voici un code VBA détaillé pour créer une plage dynamique de gestion du temps dans Excel. Ce script va :
1. Définir une plage dynamique qui s’ajuste en fonction du nombre d’entrées de temps dans une colonne spécifique.
2. Trier les entrées de temps par ordre croissant.
3. Supprimer les doublons pour garantir que seules des valeurs de temps uniques sont présentes.
4. Formater la plage de temps correctement pour une meilleure lisibilité.
5. Fournir des explications détaillées pour chaque partie du code.
📌 Code VBA : Gestion dynamique des plages de temps
Option Explicit
Sub CreateDynamicTimeRange()
Dim ws As Worksheet
Dim lastRow As Long
Dim rng As Range, timeRange As Range
Dim dict As Object
Dim cell As Range
Dim namedRange As String
Dim timeColumn As String
Dim startRow As Long
' Définir la feuille où se trouvent les données de temps
Set ws = ThisWorkbook.Sheets("TimeData") ' Modifier le nom de la feuille si nécessaire
' Définir la colonne où les valeurs de temps sont stockées
timeColumn = "A" ' Modifier si nécessaire
startRow = 2 ' Supposons que la ligne 1 contient les en-têtes
' Trouver la dernière ligne non vide dans la colonne de temps
lastRow = ws.Cells(ws.Rows.Count, timeColumn).End(xlUp).Row
' Vérifier s'il y a des données
If lastRow < startRow Then MsgBox "Aucune donnée de temps trouvée !", vbExclamation, "Erreur" Exit Sub End If ' Définir la plage contenant les valeurs de temps Set rng = ws.Range(timeColumn & startRow & ":" & timeColumn & lastRow) ' Trier la colonne de temps par ordre croissant ws.Sort.SortFields.Clear ws.Sort.SortFields.Add Key:=rng, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal With ws.Sort .SetRange rng .Header = xlNo .MatchCase = False .Orientation = xlTopToBottom .Apply End With ' Supprimer les doublons en utilisant l'objet Dictionary Set dict = CreateObject("Scripting.Dictionary") ' Parcourir la plage et stocker les valeurs uniques de temps For Each cell In rng If Not dict.exists(cell.Value) And IsDate(cell.Value) Then dict.Add cell.Value, cell.Value End If Next cell ' Supprimer toute plage dynamique existante avant de définir une nouvelle namedRange = "DynamicTimeRange" On Error Resume Next ws.Names(namedRange).Delete On Error GoTo 0 ' Définir une nouvelle plage dynamique avec des valeurs de temps uniques triées If dict.Count > 0 Then
' Écrire les temps uniques dans une nouvelle colonne (par exemple, la colonne B)
ws.Range("B" & startRow & ":B" & lastRow).ClearContents
ws.Range("B" & startRow).Resize(dict.Count, 1).Value = Application.Transpose(dict.items)
' Définir la plage dynamique
Set timeRange = ws.Range("B" & startRow & ":B" & (startRow + dict.Count - 1))
ws.Names.Add Name:=namedRange, RefersTo:=timeRange
' Formater la nouvelle plage de temps au format Heure
timeRange.NumberFormat = "hh:mm AM/PM"
MsgBox "Plage dynamique de temps créée avec succès !", vbInformation, "Succès"
Else
MsgBox "Aucune donnée de temps valide trouvée.", vbExclamation, "Erreur"
End If
' Nettoyage des objets
Set rng = Nothing
Set timeRange = Nothing
Set dict = Nothing
End Sub
📖 Explication détaillée
📌 1. Configuration de la feuille de travail et de la colonne
Set ws = ThisWorkbook.Sheets("TimeData") ' Modifier le nom de la feuille si nécessaire
timeColumn = "A" ' Modifier si nécessaire
startRow = 2 ' Supposons que la ligne 1 contient les en-têtes
- Le macro fonctionne sur la feuille nommée « TimeData ».
- Les valeurs de temps sont supposées se trouver dans la colonne A, à partir de la ligne 2 (la ligne 1 contient les en-têtes).
📌 2. Trouver la dernière ligne de données
lastRow = ws.Cells(ws.Rows.Count, timeColumn).End(xlUp).Row
- Cela trouve la dernière ligne non vide dans la colonne de temps sélectionnée.
📌 3. Trier les entrées de temps
ws.Sort.SortFields.Clear
ws.Sort.SortFields.Add Key:=rng, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ws.Sort
.SetRange rng
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.Apply
End With
- Le macro trie les valeurs de temps par ordre croissant.
📌 4. Supprimer les doublons avec un dictionnaire
Set dict = CreateObject("Scripting.Dictionary")
For Each cell In rng
If Not dict.exists(cell.Value) And IsDate(cell.Value) Then
dict.Add cell.Value, cell.Value
End If
Next cell
- Un objet Dictionary est utilisé pour garantir que seules des valeurs de temps uniques sont stockées.
📌 5. Définir la plage dynamique
ws.Names(namedRange).Delete ' Supprimer la plage existante
If dict.Count > 0 Then
ws.Range("B" & startRow & ":B" & lastRow).ClearContents
ws.Range("B" & startRow).Resize(dict.Count, 1).Value = Application.Transpose(dict.items)
Set timeRange = ws.Range("B" & startRow & ":B" & (startRow + dict.Count - 1))
ws.Names.Add Name:=namedRange, RefersTo:=timeRange
timeRange.NumberFormat = "hh:mm AM/PM"
MsgBox "Plage dynamique de temps créée avec succès !", vbInformation, "Succès"
End If
- Le macro écrit les temps uniques dans la colonne B.
- Une plage dynamique nommée « DynamicTimeRange » est ensuite créée.
📌 6. Gestion des erreurs et nettoyage
On Error Resume Next ws.Names(namedRange).Delete On Error GoTo 0
- Assure que les anciennes plages sont supprimées avant de créer une nouvelle.
🛠 Comment utiliser ce macro
1. Placez les valeurs de temps dans la colonne A de la feuille nommée "TimeData".
2. Exécutez le macro CreateDynamicTimeRange.
3. Les temps triés et uniques seront écrits dans la colonne B.
4. Une plage dynamique nommée « DynamicTimeRange » sera créée.
5. La plage de temps sera correctement formatée (hh:mm AM/PM).
🔥 Caractéristiques principales
✅ Gestion des données dynamiques – S’ajuste automatiquement lorsqu’un nouvel horaire est ajouté.
✅ Tri des valeurs de temps – Assure que les données sont correctement ordonnées.
✅ Suppression des doublons – Évite les entrées redondantes.
✅ Création d’une plage nommée – Facile à référencer dans les formules ou les rapports.
✅ Messages conviviaux – Informe l’utilisateur en cas de problème.