Créer un système de gestion dynamique des plages avec des tests de performance, Excel VBA

Créer un système de gestion dynamique des plages avec des tests de performance, Excel VBA

Voici un code VBA détaillé pour créer un système de gestion dynamique des plages avec des tests de performance dans Excel. Cela permet d’ajuster dynamiquement les plages en fonction des entrées de données et de tester la robustesse des formules en vérifiant les performances avec différents volumes de données.
Vue d’ensemble du concept
La gestion dynamique des plages implique :

• Ajuster automatiquement les plages nommées en fonction des modifications de données.
• Effectuer des tests de performance en ajoutant des données dynamiques.
• Gérer les erreurs et exceptions pour éviter les plantages.

Code VBA : Gestion Dynamique des Plages et Test de Performance
Ce script VBA :
1. Définit une plage dynamique qui s’ajuste automatiquement en fonction des données.
2. Applique un test de performance en ajoutant dynamiquement des données de test.
3. Vérifie les métriques de performance et gère les erreurs.

Option Explicit
Sub CreateDynamicRangeAndStressTest()
    Dim ws As Worksheet
    Dim lastRow As Long, lastCol As Long
    Dim rngData As Range
    Dim stressTestSize As Integer
    Dim startTime As Double, endTime As Double
    Dim i As Integer
    ' Définir la feuille de travail
    Set ws = ThisWorkbook.Sheets("DataSheet") ' Modifier si nécessaire
    ' Déterminer la dernière ligne et la dernière colonne dynamiquement
    lastRow = ws.Cells(Rows.Count, 1).End(xlUp).Row
    lastCol = ws.Cells(1, Columns.Count).End(xlToLeft).Column
    ' Définir la plage dynamique
    Set rngData = ws.Range(ws.Cells(1, 1), ws.Cells(lastRow, lastCol))
    ' Créer une plage nommée dynamiquement
    ws.Names.Add Name:="DynamicDataRange", RefersTo:=rngData
    ' Test de performance : Ajouter des données de test dynamiquement
    stressTestSize = 5000 ' Ajustez pour augmenter/diminuer la taille du test
    startTime = Timer ' Démarrer le chronomètre
    For i = 1 To stressTestSize
        ws.Cells(lastRow + i, 1).Value = "TestData " & i
        ws.Cells(lastRow + i, 2).Value = Rnd() * 1000
    Next i
    ' Mettre à jour à nouveau la plage dynamique après avoir ajouté les données du test
    lastRow = ws.Cells(Rows.Count, 1).End(xlUp).Row
    Set rngData = ws.Range(ws.Cells(1, 1), ws.Cells(lastRow, lastCol))
    ws.Names("DynamicDataRange").RefersTo = rngData
    endTime = Timer ' Fin du chronométrage
    ' Afficher les résultats du test de performance
    MsgBox "Plage dynamique mise à jour avec " & stressTestSize & " lignes supplémentaires." & vbNewLine & _
           "Temps d'exécution : " & Format(endTime - startTime, "0.00") & " secondes", vbInformation, "Résultats du Test de Performance"
End Sub

Explication détaillée
1. Définition de la plage dynamique :

• Le script trouve la dernière ligne et la dernière colonne utilisée dans la feuille de calcul.
• Il crée une plage (rngData) de la cellule A1 à la dernière cellule utilisée.
• Une plage nommée « DynamicDataRange » est définie pour suivre cette plage.

2. Test de performance :

• Il ajoute 5000 lignes de test avec des données d’exemple (modifiable via stressTestSize).
• Utilise la fonction Rnd() pour générer des valeurs aléatoires.

3. Mesure de performance :

• Utilise la fonction Timer pour mesurer le temps d’exécution.
• Affiche le résultat dans une boîte de message.

4. Mise à jour automatique de la plage :

• Après l’ajout des données de test, la plage nommée est mise à jour dynamiquement.

Comment utiliser le code
1. Créez une feuille de calcul nommée "DataSheet".
2. Exécutez la macro depuis l’éditeur VBA (ALT + F11).
3. Observez le temps d’exécution affiché dans la boîte de message.
4. Utilisez la plage nommée (DynamicDataRange) dans vos formules ou tableaux croisés dynamiques.