Vous avez vu dans les chapitres précédents comment automatiser le reporting en utilisant différents outils comme les événements ou encore les autres logiciels de la suite Office de Microsoft.
Dans ce dernier chapitre, nous allons mettre l’accent sur la sécurité autour de votre environnement de développement et vous montrer comment vous pouvez mieux sécuriser l'exécution d’une macro.
Sécurisez votre code
Comme vous avez déjà pu le voir avec les fichiers Excel, chaque fichier possède une extension. Pour Excel, les quatre principaux formats sont .xls, .xlsb, .xlsx et .xlsm.
L’extension a une réelle importance pour votre code en VBA, car, en fonction de cette dernière, le code associé à votre macro sera stocké ou non dans votre fichier.
Si vous venez d’écrire une macro dans votre fichier Excel et que vous souhaitez l’enregistrer en .xlsx, un message d’erreur va apparaître.
Excel vous signale que la fonctionnalité "Projet VB" ne peut pas être enregistrée, car l’extension .xlsx ne peut pas contenir de code VBA. Cette restriction permet de vous protéger de l'exécution de macros à l’ouverture par exemple.
Comme nous l’avons vu dans le chapitre précédent, vous pouvez exécuter du code sans que l’utilisateur le voie (lorsque vous avez utilisé l’événement "Open" par exemple).
Une personne mal intentionnée pourrait par exemple dérober des fichiers ou des données et se les envoyer par e-mail, sans que vous puissiez vous en rendre compte, juste en ouvrant un fichier Excel.
Pour pallier ce type de problème, Microsoft a créé une extension (.xlsx) qui ne peut pas contenir de code.
Pour les extensions .xls, .xlsb ou .xlsm, le code que vous avez écrit est directement enregistré dans le fichier avec vos données.
Mais alors, si un jour je supprime mon fichier, je vais aussi perdre mon code ?
Eh oui, bonne remarque, c’est bien là le problème. Votre code étant à l’intérieur de votre fichier, n’importe qui peut le modifier ou l’exécuter. Si vous perdez votre fichier, vous perdez également votre code. Il est donc important de pouvoir le sécuriser et de le sauvegarder régulièrement.
Si votre code est inclus à l’intérieur du fichier, je vous encourage vivement à faire des sauvegardes des différents modules dans des fichiers externes (Bloc-notes, Word, OneNote, site internet, etc.). Vous pourrez donc avoir une copie du code et le restaurer.
Parfois, on ne souhaite pas que le code soit directement dans le fichier. Par exemple, on ne veut pas que d’autres personnes puissent lancer le code VBA. Pour ce faire, vous pouvez utiliser par exemple un fichier qui s’appelle “Personal”. Ce nouveau fichier Excel ne va contenir que du code (vous ne pourrez pas y insérer de données).
Pour faire apparaître le fichier "Personal", il vous suffit de lancer l’enregistrement d’une macro (onglet “Développeur” ou “Affichage”), puis d’enregistrer une nouvelle macro.
Dans la partie “Enregistrer la macro dans”, choisissez “Classeur de macros personnelles”, puis cliquez sur “OK”.
Votre classeur de macros personnelles est maintenant créé ! Si vous retournez dans le VBE, vous pouvez voir dans la fenêtre des projets un nouveau fichier Excel qui s’appelle “PERSONAL.XLSB”.
Vous pouvez maintenant stocker votre code dans les différents modules de ce fichier. Votre code pourra alors s'exécuter sur les différents fichiers, mais il ne sera jamais stocké directement dans vos fichiers de reporting par exemple.
Quand vous allez quitter Excel après votre première macro dans ce fichier, Excel vous demandera si vous souhaitez enregistrer les modifications dans votre classeur de macros personnelles. Il faudra bien sûr cliquer sur "enregistrer”.
Le fait de stocker vos macros dans ce fichier empêche que des utilisateurs modifient ou exécutent votre code. Mais, souvenez-vous que cela n'empêche pas de perdre votre code. Tous vos scripts VBA étant dans ce fichier, cela devient plus simple de les sauvegarder. En effet, vous pouvez retrouver sur l’ordinateur le fichier "PERSONAL.XLSB". Généralement, il se trouve dans : C:\Users\“le nom de votre ordinateur”\AppData\Roaming\Microsoft\Excel\XLSTART.
Si vous avez stocké toutes vos macros dans ce classeur, vous pouvez maintenant sauvegarder ce fichier pour mettre en sécurité l’intégralité de vos macros. De même, si un jour vous changez d’ordinateur, ce fichier peut simplement être déplacé sur le nouvel ordinateur pour avoir accès à toutes les macros facilement et rapidement.
Maîtrisez les options avancées de sécurité
Parfois, les ordinateurs sont paramétrés pour ne pas lancer les macros. Vous avez dans ce cas un message d’Excel qui vous indique que toutes les macros ont été désactivées.
Pour changer cela, il faut :
cliquer sur l'icône d’Office (en haut à gauche de la fenêtre) ;
aller dans “Options” ;
sélectionner “Centre de gestion de la confidentialité” ;
cliquer sur “Paramètres du centre de gestion de la confidentialité” dans la fenêtre de droite.
Dans cette nouvelle fenêtre, cliquez sur “Paramètres des macros”. Vous avez ensuite quatre choix possibles :
1. "Désactiver toutes les macros sans notification" : vous n’aurez alors pas le message pour activer les macros à l’ouverture d’Excel.
2. "Désactiver toutes les macros avec notification" : c’est un choix intéressant si vous développez souvent des macros. Cela permet d’allier la praticité et la sécurité.
3. "Désactiver toutes les macros à l’exception des macros signées numériquement".
4. "Activer toutes les macros" : cela n’est pas recommandé par Excel et je ne vous le conseille pas non plus. N’importe quel code VBA pourrait se lancer à l’ouverture d’Excel.
Aussi, j’apprécie tout particulièrement la possibilité de signer une macro, mais qu’est-ce que cela signifie ? En pratique, la vraie signature numérique requiert de passer par un organisme qui atteste que votre macro vous appartient et qu’elle est donc sûre. Pour une petite macro sur votre ordinateur, vous n’avez pas besoin qu’un organisme externe authentifie votre code.
Pour simplifier cette procédure, Microsoft met à disposition un outil qui vous permet de signer vous-même une macro : Selfcert.
Pour ce faire, allez chercher cet outil. De mon côté, il se trouve dans C:\Program Files\Microsoft Office\root\Office16.
Vous pouvez ensuite lancer l’application qui s’appelle “SELFCERT.EXE”.
Donnez un nom à votre certificat : “reporting”, par exemple. Puis cliquez sur “OK”, le message suivant apparaît : “Création réussie d’un certificat pour reporting”.
Retournez maintenant dans le VBE pour affecter ce certificat à votre code :
cliquez dans la fenêtre des projets sur le fichier Excel qui contient le code VBA ;
allez dans “Outils”, puis “Signature électronique” ;
cliquez sur “Choisir” ;
choisissez le certificat que vous venez de créer ;
validez votre choix en cliquant sur “OK”.
Maintenant, il ne vous reste plus qu’à changer le paramétrage d’exécution des macros dans les options d’Excel pour le passer en “Désactiver toutes les macros à l’exception des macros signées numériquement”. Si tout s'est bien passé, votre macro doit maintenant s'exécuter, sans faire apparaître de message particulier. 😉
Sécurisez l’utilisation de vos données par les utilisateurs
Pour finir, voyons comment nous pouvons protéger le code. Dans un premier temps, vous pouvez rajouter un mot de passe pour ouvrir le VBE.
Rien de plus simple :
faites un clic droit sur votre classeur dans la fenêtre des projets du VBE ;
cliquez sur “Propriétés de VBAProject” ;
cliquez sur l’onglet “Protection de cette nouvelle fenêtre” ;
ajoutez un mot de passe pour pouvoir afficher le code.
Vous avez maintenant un code qui est protégé et qui ne peut pas être modifié. Cependant, ce code peut toujours être exécuté par n’importe quel utilisateur à l’ouverture d’Excel. Or, nous souhaitons le rendre exécutable seulement par nous-même.
Vous pouvez pour cela ajouter une condition dans le code qui va permettre de savoir quel est l’ordinateur qui essaye de lancer le code.
Nous allons utiliser le code “Username”.
Il faudra seulement définir une nouvelle variable et ajouter à cette variable le "Username" :
dim nom_utilisateur as string
nom_utilisateur = Environ("USERNAME")La variable "nom_utilisateur" contient donc maintenant le nom de la session de votre ordinateur, dans mon cas “Jeremy”.
Je n’ai plus qu’à mettre un “If” au début de mon code pour qu’il s'exécute seulement si le "Username" = "Jeremy".
À vous de jouer !
Maintenant que vous avez fait une jolie macro, vous souhaitez la sécuriser pour votre collègue.
Vous allez :
changer de place la macro et la mettre dans le fichier "Personal" ;
créer un certificat pour cette macro ;
changer le paramétrage d’Excel afin d'exécuter uniquement les macros signées ;
ajouter un mot de passe dans le VBE ;
donner le droit d'exécuter cette macro seulement à vous-même et à votre collègue.
Vous avez terminé ? Voici le corrigé, en screencast :
En résumé
Le code VBA associé aux macros peut être stocké soit directement à l’intérieur du fichier soit dans un fichier personnel.
Seules les extensions .xls, .xlsb et .xlsm permettent de stocker les macros.
Le fichier "Personal" permet de stocker les macros dans un fichier indépendant.
L’ajout d’un certificat à une macro permet d'authentifier en signant la macro avec un nom.
L’utilisation du "Username" permet de réduire l'exécution du code aux personnes qui y sont autorisées.
Pour une protection maximale, il est possible d’ajouter un mot de passe pour afficher le code dans le VBE.
Dans ce chapitre, nous avons fait un large tour d'horizon de la sécurité autour des macros et du VBE. Ce chapitre clôt également la première partie de ce cours sur l’automatisation du reporting.
Dans la prochaine partie, nous allons nous concentrer sur les différentes analyses graphiques (descriptives et temporelles) ainsi que sur les analyses bivariées et multivariées.
