Créer une macro pour modifier des tableaux Excel

Créer une macro pour modifier des tableaux dans Excel peut être une tâche puissante pour automatiser des ajustements complexes ou répétitifs. Voici un guide pour écrire une macro qui modifie des tableaux Excel.

Pré-requis

Avoir des bases en VBA (Visual Basic for Applications).
Activer l’onglet “Développeur” dans Excel :
- Fichier > Options > Personnaliser le ruban > Cochez “Développeur”.
Avoir un tableau dans une feuille Excel à titre d’exemple.

Étape 1 : Activer l’éditeur VBA

Accédez à l’onglet Développeur et cliquez sur Visual Basic.
Dans l’éditeur VBA, cliquez sur Insertion > Module pour ajouter un nouveau module.

Étape 2 : Écrire une macro de modification

Voici un exemple de macro pour modifier un tableau nommé Table1 :

Ajouter une colonne.
Modifier une cellule spécifique.
Appliquer un format.

Sub ModifierTableau()
    ' Définir une variable pour le tableau
    Dim tableau As ListObject
    Dim ws As Worksheet
    
    ' Identifier la feuille contenant le tableau
    Set ws = ThisWorkbook.Sheets("Feuil1")
    
    ' Identifier le tableau (changez "Table1" par le nom réel de votre tableau)
    Set tableau = ws.ListObjects("Table1")
    
    ' Ajouter une nouvelle colonne au tableau
    tableau.ListColumns.Add
    tableau.ListColumns(tableau.ListColumns.Count).Name = "Nouvelle Colonne"
    
    ' Modifier une cellule spécifique (ligne 2, colonne 3 dans le tableau)
    tableau.DataBodyRange(2, 3).Value = "Valeur modifiée"
    
    ' Appliquer un format à la première colonne
    tableau.ListColumns(1).Range.Font.Bold = True
    tableau.ListColumns(1).Range.Interior.Color = RGB(200, 230, 255)
    
    MsgBox "Modifications terminées !"
End Sub

Étape 3 : Exécuter la macro

Fermez l’éditeur VBA.
Retournez dans Excel.
Dans l’onglet Développeur, cliquez sur Macros.
Sélectionnez ModifierTableau et cliquez sur Exécuter.

Étape 4 : Tester et personnaliser

Testez la macro sur un tableau d’exemple.
Modifiez les paramètres pour qu’ils s’adaptent à votre situation :
- Changez le nom du tableau dans Set tableau = ws.ListObjects("Table1").
- Ajustez les indices (2, 3) pour cibler d’autres cellules.

Bonnes pratiques

Sauvegardez avant d’exécuter la macro pour éviter les pertes de données.
Commentez votre code pour expliquer chaque étape.
Utilisez On Error Resume Next pour gérer les erreurs si le tableau n’existe pas.

Exemple d’ajout de gestion des erreurs :

Sub ModifierTableauAvecErreurs()
    On Error GoTo GestionErreur
    
    ' Code de modification
    Call ModifierTableau
    
    Exit Sub
GestionErreur:
    MsgBox "Une erreur s'est produite : " & Err.Description
End Sub

Applications possibles

Ajouter automatiquement des colonnes/valeurs selon des critères.
Reformatage d’un tableau après importation.
Génération de rapports basés sur des données existantes.

Ce guide peut être adapté à des besoins spécifiques en fonction des tâches à automatiser.

Pour exécuter une macro automatiquement à l’ouverture d’un fichier Excel ou lorsqu’un événement spécifique se produit, voici les étapes :

1. Exécuter une macro automatiquement à l’ouverture du fichier

Pour exécuter une macro dès que le fichier Excel est ouvert, utilisez l’événement Workbook_Open.

Étapes :

Ouvrir l’Éditeur VBA :
- Dans Excel, allez à Développeur > Visual Basic ou appuyez sur Alt + F11.
Accéder à l’objet ThisWorkbook :
- Dans le volet de gauche, double-cliquez sur ThisWorkbook sous le dossier de votre projet VBA.
Ajouter le code dans l’événement Workbook_Open :
- Copiez ce code dans l’éditeur VBA :

Private Sub Workbook_Open()
    ' Appeler la macro ModifierTableau
    Call ModifierTableau
End Sub

Sauvegarder le fichier au format macro :
- Enregistrez le fichier sous le format Classeur Excel avec macros activées (.xlsm).
Tester :
- Fermez et rouvrez le fichier pour vérifier si la macro s’exécute automatiquement.

2. Exécuter une macro lors d’un changement sur une feuille

Pour exécuter une macro lorsqu’une modification est effectuée dans une feuille spécifique, utilisez l’événement Worksheet_Change.

Étapes :

Ouvrir l’Éditeur VBA :
- Comme avant, accédez à l’Éditeur VBA (Alt + F11).
Accéder à l’objet de la feuille :
- Double-cliquez sur la feuille dans le volet de gauche (ex. Feuil1).
Ajouter le code dans l’événement Worksheet_Change :
- Utilisez ce code comme exemple :

Private Sub Worksheet_Change(ByVal Target As Range)
    ' Vérifier si la modification concerne une colonne ou cellule spécifique
    If Not Intersect(Target, Me.Range("A1:A10")) Is Nothing Then
        ' Appeler la macro ModifierTableau
        Call ModifierTableau
    End If
End Sub

Tester :
- Faites une modification dans la plage spécifiée (ex. A1:A10) pour déclencher la macro.

3. Exécuter une macro à une heure ou intervalle précis

Pour exécuter une macro automatiquement à un moment donné, utilisez Application.OnTime.

Étapes :

Ajouter une macro pour planifier l’exécution :

Sub PlanifierMacro()
    ' Planifier l’exécution de ModifierTableau dans 10 secondes
    Application.OnTime Now + TimeValue("00:00:10"), "ModifierTableau"
End Sub

Exécuter une macro répétitive (optionnel) :
- Si vous voulez qu’elle se répète, vous pouvez rappeler PlanifierMacro à la fin de ModifierTableau.

Sub ModifierTableau()
    ' Votre code ici
    MsgBox "Macro exécutée automatiquement !"
    
    ' Planifier la prochaine exécution
    Call PlanifierMacro
End Sub

4. Exécuter une macro en fonction d’autres événements

Voici d’autres événements où une macro peut être déclenchée :

À la sélection d’une cellule : Worksheet_SelectionChange
À la modification d’un tableau : Worksheet_PivotTableUpdate
À la fermeture du classeur : Workbook_BeforeClose

Par exemple, pour exécuter une macro à la fermeture :

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Call ModifierTableau
End Sub

Conseils

Tester dans un environnement de sauvegarde : Testez toujours dans un fichier de copie pour éviter les pertes de données.
Ajouter des messages de confirmation : Ajoutez des boîtes de message pour vérifier que la macro s’exécute correctement.
Protéger vos macros : Si nécessaire, protégez vos macros avec un mot de passe pour éviter les modifications accidentelles.

Pour une startup comme LightHeritage, plusieurs principes de physique issus de l’optique géométrique, ondulatoire, et de la photonique peuvent être exploités pour créer des expériences immersives et innovantes. Voici une liste des principes physiques clés :

1. Optique Géométrique :

Ces principes servent à concevoir et contrôler les trajets lumineux dans les dispositifs holographiques ou les projections.

Réflexion : Utilisation de miroirs pour guider la lumière vers des zones précises ou créer des illusions optiques.
Réfraction : Modification des angles de propagation de la lumière à travers des lentilles pour focaliser ou dévier le faisceau.
Lentilles et focalisation : Utilisation de lentilles convexes et concaves pour ajuster la taille et la netteté des hologrammes.
Principe de Fermat : Optimisation des chemins lumineux pour minimiser les pertes d’énergie ou maximiser l’efficacité des dispositifs.

2. Optique Ondulatoire :

Ces principes sont cruciaux pour créer des hologrammes et des projections lumineuses spectaculaires.

Diffraction : Permet de créer des images à partir d’interférences lumineuses, ce qui est au cœur des hologrammes.
Interférence : Utilisée pour générer des hologrammes en combinant des ondes lumineuses cohérentes.
Polarisation : Contrôle de la direction d’oscillation des ondes lumineuses pour améliorer la qualité des projections et réduire les reflets indésirables.

3. Photonique :

Cette discipline combine optique et électronique pour créer des dispositifs avancés comme des lasers et des systèmes holographiques.

Lasers cohérents : Utilisation de lasers pour produire des hologrammes précis et lumineux grâce à leur lumière cohérente.
Fibres optiques : Transmission de lumière sur de longues distances pour les installations complexes dans les musées ou sites culturels.
Écrans à modulation optique : Contrôle de la lumière pour afficher des images holographiques dynamiques.

4. Lumière et Couleur :

Des effets visuels captivants peuvent être créés en manipulant les spectres lumineux.

Décomposition spectrale (prisme) : Création de spectacles lumineux basés sur les couleurs issues de la diffraction ou de la réfraction.
Émission de lumière (LEDs et lasers) : Choix des longueurs d’onde pour produire des couleurs spécifiques ou des effets visuels captivants.
Absorption et transmission : Permet de concevoir des filtres optiques pour des scènes holographiques réalistes.

5. Holographie :

Enregistrement des ondes lumineuses : Enregistrement de la phase et de l’amplitude des ondes réfléchies par un objet pour créer une image holographique en 3D.
Reconstruction des ondes lumineuses : Utilisation d’une lumière cohérente pour restituer les détails et la profondeur de l’objet capturé.

6. Optique Adaptative :

Déformation des miroirs : Permet de corriger les distorsions et d’améliorer la qualité des projections dans des environnements complexes.
Capteurs d’ondes lumineuses : Pour ajuster les dispositifs en fonction des variations des conditions ambiantes.

7. Interaction lumière-matière :

Phosphorescence et fluorescence : Pour créer des effets lumineux temporaires ou durables sur des surfaces spécifiques.
Dispersion : Exploiter le comportement de la lumière sur des matériaux variés pour des effets artistiques.

Sécuriser les macros Excel est essentiel pour protéger votre code et les données qu’il manipule, tout en prévenant tout accès non autorisé ou modifications accidentelles. Voici différentes étapes et techniques pour sécuriser vos macros dans Excel.

1. Protéger le projet VBA avec un mot de passe

L’ajout d’un mot de passe empêche les utilisateurs d’accéder ou de modifier le code source des macros.

Étapes :

Ouvrir l’Éditeur VBA :
- Appuyez sur Alt + F11.
Accéder aux propriétés du projet VBA :
- Dans le volet gauche, cliquez droit sur votre projet VBA (par exemple “VBAProject (NomDuFichier.xlsm)”).
- Sélectionnez Propriétés de VBAProject.
Activer la protection :
- Allez dans l’onglet Protection.
- Cochez Verrouiller le projet pour l’affichage.
- Entrez un mot de passe et confirmez-le.
Sauvegarder le fichier :
- Enregistrez le fichier, fermez Excel, puis rouvrez-le pour appliquer la protection.

2. Limiter l’exécution des macros

Excel dispose de paramètres de sécurité qui permettent de contrôler l’exécution des macros.

Étapes :

Configurer les paramètres de sécurité des macros :
- Accédez à Fichier > Options > Centre de gestion de la confidentialité > Paramètres du Centre de gestion de la confidentialité.
- Allez dans Paramètres des macros et choisissez l’option :
  - Désactiver toutes les macros avec notification : Recommande d’avertir l’utilisateur avant d’exécuter les macros.
  - Désactiver toutes les macros sauf celles signées numériquement : Permet uniquement les macros avec une signature numérique.
Signer numériquement vos macros :
- Ouvrez Outils numériques dans Windows et créez un certificat personnel.
- Dans VBA, accédez à Outils > Signature numérique et sélectionnez le certificat.

3. Restreindre l’accès à certaines macros

Vous pouvez créer des restrictions au sein de votre code VBA pour autoriser seulement certains utilisateurs.

Exemple de code : Vérification de l’utilisateur

Sub MacroSécurisée()
    Dim userName As String
    userName = Environ("USERNAME")
    
    ' Vérifiez si l'utilisateur est autorisé
    If userName <> "NomUtilisateurAutorisé" Then
        MsgBox "Vous n'êtes pas autorisé à exécuter cette macro.", vbCritical
        Exit Sub
    End If
    
    ' Code principal de la macro
    MsgBox "Bienvenue, " & userName & " !"
End Sub

4. Empêcher les modifications accidentelles du fichier

Protégez les données et formules dans les feuilles Excel en complément des macros :

Protéger une feuille :

Sélectionnez l’onglet de la feuille.
Allez dans Révision > Protéger la feuille.
Définissez un mot de passe pour limiter les modifications.

Protéger tout le classeur :

Accédez à Fichier > Informations > Protéger le classeur.
Choisissez Crypter avec un mot de passe ou Marquer comme final pour restreindre les actions.

5. Masquer des macros sensibles

Vous pouvez rendre une macro invisible dans le menu “Macros”.

Étapes :

Renommez la macro en la précédant d’un underscore ou en la mettant dans un module privé.

Exemple :

Private Sub _MacroMasquée()
    MsgBox "Cette macro est masquée."
End Sub

Ainsi, la macro n’apparaîtra pas dans la liste des macros.

6. Utiliser un fichier en lecture seule

Configurez votre fichier pour qu’il soit ouvert en lecture seule par défaut :

Configurer le fichier :
- Allez dans Fichier > Enregistrer sous.
- Dans les options avancées, cochez Lecture seule recommandée.
Limiter les permissions :
- Dans Fichier > Informations > Protéger le classeur, sélectionnez Restreindre l’accès.

7. Ajouter une journalisation

Tracez l’utilisation des macros pour surveiller toute activité non autorisée.

Exemple de journalisation :

Sub MacroAvecJournal()
    Dim logFile As String
    Dim fileNumber As Integer
    
    ' Chemin du fichier journal
    logFile = ThisWorkbook.Path & "\JournalMacro.txt"
    fileNumber = FreeFile
    
    ' Écrire l'activité dans le journal
    Open logFile For Append As #fileNumber
    Print #fileNumber, "Macro exécutée par " & Environ("USERNAME") & " à " & Now
    Close #fileNumber
    
    MsgBox "Journal mis à jour."
End Sub

8. Crypter le fichier Excel

Protégez les données du fichier entier avec un mot de passe :

Accédez à Fichier > Informations > Protéger le classeur.
Sélectionnez Crypter avec un mot de passe.
Entrez un mot de passe. Ce mot de passe sera nécessaire pour ouvrir le fichier.

Résumé des meilleures pratiques

Protéger le projet VBA pour éviter les modifications du code.
Signer numériquement vos macros pour qu’elles soient fiables.
Restreindre les permissions pour les feuilles et classeurs.
Ajouter des contrôles utilisateurs pour limiter l’accès.
Crypter le fichier pour protéger toutes les données sensibles.

Étude de Cas : Automatisation de la gestion des factures dans Excel

Contexte

Une entreprise moyenne gère ses factures mensuelles dans un fichier Excel. Ce fichier contient :

Une liste de factures avec des colonnes : numéro de facture, date, montant, statut (Payée/Non payée), client.
Un tableau récapitulatif des montants payés et non payés.
Un besoin d’automatiser :
1. La mise à jour automatique du statut des factures.
2. L’envoi d’alertes pour les factures impayées dépassant une certaine date.
3. La génération d’un fichier PDF pour les factures payées.

Objectifs

Automatiser la gestion des factures avec des macros Excel.
Sécuriser les macros et les données sensibles.

Solution Technique

Structure du fichier Excel

Feuille “Factures” :
- Colonnes : Numéro, Date, Montant, Statut, Client.
- Tableau nommé Table_Factures.
Feuille “Résumé” :
- Total des montants payés et non payés, calculé dynamiquement.
Feuille “Paramètres” :
- Seuil de date pour les factures impayées (par exemple, 30 jours après la date de facture).

Étape 1 : Macro pour mettre à jour le statut des factures

Cette macro parcourt le tableau des factures et met à jour automatiquement le statut (Payée/Non payée) en fonction d’une colonne de paiement.

Code VBA :

Sub MettreAJourStatutFactures()
    Dim ws As Worksheet
    Dim tableau As ListObject
    Dim i As Long
    Dim dateLimite As Date
    
    ' Référencer la feuille et le tableau
    Set ws = ThisWorkbook.Sheets("Factures")
    Set tableau = ws.ListObjects("Table_Factures")
    
    ' Date limite pour les factures impayées
    dateLimite = DateAdd("d", -30, Date)
    
    ' Parcourir chaque ligne du tableau
    For i = 1 To tableau.ListRows.Count
        With tableau.ListRows(i)
            ' Vérifier si la facture est payée
            If .Range(4).Value = "Payée" Then
                .Range(5).Value = "OK" ' Statut OK
            ElseIf .Range(2).Value < dateLimite Then
                .Range(5).Value = "En retard"
            Else
                .Range(5).Value = "En attente"
            End If
        End With
    Next i
    
    MsgBox "Mise à jour terminée."
End Sub

Étape 2 : Macro pour générer des alertes

Cette macro envoie une alerte pour les factures en retard.

Code VBA :

Sub AlerteFacturesEnRetard()
    Dim ws As Worksheet
    Dim tableau As ListObject
    Dim i As Long
    Dim message As String
    Dim enRetard As Boolean
    
    ' Référencer la feuille et le tableau
    Set ws = ThisWorkbook.Sheets("Factures")
    Set tableau = ws.ListObjects("Table_Factures")
    
    message = "Les factures suivantes sont en retard :" & vbCrLf
    enRetard = False
    
    ' Parcourir chaque ligne pour trouver les factures en retard
    For i = 1 To tableau.ListRows.Count
        If tableau.ListRows(i).Range(5).Value = "En retard" Then
            message = message & "- Facture " & tableau.ListRows(i).Range(1).Value & vbCrLf
            enRetard = True
        End If
    Next i
    
    ' Afficher le message ou une confirmation
    If enRetard Then
        MsgBox message, vbExclamation, "Alerte : Factures en retard"
    Else
        MsgBox "Aucune facture en retard.", vbInformation
    End If
End Sub

Étape 3 : Macro pour générer un PDF

Cette macro génère un PDF pour les factures payées.

Code VBA :

Sub GenererFacturesPayeesPDF()
    Dim ws As Worksheet
    Dim tableau As ListObject
    Dim i As Long
    Dim savePath As String
    
    ' Référencer la feuille et le tableau
    Set ws = ThisWorkbook.Sheets("Factures")
    Set tableau = ws.ListObjects("Table_Factures")
    
    ' Définir le chemin de sauvegarde
    savePath = ThisWorkbook.Path & "\FacturesPayees.pdf"
    
    ' Appliquer un filtre pour sélectionner uniquement les factures payées
    tableau.Range.AutoFilter Field:=4, Criteria1:="Payée"
    
    ' Exporter la plage filtrée en PDF
    tableau.DataBodyRange.ExportAsFixedFormat Type:=xlTypePDF, Filename:=savePath, _
        Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=True
    
    ' Désactiver le filtre
    tableau.AutoFilter.ShowAllData
    
    MsgBox "Le PDF des factures payées a été généré.", vbInformation
End Sub

Étape 4 : Sécurisation

Protéger le projet VBA :
- Ajoutez un mot de passe pour protéger le code source.
Protéger les feuilles sensibles :
- Protégez les feuilles contenant les paramètres ou données sensibles.
Limiter les actions utilisateur :
- Ajoutez une validation des utilisateurs dans MettreAJourStatutFactures :

If Environ("USERNAME") <> "AdminUser" Then
    MsgBox "Vous n'êtes pas autorisé à exécuter cette macro.", vbCritical
    Exit Sub
End If

Étape 5 : Automatisation

Automatisez ces macros à l’ouverture ou à la fermeture du fichier :

Mise à jour automatique à l’ouverture :

Private Sub Workbook_Open()
    Call MettreAJourStatutFactures
End Sub

Résultats attendus

Mise à jour automatique des statuts : Les factures sont marquées comme “Payée”, “En retard”, ou “En attente”.
Alertes sur les factures en retard : Une alerte informe de toutes les factures dépassant la date limite.
PDF des factures payées : Génération rapide et sécurisée des fichiers pour l’archivage.