OFFICE SCRIPTS – Une Introduction

 

OFFICE SCRIPTS c’est quoi ?

Grossièrement, Office Scripts est pour Excel mode online l’équivalent de ce que l’enregistreur de macros et de l’éditeur VBE est pour EXCEL mode bureau.

Nous écrivons les scripts d’OFFICE SCRIPTS en langage TypeScript lui-même hérité du langage JavaScript (ce qui rapproche ces scripts de ceux écrits en Google Apps Script pour les GOOGLE SHEETS).

Usage basique d’OFFICE SCRIPTS

Sur le même principe que l’enregistreur de macros d’EXCEL mode bureau, l’enregistreur d’actions nous permet d’enregistrer une série d’actions dans EXCEL online, de donner un nom à cette série puis de la reproduire en l’exécutant.

Usage avancé d’OFFICE SCRIPTS

Sur le même principe que l’éditeur VBE d’EXCEL mode bureau, l‘éditeur de code intégré nous permet de créer des scripts à partir de rien ou de modifier les scripts générés par l’enregistreur d’actions. Avant de nous lancer dans l’écriture de code, nous devons posséder un minima de connaissance du langage TypeScript. L’investissement nécessaire à cet apprentissage dépend grandement de notre connaissance préalable de la programmation (Si nous maîtrisons le langage Javascript, cet investissement devrait être relativement léger).

Ce qu’il nous faut pour bénéficier d’OFFICE SCRIPTS

Pour pouvoir bénéficier d’OFFICE SCRIPTS, nous devons disposer d’un des abonnements Microsoft 365 listés ici.

Dans sa version de mars 2022, OFFICE SCRIPTS n’est pas disponible dans EXCEL mode bureau.

Lorsque nous disposons d’un abonnement Microsoft 365 éligible à OFFICE SCRIPTS, l’onglet Automatiser est ajouté au ruban EXCEL mode online :

Menu_Debogage_Compiler

Trouvons de l’aide sur OFFICE SCRIPTS

En mars 2022 le produit OFFICE SCRIPTS est encore récent et, de ce fait, l’offre d’aide est encore limitée.

La documentation en ligne d’OFFICE SCRIPTS de Microsoft est accessible ici.

Nous disposons de vidéos de présentation d’Office Scripts. En premier lieu celles réalisées par Microsoft ou encore celles réalisées par d’autres contributeurs : celle de Kevin Stratvert (en anglais) ou celle de Leila Gharani (en anglais) par exemple.

Au moment de l’écriture de cet article, aucune formation dédiée à Office Scripts n’a pu être trouvée. A défaut, nous pouvons, si besoin, nous former à JavaScript. La plateforme de formations Udemy en propose plusieurs dont certaines réalisées par des formateurs de langue française.

Nous pouvons soumettre nos problèmes et questions sur les forums d’échanges en français tel que celui d’EXCEL-PRATIQUE auquel nous contribuons.

Voyons les différences essentielles entre OFFICE SCRIPTS et VBA

(Constat de mars 2022)

Portée d’Office Scripts versus portée du VBA

  • Alors que le VBA n’est utilisable dans EXEL qu’en mode bureau, a contrario, OFFICE SCRIPTS n’est actuellement disponible qu’en mode online. Il semble cependant que la volonté de Microsoft soit d’étendre l’usage OFFICE SCRIPTS dans les années à venir. Pour autant, compte-tenu du nombre considérable de solutions VBA actuellement en œuvre, il est certain que la fin du VBA n’est pas pour demain.

Stockage des Scripts

  • Avec le VBA les macros sont stockées dans le même fichier que le classeur alors que les fichiers de scripts OFFICE SCRIPTS (d’extension .osts) sont stockés dans le dossier « Scripts Office » de notre espace OneDrive. Cela offre l’avantage de pouvoir aisément et à notre convenance partager nos scripts entre nos divers classeurs et d’en faire bénéficier d’autres utilisateurs de notre organisation.
Menu_Debogage_Compiler

Déclaration des objets manipulés

  • Alors que VBA tolère la déclaration implicite des objets manipulés dans un classeur, OFFICE SCRIPTS impose une déclaration explicite de tout objet manipulé. Ainsi par exemple pour l’objet ‘worksheet’, VBA (à défaut d’une déclaration explicite) considère la feuille sélectionnée dans le classeur comme étant la feuille manipulée, alors que pour OFFICE SCRIPTS la déclaration explicite de la feuille manipulée est impérative. Nous réalisons cette déclaration dans le code TypeScript avec une instruction let :
Menu_Debogage_Compiler

Référencement par indice

  • Une différence essentielle : le référencement des cellules par indice.
    Dans VBA le référencement des cellules par indice a pour base 1 alors que dans OFFICE SCRIPTS le référencement des cellules par indice a pour base 0.
    Ainsi, pour référencer par indice la cellule « A1 » de la feuille active, dans VBA nous écrivons :
    set oCell = Activesheet.Cells(1,1)
    alors que dans un script OFFICE SCRIPTS nous écrivons :
    let oCell = SelectedSheet.getCells(0,0)

Déclenchement des scripts

  • Dans la version de mars 2022, OFFICE SCRIPTS est capable de déclencher l’exécution de script au moyen d’un bouton déposé sur la feuille du classeur.
    Contrairement aux macros VBA qui peuvent être déclenchées sur un évènement (par exemple lorsque la valeur d’une certaine cellule change) OFFICE SCRIPTS n’en a pas la capacité dans sa version actuelle (gageons que ces limitations évolueront dans un futur proche, restons vigilants…).
  • Au demeurant, l’application POWER AUTOMATE (incluse dans les abonnements Microsoft 365) nous permet de déclencher les scripts OFFICE SCRIPTS sur certains évènements comme : l’arrivée d’un mail dans OUTLOOK 365 ou la survenue d’une heure ou d’une date déterminée.

Interaction avec les utilisateurs

  • Encore une différence essentielle : dans la version mars 2022, OFFICE SCRIPTS ne nous permet pas d’interagir avec les utilisateurs.
    Avec VBA nous interagissons avec les utilisateurs au moyen de formulaires « userforms » ou avec l’instruction « Input » ou bien encore avec l’instruction « Msgbox ». Toutes ces techniques sont actuellement indisponibles dans OFFICE SCRIPTS. L’application POWER AUTOMATE (incluse dans les abonnements Microsoft 365) pourrait nous offrir une alternative.

Gestion des erreurs et exceptions

  • Une bonne nouvelle : OFFICE SCRIPTS comporte une instruction ‘try…catch…finally’.
    Grâce à l’instruction ‘try…catch…finally’ OFFICE SCRIPTS nous permet de gérer les erreurs et les exceptions de façon structurée.
    (Un ouf de soulagement est poussé par les tenants de la programmation structurée, dont nous sommes, horrifiés par les instructions « On Error… » parfois incontournables en VBA).

 Ajout automatique de commentaires par Office Scripts

  • L’enregistreur d’actions OFFICE SCRIPTS ajoute automatiquement des lignes de commentaires (en anglais actuellement) dans le code qu’il génére. Par exemple, dans le script suivant les lignes de commentaires ajoutés automatiquement sont surlignés en jaune :
Menu_Debogage_Compiler

Illustrons OFFICE SCRIPTS par un exemple

A titre d’exemple, reprenons l’application EXCELLONS ‘Jours fériés en France’ téléchargeable ici.

Application EXCEL "Jours fériés en France"

Lorsque nous ouvrons ce classeur dans EXCEL en mode bureau, si nous modifions le territoire ou l’année, le tableau des jours fériés est automatiquement mis à jour, de même que le nombre de jours ouvrés.

Par contre, lorsque nous ouvrons ce classeur dans EXCEL en mode online, si nous modifions le territoire ou l’année… OUPS! Rien ne se passe…

En effet, EXCEL mode online ne prend pas en charge ni les évènements du classeur ni les macros VBA (comme vu dans le chapitre précédent). Dans le cas où nous voudrions transposer les fonctions réalisées par VBA, il nous faut réaliser un script OFFICE SCRIPTS en TypeScript.

Une fois mis au point, ce script sera exécuté dans EXCEL mode online de la façon suivante:

Application EXCEL "Jours fériés en France"

A noter que les Office scripts se comportent de façon identique sur plateforme WINDOWS et MAC. A contrario, le code VBA de ce classeur ne peut s’exécuter sur MAC (du fait de l’absence de l’objet ‘MSXML2.XMLHTTP’ dans cette environnement).

Ci-dessous, nous fournissons l’intégralité du code Office Scripts permettant de réaliser, en environnement EXCEL Online, la mise à jour du tableau de résultats en fonction du territoire et de l’année choisis :

//OfficeScript de récupération des jours fériés en France pour un territoire donné et une année donnée.
async function main(workbook: ExcelScript.Workbook){
//On vérifie que le classeur est celui attendu
const cWorkbookName = 'JoursFeries.xslm';
let sWorkbookName: string = workbook.getName();
if (sWorkbookName = cWorkbookName) {
    try {
        //On affecte la feuille et on vérifie que la feuille active est celle attendue
        let oActiveSheet: ExcelScript.Worksheet = workbook.getWorksheet('Accueil');
        try {
            //On affecte toutes les zones nommées et on vérifie leur présence
            let sChoixZone = oActiveSheet.getRange('txtZone').getValue();
            let sChoixAnnee = oActiveSheet.getRange('txtAnnee').getValue().toString();
            let oCellStatut = oActiveSheet.getRange('txtStatut');
            let oTableau: ExcelScript.Table = oActiveSheet.getTable('tblResult');
            let oCellMsg = oActiveSheet.getRange('txtMessage');

            //On efface le message et le statut
            oCellMsg.clear(ExcelScript.ClearApplyTo.all);
            oCellStatut.clear(ExcelScript.ClearApplyTo.all);

            //On efface le contenu du tableau de résultats
            oTableau.getRangeBetweenHeaderAndTotal().clear(ExcelScript.ClearApplyTo.all)

            //On initialise la cellule statut
            oCellStatut.getFormat().getFill().setColor('orange');
            oCellStatut.setValue('En cours...');

            //On déclare l'URL racine de l'API
            const cUrl = 'https://calendrier.api.gouv.fr/jours-feries/';
            //On constitue la requête pour l'API
            const sRequestString = cUrl + sChoixZone + '/' + sChoixAnnee + '.json';
            //On constitue la formule à copier dans la colonne 'Jour de la semaine' pour obtenir le jour de la semaine
            const sFormula = "=SI(ESTNUM([@[Jour férié]]);TEXTE([@[Jour férié]];\"jjjj\");\"\")";

            //On affecte l'objet Request API
            const oRequest = await fetch(sRequestString);            
            try {
                //On teste le statut de la réponse de l'API
                if (oRequest.status === 200) {
                    //On récupère la réponse de l'API
                    const oResponseJSON: string = await oRequest.json();
                   
                    //On parse la réponse de l'API
                    const sResponse = JSON.stringify(oResponseJSON);
                    let lLen = sResponse.length;
                    let sUsageData = sResponse.substring(1, lLen - 1);
                    const aUsageData = sUsageData.split(',');

                    //On traite chaque ligne de la réponse parsée
                    for (let i = 0; i < aUsageData.length; i++) {
                        let aUsageText = aUsageData[i].split(':');

                        //On remplit la première colonne du tableau avec les dates reçues
                        let sText: string  = aUsageText[0].trim();
                        lLen = sText.length;                    
                        sText = sText.substring(1, lLen - 1);
                        oTableau.getRangeBetweenHeaderAndTotal().getCell(i, 0).setValue(sText);
                        oTableau.getRangeBetweenHeaderAndTotal().getCell(i, 0).getFormat().setHorizontalAlignment(ExcelScript.HorizontalAlignment.center);

                        //On remplit la deuxième colonne du tableau avec le libellé reçu
                        let sText2: string = aUsageText[1].trim();
                        lLen = sText2.length;
                        sText2 = sText2.substring(1, lLen - 1);
                        oTableau.getRangeBetweenHeaderAndTotal().getCell(i, 1).setValue(sText2);

                        //On remplit la troisième colonne du tableau avec la formule donnant le jour de la semaine
                        oTableau.getRangeBetweenHeaderAndTotal().getCell(i, 2).setFormulaLocal(sFormula);                        
                    }
                    //On met en forme le message de résultat et le statut à OK
                    oCellStatut.getFormat().getFill().setColor('green');
                    oActiveSheet.getRange('E5').getFormat().getFont().setColor("FFFFFF");
                    oCellStatut.getFormat().setHorizontalAlignment(ExcelScript.HorizontalAlignment.center);
                    oCellStatut.setValue('OK');
                    oCellMsg.setValue("L'API a renvoyé " + aUsageData.length + " jours fériés en " + sChoixAnnee + " pour le territoire '" + sChoixZone + "'." );
                    oCellMsg.getFormat().getFont().setColor('green');

                    //On fait pointer le surseur sur la ligne de titre
                    let oRange = oActiveSheet.getRange("B2:D2");
                    oRange.select();
                }
                else {
                    //Dans le cas où l'on obtient une erreur de l'API
                    oCellStatut.getFormat().getFill().setColor('red');
                    oCellStatut.setValue('Erreur');
                    oCellStatut.getFormat().setHorizontalAlignment(ExcelScript.HorizontalAlignment.center);
                    //On ajoute un commentaire à la cellule contenant le message 
                    let oComment = oActiveSheet.addComment(oCellMsg.getAddress(), "L'API ne peut répondre à la requête pour le territoire et l'année indiquée !");
                    
                }
            }
            catch {
                //Dans le cas où l'on ne peut atteindre l'API
                oCellStatut.getFormat().getFill().setColor('orange');
                oCellStatut.setValue('Indisponibilité');
                oCellStatut.getFormat().setHorizontalAlignment(ExcelScript.HorizontalAlignment.center);
                oCellMsg.setValue("Le service interrogé est momentanément indisponible. Attendre quelques minutes puis retenter l'exécution de ce script.");
                console.log(oRequest.status);
            }
        }
        catch{																											//Dans le cas où une zone nommée n'est pas présente
            console.log("Le classeur actif ne semble pas correspondre à ce traitement. Traitement stoppé.")
        }
    }
    catch {																												//Dans le cas où la feuille attendue n'est pas présente
        console.log("La feuille voulue n'est pas présente dans le classeur actif. Traitement stoppé.")
    }
}
else {
    //Dans le cas où le classeur n'est pas celui attendu
    console.log(sWorkbookName);
}
}

EXCEL 365 Généralités

Excellons - EXCEL 365

Microsoft 365 : Une approche innovante

L’ambition affichée par Microsoft avec Microsoft 365 est d’offrir aux entreprises de toutes tailles un ensemble de services et d’applications favorisant le « travail hybride » de leurs collaborateurs en capitalisant sur les connaissances acquises sur les applications bureautiques classiques (EXCEL, WORD, POWERPOINT, OUTLOOK…).

Par « travail hybride », Microsoft entend le travail en tous lieux, de façon collaborative et de manière sécurisée quelque soit la plateforme matérielle utilisée : PC, MAC, smartphones, tablettes…

En mode online, les applications bureautiques s’exécutent en dans un navigateur WEB. Il est, de ce fait, concevable que ces applications soient potentiellement accessibles sur toutes les plateformes matérielles du marché : PC, MAC, LINUX. Pour connaître en détail les navigateurs supportés, reportons-nous à la page du support Microsoft ‘Navigateurs fonctionnant avec Office pour le WEB’.

Sur les environnements pouvant les accueillir, Microsoft 365 apporte des applications bureautiques s’exécutant soit en mode online, soit en mode bureau et de basculer d’un mode à un autre par un simple clic.

Par exemple le ruban EXCEL en mode online :

Microsoft 365 : Aspects financiers

Dans le passé, nous étions habitués à acquérir les produits Microsoft en payant une fois pour toute une licence et, éventuellement, de payer une nouvelle redevance (réduite) pour les nouvelles versions (upgrades).
Ce modèle financier est maintenant obsolète chez Microsoft comme chez la plupart des éditeurs de logiciels.

Avec Microsoft 365, nous payons un abonnement pour un laps de temps déterminé. Le prix de ces abonnements s’échelonne de 0 € (offre Office 365 A1) à près de 65 € (offre Microsoft 365 E5) par utilisateur et par mois (prix TTC en septembre 2021. Consultons le site Microsoft 365 pour obtenir les coûts actualisés des nombreux abonnements proposés).

Au delà des applications classiques EXCEL, WORD, POWERPOINT, OUTLOOK, Microsoft 635 offre l’accès à un nombre impressionnant d’applications parmi lesquelles Microsoft Teams, Power Apps, Power BI, Power Automate, Visio, OneDrive, Yammer,…

Bien évidemment, en fonction de l’abonnement souscrit, nous aurons accès à un ensemble plus ou moins fourni des services et des applications disponibles (consulter le site Microsoft 365 pour connaître la liste des applications et services inclues dans chaque abonnement).

EXCEL 365 : Aspects opérationnels

 

L’usage d’EXCEL 365 en mode online est similaire à la version classique d’EXCEL.

Toutefois, un certain nombre de restrictions affectent le mode online (restrictions détaillées dans l’article du support Microsoft « Différences entre l’utilisation d’un classeur dans le navigateur et dans Excel »).

Ajoutons qu’en mode online (version septembre 2021) :

  • Le nommage des plages de cellules n’est pas disponible. Cependant en mode online, les noms de plages affectés en mode bureau sont utilisables dans les formules.
  • Les macros VBA réalisées en mode bureau sont inopérantes en mode online. A la place du VBA en mode online,  l’automatisation des tâches se réalise au moyen d’OfficeScript combiné à Power Automate. Reportons-nous à l’article ‘Office Scripts – Une Introduction’ pour plus de détail.

Enfin, nos classeurs EXCEL 365 sont stockés dans le cloud (OneDrive) pour pouvoir les partager avec les utilisateurs autorisés dans notre environnement de travail. 

Le débogage de code VBA

DEBUTONS LE DEBOGAGE : COMPILONS ! 

Pourquoi compiler ? 

Quand compiler ?

Comment compiler ?

DEBOGUONS LES ERREURS D’EXECUTION ET DE LOGIQUE

Ajoutons la barre de menu du débogueur

Exécutons le code en mode pas à pas

Examinons les techniques et outils à notre disposition

Affichage de la valeur d’une variable

Mise en place de point d’arrêt

Mise en place d’espions

La fenêtre des « Variables locales »

La fenêtre d’exécution

Utilisation de l’instruction « Debug.Print »

Utilisation de l’instruction « Msgbox »

APRES DEBOGAGE, N’OUBLIONS PAS !

 

 

DEBUTONS LE DEBOGAGE : COMPILONS !

Le langage VBA est du type « interprété » alors que d’autre langages sont de type « compilé » (tels C++, C#, Pascal…)

Cela signifie qu’il n’y a pas nécessité de compiler les codes VBA pour pouvoir les exécuter.

L’interpréteur VBA se charge de transformer « à la volée », ligne à ligne, le code VBA compréhensible par l’humain en code compréhensible par la machine (exécutable).

Toutefois, VBA possède une fonction de compilation produisant à partir du code source VBA un autre objet exécutable par Windows.

Il semble que cette opération n’ait pas d’impact significatif sur les performances d’exécution des codes VBA, contrairement à ce qu’on pourrait attendre.

 

Pourquoi compiler ?

En compilant notre code, nous pourrons éliminer les erreurs de syntaxe non corrigées ou non détectées lors de la saisie du code dans l’éditeur VBE.

Ici, une liste non exhaustive des erreurs commises couramment détectées lors de la compilation :

  • Instruction IF sans END IF
  • Directive WITH sans END WITH
  • Instruction SELECT sans END SELECT
  • Instruction FOR ou FOR EACH sans NEXT
  • Appel de procédure ou fonction inexistantes ou non atteignables
  • Passage de paramètres non conformes ou omis lors d’appel aux procédures et fonctions.
  • Variable non déclarée en cas d’utilisation de la clause « Option Explicit ».

VBA ne nous contraint pas à déclarer toutes les variables utilisées dans le code (d’ailleurs, par exemple, l’enregistreur de macro ne le fait pas).

Cependant, beaucoup d’auteurs de référence recommandent l’utilisation de la clause « Option explicit »

Concernant l’utilisation des variables en VBA, nous nous reporterons utilement aux chapitres de la formation VBA du site « EXCEL-PRATIQUE » :

https://www.excel-pratique.com/fr/vba/variables

https://www.excel-pratique.com/fr/vba/variables_suite

 

Quand compiler ?

Le compilateur VBA n’indique pas les erreurs en bloc mais s’arrête à chaque fois qu’il en rencontre une pour la signaler.

Nous avons tout intérêt, pour corriger nos erreurs de syntaxe, à lancer régulièrement une compilation au fur et à mesure de l’écriture d’une séquence de code conséquente.

Comment compiler ?

Nous exécutons la compilation de notre projet VBA par l’activation des choix dans le menu VBE : Débogage/Compiler [nom de projet] ou le raccourci clavier : ‘Alt+D+l’ :

Menu_Debogage_Compiler

 

Lorsqu’un projet est compilé sans erreur, le choix « Compiler [nom de projet] » apparaît en grisé dans le sous- menu « Débogage ».

 

DEBOGUONS LES ERREURS D’EXECUTION ET DE LOGIQUE

Une fois notre code compilé sans erreur, nous nous attaquons au débogage des erreurs se produisant à l’exécution ainsi que des erreurs de logique.

 

Ajoutons la barre de menu du débogueur

Pour nous faciliter la tâche, nous pouvons ajouter à l’éditeur VBA la barre de menu spécifique au débogage : En cliquant droit sur la barre de menu et en sélectionnant « Débogage » :

La barre suivante est ajoutée :

Cette barre reprend les fonctions accessibles dans le sous-menu « Débogage » :

Exécutons le code en mode pas à pas

Dans l’éditeur VBE, nous avons la possibilité d’exécuter le code suivant diverses modalités :

En positionnant le curseur sur le début d’une procédure ou fonction et en cliquant sur le bouton dans la barre de débogage   (ou F8) nous exécutons le code en mode pas à pas, sur chaque ligne d’instructions de notre code :

De cette façon, nous suivrons le cheminement de notre code, en vérifiant la logique et corrigeant les erreurs.

Nous ne pouvons exécuter de cette façon que les procédures (Sub) ou fonctions (Function) qui n’attendent aucun paramètre d’entrée. Pour déboguer une procédure ou fonction attendant des paramètres, nous débuterons le débogage dans une procédure ou fonction appelante transmettant les paramètres attendus.

Après avoir activé le mode pas à pas, la ligne de la procédure ou fonction à exécuter est surlignée en jaune :

Lorsque nous cliquons une nouvelle fois sur le bouton « Pas à pas » (ou F8), le débogueur se positionne sur la première instruction à exécuter, en négligeant le lignes de déclarations de constantes ou de variables :

Ainsi de suite jusqu’à la fin de la procédure ou fonction (End Sub/End Function)

En mode d’exécution pas à pas, en plaçant le curseur sur la flèche jaune dans la marge tout en maintenant le clic gauche de la souris, nous avons la possibilité de déplacer la prochaine exécution en aval ou en amont des instructions du code.

Examinons les techniques et outils à notre disposition

 

Affichage de la valeur d’une variable

En suivant l’exécution de notre code en mode « pas à pas », nous avons la possibilité de connaître la valeur de variables impliquée dans l’instruction à exécuter, en plaçant simplement le curseur sur cette variable : un « tip » nous indique sa valeur :

Mise en place de point d’arrêt

Nous positionnons un point d’arrêt en cliquant dans la marge devant une instruction (ou en cliquant sur le bouton  ou F9).

La ligne d’instruction apparaît alors surlignée en rouge foncé avec un repère de même couleur dans la marge :

Une fois un point d’arrêt positionné, lorsque nous exécutons le code en mode « continu » (F5), l’exécution s’arrêtera sur la ligne avant de l’exécuter :

Pour effacer tous les points d’arrêts positionnés : (Ctrl+Maj+F9).

 

Mise en place d’espions

 

Avec la mise en place d’espions, nous avons la possibilité de définir des expressions permettant soit de visualiser leurs valeurs tout au long de l’exécution, soit de stopper l’exécution lorsque l’expression devient vraie, soit de stopper l’exécution lorsque leurs valeurs changent.

Nous positionnons un espion en cliquant le bouton  (ou Alt+D+u).

Dans l’exemple suivant nous positionnons un espion permettant de suivre l’évolution de la valeur de la variable objet « oRange » :

La fenêtre « Espions » que nous faisons apparaître en cliquant sur le bouton (ou Alt+A+s), liste tous les espions positionnés avec leurs valeurs :

La fenêtre des « Variables locales »

La fenêtre « Variables locales » que nous faisons apparaître en cliquant sur le bouton   (ou Alt+A+v), liste toutes les variables de la procédure en cours :

Et pour les tableaux mémoire (array), la liste de toutes leurs dimensions et valeurs :

De plus, nous avons la possibilité dans cette fenêtre d’ouvrir, pour les variables objets, la liste de toutes les propriétés de l’objet avec leurs valeurs :

La fenêtre d’exécution

La fenêtre « d’exécution » que nous faisons apparaître en cliquant sur le bouton  (ou Ctrl+G), nous offre une multitude d’utilisations :

 

  • En y formulant une question, nous pouvons obtenir immédiatement la réponse.
    Par exemple si nous voulons connaitre le dossier du classeur actif, il suffit d’enter dans la fenêtre le signe « ? » suivi de la propriété recherchée. La valeur est alors restituée dans la ligne suivante de la fenêtre :

 

  • Nous pouvons également y exécuter une instruction en dehors du code de la procédure exécutée. Par exemple avec l’instruction suivante, on modifie la couleur du fond d’une plage définie au préalable :

Résultat obtenu :

  • Nous pouvons exécuter une procédure ou une fonction en lui passant des paramètres et obtenir la valeur retournée :
  • Nous pouvons affecter une valeur à une variable :
  • Nous pouvons y voir figurer les valeurs affectées par l’instruction « Print» (Cf ci-dessous).

Utilisation de l’instruction « Debug.Print »

L’instruction « Debug.Print » suivi d’une valeur introduite dans le code, nous permet de visualiser cette valeur dans la « Fenêtre exécution » (Ctrl+G)

Notons que cette instruction a l’avantage de pouvoir être laissée dans le code après mise au point du fait que son résultat n’apparaîtra pas à l’utilisateur dans un usage ordinaire.

Utilisation de l’instruction « Msgbox »

L’instruction « Msgbox » suivi d’une variable, permet d’obtenir l’affichage d’un message contenant la valeur de cette variable :

Le déroulement du code est interrompu tant que l’on ne clique pas sur le bouton « OK » du message.

Nous pouvons noter que le désavantage de cette instruction réside dans le fait qu’elle ne doit pas subsister dans le code une fois la mise au point finalisée pour ne pas faire apparaître aux utilisateurs un message obscur interrompant le déroulement du programme.

 

 

APRES DEBOGAGE, N’OUBLIONS PAS !

Après notre phase de mise au point, nous n’oublions pas :

  • De supprimer tous les points d’arrêts positionnés.
  • De supprimer tous les espions interrompant le code.
  • De supprimer toutes les instructions « Msgbox » introduites dans le code pour afficher des valeurs.

 

Allons vers d’autres articles « Bonnes Pratiques VBA ».

Version PDF de cet article : Outils de débogage VBA

Révéler les lignes et colonnes cachées

 

Cette macro révèle les lignes et colonnes cachées dans toutes les feuilles d’un classeur.

L’utilisateur doit indiquer le classeur à traiter.

Sub MontrerTout()
'------------------------------------------------------------------------------------------------
'Procédure pour révéler toutes les lignes et colonnes cachées dans toutes les feuilles d'un classeur
'-l'utilisateur doit choisir le classeur à traiter
'-En fin de traitement le classeur traité reste ouvert
'Auteur : Excellons.org
'Date : juin 2019
'------------------------------------------------------------------------------------------------
Const cFilter = "Classeur EXCEL(*.xls*), *.xls*"
    
    Dim oWB As Workbook
    Dim oSheet As Worksheet
    Dim sWBName As Variant
    Dim sExtension As String
    Dim sDecoupe() As String
    
    'On demande à l'utilisateur d'indiquer le nom du classeur à traiter
    sWBName = Application.GetOpenFilename(cFilter, 1, "Choisissez le classeur à traiter", , False)
      
    'On s'assure que l'utilisateur n'a pas quitté la boite de dialogue sans indiquer de fichier. Sinon on met fin à l'opération.
    If sWBName = False Then
        MsgBox "Aucun classeur indiqué !" & vbCrLf & vbCrLf & "Opération sans objet.", vbCritical, "FIN D'OPERATION"
        Exit Sub
    End If
    
    'On s'assure que l'utilisateur a indiqué un classeur EXCEL
    sDecoupe() = Split(sWBName, ".") 'On découpe le nom en 2 pour récupérer son extension
    'On s'assure que le fichier choisi possède une extension de classeur EXCEL. Sinon on met fin à l'opération.
    If Left(sDecoupe(1), 3) <> "xls" Then
        MsgBox "Le fichier indiqué ne semble pas être un classeur EXCEL !" & vbCrLf & vbCrLf & "Opération sans objet.", vbCritical, "FIN D'OPERATION"
        Exit Sub
    End If
        
    'On active la séquence de gestion d'erreurs
    On Error GoTo Gestion_Err
    
    'On affecte l'objet local classeur
    Set oWB = Application.Workbooks.Open(sWBName)
    
    'On boucle sur toutes les feuilles du classeur
    For Each oSheet In oWB.Worksheets
        'On révèle toutes les lignes de la feuille
        oSheet.Rows.EntireRow.Hidden = False
        'On rend visible le contenu de toutes les lignes
        oSheet.Rows.EntireRow.AutoFit
        'On révèle toutes les colonnes de la feuille
        oSheet.Columns.EntireColumn.Hidden = False
        'On rend visible le contenu de toutes les colonnes
        oSheet.Columns.EntireColumn.AutoFit
   Next
    
    'Séquence de fin de traitement
    MsgBox "Les lignes et colonnes du classeur '" & sWBName & "' ont toutes été rélévées.", vbExclamation, "FIN D'OPERATION"
    
    On Error GoTo 0 'On annule le branchement vers de gestion d'erreur
    
    'On fait le ménage
    Set oSheet = Nothing
    Set oWB = Nothing
    Exit Sub
'Séquence de traitement des erreurs
Gestion_Err:
    Select Case Err
        Case Else
            MsgBox "La révélation de toutes les lignes et colonnes du classeur '" & sWBName & "' a rencontré l'erreur suivante :" & vbCrLf _
                    & Err & "-" & Err.Description, vbCritical, "IMPOSSIBLE DE POURSUIVRE LE TRAITEMENT"
    End Select
    
    'On fait le ménage
    On Error GoTo 0 'On annule le branchement vers de gestion d'erreur
    Set oSheet = Nothing
    Set oWB = Nothing
End Sub

Protéger/déprotéger les feuilles

Toutes feuilles d’un classeur sont protégées avec la première macro en leur affectant un mot de passe donné.

La seconde macro déprotège toutes les feuilles d’un classeur précédemment protégées par un mot de passe donné.

Les mots de passe peuvent être modifiés.

Macro de protection des feuilles d'un classeur

Sub ProtegeFeuilles()
'------------------------------------------------------------------------------------------------
'Procédure de protection des feuilles d'un classeur avec le mot de passe générique cPWD
'-l'utilisateur doit choisir le classeur à protéger
'-Le classeur est fermé a l'issue du traitement
'Auteur : Excellons.org
'Date : juin 2019
'------------------------------------------------------------------------------------------------
Const cPWD = "0987654"      'Password à adapter
Const cFilter = "Classeur EXCEL(*.xls*), *.xls*"
    
    Dim oWB As Workbook
    Dim oSheet As Worksheet
    Dim sWBName As Variant
    Dim sExtension As String
    Dim sDecoupe() As String
    
    'On demande à l'utilisateur d'indiquer le nom du classeur à protéger
    sWBName = Application.GetOpenFilename(cFilter, 1, "Choisissez le classeur à protéger", , False)
      
    'On s'assure que l'utilisateur n'a pas quitté la boite de dialogue sans indiquer de fichier. Sinon on met fin à l'opération.
    If sWBName = False Then
        MsgBox "Aucun classeur indiqué !" & vbCrLf & vbCrLf & "Opération sans objet.", vbCritical, "FIN D'OPERATION"
        Exit Sub
    End If
    
    'On s'assure que l'utilisateur a indiqué un classeur EXCEL
    sDecoupe() = Split(sWBName, ".") 'On découpe le nom en 2 pour récupérer son extension
    'On s'assure que le fichier choisi possède une extension de classeur EXCEL. Sinon on met fin à l'opération.
    If Left(sDecoupe(1), 3) <> "xls" Then
        MsgBox "Le fichier indiqué ne semble pas être un classeur EXCEL !" & vbCrLf & vbCrLf & "Opération impossible.", vbCritical, "FIN D'OPERATION"
        Exit Sub
    End If
        
    'On active la séquence de gestion d'erreurs
    On Error GoTo Gestion_Err
    
    'On affecte l'objet local classeur
    Set oWB = Application.Workbooks.Open(sWBName)
    
    'On boucle sur toutes les feuilles du classeur
    For Each oSheet In oWB.Worksheets
        'On protège la feuille avec le mot de passe
        oSheet.Protect cPWD
    Next
    
    'Séquence de fin de traitement
    oWB.Close True   'On ferme le classeur protégé
    MsgBox "Les feuilles du classeur '" & sWBName & "' sont protégées avec le mot de passe générique.", vbExclamation, "FIN D'OPERATION"
    
    On Error GoTo 0 'On annule le branchement vers de gestion d'erreur
    
    'On fait le ménage
    Set oSheet = Nothing
    Set oWB = Nothing
    Exit Sub
'Séquence de traitement des erreurs
Gestion_Err:
    Select Case Err
        Case Else
            MsgBox "L'opération de protection du classeur '" & sWBName & "' a rencontré l'erreur suivante :" & vbCrLf _
                    & Err & "-" & Err.Description, vbCritical, "IMPOSSIBLE DE PROTEGER LE CLASSEUR"
    End Select
    oWB.Close False
    'On fait le ménage
    On Error GoTo 0 'On annule le branchement vers de gestion d'erreur
    Set oSheet = Nothing
    Set oWB = Nothing
End Sub

Macro de déprotection des feuilles d'un classeur

Sub DeprotegeFeuilles()
'------------------------------------------------------------------------------------------------
'Procédure de déprotection des feuilles d'un classeur protégé avec le mot de passe générique
'-l'utilisateur doit choisir le classeur à déprotéger
'-Le classeur est fermé a l'issue du traitement
'Auteur : Excellons.org
'Date : juin 2019
'------------------------------------------------------------------------------------------------
Const cPWD = "0987654"      'Password à adapter
Const cFilter = "Classeur EXCEL(*.xls*), *.xls*"
    
    Dim oWB As Workbook
    Dim oSheet As Worksheet
    Dim sWBName As Variant
    Dim sExtension As String
    Dim sDecoupe() As String
    
    'On demande à l'utilisateur d'indiquer le nom du classeur à déprotéger
    sWBName = Application.GetOpenFilename(cFilter, 1, "Choisissez le classeur à déprotéger", , False)
      
    'On s'assure que l'utilisateur n'a pas quitté la boite de dialogue sans indiquer de fichier. Sinon on met fin à l'opération.
    If sWBName = False Then
        MsgBox "Aucun classeur indiqué !" & vbCrLf & vbCrLf & "Opération sans objet.", vbCritical, "FIN D'OPERATION"
        Exit Sub
    End If
    
    'On s'assure que l'utilisateur a indiqué un classeur EXCEL
    sDecoupe() = Split(sWBName, ".") 'On découpe le nom en 2 pour récupérer son extension
    'On s'assure que le fichier choisi possède une extension de classeur EXCEL. Sinon on met fin à l'opération.
    If Left(sDecoupe(1), 3) <> "xls" Then
        MsgBox "Le fichier indiqué ne semble pas être un classeur EXCEL !" & vbCrLf & vbCrLf & "Opération impossible.", vbCritical, "FIN D'OPERATION"
        Exit Sub
    End If
        
    'On active la séquence de gestion d'erreurs
    On Error GoTo Gestion_Err
    
    'On affecte l'objet local classeur
    Set oWB = Application.Workbooks.Open(sWBName)
    
    'On boucle sur toutes les feuilles du classeur
    For Each oSheet In oWB.Worksheets
         'On déprotège la feuille
        oSheet.Unprotect cPWD
    Next
    
    'Séquence de fin de traitement
    oWB.Close True   'On ferme le classeur
    MsgBox "Les feuilles du classeur '" & sWBName & "' sont déprotégées du mot de passe générique.", vbExclamation, "FIN D'OPERATION"
    
    On Error GoTo 0 'On annule le branchement vers de gestion d'erreur
    
    'On fait le ménage
    Set oSheet = Nothing
    Set oWB = Nothing
    Exit Sub
'Séquence de traitement des erreurs
Gestion_Err:
    Select Case Err
        Case Else
            MsgBox "L'opération de déprotection du classeur '" & sWBName & "' a rencontré l'erreur suivante :" & vbCrLf _
                    & Err & "-" & Err.Description, vbCritical, "IMPOSSIBLE DE DEPROTEGER LE CLASSEUR"
    End Select
    
    'On fait le ménage
    On Error GoTo 0 'On annule le branchement vers de gestion d'erreur
    Set oSheet = Nothing
    Set oWB = Nothing
End Sub