Créer des formules de plage dynamique, Excel VBA

Créer des formules de plage dynamique, Excel VBA

Voici un guide détaillé pour créer des formules de plage dynamique avec VBA dans Excel. 

Étape 1 : Ouvrir Excel et accéder à Visual Basic for Applications (VBA) 

1. Ouvrez votre fichier Excel. 
2. Appuyez sur Alt + F11 pour ouvrir l’éditeur Visual Basic for Applications (VBA). 
3. Dans l’éditeur VBA, vous verrez une fenêtre avec la liste des classeurs et des feuilles ouverts sur le côté gauche. Vous utiliserez cette fenêtre pour insérer et gérer votre code. 

Étape 2 : Insérer un module 

1. Dans l’éditeur VBA, cliquez sur Insertion > Module dans la barre de menu. Cela ajoutera un nouveau module à votre projet. 
2. Une nouvelle fenêtre s’ouvrira dans laquelle vous pourrez écrire votre code VBA. 

Étape 3 : Écrire le code VBA pour les formules de plage dynamique 

Dans cet exemple, nous allons écrire un code VBA qui calcule dynamiquement une formule en fonction d’une plage. Imaginons que vous souhaitiez référencer dynamiquement une plage de cellules dans une colonne (par exemple la colonne A) et appliquer une formule de SOMME pour calculer la somme de ces cellules. 

Voici le code : 

Sub CreerFormulePlageDynamique() 
    Dim ws As Worksheet 
    Dim plageDynamique As Range 
    Dim derniereLigne As Long 
    ' Définir la feuille de travail sur laquelle vous souhaitez travailler 
    Set ws = ThisWorkbook.Sheets("Feuil1") 
    ' Trouver la dernière ligne utilisée dans la colonne A 
    derniereLigne = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row 
    ' Définir la plage dynamique dans la colonne A de la ligne 1 à la dernière ligne utilisée 
    Set plageDynamique = ws.Range("A1:A" & derniereLigne) 
    ' Appliquer une formule basée sur la plage dynamique (exemple : une formule de SOMME en B1) 
    ws.Range("B1").Formula = "=SOMME(" & plageDynamique.Address & ")" 
    ' Optionnel : Ajouter une boîte de message pour confirmer l'action 
    MsgBox "Formule de plage dynamique appliquée avec succès !"
End Sub

Étape 4 : Exécuter la macro 

Pour exécuter le code : 

1. Appuyez sur F5 lorsque vous êtes dans l’éditeur VBA, ou allez dans Exécuter > Exécuter Sub/UserForm pour lancer la macro. 
2. Lorsque vous exécutez la macro, Excel calculera la plage dynamique et appliquera la formule dans la cellule B1. La formule additionnera toutes les valeurs dans la colonne A, de la ligne 1 jusqu’à la dernière ligne avec des données. 

Explication : 

Concepts clés : 

1. Objet Worksheet (ws) : La variable ws est utilisée pour référencer la feuille spécifique sur laquelle nous souhaitons appliquer la formule de plage dynamique. Dans cet exemple, elle fait référence à « Feuil1 ». 
2. Calcul de la dernière ligne (derniereLigne) : Nous déterminons la dernière ligne utilisée de la colonne A avec ws.Cells(ws.Rows.Count, « A »).End(xlUp).Row. Cela permet de capturer dynamiquement toute la plage de données, même si des lignes sont ajoutées ou supprimées. 
3. Plage dynamique (plageDynamique) : Nous créons une plage dynamique qui commence à la cellule A1 et s’étend jusqu’à la dernière ligne de données de la colonne A. La ligne Set plageDynamique = ws.Range(« A1:A » & derniereLigne) permet de définir cette plage de manière dynamique. 
4. Application de la formule : La formule est appliquée à la cellule B1. Nous utilisons ws.Range(« B1 »).Formula = « =SOMME( » & plageDynamique.Address & « ) » pour insérer une formule qui somme la plage dynamique. La méthode plageDynamique.Address ajuste automatiquement la plage dans la formule. 
5. Boîte de message : La boîte de message est une fonctionnalité optionnelle qui vous donne un retour une fois que la macro est terminée. 

Résultat : 

• Le code ajuste dynamiquement la plage pour inclure toujours les données de la colonne A, peu importe le nombre de lignes ajoutées ou supprimées. 
• La formule de SOMME dans la cellule B1 additionnera toujours la plage correcte. 

Cette approche peut être étendue à des formules plus complexes ou à différents types de plages. Vous pouvez l’adapter à vos besoins spécifiques dans votre classeur.